Package org.apache.derbyTesting.junit

Examples of org.apache.derbyTesting.junit.IndexStatsUtil


        //  prior to 10.5 and hence we can't cause the hanging statistics to
        //  appear in order to test the drop statistics after hard upgrade
        if (!oldAtLeast(10, 5)) return;

        // Helper object to obtain information about index statistics.
        IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
        Statement s = createStatement();
        // The expected initial number of statistics entries in TEST_TAB_2.
        final int expected =
                DisposableIndexStatistics.hasDerby5681Bug(getOldVersion()) ?
                    2 : 1;
       
        switch (getPhase())
        {
        case PH_CREATE:
            s.executeUpdate("CREATE TABLE TEST_TAB_1 (c11 int not null,"+
                    "c12 int not null, c13 int)");
            s.executeUpdate("INSERT INTO TEST_TAB_1 VALUES(1,1,1),(2,2,2)");
            s.executeUpdate("ALTER TABLE TEST_TAB_1 "+
                    "ADD CONSTRAINT TEST_TAB_1_PK_1 "+
                    "PRIMARY KEY (c11)");
            //The statistics for primary key constraint has been added
            stats.assertTableStats("TEST_TAB_1",1);
           
            s.executeUpdate("CREATE TABLE TEST_TAB_2 (c21 int not null)");
            s.executeUpdate("INSERT INTO TEST_TAB_2 VALUES(1),(2)");
            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                    "ADD CONSTRAINT TEST_TAB_2_PK_1 "+
                    "PRIMARY KEY (c21)");
            stats.assertTableStats("TEST_TAB_2",1);
            //DERBY-5702 Add a foreign key constraint and now we should find 2 rows
            // of statistics for TEST_TAB_2 - 1 for primary key and other for
            // foreign key constraint
            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                    "ADD CONSTRAINT TEST_TAB_2_FK_1 "+
                    "FOREIGN KEY(c21) REFERENCES TEST_TAB_1(c11)");
            //DERBY-5702 Like primary key earlier, adding foreign key constraint
            // didn't automatically add a statistics row for it. Have to run update
            // statistics manually to get a row added for it's stat
            stats.assertTableStats("TEST_TAB_2",1);
            //Need to do a compress table to create the statistics for foreign
            // key constraint. Update statisitcs procedure is only available
            // in 10.5 and upwards and hence can't use that procedure here
            // since we are testing older releases too.
            s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
            //s.execute("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP','TEST_TAB_2',1)");
            stats.assertTableStats("TEST_TAB_2",2);
            s.executeUpdate("ALTER TABLE TEST_TAB_2 "+
                    "DROP CONSTRAINT TEST_TAB_2_FK_1");
            //Dropping the foreign key constraint does not remove it's
            // statistics row because of DERBY-5681.
            stats.assertTableStats("TEST_TAB_2", expected);
            assertStatementError("42Y03", s,
            "CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
            break;

        case PH_SOFT_UPGRADE:
        case PH_POST_SOFT_UPGRADE:
            assertStatementError("42Y03", s,
                       "CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
            break;

        case PH_HARD_UPGRADE:
            stats.assertTableStats("TEST_TAB_2", expected);
            s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
            stats.assertNoStatsTable("TEST_TAB_2");
            s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','TEST_TAB_2', null)");
            stats.assertNoStatsTable("TEST_TAB_2");
            break;

        case PH_POST_HARD_UPGRADE:
            //Make sure that the new procedure is still available
            s.execute("CALL SYSCS_UTIL.SYSCS_DROP_STATISTICS('APP','TEST_TAB_2', null)");
View Full Code Here


                ")");

        // Insert data
        insertData(con);

        IndexStatsUtil stats = new IndexStatsUtil(con);
        // Add constraints
        stmt.executeUpdate("alter table " + pktbl + " add constraint " +
                "PK_TWOCOL_PKTAB primary key (id1, id2)");
        stats.getStatsTable(pktbl, 2);
        stmt.executeUpdate("alter table " + fktbl + " add constraint " +
                "PK_FKTAB primary key (id)");
        stats.getStatsTable(fktbl, 1);
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "PK_MAIN primary key (id)");
        stats.getStatsTable(tbl, 1);
        stmt.executeUpdate("create index DUPS_MAIN on " + tbl + "(nonunique)");
        stats.getStatsTable(tbl, 2);
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "FKS_MAIN foreign key (fk_self) references " + tbl + "(id)");
        stats.getStatsTable(tbl, 3);
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "FKSNN_MAIN foreign key (fk_self_notnull) references " +
                tbl + "(id)");
        stats.getStatsTable(tbl, 4);

        int preFkAddition = stats.getStatsTable(tbl).length;
        // This doesn't trigger DERBY-5681.
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "fk_to_be_dropped foreign key (fk_dropped) " +
                "references " + fktbl + "(id)");
        Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1);
        stmt.executeUpdate("alter table " + tbl + " drop constraint " +
                "fk_to_be_dropped");
        Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition);

        // Trigger DERBY-5681.
        stmt.executeUpdate("alter table " + tbl + " add constraint " +
                "fk_on_pk foreign key (id) " +
                "references " + fktbl + "(id)");
        stmt.executeUpdate("call syscs_util.syscs_update_statistics(" +
                "'APP', '" + tbl + "', null)");
        Assert.assertTrue(stats.getStatsTable(tbl).length == preFkAddition +1);
        stmt.executeUpdate("alter table " + tbl + " drop constraint " +
                "fk_on_pk");
        // Derby failed to drop the statistics when the constraint got dropped.
        // DERBY-5681: Originally fixed in 10.9, but has now been backported
        //      all the way back to 10.3.
        int tableStatsCount = stats.getStatsTable(tbl).length;
        if (hasDerby5681Bug(oldVersion)) {
            Assert.assertEquals(preFkAddition +1, tableStatsCount);
        } else {
            Assert.assertEquals(preFkAddition, tableStatsCount);
        }
