db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject 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...
Date Tue, 16 Jun 2009 04:31:09 GMT
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



Mime
View raw message