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/05/08 05:59:38 UTC
svn commit: r1480153 - in /db/derby/code/branches/10.10: ./
java/engine/org/apache/derby/impl/sql/compile/
java/testing/org/apache/derbyTesting/functionTests/master/
java/testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: mamta
Date: Wed May 8 03:59:37 2013
New Revision: 1480153
URL: http://svn.apache.org/r1480153
Log:
DERBY-6045 (in list multi-probe by primary key not chosen on tables with >256 rows)
Backporting code changes and some additional tests to 10.10
Modified:
db/derby/code/branches/10.10/ (props changed)
db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
Propchange: db/derby/code/branches/10.10/
------------------------------------------------------------------------------
Merged /db/derby/code/trunk:r1463378,1465830,1466097,1479607
Modified: db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java?rev=1480153&r1=1480152&r2=1480153&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java (original)
+++ db/derby/code/branches/10.10/java/engine/org/apache/derby/impl/sql/compile/FromBaseTable.java Wed May 8 03:59:37 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/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out?rev=1480153&r1=1480152&r2=1480153&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out (original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/master/refActions1.out Wed May 8 03:59:37 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/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java?rev=1480153&r1=1480152&r2=1480153&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java (original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java Wed May 8 03:59:37 2013
@@ -89,6 +89,13 @@ public class InListMultiProbeTest extend
"var_type SMALLINT NOT NULL " +
")";
+ private final static String DERBY_6045_DATA_TABLE2 = "MT_GAF_TOP_LEVEL_TERM_COUNTS";
+ private final static String CREATE_DERBY_6045_DATA_TABLE2 =
+ "CREATE TABLE " + DERBY_6045_DATA_TABLE2 +
+ "(mt BIGINT NOT NULL, term BIGINT NOT NULL, "+
+ "term_index INTEGER NOT NULL, " +
+ "usage_count BIGINT NOT NULL )";
+
private final static String CREATE_DATA_TABLE =
"CREATE TABLE " + DATA_TABLE + " (" +
"ID BIGINT NOT NULL ," +
@@ -234,6 +241,89 @@ public class InListMultiProbeTest extend
"insert into d3603_c (c_id, d_id, t_o, t_i) values (21, 1, 1, 1)",
};
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for DELETE statement
+ // on a table with appropriate indexes. This happens with or without
+ // update statistics and with or without parameterized statement.
+ public void testDerby6045DeleteTest()
+ throws SQLException
+ {
+ Statement s = createStatement();
+ s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ dropTable(DERBY_6045_DATA_TABLE2);
+ // Create the test table, primary key and insert data
+ s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE2);
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE2 +
+ " ADD CONSTRAINT kb_mt_gaf_top_level_term_counts_pk" +
+ " PRIMARY KEY (mt, term, term_index)");
+ s.executeUpdate("CREATE INDEX " +
+ "kb_mt_gaf_top_level_term_counts_mt_index "+
+ "ON " + DERBY_6045_DATA_TABLE2+"(mt)");
+ s.executeUpdate("CREATE INDEX " +
+ "kb_mt_gaf_top_level_term_counts_term_index "+
+ "ON " + DERBY_6045_DATA_TABLE2+"(term)");
+ //insert requested number of rows in the table
+ PreparedStatement ps = s.getConnection().prepareStatement(
+ "insert into " + DERBY_6045_DATA_TABLE2 +
+ " VALUES (?, ?, ?, ?)");
+ int numberOfRows = 10000;
+ for (int i=1; i<=numberOfRows; i++) {
+ ps.setInt(1, i);
+ ps.setInt(2, i);
+ ps.setInt(3, i);
+ ps.setInt(4, i);
+ ps.executeUpdate();
+ }
+ //do not run update statisitcs and do not use PreparedStatement
+ deleteRows(false, false);
+ //do not run update statisitcs but do use PreparedStatement
+ deleteRows(false, true);
+ //run update statisitcs but do not use PreparedStatement
+ deleteRows(true, false);
+ //run update statisitcs and use PreparedStatement
+ deleteRows(true, true);
+
+ dropTable(DERBY_6045_DATA_TABLE2);
+ ps.close();
+ s.close();
+ }
+
+ void deleteRows(boolean runUpdateStatistics,
+ boolean useParameterMarkers)
+ throws SQLException
+ {
+ Statement s;
+ PreparedStatement ps;
+ RuntimeStatisticsParser rtsp;
+
+ s = createStatement();
+ if (runUpdateStatistics) {
+ s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'MT_GAF_TOP_LEVEL_TERM_COUNTS', null)");
+ }
+
+ if (useParameterMarkers) {
+ ps = prepareStatement("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = ?) ");
+ ps.setInt(1,1);
+ ps.execute();
+ } else {
+ s.execute("DELETE FROM MT_GAF_TOP_LEVEL_TERM_COUNTS WHERE (term = 2) ");
+ }
+ rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+ assertTrue(rtsp.usedIndexScan());
+
+ if (useParameterMarkers) {
+ ps = prepareStatement("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = ?) OR (mt = ?)");
+ ps.setInt(1,3);
+ ps.setInt(2,4);
+ ps.execute();
+ } else {
+ s.execute("DELETE FROM mt_gaf_top_level_term_counts WHERE (term = 5) OR (mt = 6)");
+ }
+ rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
+ assertTrue(rtsp.usedIndexScan());
+ }
+
/**
* Executes three different types of queries ("strategies") repeatedly
* with an increasing number of values in the IN list. Underneath we
@@ -903,9 +993,146 @@ public class InListMultiProbeTest extend
// DERBY-6045 (in list multi-probe by primary key not chosen on tables
// with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // after running the update statistics. This test DOES NOT use
+ // parameters in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithUpdateStatistics()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to run update statisitcs after
+ // inserting data in the table. 3rd param says do not use
+ // parameter in the SELECT sql to identify the rows in the
+ // where clause
+ helperDerby6045(10, true, false);
+ helperDerby6045(24, true, false);
+ helperDerby6045(10000, true, false);
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // even though update statistics was not run. This test DOES NOT use
+ // parameters in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithoutUpdateStatistics()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to DO Not run update statisitcs after
+ // inserting data in the table. 3rd param says do not use
+ // parameter in the SELECT sql to identify the rows in the
+ // WHERE clause
+ helperDerby6045(10, false, false);
+ helperDerby6045(24, false, false);
+ helperDerby6045(10000, false, false);
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // after running the update statistics. This test USES parameters
+ // in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithUpdateStatisticsAndParams()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below.
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to run update statisitcs after
+ // inserting data in the table. 3rd param says to use parameters
+ // in the SELECT sql to identify the rows in the where clause
+ helperDerby6045(10, true, true);
+ helperDerby6045(24, true, true);
+ helperDerby6045(10000, true, true);
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // with >256 rows)
+ // Following test shows that we use index scan for 10, 24 and 10K rows
+ // even though no update statistics were run. This test USES parameters
+ // in the WHERE clause of the SELECT sql.
+ public void testDerby6045WithoutUpdateStatisticsAndWithParams()
+ throws SQLException
+ {
+ //The reason behind running the test with 2 sets of small rows,
+ // namely 10 and 24 rows is in DERBY-6045, user found that we
+ // used index scan for 10 rows but switched to table scan for
+ // 24 rows. 10000 rows case used index scan. This test shows
+ // that after fixing DERBY-6045, we use index scan for all
+ // three cases below.
+ //In the following call, first param is number of rows in the
+ // table. 2nd param says to DO Not run update statisitcs after
+ // inserting data in the table. 3rd param says to use parameters
+ // in the SELECT sql to identify the rows in the where clause
+ helperDerby6045(10, false, true);
+ helperDerby6045(24, false, true);
+ helperDerby6045(10000, false, true);
+ }
+
+ // Following method will create a brand new table with primary key,
+ // insert passed number of rows, run update statistics if the
+ // passed parameter to method requests for one and then run
+ // three queries and check that they all use index scan
+ // @param numberOfRows number of rows to be inserted into a brand new table
+ // @param updateStatistics if True, run update statistics after inserting
+ // data into the table
+ // @param useParameterMarkers if True, use parameter in the SELECT sql
+ // to identify the rows in the WHERE clause
+ public void helperDerby6045(int numberOfRows,
+ boolean updateStatistics,
+ boolean useParameterMarkers)
+ throws SQLException
+ {
+ Statement s = createStatement();
+ s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ dropTable(DERBY_6045_DATA_TABLE);
+ // Create the test table, primary key and insert data
+ s.executeUpdate(CREATE_DERBY_6045_DATA_TABLE);
+ s.executeUpdate("ALTER TABLE " + DERBY_6045_DATA_TABLE +
+ " ADD CONSTRAINT kb_variable_term_term_id_pk" +
+ " PRIMARY KEY (term_id)");
+
+ //insert requested number of rows in the table
+ PreparedStatement ps = s.getConnection().prepareStatement(
+ "insert into " + DERBY_6045_DATA_TABLE +
+ " VALUES (?, '?var0', 1)");
+ for (int i=1; i<=numberOfRows; i++) {
+ ps.setInt(1, i);
+ ps.executeUpdate();
+ }
+
+ if (updateStatistics) {
+ s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
+ }
+
+ runThreeQueries(0, useParameterMarkers);
+
+ dropTable(DERBY_6045_DATA_TABLE);
+ ps.close();
+ s.close();
+ }
+
+ // DERBY-6045 (in list multi-probe by primary key not chosen on tables
+ // 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();
@@ -925,7 +1152,7 @@ public class InListMultiProbeTest extend
ps.setInt(1, i);
ps.executeUpdate();
}
- runThreeQueries(0);
+ runThreeQueries(0, false);
//Add 14 more rows
for (int i=11; i<=25; i++) {
@@ -938,7 +1165,7 @@ public class InListMultiProbeTest extend
// exactly like the earlier queries in this fixture, we would
// end up using the earloer query plan rather than creating a
// new query plan which is going recognize the additional rows.
- runThreeQueries(1);
+ runThreeQueries(1, false);
//Add 10K more rows
for (int i=26; i<=10000; i++) {
@@ -949,7 +1176,7 @@ public class InListMultiProbeTest extend
// it looks different from the queries run earlier and hence they will
// get compiled rather than existing query plan getting picked up from
// statement cache.
- runThreeQueries(2);
+ runThreeQueries(2, false);
s.close();
}
@@ -962,7 +1189,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();
@@ -987,7 +1214,7 @@ public class InListMultiProbeTest extend
" VALUES (" + i + ", \'?var"+i+"\',"+ (((i %2) == 0) ? 1 : 4) + ")");
}
s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
- runThreeQueries(0);
+ runThreeQueries(0, false);
s.close();
}
@@ -1018,7 +1245,7 @@ public class InListMultiProbeTest extend
" VALUES (" + i + ", \'?var"+i+"\',"+ (((i %2) == 0) ? 1 : 4) + ")");
}
s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
- runThreeQueries(0);
+ runThreeQueries(0, false);
s.close();
}
@@ -1028,11 +1255,13 @@ public class InListMultiProbeTest extend
// plans to be created based on the current number of rows in the table
// @param numOfWhiteSpace Number of white spaces that will be put in
// SELECT queries below
- private void runThreeQueries(int numOfWhiteSpace)
+ private void runThreeQueries(int numOfWhiteSpace,
+ boolean useParameterMarkers)
throws SQLException
{
RuntimeStatisticsParser rtsp;
Statement s = createStatement();
+ PreparedStatement ps;
String whiteSpace = "";
for (int i=1; i<=numOfWhiteSpace; i++)
@@ -1040,21 +1269,55 @@ public class InListMultiProbeTest extend
whiteSpace = whiteSpace + " ";
}
- s.executeQuery("SELECT * FROM " + whiteSpace + DERBY_6045_DATA_TABLE +
- " WHERE TERM_ID = 11");
+ if (useParameterMarkers) {
+ ps = prepareStatement("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE TERM_ID = ?");
+ ps.setInt(1, 11);
+ JDBC.assertDrainResults(ps.executeQuery());
+ } else {
+ s.executeQuery("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE TERM_ID = 11");
+ }
+
rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
assertTrue(rtsp.usedIndexScan());
- s.executeQuery("SELECT * FROM " + whiteSpace + DERBY_6045_DATA_TABLE +
- " WHERE (TERM_ID = 11) OR " +
- "(TERM_ID =21) OR (TERM_ID = 31)");
+ if (useParameterMarkers) {
+ ps = prepareStatement("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID = ?) OR " +
+ "(TERM_ID = ?) OR (TERM_ID = ?)");
+ ps.setInt(1, 11);
+ ps.setInt(2, 21);
+ ps.setInt(3, 31);
+ JDBC.assertDrainResults(ps.executeQuery());
+ } else {
+ s.executeQuery("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID = 11) OR " +
+ "(TERM_ID =21) OR (TERM_ID = 31)");
+ }
rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
assertTrue(rtsp.usedIndexScan());
- s.executeQuery("SELECT * FROM " + whiteSpace + DERBY_6045_DATA_TABLE
- + " WHERE (TERM_ID IN (11, 21, 31))");
+ if (useParameterMarkers) {
+ ps = prepareStatement("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID IN (?, ?, ?))");
+ ps.setInt(1, 11);
+ ps.setInt(2, 21);
+ ps.setInt(3, 31);
+ JDBC.assertDrainResults(ps.executeQuery());
+ } else {
+ s.executeQuery("SELECT * FROM " + whiteSpace +
+ DERBY_6045_DATA_TABLE +
+ " WHERE (TERM_ID IN (11, 21, 31))");
+ }
rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
assertTrue(rtsp.usedIndexScan());
+ s.close();
}
public void testDerby3603()
Modified: db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql?rev=1480153&r1=1480152&r2=1480153&view=diff
==============================================================================
--- db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql (original)
+++ db/derby/code/branches/10.10/java/testing/org/apache/derbyTesting/functionTests/tests/lang/refActions1.sql Wed May 8 03:59:37 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