View Full Code Here

     */
    private int getAllRelevantStats(List<IndexStatsUtil.IdxStats> list)
            throws SQLException {
        boolean oldAutoCommitValue = con.getAutoCommit();
        con.setAutoCommit(true);
        IndexStatsUtil stats = new IndexStatsUtil(con);
        String[] tables = getTableNames();
        int count = 0;
        for (int i=0; i < tables.length; i++) {
            IndexStatsUtil.IdxStats[] entries = stats.getStatsTable(tables[i]);
            if (list != null) {
                list.addAll(Arrays.asList(entries));
            }
            count += entries.length;
        }
        stats.release(false);
        con.setAutoCommit(oldAutoCommitValue);
        return count;
    }
View Full Code Here

    public void setUp()
            throws SQLException {
        if (stats != null) {
            stats.release();
        }
        stats = new IndexStatsUtil(openDefaultConnection(), DEFAULT_TIMEOUT);
    }
View Full Code Here

        PreparedStatement ps = con.prepareStatement(
                "select * from " + TAB + " where id = ?");
        ps.close();

        // Get statistics for the non-unique index.
        IdxStats[] myStats = new IndexStatsUtil(
                ds.getConnection(), DEFAULT_TIMEOUT).getStatsTable(TAB, 1);
        assertEquals(1, myStats.length);
        assertTrue(myStats[0].rows == 300);

        // Shutdown database and try to delete it.
View Full Code Here

        Statement stmt = con.createStatement();
        stmt.executeUpdate("drop table " + TAB1);
        stmt.close();

        // Trigger stats update on secondary table.
        IndexStatsUtil myStats =
                new IndexStatsUtil(ds.getConnection(), DEFAULT_TIMEOUT);
        myStats.assertNoStatsTable(TAB2);
        con.prepareStatement("select * from " + TAB2 + " where id = ?");
        myStats.assertTableStats(TAB2, 1);
        myStats.release();

        // Shutdown, then delete database directory.
        JDBCDataSource.shutdownDatabase(ds);
        assertDirectoryDeleted(constructDbPath(db));
    }
View Full Code Here

        stmt.executeUpdate("call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', '" +
                TAB1 + "', 0)");
        stmt.close();
        // There should still be a statistics object written during the
        // compress operation.
        IndexStatsUtil myStats =
                new IndexStatsUtil(ds.getConnection(), DEFAULT_TIMEOUT);
        myStats.assertTableStats(TAB1, 1);

        // Trigger stats update on secondary table, make sure the daemon can
        // still process work.
        myStats.assertNoStatsTable(TAB2);
        con.prepareStatement("select * from " + TAB2 + " where id = ?");
        myStats.assertTableStats(TAB2, 1);
        myStats.release();

        // Shutdown database and try to delete it.
        JDBCDataSource.shutdownDatabase(ds);
        assertDirectoryDeleted(constructDbPath(db));
    }
