Package org.apache.derbyTesting.junit

Examples of org.apache.derbyTesting.junit.RuntimeStatisticsParser$Qualifier


    //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"));
        //cleanup
        s.executeUpdate("DROP TABLE t2");
        //End of test case for better index selection after statistics
        //availability
        stats.release();
View Full Code Here



        s.execute("CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");

        ResultSet rs;
        RuntimeStatisticsParser rtsp;

        // 0. Only the variant with the subquery did the re-write of outer to
        // inner join prior to this fix. When the re-write is performed, the
        // optimizer chooses a hash join on CITIES, which substantially speeds
        // up the query.

        rs = s.executeQuery("SELECT * FROM CITIES LEFT OUTER JOIN " +
                            "    (SELECT * FROM FLIGHTS, COUNTRIES) S " +
                            "  ON CITIES.AIRPORT = S.ORIG_AIRPORT " +
                            "  WHERE S.COUNTRY_ISO_CODE = 'US'");

        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());

        // 1. Equivalent variant failed to rewrite prior to patch and was slow.
        rs = s.executeQuery("SELECT * FROM CITIES LEFT OUTER JOIN FLIGHTS " +
                            "    INNER JOIN COUNTRIES ON 1=1 " +
                            "    ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT " +
                            "  WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'");


        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);

        // Check that outer join has been rewritten
        assertFalse(rtsp.usedNLLeftOuterJoin());
        assertTrue(rtsp.usedHashJoin());


        // 1b. Equivalent variant of 1, just use ROJ instead.
        rs = s.executeQuery("SELECT * FROM FLIGHTS " +
                            "    INNER JOIN COUNTRIES ON 1=1 " +
                            "    RIGHT OUTER JOIN CITIES " +
                            "    ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT " +
                            "  WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'");


        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);

        // Check that outer join has been rewritten
        assertFalse(rtsp.usedNLLeftOuterJoin()); // ROJ is made LOJ in case
                                                 // still used
        assertTrue(rtsp.usedHashJoin());


        // 2. Equivalent variant failed to rewrite prior to patch and was slow.
        rs = s.executeQuery("SELECT * FROM CITIES LEFT OUTER JOIN " +
                            "   (FLIGHTS CROSS JOIN COUNTRIES) " +
                            "  ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT " +
                            "  WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'");
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);

        // Check that outer join has been rewritten
        assertFalse(rtsp.usedNLLeftOuterJoin());
        assertTrue(rtsp.usedHashJoin());

        // 2b. Equivalent variant of 2, just use ROJ instead.
        rs = s.executeQuery(
            "SELECT * FROM " +
            "   (FLIGHTS CROSS JOIN COUNTRIES) RIGHT OUTER JOIN " +
            "    CITIES ON CITIES.AIRPORT = FLIGHTS.ORIG_AIRPORT " +
            "  WHERE COUNTRIES.COUNTRY_ISO_CODE = 'US'");
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);

        // Check that outer join has been rewritten
        assertFalse(rtsp.usedNLLeftOuterJoin()); // ROJ is made LOJ in case
                                                 // still used
        assertTrue(rtsp.usedHashJoin());

    }
View Full Code Here

     * for both ascending and descending order by clause. 
     */
    public void testDerby4331() throws SQLException {
        Statement s;
        ResultSet rs;
        RuntimeStatisticsParser rtsp;
        String [][] desc_result = new String[][] {
            {"3"},{"3"},{"2"},{"2"},{"2"},{"1"}};
        String [][] asc_result  = new String[][] {
            {"1"},{"2"},{"2"},{"2"},{"3"},{"3"}};
       
View Full Code Here

        String sql1 =
          "SELECT t1.id, t1.name FROM test2 t2 INNER JOIN test1 t1 "+
          "ON t2.rel_id = t1.id WHERE t2.entity_id = 1 ORDER BY t1.id ASC";
        Statement s;
        ResultSet rs;
        RuntimeStatisticsParser rtsp;
        String [][] result;

        s = createStatement();
        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        result = new String[][] {
                {"101", "Pupy"},{"102", "Tom"}, {"103", "Jerry"}};
        JDBC.assertFullResultSet(rs, result);
    }
View Full Code Here

     */
    public void testAdditionalOrderByCases() throws SQLException {
        String sql1;
        Statement s;
        ResultSet rs;
        RuntimeStatisticsParser rtsp;
        String [][] result;

        s = createStatement();
        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
       
        sql1 = "select a.col1, b.col2, c.col2 from a, b, c where c.col1=3 " +
        "order by a.col1, c.col1";
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        result = new String[][] {
                {"1", "2", "3"},{"1", "2", "3"}, {"1", "2", "3"},  
                {"1", "2", "3"},{"1", "2", "3"}, {"1", "2", "3"},  
                {"1", "2", "3"},{"1", "2", "3"}};
        JDBC.assertFullResultSet(rs, result);
       
        sql1 = "select a.col1, b.col2, c.col2 from a, b, c where a.col1=1 "+
        "and b.col1 = 2 and c.col1=3 order by a.col1, b.col1, c.col1";
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        JDBC.assertFullResultSet(rs, result);

        sql1 = "select c.col1, b.col1, a.col1 from a, b, c where a.col1=1 "+
        "and b.col1 = 2 and c.col1=3 order by c.col1, b.col1, a.col1";
        result = new String[][] {
                {"3", "2", "1"},{"3", "2", "1"}, {"3", "2", "1"},  
                {"3", "2", "1"},{"3", "2", "1"}, {"3", "2", "1"},  
                {"3", "2", "1"},{"3", "2", "1"}};
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertFalse(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        JDBC.assertFullResultSet(rs, result);

        sql1 = "select c.col1, b.col1, a.col1 from a, b, c where a.col1=1 "+
        "and b.col1 = 2 and c.col1=3 order by c.col2, b.col2, a.col2";
        rs = s.executeQuery(sql1);
    rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.whatSortingRequired());
        rs = s.executeQuery(sql1);
        JDBC.assertFullResultSet(rs, result);
    }
