Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Subject: svn commit: r1450363 -
 /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java
Date: Tue, 26 Feb 2013 19:05:56 -0000
To: derby-commits@db.apache.org
From: mamta@apache.org
Message-Id: <20130226190556.CEE2723888EA@eris.apache.org>

Author: mamta
Date: Tue Feb 26 19:05:56 2013
New Revision: 1450363

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

Adding another junit test which has following 2 test cases(the test is named such that it won't get run for now until we understand the impact of the 2nd unique index on the selection of table scan vs index scan for 10K rows)
    //  1)If we insert 10K rows to an empty table with primary key on column
    //    being used in the where clause, we use index scan for the queries
    //    being tested
    //  2)To the table above, if we add another unique index on 2 columns 
    //    which are being used in the select clause, we stop using index scan
    //    for SELECT queries with IN and OR clause on the primary key


Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/InListMultiProbeTest.java

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=1450363&r1=1450362&r2=1450363&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 Feb 26 19:05:56 2013
@@ -189,12 +189,6 @@ public class InListMultiProbeTest extend
             */
             protected void decorateSQL(Statement s) throws SQLException
             {
-                // Create the test table and data for DERBY-6045
-                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)");
-
                 // Create the test table.
                 s.executeUpdate(CREATE_DATA_TABLE);
                 // Insert test data.
@@ -917,7 +911,13 @@ public class InListMultiProbeTest extend
     {
         Statement s = createStatement();
         s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
-        s.executeUpdate("DELETE FROM " + DERBY_6045_DATA_TABLE);
+        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 10 rows
         PreparedStatement ps = s.getConnection().prepareStatement(
             "insert into " + DERBY_6045_DATA_TABLE +
@@ -951,8 +951,47 @@ public class InListMultiProbeTest extend
         // get compiled rather than existing query plan getting picked up from
         // statement cache.
         runThreeQueries(2);
+        s.close();
+    }
 
-        s.executeUpdate("DROP TABLE " + DERBY_6045_DATA_TABLE);
+    // DERBY-6045 (in list multi-probe by primary key not chosen on tables 
+    //  with >256 rows)
+    // Following test shows 2 cases
+    //  1)If we insert 10K rows to an empty table with primary key on column
+    //    being used in the where clause, we use index scan for the queries
+    //    being tested
+    //  2)To the table above, if we add another unique index on 2 columns 
+    //    which are being used in the select clause, we stop using index scan
+    //    for SELECT queries with IN and OR clause on the primary key
+    public void xtestDerby6045InsertAllRows() 
+        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 10K rows
+        for (int i=1; i<=10000; i++) {
+            s.executeUpdate("insert into " + DERBY_6045_DATA_TABLE +
+    		" VALUES (" + i + ", \'?var"+i+"\',"+ (((i %2) == 0) ? 1 : 4) + ",1)");
+        }
+        runThreeQueries(0);
+        s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
+        runThreeQueries(1);
+
+        //create additional unique key. Creation of this unique key is making
+        // the select queries with IN and OR clause on the primary key to use
+        // table scan
+        s.executeUpdate("ALTER TABLE  " + DERBY_6045_DATA_TABLE + 
+            " ADD CONSTRAINT kb_variable_term_variable_name_unique " +
+            " UNIQUE (var_name, var_type)");
+        s.execute("call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', 'VARIABLE_TERM', null)");
+        runThreeQueries(1);
         s.close();
     }