Examples of org.apache.derbyTesting.junit.RuntimeStatisticsParser

org.apache.derbyTesting.junit.RuntimeStatisticsParser

            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());
    }

View Full Code Here

    //  SELECT queries below
    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++)
        {
            whiteSpace = whiteSpace + " ";
        }
        
        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());


        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());


        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();
    }

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

        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=hash \n" +
                "where test.two = template.two").close();
        checkEstimatedRowCount(conn,8020012.5);
        RuntimeStatisticsParser rtsp = 
              SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // choose NL join, no index. Selectivity should be the same
        // join on two. template inner, NL, no index, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.two = template.two").close();
        checkEstimatedRowCount(conn,8020012.5);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertFalse(rtsp.usedHashJoin());
        
        // choose NL join, index template_two. Selectivity should be the same
        // join on two. template inner, NL, index=two, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_two \n" +
                "where test.two = template.two").close();
        checkEstimatedRowCount(conn,8020012.5);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
        
        // do joins on 20
        // first NL
        // join on twenty. template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty").close();
        // Rowcount should be same as testSingleColumnSelectivityHash
        checkEstimatedRowCount(conn,802001.25);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        // join on 20 but use index 20_2
        // cost as well as selectivity should be divided using selectivity
        // cost should same as template_twenty, or just a shade more...
        // join on twenty. template inner, NL, index=template_22, all rows
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_22 \n" +
                "where test.twenty = template.twenty").close();
        checkEstimatedRowCount(conn,802001.25);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_22"));
        
        // join on twenty but no index
        // note: the original test had this comment:
        // 'rc should be divided using selectivity. cost should be way different'
        // however, it seems the ec is identical.
        // join on twenty, template inner, NL, index=null, all rows
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.twenty = template.twenty").close();
        checkEstimatedRowCount(conn,802001.25);
        
        // still single column, try stuff on 100 but with extra qualification
        // on outer table.
        // row count is 100 * 4000 * 0.01 = 4000
        // join on hundred. 
        // template inner, NL, index=template_102, 100 rows from outer
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_102 \n" +
                "where test.hundred = template.hundred and test.id <= 100").close();
        // note: original cloudscape result was expecting 3884.85 here.
        checkEstimatedRowCount(conn,3924.9);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEST", "TEST_ID"));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_102"));
        
        // join on hundred. 
        // template inner, NL, index=null, 100 rows from outer
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.hundred = template.hundred and test.id <= 100").close();
        checkEstimatedRowCount(conn,3924.9);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEST", "TEST_ID"));
        
        // join on hundred. 
        // template inner, hash, index=null, 100 rows from outer.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=hash, " +
                "index=null \n" +
                "where test.hundred = template.hundred and test.id <= 100").close();
        checkEstimatedRowCount(conn,3924.9);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEST", "TEST_ID"));
    }

View Full Code Here

                "test, template --DERBY-PROPERTIES joinStrategy=hash, " +
                "index=null \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // join on twenty/two. template inner, NL, index=template_two, all rows
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_two \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
        
        // join on twenty/two. 
        // template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        // join on twenty/two. template inner, NL, index=template_22, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_22 \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_22"));
        
        // multi predicate tests continued
        // drop index twenty, two -- use above predicates
        // should be smart enough to figure out the selectivity by
        // combining twenty and two.
        s.executeUpdate("drop index template_22");
        
        // join on twenty/two. index twenty_two dropped. 
        // template inner, hash, index=null, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=hash, " +
                "index=null \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedHashJoin());
        
        // join on twenty/two. index twenty_two dropped. 
        // template inner, NL, index=template_two, all rows.'
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_two \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWO"));
        
        // join on twenty/two. index twenty_two dropped. 
        // template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,401000.625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        s.executeUpdate("drop index template_two");
        
        // we only have index template_twenty
        // for the second predicate we should use 0.1 instead of 0.5
        // thus reducing earlier row count by a factor of 5
        // 80,000 instead of 400,000
        
        // join on twenty/two. index twenty_two and two dropped. 
        // template inner, NL, index=null, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,80200.12500000001);
        
        // join on twenty/two. index twenty_two and two dropped. 
        // template inner, NL, index=template_twenty, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_twenty \n" +
                "where test.twenty = template.twenty " +
                "and test.two = template.two").close();
        checkEstimatedRowCount(conn,80200.12500000001);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_TWENTY"));
        
        // now drop index template_twenty
        // selectivity should become 0.1 * 0.1 = 0.01
        // 16 * 10^6 * .01 = 160,000

