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

Example 11 with Explain

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

the class TestOptimizations method testSortIndex.

private void testSortIndex() throws SQLException {
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("drop table test if exists");
    stat.execute("create table test(id int)");
    stat.execute("create index idx_id_desc on test(id desc)");
    stat.execute("create index idx_id_asc on test(id)");
    ResultSet rs;
    rs = stat.executeQuery("explain select * from test " + "where id > 10 order by id");
    rs.next();
    assertContains(rs.getString(1), "IDX_ID_ASC");
    rs = stat.executeQuery("explain select * from test " + "where id < 10 order by id desc");
    rs.next();
    assertContains(rs.getString(1), "IDX_ID_DESC");
    rs.next();
    stat.execute("drop table test");
    conn.close();
}

Example 12 with Explain

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

the class TestOptimizations method testConvertOrToIn.

private void testConvertOrToIn() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key, name varchar(255))");
    stat.execute("insert into test values" + "(1, '1'), (2, '2'), (3, '3'), (4, '4'), (5, '5')");
    ResultSet rs = stat.executeQuery("EXPLAIN PLAN FOR SELECT * " + "FROM test WHERE ID=1 OR ID=2 OR ID=3 OR ID=4 OR ID=5");
    rs.next();
    assertContains(rs.getString(1), "ID IN(1, 2, 3, 4, 5)");
    rs = stat.executeQuery("SELECT COUNT(*) FROM test " + "WHERE ID=1 OR ID=2 OR ID=3 OR ID=4 OR ID=5");
    rs.next();
    assertEquals(5, rs.getInt(1));
    conn.close();
}

Example 13 with Explain

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

the class TestOptimizations method testLike.

private void testLike() throws Exception {
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create table test(name varchar primary key) as " + "select x from system_range(1, 10)");
    ResultSet rs = stat.executeQuery("explain select * from test " + "where name like ? || '%' {1: 'Hello'}");
    rs.next();
    // ensure the ID = 10 part is evaluated first
    assertContains(rs.getString(1), "PRIMARY_KEY_");
    stat.execute("drop table test");
    conn.close();
}

Example 14 with Explain

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

the class TestOptimizations method testOptimizeInJoin.

private void testOptimizeInJoin() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int primary key)");
    stat.execute("insert into test select x from system_range(1, 1000)");
    ResultSet rs = stat.executeQuery("explain select * " + "from test where id in (400, 300)");
    rs.next();
    String plan = rs.getString(1);
    if (plan.indexOf("/* PUBLIC.PRIMARY_KEY_") < 0) {
        fail("Expected using the primary key, got: " + plan);
    }
    conn.close();
}

Example 15 with Explain

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

the class TestOptimizations method testExistsSubquery.

private void testExistsSubquery() throws Exception {
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create table test(id int) as select x from system_range(1, 10)");
    ResultSet rs = stat.executeQuery("explain select * from test " + "where exists(select 1 from test, test, test) and id = 10");
    rs.next();
    // ensure the ID = 10 part is evaluated first
    assertContains(rs.getString(1), "WHERE (ID = 10)");
    stat.execute("drop table test");
    conn.close();
}