View Full Code Here

     * @throws SQLException if creating/populating the table fails
     */
    private void createAndInsertSimple(Connection con, String table, int rows)
            throws SQLException {
        Statement s;
        IndexStatsUtil myStats;
        if (con == null) {
            con = getConnection();
            s = createStatement();
            myStats = stats;
        } else {
            s = con.createStatement();
            myStats = new IndexStatsUtil(con);
        }
        // See if the table exists, and if so, drop it.
        dropIfExists(con, table);
        // Create table.
        s.executeUpdate(
                "create table " + table + "(id int primary key, val int)");
        s.executeUpdate("create index NON_UNIQUE_INDEX_" + table + " on " +
                table + "(val)");

        myStats.assertNoStatsTable(table);

        // Insert data
        long start = System.currentTimeMillis();
        println("created " + table + ", inserting " + rows + " rows");
        insertSimple(con, table, rows, 0);
        println("completed in " + (System.currentTimeMillis() - start) + " ms");
        myStats.assertNoStatsTable(table);
    }
View Full Code Here

    }

    /** Runs the real test case. */
    private void assertOnSCUI(boolean keepDisposable)
            throws SQLException {
        IndexStatsUtil stats = new IndexStatsUtil(
                openDefaultConnection(), 20*1000); // 20 seconds timeout
        // Create table.
        String TAB = "STAT_SCUI";
        dropTable(TAB);
        Statement stmt = createStatement();
        stmt.executeUpdate("create table " + TAB +
                " (id int not null, val int)");
        stats.assertNoStatsTable(TAB);
        PreparedStatement psIns = prepareStatement(
                "insert into " + TAB + " values (?,?)");
        setAutoCommit(false);
        for (int i=0; i < 20; i++) {
            psIns.setInt(1, i);
            psIns.setInt(2, i);
            psIns.executeUpdate();
        }
        commit();
        setAutoCommit(true);
        stmt.executeUpdate("alter table " + TAB + " add constraint PK_" + TAB +
                " primary key(id)");
        stats.assertTableStats(TAB, keepDisposable ? : 0);
        stmt.executeUpdate(
                "create unique index UNIQ_IDX_" + TAB + " ON " + TAB + "(val)");
        stats.assertTableStats(TAB, keepDisposable ? : 0);
        PreparedStatement ps = prepareStatement(
                "call SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP', ?, ?)");
        // Update stats for all indexes.
        ps.setString(1, TAB);
        ps.setNull(2, Types.VARCHAR);
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? : 0);

        // Update stats for one specific index.
        ps.setString(2, "UNIQ_IDX_" + TAB);
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? : 0);

        // Drop statistics.
        stmt.execute("call SYSCS_UTIL.SYSCS_DROP_STATISTICS(" +
                "'APP', '" + TAB + "', null)");
        stats.assertNoStatsTable(TAB);

        // Update and assert again, this time in the reverse order.
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? : 0);
        ps.setNull(2, Types.VARCHAR);
        ps.execute();
        stats.assertTableStats(TAB, keepDisposable ? : 0);
        IndexStatsUtil.IdxStats[] oldStats = stats.getStatsTable(TAB);
       
        // Insert more data to trigger an automatic update
        setAutoCommit(false);
        for (int i=20; i < 2000; i++) {
            psIns.setInt(1, i);
            psIns.setInt(2, i);
            psIns.executeUpdate();
        }
        commit();
        setAutoCommit(true);
        // Trigger the scheduling logic to get the istat daemon going
        prepareStatement("select * from " + TAB + " where id = ?");
        IndexStatsUtil.IdxStats[] newStats =
                stats.getNewStatsTable(TAB, oldStats);
        assertEquals(oldStats.length, newStats.length);
        for (int i=0; i < oldStats.length; i++) {
            assertEquals(keepDisposable, newStats[i].after(oldStats[i]));
        }

        // Cleanup
        dropTable(TAB);
        stats.release();
    }
