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 2013/04/02 06:28:23 UTC
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/
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