db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ma...@apache.org
Subject svn commit: r1463378 - in /db/derby/code/trunk/java: engine/org/apache/derby/impl/sql/compile/ testing/org/apache/derbyTesting/functionTests/master/ testing/org/apache/derbyTesting/functionTests/tests/lang/
Date Tue, 02 Apr 2013 04:28:23 GMT
Author: mamta
Date: Tue Apr  2 04:28:23 2013
New Revision: 1463378

URL: http://svn.apache.org/r1463378
Log:
DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows)

Changes for DERBY-3790 (Investigate if request for update statistics can be skipped for certain
kind of indexes, one instance may be unique indexes based on one column.) caused Derby to
use table scan rather than index scan since we do not generate stats for single column unique
index. As part of that change, we forgot to make code changes in optimizer to compensate for
missing stats for such indexes. As found in DERBY-6045 (in list multi-probe by primary key
not chosen on tables with >256 rows), even though we do not keep the statistics for single-column
unique indexes, we should improve the selectivity of such an index when the index is being
considered by the optimizer.

Making the relevant code changes now allows us to enable the tests in InListMultiProbeTest.java
since they will now run correctly by picking up index scan rather than table scan.

Additionally, one test in refActions1.sql does not have order by to it and there are only
3 rows in the table. Without the changes for DERBY-6045 we were using index scan for it but
now it uses table scan which is giving the rows in different order. I have added order by
to the query so plan selection does not change order of the rows. The reason behind table
scan might be that with only 3 rows in the table, it is cheaper to do table sccan rather than
index scan. 


Modified:
    db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql

Modified: db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=1463378&r1=1463377&r2=1463378&view=diff
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Tue
Apr  2 04:28:23 2013
@@ -1328,13 +1328,32 @@ public class FromBaseTable extends FromT
 				if (statCompositeSelectivity == -1.0d)
 					statCompositeSelectivity = 1.0d;
 			}
-
-			if (seenFirstColumn && statisticsForConglomerate &&
-				(startStopPredCount > 0))
-			{
-				statStartStopSelectivity = 
-					tableDescriptor.selectivityForConglomerate(cd, startStopPredCount);
-			}
+			
+            if (seenFirstColumn && (startStopPredCount > 0))
+            {
+                if (statisticsForConglomerate) {
+                    statStartStopSelectivity =
+                        tableDescriptor.selectivityForConglomerate(cd, 
+                            startStopPredCount);				
+                } else if (cd.isIndex())  {
+                    //DERBY-3790 (Investigate if request for update 
+                    // statistics can be skipped for certain kind of 
+                    // indexes, one instance may be unique indexes based 
+                    // on one column.) But as found in DERBY-6045 (in list
+                    // multi-probe by primary key not chosen on tables with
+                    // >256 rows), even though we do not keep the 
+                    // statistics for single-column unique indexes, we 
+                    // should improve the selectivity of such an index
+                    // when the index is being considered by the optimizer.
+                    IndexRowGenerator irg = cd.getIndexDescriptor();
+                    if (irg.isUnique() 
+                        && irg.numberOfOrderedColumns() == 1 
+                        && startStopPredCount == 1) {
+                            statStartStopSelectivity = 
+                                (double)(1/(double)baseRowCount());
+                    }
+                }
+            }
 
 			/*
 			** Factor the non-base-table predicates into the extra

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out?rev=1463378&r1=1463377&r2=1463378&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
Tue Apr  2 04:28:23 2013
@@ -8853,7 +8853,8 @@ union all
 ij> -- #BEGIN;
 select * from db2test.dept where dno in (select vdno from
   db2test.vempunion)
-  and dno in ('K55', 'K52');
+  and dno in ('K55', 'K52')
+  order by dno;
 C0         |DNO|DNAME     |DMGRNAME  
 -------------------------------------
 2          |K52|OFC       |ROBIN     

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java?rev=1463378&r1=1463377&r2=1463378&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
Tue Apr  2 04:28:23 2013
@@ -905,7 +905,7 @@ public class InListMultiProbeTest extend
     //  with >256 rows)
     // Following test shows that we should continue using index scan 
     //  even after adding extra rows to the table.
-    public void xtestDerby6045()
+    public void testDerby6045()
         throws SQLException
     {
         Statement s = createStatement();
@@ -962,7 +962,7 @@ public class InListMultiProbeTest extend
     //    doing table scan rather than index scan.
     //    If the unique key is removed from the table, the same query
     //    will start doing index scan.
-    public void xtestDerby6045InsertAllRowsAdditionalUniqueIndex() 
+    public void testDerby6045InsertAllRowsAdditionalUniqueIndex() 
             throws SQLException
     {
          Statement s = createStatement();

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql?rev=1463378&r1=1463377&r2=1463378&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
(original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
Tue Apr  2 04:28:23 2013
@@ -3143,7 +3143,8 @@ union all
 -- #BEGIN;
 select * from db2test.dept where dno in (select vdno from
   db2test.vempunion)
-  and dno in ('K55', 'K52');
+  and dno in ('K55', 'K52')
+  order by dno;
 -- #END;
 
 delete from db2test.dept where dno in (select vdno from



Mime
View raw message