db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Select Query Call Stack
Date Mon, 19 Aug 2013 13:13:45 GMT
On 8/18/13 12:50 PM, Madushanka Fonseka wrote:
> Hi.
> I would like to know to execute a query like
> select e.id <http://e.id/>,d.name <http://d.name/> from employees e, 
> departments d
>                 where e.salary> 50000
> what would be the  call stack and how byte codes get 
> connected/called/generated along with the help f resultset n execution 
> factories ?
Hi Madushanka,

The user guides contain lots of useful information about how to produce 
a picture of the execution-time plan. Please see the section titled 
"Working with RunTimeStatistics" in the Tuning Guide.

At the end of this message I have included a program which shows how to 
get an abbreviated version of the RunTimeStatistics. This may help get 
you started. The program runs a query and produces the ResultSet graph 
which Derby runs at execution time. For the sample query...

     select * from sys.systables t, sys.syscolumns c where t.tableid = 

...this program produces the following graph...

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<top type="ProjectRestrictResultSet">
<source type="HashJoinResultSet">
<leftResultSet tableName="SYSCOLUMNS" type="BulkTableScanResultSet"/>
<rightResultSet tableName="SYSTABLES" type="HashScanResultSet"/>

 From this plan shape, we can say the following about the processing flow:

1) Query evaluation starts with reading SYSCOLUMNS via a 

2) Rows from that ResultSet flow up to its parent, a HashJoinResultSet. 
For each row coming from the left (SYSCOLUMNS) child, the 
HashJoinResultSet asks its right child for all matches.

3) The right child reads SYSTABLES via a HashScanResultSet. That means 
that at initialization time, all of the rows from SYSTABLES are read and 
a HashMap is built linking keys to full rows.

4) When asked to match a given key from an outer, driving row, the 
HashScanResultSet returns all rows which match that key.

5) Once the HashJoinResultSet has joined a row from its left and right 
children, it passes the joined row up to its parent, the top level 
ProjectRestrictResultSet. The ProjectRestrictResultSet performs 
additional filtering of the results.

Once you know what the plan shape is, you can dig into the specific 
ResultSets to see how they operate.

Note that not all expressions are compiled into Java byte code. Simple 
expressions like "e.salary> 50000" are pushed all the way down into the 
storage layer and are evaluated there. These simple expressions are 
evaluated as arrays of org.apache.derby.iapi.store.access.Qualifier.

Hope this helps,

import java.sql.*;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;


public class w
     public  static  void    main( String... args ) throws Exception
         Connection  conn = DriverManager.getConnection( 
"jdbc:derby:memory:db;create=true" );
         ResultSet   rs = conn.prepareStatement
             ( "select * from sys.systables t, sys.syscolumns c where 
t.tableid = c.referenceid" ).executeQuery();
         Document    doc = NewOptimizerOverridesTest.getLastQueryPlan( 
conn, rs );

         printDocument( doc );

     private static  void    printDocument( Document doc ) throws Exception
         TransformerFactory transformerFactory = 
         Transformer transformer = transformerFactory.newTransformer();
         DOMSource source = new DOMSource( doc );
         StreamResult result = new StreamResult( System.out );

         // pretty-print
         transformer.setOutputProperty( OutputKeys.OMIT_XML_DECLARATION, 
"no" );
         transformer.setOutputProperty( OutputKeys.METHOD, "xml" );
         transformer.setOutputProperty( OutputKeys.INDENT, "yes" );
         transformer.setOutputProperty( OutputKeys.ENCODING, "UTF-8" );
"{http://xml.apache.org/xslt}indent-amount", "4" );

         transformer.transform( source, result );

View raw message