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 =
c.referenceid
...this program produces the following graph...
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<planTrace>
<top type="ProjectRestrictResultSet">
<source type="HashJoinResultSet">
<leftResultSet tableName="SYSCOLUMNS" type="BulkTableScanResultSet"/>
<rightResultSet tableName="SYSTABLES" type="HashScanResultSet"/>
</source>
</top>
</planTrace>
From this plan shape, we can say the following about the processing flow:
1) Query evaluation starts with reading SYSCOLUMNS via a
BulkTableScanResultSet.
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,
-Rick
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;
import
org.apache.derbyTesting.functionTests.tests.lang.NewOptimizerOverridesTest;
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 =
TransformerFactory.newInstance();
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" );
transformer.setOutputProperty(
"{http://xml.apache.org/xslt}indent-amount";, "4" );
transformer.transform( source, result );
}
}
