Examples of org.apache.derbyTesting.junit.RuntimeStatisticsParser

org.apache.derbyTesting.junit.RuntimeStatisticsParser

                "where t10.two = 1 " +
                "and t10.hundred = 2 " +
                "and t10.a = 2 " +
                "and t10.b = 2").close();
        checkEstimatedRowCount(conn,7945.920000000);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("COMPLEX", "COMPLEXIND"));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
    }

View Full Code Here

        }


        // Expect this to use table scan, as the above update has basically
        // made all the rows in the table be equal to "1", thus using the index
        // does not help if all the rows are going to qualify.
        RuntimeStatisticsParser rsp =
                SQLUtilities.getRuntimeStatisticsParser(stmt);
        if (!rsp.usedTableScan()) {
            // Dump the full plan to help debug DERBY-6336.
            fail("Expected table scan. Full plan:\n" + rsp.toString());
        }


        // Change the row count significantly
        stmt.executeUpdate("insert into t select c1,c2,c3 from t where c1<128");

View Full Code Here

    //statistics available for T2I2 to show that it is a better index
    ps = prepareStatement("SELECT * FROM t2 WHERE c21=? AND c22=?");
      ps.setInt(1, 0);
        ps.setString(2, "Tuple 4");
        JDBC.assertDrainResults(ps.executeQuery());
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I1"));


    //Running the update statistics below will create statistics for T2I2
    s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T2','T2I2')");
        stats.assertIndexStats("T2I2", 1);


        //Rerunning the query "SELECT * FROM t2 WHERE c21=? AND c22=?" and
        //looking at it's plan will show that this time it picked up more
        //efficient index which is T2I2.
        JDBC.assertDrainResults(ps.executeQuery());
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I2"));


    //Drop statistics for T2I2 and we should see that we go back to using
    // T2I1 rather than T2I2
    s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','T2','T2I2')");
        stats.assertIndexStats("T2I2", 0);


        //Rerunning the query "SELECT * FROM t2 WHERE c21=? AND c22=?" and
        // looking at it's plan will show that this time it picked up T2I1
        // rather than more efficient index T2I2  because no stats exists
        // for T2I2
        JDBC.assertDrainResults(ps.executeQuery());
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("T2","T2I1"));


        //cleanup
        s.executeUpdate("DROP TABLE t2");
        //End of test case for better index selection after statistics
        //availability

View Full Code Here

     * @param table the table being accessed
     * @param index the index that should be used
     */
    private void assertIndex(String table, String index)
            throws SQLException {
        RuntimeStatisticsParser parser =
                SQLUtilities.getRuntimeStatisticsParser(createStatement());
        if (!parser.usedSpecificIndexForIndexScan(table, index)) {
            fail("Should have used index " + index + " when accessing table " +
                 table + ". Actual plan:\n" + parser);
        }
    }

View Full Code Here

    // would do table scan on TABLE1 rather than an index scan.
    // SELECT * FROM Table1 T1, Table2 t0 
    //  WHERE t1.ID = t0.Table1_ID and t0.Table3_ID = SOME_CONSTANT
    private void confirmIndexScanUsage(Statement stmt, int some_constant)
        throws SQLException {
        RuntimeStatisticsParser rtsp;
        boolean constraintUsed;
        rtsp = SQLUtilities.getRuntimeStatisticsParser(stmt);
        constraintUsed = rtsp.usedConstraintForIndexScan("TABLE1");
        if (!constraintUsed){
            assertTrue("Should have done index scan but did table scan on " + 
                "TABLE1 for t0.Table3_ID = "+some_constant, constraintUsed);
        }
        constraintUsed = rtsp.usedConstraintForIndexScan("TABLE2");
        if (!constraintUsed){
            assertTrue("Should have done index scan but did table scan on " +
                "TABLE2 for t0.Table3_ID = "+some_constant, constraintUsed);
        }      
    }

View Full Code Here

        //the token derby-properties is case insensitive.
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1" +
            " --DeRbY-pRoPeRtIeS index = t1_c1"),
            FULL_TABLE);        
        RuntimeStatisticsParser rtsp = 
            SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1"));
        
        //-- misspell derby-properties and make sure that 
        //it gets treated as a regular comment 
        //rather than optimizer override
        JDBC.assertFullResultSet(
                st.executeQuery("select * from t1 " +
                        " --DeRbY-pRoPeRtIeAAAA index = t1_c1"),
                        FULL_TABLE);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue("not using t1_c1, but what derby thinks is best index.",
                rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1C2C3"));
        
        
        //"--DeRbY-pRoPeRtIeSAAAA index = t1_c1" is 
        //treated as "--DeRbY-pRoPeRtIeS AAAA index = t1_c1"
        assertStatementError("42Y44", st, "select * from t1 " +
                " --DeRbY-pRoPeRtIeSAAAA index = t1_c1");
        
        //-- force index, delimited identifier
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1 " +
                    "--derby-properties index = \"t1_c2c1\""),
                FULL_TABLE);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "t1_c2c1"));
        
        //If the property spelled wrong gets treated 
        //as an optimizer override, the following test will fail.
        assertStatementError("42Y46", st, "select * from t1 " +
                       " --DeRbY-pRoPeRtIeS index = t1_notexisting");

