Examples with Plan org.h2.table.Plan used on opensource projects

Example 31 with Plan

use of org.h2.table.Plan in project h2database by h2database.

the class TestMVTableEngine method testExplainAnalyze.

private void testExplainAnalyze() throws Exception {
    if (config.memory) {
        return;
    }
    Connection conn;
    Statement stat;
    deleteDb(getTestName());
    String url = getTestName() + ";MV_STORE=TRUE";
    url = getURL(url, true);
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("create table test(id identity, name varchar) as " + "select x, space(1000) from system_range(1, 1000)");
    ResultSet rs;
    conn.close();
    conn = getConnection(url);
    stat = conn.createStatement();
    rs = stat.executeQuery("explain analyze select * from test");
    rs.next();
    String plan = rs.getString(1);
    // expect about 1000 reads
    String readCount = plan.substring(plan.indexOf("reads: "));
    readCount = readCount.substring("reads: ".length(), readCount.indexOf('\n'));
    int rc = Integer.parseInt(readCount);
    assertTrue(plan, rc >= 1000 && rc <= 1200);
    conn.close();
}

Example 32 with Plan

use of org.h2.table.Plan in project h2database by h2database.

the class TestMVTableEngine method testCount.

private void testCount() throws Exception {
    if (config.memory) {
        return;
    }
    Connection conn;
    Connection conn2;
    Statement stat;
    Statement stat2;
    deleteDb(getTestName());
    String url = getTestName() + ";MV_STORE=TRUE;MVCC=TRUE";
    url = getURL(url, true);
    conn = getConnection(url);
    stat = conn.createStatement();
    stat.execute("create table test(id int)");
    stat.execute("create table test2(id int)");
    stat.execute("insert into test select x from system_range(1, 10000)");
    conn.close();
    ResultSet rs;
    String plan;
    conn2 = getConnection(url);
    stat2 = conn2.createStatement();
    rs = stat2.executeQuery("explain analyze select count(*) from test");
    rs.next();
    plan = rs.getString(1);
    assertTrue(plan, plan.indexOf("reads:") < 0);
    conn = getConnection(url);
    stat = conn.createStatement();
    conn.setAutoCommit(false);
    stat.execute("insert into test select x from system_range(1, 1000)");
    rs = stat.executeQuery("select count(*) from test");
    rs.next();
    assertEquals(11000, rs.getInt(1));
    // not yet committed
    rs = stat2.executeQuery("explain analyze select count(*) from test");
    rs.next();
    plan = rs.getString(1);
    // transaction log is small, so no need to read the table
    assertTrue(plan, plan.indexOf("reads:") < 0);
    rs = stat2.executeQuery("select count(*) from test");
    rs.next();
    assertEquals(10000, rs.getInt(1));
    stat.execute("insert into test2 select x from system_range(1, 11000)");
    rs = stat2.executeQuery("explain analyze select count(*) from test");
    rs.next();
    plan = rs.getString(1);
    // transaction log is larger than the table, so read the table
    assertContains(plan, "reads:");
    rs = stat2.executeQuery("select count(*) from test");
    rs.next();
    assertEquals(10000, rs.getInt(1));
    conn2.close();
    conn.close();
}

Example 33 with Plan

use of org.h2.table.Plan in project h2database by h2database.

the class TestOptimizerHints method checkPlanComma.

private void checkPlanComma(Statement s, String... t) throws SQLException {
    StatementBuilder from = new StatementBuilder();
    for (String table : t) {
        from.appendExceptFirst(", ");
        from.append(table);
    }
    String plan = plan(s, "select 1 from " + from.toString() + " where t1.id = t2.t1_id " + "and t2.id = t4.t2_id and t3.id = t4.t3_id");
    int prev = plan.indexOf("FROM PUBLIC." + t[0].toUpperCase());
    for (int i = 1; i < t.length; i++) {
        int next = plan.indexOf("INNER JOIN PUBLIC." + t[i].toUpperCase());
        assertTrue("Wrong plan for : " + Arrays.toString(t) + "\n" + plan, next > prev);
        prev = next;
    }
}

Example 34 with Plan

use of org.h2.table.Plan in project h2database by h2database.

the class TestOptimizerHints method checkPlanJoin.

private void checkPlanJoin(Statement s, boolean on, boolean left, String... t) throws SQLException {
    StatementBuilder from = new StatementBuilder();
    for (int i = 0; i < t.length; i++) {
        if (i != 0) {
            if (left) {
                from.append(" left join ");
            } else {
                from.append(" inner join ");
            }
        }
        from.append(t[i]);
        if (on && i != 0) {
            from.append(" on 1=1 ");
        }
    }
    String plan = plan(s, "select 1 from " + from.toString() + " where t1.id = t2.t1_id " + "and t2.id = t4.t2_id and t3.id = t4.t3_id");
    int prev = plan.indexOf("FROM PUBLIC." + t[0].toUpperCase());
    for (int i = 1; i < t.length; i++) {
        int next = plan.indexOf((!left ? "INNER JOIN PUBLIC." : on ? "LEFT OUTER JOIN PUBLIC." : "PUBLIC.") + t[i].toUpperCase());
        if (prev > next) {
            System.err.println(plan);
            fail("Wrong plan for : " + Arrays.toString(t) + "\n" + plan);
        }
        prev = next;
    }
}

Example 35 with Plan

use of org.h2.table.Plan in project h2database by h2database.

the class TestOptimizations method testUseIndexWhenAllColumnsNotInOrderBy.

private void testUseIndexWhenAllColumnsNotInOrderBy() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, account int, tx int)");
    stat.execute("insert into test select x, x*100, x from system_range(1, 10000)");
    stat.execute("analyze sample_size 5");
    stat.execute("create unique index idx_test_account_tx on test(account, tx desc)");
    ResultSet rs;
    rs = stat.executeQuery("explain analyze " + "select tx from test " + "where account=22 and tx<9999999 " + "order by tx desc limit 25");
    rs.next();
    String plan = rs.getString(1);
    assertContains(plan, "index sorted");
    conn.close();
}