View Full Code Here

        "AND table1.id=m1.id AND m1.name='PostComponentId' "+
        "AND m1.value='21857' ORDER BY m0.value";

        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        ResultSet rs = s.executeQuery(sql1);
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedTableScan("TABLE2"));
    assertTrue(rtsp.whatSortingRequired());

        rs = s.executeQuery(sql1);
        String[][] result = {
                {"4294967297", "000001", "21857"},
                {"2147483653", "000002", "21857"},
View Full Code Here

        "m1.name='PostComponentId' AND m1.value='21857' " +
        "ORDER BY m0.value";

        s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        ResultSet rs = s.executeQuery(sql1);
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("TABLE2","KEY3"));
    assertTrue(rtsp.usedTableScan("TABLE1"));
    assertTrue(rtsp.whatSortingRequired());
 
        rs = s.executeQuery(sql1);
        String[][] result = {
                {"4294967297", "000001", "21857"},
                {"2147483653", "000002", "21857"},
View Full Code Here

        "AND table1.id=m1.id AND m1.name='PostComponentId' AND "+
        " m1.value='21857' ORDER BY m0.value";

    s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
        ResultSet rs = s.executeQuery(sql1);
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(
        s);
    assertTrue(rtsp.usedSpecificIndexForIndexScan("TABLE2","KEY3"));
    assertTrue(rtsp.usedTableScan("TABLE1"));
    assertTrue(rtsp.whatSortingRequired());

    rs = s.executeQuery(sql1);
        String[][] result = {
                {"4294967297", "000001", "21857"},
                {"2147483653", "000002", "21857"},
View Full Code Here

        int expRowCount) throws SQLException
    {
        if (!rStat.next())
            return false;

        RuntimeStatisticsParser rsp =
            new RuntimeStatisticsParser(rStat.getString(1));

        return (rsp.usedIndexRowToBaseRow() && rsp.usedIndexScan()
            && (rsp.rowsQualifiedEquals(expRowCount)));
    }
View Full Code Here

                  {"10", "cccc"},
                       {"11", "hhhh"},
                 {"12", "rrrr"} };                    
    stmt.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");     
    JDBC.assertFullResultSet(stmt.executeQuery("select i, b from t3 FOR UPDATE"), expectedValues);
    RuntimeStatisticsParser rtsp = SQLUtilities.getRuntimeStatisticsParser(stmt);
    assertTrue(rtsp.usedTableScan());
    assertFalse(rtsp.usedDistinctScan());
    commit();

    PreparedStatement p = prepareStatement("select i, b from t3  where i = ? FOR UPDATE");
                p.setString(1, "7");
                p.executeQuery();
    String [][] expectedValues1 = { {"7", "iiii" } };
    JDBC.assertFullResultSet(p.getResultSet(), expectedValues1);
    RuntimeStatisticsParser rtsp2 = SQLUtilities.getRuntimeStatisticsParser(stmt);
    assertFalse(rtsp2.usedTableScan());
    assertFalse(rtsp2.usedDistinctScan());
    p.close();
    commit();


    p = prepareStatement("select i, b from t3 where i < ? FOR UPDATE");
                p.setString(1, "7");
                p.executeQuery();
    String[][] expectedValues2 =  { {"1", "hhhh" },
            {"2", "uuuu" },
            {"3", "yyyy" },
            {"4", "aaaa" },
            {"5", "jjjj" },
            {"6", "rrrr" } };
    JDBC.assertFullResultSet(p.getResultSet(), expectedValues2);
    RuntimeStatisticsParser rtsp3 = SQLUtilities.getRuntimeStatisticsParser(stmt);
    assertFalse(rtsp3.usedTableScan());
    assertFalse(rtsp3.usedDistinctScan());             
    p.close();
    commit();


    p = prepareStatement("select i, b from t3  where b = ? FOR UPDATE");
                p.setString(1, "cccc");
                p.executeQuery();
    String[][] expectedValues3 = { {"10", "cccc" } };
    JDBC.assertFullResultSet(p.getResultSet(), expectedValues3);
    RuntimeStatisticsParser rtsp4 = SQLUtilities.getRuntimeStatisticsParser(stmt);
    assertFalse(rtsp4.usedTableScan());
    assertFalse(rtsp4.usedDistinctScan());
    p.close();
    commit();

          stmt.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)")
            stmt.close();
View Full Code Here

TOP

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

Copyright © 2018 www.massapicom. All rights reserved.
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.