View Full Code Here

                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_102 \n" +
                "where test.hundred = template.hundred " +
                "and 1 = template.two").close();
        checkEstimatedRowCount(conn,80200.125);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_102"));
        
        // hundred and twenty
        // we can use statistics for 100,2 to get selectivity for 100 and
        // twenty and twenty to get selectivity for 20
        // selectivity should 0.01 * 0.05 = 0.0005 -> 80,000
        // join on hundred. constant pred on twenty. 
        // NL, index=null, all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=null \n" +
                "where test.hundred = template.hundred " +
                "and 1 = template.twenty").close();
        checkEstimatedRowCount(conn,8020.0125);
        
        // 'join on hundred. constant pred on twenty. 
        // NL, index=template_102 all rows.
        s.executeQuery("select template.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "test, template --DERBY-PROPERTIES joinStrategy=nestedLoop, " +
                "index=template_102 \n" +
                "where test.hundred = template.hundred " +
                "and 1 = template.twenty").close();
        checkEstimatedRowCount(conn,8020.0125);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("TEMPLATE", "TEMPLATE_102"));
    }

View Full Code Here

                "t1, t2, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t1.twenty = t2.twenty and " +
                "t2.two = t3.two").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        RuntimeStatisticsParser rtsp = 
                SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 2, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        
        // t1 x t2 -> 16 * 10^4.
        // x t3    -> 32 * 10^7
        // additional pred -> 32 * 10^5
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two and " +
                "t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        // variations on above query; try different join strategies
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 --DERBY-PROPERTIES joinStrategy=hash \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 --DERBY-PROPERTIES joinStrategy=nestedLoop \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2 --DERBY-PROPERTIES joinStrategy=hash \n, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2 --DERBY-PROPERTIES joinStrategy=hash \n, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        
        // duplicate predicates; this time t1.hundred=?
        // will show up twice when t1 is optimized at the end
        // selectivity should be same as above
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // variations on above query; try different join strategies
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2, t1 --DERBY-PROPERTIES joinStrategy=hash \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2, t1 --DERBY-PROPERTIES joinStrategy=nestedLoop \n" +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3 --DERBY-PROPERTIES joinStrategy=nestedLoop \n, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2 --DERBY-PROPERTIES joinStrategy=hash \n, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // some more variations on the above theme
        // some constant predicates thrown in.
        // remember hundred is a char column
        // -- for some reason if you give the constant 
        // as a numeric argument it doesn't recognize that 
        // as a constant start/stop value for the index 
        // The error is that the types must be comparable.
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred " +
                "and t1.hundred='1'").close();
        checkEstimatedRowCount(conn,30458.025);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T2", "T2_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T1", "T1_HUNDRED"));
        assertTrue(rtsp.findString("Bit set of columns fetched=All", 1));
        
        // we have t1.100=t2.100 and t1.100=t3.100, so 
        // t2.100=t3.100 is redundant. 
        // row count shouldn't factor in the redundant predicate.
        // row count should be 3200000.0
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t1.hundred = t3.hundred " +
                "and t2.hundred = t3.hundred").close();
        checkEstimatedRowCount(conn,3212015.00625);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T3", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // slightly different join predicates-- use composite stats.
        // t1 x t2            --> 16 * 10.4.
        //         x t3       --> 16 * 10.4 * 4000 * 1/40 = 16*10.6
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t3, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        // same as above but muck around with join order.
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t2, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t2, t1, t3 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString(
                "Hash Scan ResultSet for T3 using index T3_TWO_TWENTY", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t1, t3, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t2, t1 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES joinOrder=fixed \n" + 
                "t3, t1, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,1.606007503125E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
        
        // and just for fun, what would we have gotten without statistics.
        s.executeQuery("select t1.id from " +
                "--DERBY-PROPERTIES useStatistics=false, joinOrder=fixed \n" + 
                "t3, t1, t2 " +
                "where t1.hundred = t2.hundred " +
                "and t2.two = t3.two " +
                "and t2.twenty = t3.twenty").close();
        checkEstimatedRowCount(conn,6.4240300125000015E7);
        rtsp = SQLUtilities.getRuntimeStatisticsParser(s);
        assertTrue(rtsp.usedSpecificIndexForIndexScan("T3", "T3_TWO_TWENTY"));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 1}", 1));
        assertTrue(rtsp.findString("Table Scan ResultSet for T1", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={0, 3}", 1));
        assertTrue(rtsp.findString("Hash Scan ResultSet for T2", 1));
        assertTrue(rtsp.findString("Bit set of columns fetched={1, 2, 3}", 1));
    }

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

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.