You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ma...@apache.org on 2009/06/16 06:31:09 UTC
svn commit: r785073 [1/2] - in /db/derby/code/branches/10.4/java:
engine/org/apache/derby/iapi/sql/compile/
engine/org/apache/derby/impl/sql/compile/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/te...
Author: mamta
Date: Tue Jun 16 04:31:08 2009
New Revision: 785073
URL: http://svn.apache.org/viewvc?rev=785073&view=rev
Log:
Merging changes for DERBY-3926 from trunk into 10.4.2.1 codeline. In addition to following merged changes I had to add couple methods into RuntimeStatisticsParser.java by hand because those methods did not exist in 10.4.2.1 codeline
The merged changes are as follows
This will add OrderByAndSortAvoidance test
svn merge -r 767583:767584 https://svn.apache.org/repos/asf/db/derby/code/trunk/
Tars Joris contributed ASF grant to this new test data and changed the test above based on that data
svn merge -r 769146:769147 https://svn.apache.org/repos/asf/db/derby/code/trunk/
There were non-ascii characters in the test. Knut fixed that
svn merge -r 777885:777886 https://svn.apache.org/repos/asf/db/derby/code/trunk/
This will make the actual code changes for DERBY-3926
svn merge -r 783167:783168 https://svn.apache.org/repos/asf/db/derby/code/trunk/
This will fix the javadoc
svn merge -r 783640:783641 https://svn.apache.org/repos/asf/db/derby/code/trunk/
Added:
db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java (contents, props changed)
- copied, changed from r767584, db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/OrderByAndSortAvoidance.java
Modified:
db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java
db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java
db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java
db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java
db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/wisc_setup.sql
db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/junit/RuntimeStatisticsParser.java
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/OptimizablePredicateList.java Tue Jun 16 04:31:08 2009
@@ -122,6 +122,23 @@
* hash table on a next are qualifiers.
*/
public void markAllPredicatesQualifiers();
+
+ /**
+ * Check into the predicate list if the passed column has an equijoin
+ * predicate on it.
+ *
+ * @param optTable
+ * @param columnNumber
+ * @param isNullOkay
+ * @return the position of the predicate in the list which corresponds to
+ * the equijoin. If no quijoin predicate found, then the return value
+ * will be -1
+ * @throws StandardException
+ */
+ public int hasEqualityPredicateOnOrderedColumn(Optimizable optTable,
+ int columnNumber,
+ boolean isNullOkay)
+ throws StandardException;
/**
* Is there an optimizable equality predicate on the specified column?
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RequiredRowOrdering.java Tue Jun 16 04:31:08 2009
@@ -24,6 +24,7 @@
import org.apache.derby.iapi.error.StandardException;
import org.apache.derby.iapi.util.JBitSet;
+import org.apache.derby.impl.sql.compile.PredicateList;
/**
* This interface provides a representation of the required ordering of rows
@@ -43,6 +44,11 @@
* given a RowOrdering.
*
* @param rowOrdering The order of rows in question
+ * @param optimizableList The current join order being considered by
+ * the optimizer. We need to look into this to determine if the outer
+ * optimizables are single row resultset if the order by column is
+ * on an inner optimizable and that inner optimizable is not a one
+ * row resultset. DERBY-3926
*
* @return SORT_REQUIRED if sorting is required,
* ELIMINATE_DUPS if no sorting is required but duplicates
@@ -52,7 +58,8 @@
*
* @exception StandardException Thrown on error
*/
- int sortRequired(RowOrdering rowOrdering) throws StandardException;
+ int sortRequired(RowOrdering rowOrdering, OptimizableList optimizableList)
+ throws StandardException;
/**
* Tell whether sorting is required for this RequiredRowOrdering,
@@ -63,6 +70,11 @@
*
* @param rowOrdering The order of rows in the partial join order
* @param tableMap A bit map of the tables in the partial join order
+ * @param optimizableList The current join order being considered by
+ * the optimizer. We need to look into this to determine if the outer
+ * optimizables are single row resultset if the order by column is
+ * on an inner optimizable and that inner optimizable is not a one
+ * row resultset. DERBY-3926
*
* @return SORT_REQUIRED if sorting is required,
* ELIMINATE_DUPS if no sorting is required by duplicates
@@ -72,7 +84,8 @@
*
* @exception StandardException Thrown on error
*/
- int sortRequired(RowOrdering rowOrdering, JBitSet tableMap)
+ int sortRequired(RowOrdering rowOrdering, JBitSet tableMap,
+ OptimizableList optimizableList)
throws StandardException;
/**
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/iapi/sql/compile/RowOrdering.java Tue Jun 16 04:31:08 2009
@@ -130,13 +130,27 @@
/**
* Tell this RowOrdering that it is always ordered on the given column
* of the given optimizable. This is useful when a column in the
- * optimizable has an equals comparison with a constant expression.
- * This is reset when the optimizable is removed from this RowOrdering.
+ * optimizable has an equals comparison with a constant expression or
+ * it is involved in a equijoin with an optimizable which is always
+ * ordered on the column on which the equijoin is happening. This is
+ * reset when the optimizable is removed from this RowOrdering.
*
* @param optimizable The table in question
* @param columnNumber The number of the column in question.
*/
void columnAlwaysOrdered(Optimizable optimizable, int columnNumber);
+
+ /**
+ * Return true if the column is always ordered. That will be true if the
+ * column has a constant comparison predicate on it or it is involved in
+ * a equijoin with an optimizable which is always ordered on the column
+ * on which the equijoin is happening.
+ *
+ * @param tableNumber The table in question
+ * @param columnNumber The number of the column in question.
+ * @return true means this column is always ordered
+ */
+ public boolean isColumnAlwaysOrdered(int tableNumber, int columnNumber);
/**
* Ask whether the given table is always ordered.
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Tue Jun 16 04:31:08 2009
@@ -461,6 +461,13 @@
for (int i = 0; i < baseColumnPositions.length; i++)
{
+ //Check if the order by column has equijoin on another
+ //column which is already identified as an ordered column
+ if (doesOrderByColumnHaveEquiJoin(
+ irg, predList, rowOrdering))
+ rowOrdering.columnAlwaysOrdered(this,
+ baseColumnPositions[i]);
+
/*
** Don't add the column to the ordering if it's already
** an ordered column. This can happen in the following
@@ -4358,6 +4365,86 @@
return true;
}
+ //Check if the columns in the index have an equijoin on them
+ //with other already ordered columns from the other optimizables. This
+ //is done by going through the columns in the index and checking the
+ //predicate list for equijoins on the index columns.
+ private boolean doesOrderByColumnHaveEquiJoin(IndexRowGenerator irg,
+ OptimizablePredicateList predList, RowOrdering ro)
+ throws StandardException
+ {
+ if (predList == null)
+ {
+ return false;
+ }
+
+ // is this a unique index.
+ if (! irg.isUnique())
+ {
+ return false;
+ }
+
+ PredicateList restrictionList = (PredicateList) predList;
+
+ int[] baseColumnPositions = irg.baseColumnPositions();
+
+ for (int index = 0; index < baseColumnPositions.length; index++)
+ {
+ // get the column number at this position
+ int curCol = baseColumnPositions[index];
+
+ //Check if this column from the index has an equi join predicate
+ //on them.
+ int j = restrictionList.hasEqualityPredicateOnOrderedColumn(
+ this, curCol, true);
+ if (j == -1)
+ return false;
+
+ //We have found a predicate which has an equi join involving the
+ //index column. Now ensure that the equi join is with a column
+ //which is already identified as always ordered.
+ Predicate predicate;
+ predicate = (Predicate) restrictionList.elementAt(j);
+ ValueNode vn = predicate.getAndNode().getLeftOperand();
+ ColumnReference cr;
+ if (vn instanceof BinaryRelationalOperatorNode)
+ {
+ BinaryRelationalOperatorNode bon =
+ (BinaryRelationalOperatorNode) vn;
+ cr = null;
+ if (bon.columnOnOneSide(this) ==
+ BinaryRelationalOperatorNode.LEFT)
+ {
+ //If the index column is on left side, then look for the
+ //operand on the other side to see if it is of type
+ //ColumnReference. If it is, then check if that column
+ //is identified as always ordered
+ if (bon.getRightOperand() instanceof ColumnReference)
+ cr = (ColumnReference)bon.getRightOperand();
+ } else
+ {
+ //If the index column is on right side, then look for the
+ //operand on the other side to see if it is of type
+ //ColumnReference. If it is, then check if that column
+ //is identified as always ordered
+ if (bon.getLeftOperand() instanceof ColumnReference)
+ cr = (ColumnReference)bon.getLeftOperand();
+ }
+ if (cr!=null)
+ {
+ //We have found that the index column is involved in an
+ //equijoin with another column. Now check if that other
+ //column is always ordered
+ if (ro.orderedOnColumn(1, cr.getTableNumber(),
+ cr.getColumnNumber()))
+ return true;
+ }
+ }
+ }
+
+ return false;
+ }
+
/**
* Is this a one-row result set with the given conglomerate descriptor?
*/
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OptimizerImpl.java Tue Jun 16 04:31:08 2009
@@ -1789,8 +1789,9 @@
if (requiredRowOrdering != null &&
curOpt.considerSortAvoidancePath())
{
- if (requiredRowOrdering.sortRequired(bestRowOrdering) ==
- RequiredRowOrdering.NOTHING_REQUIRED)
+ if (requiredRowOrdering.sortRequired(
+ bestRowOrdering, optimizableList) ==
+ RequiredRowOrdering.NOTHING_REQUIRED)
{
if (optimizerTrace)
{
@@ -2246,8 +2247,9 @@
** path avoid a sort?
*/
if (requiredRowOrdering.sortRequired(currentRowOrdering,
- assignedTableMap)
- == RequiredRowOrdering.NOTHING_REQUIRED)
+ assignedTableMap,
+ optimizableList)
+ ==RequiredRowOrdering.NOTHING_REQUIRED)
{
ap = optimizable.getBestSortAvoidancePath();
bestCostEstimate = ap.getCostEstimate();
@@ -2377,7 +2379,8 @@
** path avoid a sort?
*/
if (requiredRowOrdering.sortRequired(currentRowOrdering,
- assignedTableMap)
+ assignedTableMap,
+ optimizableList)
== RequiredRowOrdering.NOTHING_REQUIRED)
{
ap = optimizable.getBestSortAvoidancePath();
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OrderByList.java Tue Jun 16 04:31:08 2009
@@ -22,7 +22,9 @@
package org.apache.derby.impl.sql.compile;
import org.apache.derby.iapi.sql.compile.CompilerContext;
+import org.apache.derby.iapi.sql.compile.Optimizable;
import org.apache.derby.iapi.sql.compile.CostEstimate;
+import org.apache.derby.iapi.sql.compile.OptimizableList;
import org.apache.derby.iapi.sql.compile.RequiredRowOrdering;
import org.apache.derby.iapi.sql.compile.RowOrdering;
import org.apache.derby.iapi.sql.compile.C_NodeTypes;
@@ -437,9 +439,10 @@
*
* @exception StandardException Thrown on error
*/
- public int sortRequired(RowOrdering rowOrdering) throws StandardException
+ public int sortRequired(RowOrdering rowOrdering,
+ OptimizableList optimizableList) throws StandardException
{
- return sortRequired(rowOrdering, (JBitSet) null);
+ return sortRequired(rowOrdering, (JBitSet) null, optimizableList);
}
/**
@@ -447,7 +450,9 @@
*
* @exception StandardException Thrown on error
*/
- public int sortRequired(RowOrdering rowOrdering, JBitSet tableMap)
+ public int sortRequired(RowOrdering rowOrdering,
+ JBitSet tableMap,
+ OptimizableList optimizableList)
throws StandardException
{
/*
@@ -531,7 +536,92 @@
return RequiredRowOrdering.NOTHING_REQUIRED;
}
}
+ /*
+ * Does this order by column belong to the outermost optimizable in
+ * the current join order?
+ *
+ * If yes, then we do not need worry about the ordering of the rows
+ * feeding into it. Because the order by column is associated with
+ * the outermost optimizable, optimizer will not have to deal with
+ * the order of any rows coming in from the previous optimizables.
+ *
+ * But if the current order by column belongs to an inner
+ * optimizable in the join order, then go through the following
+ * if condition logic.
+ */
+ /* If the following boolean is true, then it means that the join
+ * order being considered has more than one table
+ */
+ boolean moreThanOneTableInJoinOrder = tableMap!=null?
+ (!tableMap.hasSingleBitSet()) : false;
+ if (moreThanOneTableInJoinOrder)
+ {
+ /*
+ * First check if the order by column has a constant comparison
+ * predicate on it or it belongs to an optimizable which is
+ * always ordered(that means it is a single row table) or the
+ * column is involved in an equijoin with an optimizable which
+ * is always ordered on the column on which the equijoin is
+ * happening. If yes, then we know that the rows will always be
+ * sorted and hence we do not need to worry if (any) prior
+ * optimizables in join order are one-row resultsets or not.
+ */
+ if ((!rowOrdering.alwaysOrdered(cr.getTableNumber())) &&
+ (!rowOrdering.isColumnAlwaysOrdered(
+ cr.getTableNumber(), cr.getColumnNumber())))
+ {
+ /*
+ * The current order by column is not always ordered which
+ * means that the rows from it will not necessarily be in
+ * the sorted order on that column. Because of this, we
+ * need to make sure that the outer optimizables (outer to
+ * the order by columns's optimizable) in the join order
+ * are all one row optimizables, meaning that they can at
+ * the most return only one row. If they return more than
+ * one row, then it will require multiple scans of the
+ * order by column's optimizable and the rows returned
+ * from those multiple scans may not be ordered correctly.
+ */
+ for (int i=0; i < optimizableList.size(); i++)
+ {
+ //Get one outer optimizable at a time from the join
+ //order
+ Optimizable considerOptimizable =
+ optimizableList.getOptimizable(i);
+ //If we have come across the optimizable for the order
+ //by column in the join order, then we do not need to
+ //look at the inner optimizables in the join order. As
+ //long as the outer optimizables are one row resultset,
+ //we are fine to consider sort avoidance.
+ if (considerOptimizable.getTableNumber() ==
+ cr.getTableNumber())
+ break;
+ /*
+ * The following if condition is checking if the
+ * outer optimizable to the order by column's
+ * optimizable is one row resultset or not.
+ *
+ * If the outer optimizable is one row resultset,
+ * then move on to the next optimizable in the join
+ * order and do the same check on that optimizable.
+ * Continue this until we are done checking that all
+ * the outer optimizables in the join order are single
+ * row resultsets. If we run into an outer optimizable
+ * which is not one row resultset, then we can not
+ * consider sort avoidance for the query.
+ */
+ if (rowOrdering.alwaysOrdered(
+ considerOptimizable.getTableNumber()))
+ continue;
+ else
+ //This outer optimizable can return more than
+ //one row. Because of this, we can't avoid the
+ //sorting for this query.
+ return RequiredRowOrdering.SORT_REQUIRED;
+ }
+ }
+ }
if ( ! rowOrdering.alwaysOrdered(cr.getTableNumber()))
{
/*
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/PredicateList.java Tue Jun 16 04:31:08 2009
@@ -326,6 +326,45 @@
}
/**
+ * @see OptimizablePredicateList#hasEqualityPredicateOnOrderedColumn
+ *
+ * @exception StandardException Thrown on error
+ */
+ public int hasEqualityPredicateOnOrderedColumn(Optimizable optTable,
+ int columnNumber,
+ boolean isNullOkay)
+ throws StandardException
+ {
+ ValueNode opNode = null;
+ int size = size();
+ for (int index = 0; index < size; index++)
+ {
+ AndNode andNode;
+ Predicate predicate;
+ predicate = (Predicate) elementAt(index);
+ //We are not looking at constant comparison predicate.
+ if (predicate.getReferencedMap().hasSingleBitSet())
+ {
+ continue;
+ }
+
+ andNode = (AndNode) predicate.getAndNode();
+
+ // skip non-equality predicates
+ opNode = andNode.getLeftOperand();
+
+ if (opNode.optimizableEqualityNode(optTable,
+ columnNumber,
+ isNullOkay))
+ {
+ return index;
+ }
+ }
+
+ return -1;
+ }
+
+ /**
* @see OptimizablePredicateList#hasOptimizableEqualityPredicate
*
* @exception StandardException Thrown on error
Modified: db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java (original)
+++ db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/RowOrderingImpl.java Tue Jun 16 04:31:08 2009
@@ -39,7 +39,9 @@
** This ColumnOrdering represents the columns that can be considered
** ordered no matter what. For example, columns that are compared to
** constants with = are always ordered. Also, all columns in a one-row
- ** result set are ordered.
+ ** result set are ordered. Another instance of always ordered is when
+ ** the column is involved in an equijoin with an optimizable which is
+ ** always ordered on the column on which the equijoin is happening.
*/
ColumnOrdering columnsAlwaysOrdered;
@@ -60,6 +62,11 @@
columnsAlwaysOrdered = new ColumnOrdering(RowOrdering.DONTCARE);
alwaysOrderedOptimizables = new Vector();
}
+
+ /** @see RowOrdering#isColumnAlwaysOrdered */
+ public boolean isColumnAlwaysOrdered(int tableNumber, int columnNumber){
+ return (columnsAlwaysOrdered.contains(tableNumber, columnNumber));
+ }
/**
* @see RowOrdering#orderedOnColumn
Modified: db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out?rev=785073&r1=785072&r2=785073&view=diff
==============================================================================
--- db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out (original)
+++ db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/wisconsin.out Tue Jun 16 04:31:08 2009
@@ -21828,97 +21828,112 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
-Hash Exists Join ResultSet:
+Project-Restrict ResultSet (6):
<filtered number of opens>
-<filtered rows seen from the left>
-<filtered rows seen from the right>
+<filtered rows seen>
Rows filtered = 0
-<filtered rows returned>
+restriction = false
+projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
-Left result set:
- Index Row to Base Row ResultSet for TENKTUP1:
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Hash Exists Join ResultSet:
<filtered number of opens>
- <filtered rows seen>
- Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ <filtered rows seen from the left>
+ <filtered rows seen from the right>
+ Rows filtered = 0
+ <filtered rows returned>
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ Left result set:
+ Index Row to Base Row ResultSet for TENKTUP2:
<filtered number of opens>
<filtered rows seen>
- Rows filtered = 0
- Fetch Size = 1
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=2
- Number of deleted rows visited=0
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- Scan type=btree
- Tree height=2
- start position:
+ Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ <filtered number of opens>
+ <filtered rows seen>
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
None
- stop position:
+ stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
- qualifiers:
+ qualifiers:
None
-Right result set:
- Index Row to Base Row ResultSet for TENKTUP2:
- <filtered number of opens>
- <filtered rows seen>
- Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking:
+ Right result set:
+ Index Row to Base Row ResultSet for TENKTUP1:
<filtered number of opens>
- Hash table size = 2500
- Hash key is column number 0
<filtered rows seen>
- Rows filtered = 0
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=2
- Number of deleted rows visited=0
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- Scan type=btree
- Tree height=2
- start position:
+ Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking:
+ <filtered number of opens>
+ Hash table size = 2500
+ Hash key is column number 0
+ <filtered rows seen>
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
None
- stop position:
+ stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
- scan qualifiers:
+ scan qualifiers:
None
- next qualifiers:
+ next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> commit;
-ij> -- 25% of rows from joining table
+ij> -- force TENKTUP1 as the outermost join table to make sure
+-- that no sorting is necessary. DERBY-3926
get cursor c as
- 'select * from TENKTUP1, TENKTUP2
+ 'select * from --DERBY-PROPERTIES joinOrder=FIXED
+ TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1';
@@ -21929,7 +21944,8 @@
Statement Name:
C
Statement Text:
- select * from TENKTUP1, TENKTUP2
+ select * from --DERBY-PROPERTIES joinOrder=FIXED
+ TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1
@@ -21944,6 +21960,7 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
+User supplied optimizer overrides for join are { joinOrder=FIXED }
Hash Exists Join ResultSet:
<filtered number of opens>
<filtered rows seen from the left>
@@ -22032,11 +22049,13 @@
Unknown return value: false
Negate comparison result: false
ij> commit;
-ij> -- 10% of rows from joining table
+ij> -- This time, force TENKTUP2 as the outermost join table to make sure
+-- that still no sorting is necessary. DERBY-3926
get cursor c as
- 'select * from TENKTUP1, TENKTUP2
+ 'select * from --DERBY-PROPERTIES joinOrder=FIXED
+ TENKTUP2, TENKTUP1
where TENKTUP1.unique1 = TENKTUP2.unique1
- and TENKTUP2.unique1 < 1000
+ and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1';
ij> close c;
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
@@ -22045,9 +22064,10 @@
Statement Name:
C
Statement Text:
- select * from TENKTUP1, TENKTUP2
+ select * from --DERBY-PROPERTIES joinOrder=FIXED
+ TENKTUP2, TENKTUP1
where TENKTUP1.unique1 = TENKTUP2.unique1
- and TENKTUP2.unique1 < 1000
+ and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1
Parse Time: 0
Bind Time: 0
@@ -22060,6 +22080,7 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
+User supplied optimizer overrides for join are { joinOrder=FIXED }
Hash Exists Join ResultSet:
<filtered number of opens>
<filtered rows seen from the left>
@@ -22071,7 +22092,7 @@
next time (milliseconds) = 0
close time (milliseconds) = 0
Left result set:
- Index Row to Base Row ResultSet for TENKTUP1:
+ Index Row to Base Row ResultSet for TENKTUP2:
<filtered number of opens>
<filtered rows seen>
Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
@@ -22079,7 +22100,7 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
<filtered number of opens>
<filtered rows seen>
Rows filtered = 0
@@ -22106,7 +22127,7 @@
qualifiers:
None
Right result set:
- Index Row to Base Row ResultSet for TENKTUP2:
+ Index Row to Base Row ResultSet for TENKTUP1:
<filtered number of opens>
<filtered rows seen>
Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
@@ -22114,9 +22135,9 @@
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking:
+ Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking:
<filtered number of opens>
- Hash table size = 1000
+ Hash table size = 2500
Hash key is column number 0
<filtered rows seen>
Rows filtered = 0
@@ -22148,11 +22169,11 @@
Unknown return value: false
Negate comparison result: false
ij> commit;
-ij> -- 5% of rows from joining table
+ij> -- 25% of rows from joining table
get cursor c as
'select * from TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
- and TENKTUP2.unique1 < 500
+ and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1';
ij> close c;
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
@@ -22163,7 +22184,7 @@
Statement Text:
select * from TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
- and TENKTUP2.unique1 < 500
+ and TENKTUP2.unique1 < 2500
order by TENKTUP1.unique1
Parse Time: 0
Bind Time: 0
@@ -22176,99 +22197,112 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
-Hash Exists Join ResultSet:
+Project-Restrict ResultSet (6):
<filtered number of opens>
-<filtered rows seen from the left>
-<filtered rows seen from the right>
+<filtered rows seen>
Rows filtered = 0
-<filtered rows returned>
+restriction = false
+projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
-Left result set:
- Index Row to Base Row ResultSet for TENKTUP1:
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Hash Exists Join ResultSet:
<filtered number of opens>
- <filtered rows seen>
- Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ <filtered rows seen from the left>
+ <filtered rows seen from the right>
+ Rows filtered = 0
+ <filtered rows returned>
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ Left result set:
+ Index Row to Base Row ResultSet for TENKTUP2:
<filtered number of opens>
<filtered rows seen>
- Rows filtered = 0
- Fetch Size = 1
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=2
- Number of deleted rows visited=0
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- Scan type=btree
- Tree height=2
- start position:
+ Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ <filtered number of opens>
+ <filtered rows seen>
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
None
- stop position:
+ stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
- qualifiers:
+ qualifiers:
None
-Right result set:
- Index Row to Base Row ResultSet for TENKTUP2:
- <filtered number of opens>
- <filtered rows seen>
- Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking:
+ Right result set:
+ Index Row to Base Row ResultSet for TENKTUP1:
<filtered number of opens>
- Hash table size = 500
- Hash key is column number 0
<filtered rows seen>
- Rows filtered = 0
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=2
- Number of deleted rows visited=0
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- Scan type=btree
- Tree height=2
- start position:
+ Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking:
+ <filtered number of opens>
+ Hash table size = 2500
+ Hash key is column number 0
+ <filtered rows seen>
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
None
- stop position:
+ stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
- scan qualifiers:
+ scan qualifiers:
None
- next qualifiers:
+ next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
ij> commit;
-ij> -- 1% of rows from joining table
+ij> -- 10% of rows from joining table
get cursor c as
'select * from TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
- and TENKTUP2.unique1 < 100
+ and TENKTUP2.unique1 < 1000
order by TENKTUP1.unique1';
ij> close c;
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
@@ -22279,7 +22313,7 @@
Statement Text:
select * from TENKTUP1, TENKTUP2
where TENKTUP1.unique1 = TENKTUP2.unique1
- and TENKTUP2.unique1 < 100
+ and TENKTUP2.unique1 < 1000
order by TENKTUP1.unique1
Parse Time: 0
Bind Time: 0
@@ -22292,88 +22326,359 @@
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
-Hash Exists Join ResultSet:
+Project-Restrict ResultSet (6):
<filtered number of opens>
-<filtered rows seen from the left>
-<filtered rows seen from the right>
+<filtered rows seen>
Rows filtered = 0
-<filtered rows returned>
+restriction = false
+projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
-Left result set:
- Index Row to Base Row ResultSet for TENKTUP1:
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Hash Exists Join ResultSet:
<filtered number of opens>
- <filtered rows seen>
- Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
- constructor time (milliseconds) = 0
+ <filtered rows seen from the left>
+ <filtered rows seen from the right>
+ Rows filtered = 0
+ <filtered rows returned>
+ constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Index Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ Left result set:
+ Index Row to Base Row ResultSet for TENKTUP2:
<filtered number of opens>
<filtered rows seen>
- Rows filtered = 0
- Fetch Size = 1
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=2
- Number of deleted rows visited=0
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- Scan type=btree
- Tree height=2
- start position:
+ Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ <filtered number of opens>
+ <filtered rows seen>
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
None
- stop position:
+ stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
- qualifiers:
+ qualifiers:
None
-Right result set:
- Index Row to Base Row ResultSet for TENKTUP2:
+ Right result set:
+ Index Row to Base Row ResultSet for TENKTUP1:
+ <filtered number of opens>
+ <filtered rows seen>
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking:
+ <filtered number of opens>
+ Hash table size = 1000
+ Hash key is column number 0
+ <filtered rows seen>
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
+ None
+ stop position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+0
+ scan qualifiers:
+None
+ next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+ij> commit;
+ij> -- 5% of rows from joining table
+get cursor c as
+ 'select * from TENKTUP1, TENKTUP2
+ where TENKTUP1.unique1 = TENKTUP2.unique1
+ and TENKTUP2.unique1 < 500
+ order by TENKTUP1.unique1';
+ij> close c;
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------
+Statement Name:
+ C
+Statement Text:
+ select * from TENKTUP1, TENKTUP2
+ where TENKTUP1.unique1 = TENKTUP2.unique1
+ and TENKTUP2.unique1 < 500
+ order by TENKTUP1.unique1
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Project-Restrict ResultSet (6):
+<filtered number of opens>
+<filtered rows seen>
+Rows filtered = 0
+restriction = false
+projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Hash Exists Join ResultSet:
<filtered number of opens>
- <filtered rows seen>
- Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ <filtered rows seen from the left>
+ <filtered rows seen from the right>
+ Rows filtered = 0
+ <filtered rows returned>
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- Hash Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking:
+ Left result set:
+ Index Row to Base Row ResultSet for TENKTUP2:
<filtered number of opens>
- Hash table size = 100
- Hash key is column number 0
<filtered rows seen>
- Rows filtered = 0
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
- scan information:
- Bit set of columns fetched=All
- Number of columns fetched=2
- Number of deleted rows visited=0
- <filtered number of pages visited>
- <filtered number of rows qualified>
- <filtered number of rows visited>
- Scan type=btree
- Tree height=2
- start position:
+ Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ <filtered number of opens>
+ <filtered rows seen>
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
None
- stop position:
+ stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
- scan qualifiers:
+ qualifiers:
None
- next qualifiers:
+ Right result set:
+ Index Row to Base Row ResultSet for TENKTUP1:
+ <filtered number of opens>
+ <filtered rows seen>
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking:
+ <filtered number of opens>
+ Hash table size = 500
+ Hash key is column number 0
+ <filtered rows seen>
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
+ None
+ stop position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+0
+ scan qualifiers:
+None
+ next qualifiers:
+Column[0][0] Id: 0
+Operator: =
+Ordered nulls: false
+Unknown return value: false
+Negate comparison result: false
+ij> commit;
+ij> -- 1% of rows from joining table
+get cursor c as
+ 'select * from TENKTUP1, TENKTUP2
+ where TENKTUP1.unique1 = TENKTUP2.unique1
+ and TENKTUP2.unique1 < 100
+ order by TENKTUP1.unique1';
+ij> close c;
+ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
+1
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------
+Statement Name:
+ C
+Statement Text:
+ select * from TENKTUP1, TENKTUP2
+ where TENKTUP1.unique1 = TENKTUP2.unique1
+ and TENKTUP2.unique1 < 100
+ order by TENKTUP1.unique1
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Project-Restrict ResultSet (6):
+<filtered number of opens>
+<filtered rows seen>
+Rows filtered = 0
+restriction = false
+projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Hash Exists Join ResultSet:
+ <filtered number of opens>
+ <filtered rows seen from the left>
+ <filtered rows seen from the right>
+ Rows filtered = 0
+ <filtered rows returned>
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ Left result set:
+ Index Row to Base Row ResultSet for TENKTUP2:
+ <filtered number of opens>
+ <filtered rows seen>
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ Index Scan ResultSet for TENKTUP2 using index TK2UNIQUE1 at serializable isolation level using share row locking chosen by the optimizer
+ <filtered number of opens>
+ <filtered rows seen>
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
+ None
+ stop position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+0
+ qualifiers:
+None
+ Right result set:
+ Index Row to Base Row ResultSet for TENKTUP1:
+ <filtered number of opens>
+ <filtered rows seen>
+ Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15}
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ Hash Scan ResultSet for TENKTUP1 using index TK1UNIQUE1 at serializable isolation level using share row locking:
+ <filtered number of opens>
+ Hash table size = 100
+ Hash key is column number 0
+ <filtered rows seen>
+ Rows filtered = 0
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of deleted rows visited=0
+ <filtered number of pages visited>
+ <filtered number of rows qualified>
+ <filtered number of rows visited>
+ Scan type=btree
+ Tree height=2
+ start position:
+ None
+ stop position:
+ >= on first 1 column(s).
+ Ordered null semantics on the following columns:
+0
+ scan qualifiers:
+None
+ next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false