View Full Code Here

        st.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1 " +
            "--derby-properties index = null"), FULL_TABLE);
        RuntimeStatisticsParser rtsp = 
            SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue("force table scan", rtsp.usedTableScan());
        
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1 " +
            "--derby-properties constraint = null"), FULL_TABLE);

View Full Code Here

                {"1"}, {"1"}, {"1"}, {"1"},
                {"1"}, {"1"}, {"1"}, {"1"},
                {"1"}, {"1"}, {"1"}, {"1"},
                {"1"}, {"1"}, {"1"}, {"1"},
            });
        RuntimeStatisticsParser rtsp = 
            SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1"));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T2", "T2_C2"));
        
        JDBC.assertFullResultSet(
            st.executeQuery("select 1 from " +
                " --derby-properties joinOrder=fixed\n" +
                "t1, t2 where t1.c1 = t2.c1"),
            new String[][]{{"1"}, {"1"}, {"1"}, {"1"}, }
        );
        
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1" +
                " --derby-properties index = t1_c1\n" +  
                "left outer join t2 " +
                "--derby-properties index = t2_c2\n" +  
                "on t1.c1 = t2.c1"),
            new String[][]{
                    {"1", "1", "1", "1", "1", "1"}, 
                    {"2", "2", "2", "2", "2", "2"},
                    {"3", "3", "3", "3", "3", "3"},
                    {"4", "4", "4", "4", "4", "4"},
            });
        rtsp = 
            SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1"));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T2", "T2_C2"));
        
        st.close();
    }

View Full Code Here

        
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1" +
                " --derby-properties index = t1_c1\n" +  
                "where c1 = c1"), FULL_TABLE);
        RuntimeStatisticsParser rtsp = 
            SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1"));
        
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1" +
                " --derby-properties index = t1_c1\n" +  
                "where c1 = c2"), FULL_TABLE);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1"));
        
        JDBC.assertFullResultSet(
            st.executeQuery("select * from t1" +
                " --derby-properties index = t1_c1\n" +  
                "where c1 + 1 = 1 + c1"), FULL_TABLE);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(st);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_C1"));
        
        st.close();
    }

View Full Code Here

     */
    public void testSelectSubqueriesSortAvoidance() throws SQLException {
        setAutoCommit(false);
        Statement s = createStatement();
        ResultSet rs;
        RuntimeStatisticsParser rtsp;
        s.executeUpdate("create table ts(i int, j int)");
        PreparedStatement ps = prepareStatement("insert into ts values(?,?)");
        for (int i=0; i < 100; i++) {
            ps.setInt(1,i);
            ps.setInt(2,i*2);
            ps.execute();
        }


        s.executeUpdate("create unique index t_i on ts(i)");
        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");


        // ORDER BY inside a subquery should make use of index to avoid
        // sorting.
        rs = s.executeQuery("select * from (select i from ts order by i)tt");
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);


        // Verify that we use the index scan here and no sorting is incurred
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TS","T_I"));
        assertFalse(rtsp.whatSortingRequired());


        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
        rollback();
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.apache.derbyTesting.junit.RuntimeStatisticsParser

org.apache.derbyTesting.functionTests.tests.jdbcapi.SetTransactionIsolationTest

org.apache.derbyTesting.functionTests.tests.lang.DistinctTest

org.apache.derbyTesting.functionTests.tests.lang.DynamicLikeOptimizationTest

org.apache.derbyTesting.functionTests.tests.lang.ForUpdateTest

org.apache.derbyTesting.functionTests.tests.lang.GroupByTest

org.apache.derbyTesting.functionTests.tests.lang.InbetweenTest

org.apache.derbyTesting.functionTests.tests.lang.InListMultiProbeTest

org.apache.derbyTesting.functionTests.tests.lang.JoinTest

org.apache.derbyTesting.functionTests.tests.lang.OptimizerOverridesTest

org.apache.derbyTesting.functionTests.tests.lang.OrderByAndOffsetFetchInSubqueries

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.