Examples with Explain org.h2.command.dml.Explain used on opensource projects

Example 26 with Explain

use of org.h2.command.dml.Explain 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 27 with Explain

use of org.h2.command.dml.Explain 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 28 with Explain

use of org.h2.command.dml.Explain in project h2database by h2database.

the class TestOptimizations method testFastRowIdCondition.

private void testFastRowIdCondition() throws Exception {
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.executeUpdate("create table many(id int) " + "as select x from system_range(1, 10000)");
    ResultSet rs = stat.executeQuery("explain analyze select * from many " + "where _rowid_ = 400");
    rs.next();
    assertContains(rs.getString(1), "/* scanCount: 2 */");
    conn.close();
}

Example 29 with Explain

use of org.h2.command.dml.Explain in project h2database by h2database.

the class TestOptimizations method testIdentityIndexUsage.

private void testIdentityIndexUsage() throws Exception {
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create table test(a identity)");
    stat.execute("insert into test values()");
    ResultSet rs = stat.executeQuery("explain select * from test where a = 1");
    rs.next();
    assertContains(rs.getString(1), "PRIMARY_KEY");
    stat.execute("drop table test");
    conn.close();
}

Example 30 with Explain

use of org.h2.command.dml.Explain 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();
}