View Full Code Here

    /**
     * Test for update statistics
     */
    public void testUpdateStatistics() throws SQLException {
        // Helper object to obtain information about index statistics.
        IndexStatsUtil stats = new IndexStatsUtil(openDefaultConnection());
        Statement s = createStatement();
        //following should fail because table APP.T1 does not exist
        assertStatementError("42Y55", s,
            "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
        s.executeUpdate("CREATE TABLE t1 (c11 int, c12 varchar(128))");
        //following will pass now because we have created APP.T1
        s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1',null)");
        //following should fail because index I1 does not exist on table APP.T1
        assertStatementError("42X65", s,
            "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
        s.executeUpdate("CREATE INDEX i1 on t1(c12)");
        //following will pass now because we have created index I1 on APP.T1
        s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");

        //The following set of subtest will ensure that when an index is
        //created on a table when there is no data in the table, then Derby
        //will not generate a row for it in sysstatistics table. If the index
        //is created after the table has data on it, there will be a row for
        //it in sysstatistics table. In order to generate statistics for the
        //first index, users can run the stored procedure
        //SYSCS_UPDATE_STATISTICS
        //So far the table t1 is empty and we have already created index I1 on
        //it. Since three was no data in the table when index I1 was created,
        //there will be no row in sysstatistics table
        stats.assertNoStats();
        //Now insert some data into t1 and then create a new index on the
        //table. This will cause sysstatistics table to have one row for this
        //new index. Old index will still not have a row for it in
        //sysstatistics table
        s.executeUpdate("INSERT INTO T1 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d')");
        s.executeUpdate("CREATE INDEX i2 ON t1(c11)");
        stats.assertStats(1);
        //Now update the statistics for the old index I1 using the new stored
        //procedure. Doing this should add a row for it in sysstatistics table
        s.execute("CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('APP','T1','I1')");
        stats.assertStats(2);

        //calls to system procedure for update statistics is internally
        //converted into ALTER TABLE ... sql but that generated sql format
        //is not available to end user to issue directly. Write a test case
        //for that sql syntax
        assertStatementError("42X01", s,
            "ALTER TABLE APP.T1 ALL UPDATE STATISTICS");
        assertStatementError("42X01", s,
            "ALTER TABLE APP.T1 UPDATE STATISTICS I1");
        //cleanup
        s.executeUpdate("DROP TABLE t1");

        //Try update statistics on global temporary table
    s.executeUpdate("declare global temporary table SESSION.t1(c11 int, c12 int) on commit delete rows not logged");
    s.executeUpdate("insert into session.t1 values(11, 1)");
        //following should fail because update statistics can't be issued on
    //global temporary tables
        assertStatementError("42995", s,
            "CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('SESSION','T1',null)");
       
        //Following test will show that updating the statistics will make a
        //query pickup better index compare to prior to statistics availability.
        //
        //Check statistics update causes most efficient index usage
        //Create a table with 2 non-unique indexes on 2 different columns.
        //The indexes are created when the table is still empty and hence
        //there are no statistics available for them in sys.sysstatistics.
        //The table looks as follows
        //        create table t2(c21 int, c22 char(14), c23 char(200))
        //        create index t2i1 on t2(c21)
        //        create index t2i2 on t2(c22)
        //Load the data into the table and running following query will
        //pickup index t2i1 on column c21
        //        select * from t2 where c21=? and c22=?
        //But once you make the statistics available for t2i2, the query
        //will pickup index t2i2 on column c22 for the query above
        //
        //Start of test case for better index selection after statistics
        //availability
        s.executeUpdate("CREATE TABLE t2(c21 int, c22 char(14), c23 char(200))");
        //No statistics will be created for the 2 indexes because the table is
        //empty
        s.executeUpdate("CREATE INDEX t2i1 ON t2(c21)");
        s.executeUpdate("CREATE INDEX t2i2 ON t2(c22)");
        stats.assertNoStats();
       
        PreparedStatement ps = prepareStatement("INSERT INTO T2 VALUES(?,?,?)");
        for (int i=0; i<1000; i++) {
          ps.setInt(1, i%2);
            ps.setString(2, "Tuple " +i);
            ps.setString(3, "any value");
            ps.addBatch();
        }
        ps.executeBatch();

    s.execute("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
   
    //Executing the query below and looking at it's plan will show that
    //we picked index T2I1 rather than T2I2 because there are no
    //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

TOP

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

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.