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

Example 36 with Plan

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

the class TestOptimizations method assertExplainRoundTrip.

private void assertExplainRoundTrip(Connection conn, String sql) throws SQLException {
    Statement stat = conn.createStatement();
    ResultSet rs = stat.executeQuery("explain " + sql);
    rs.next();
    String plan = rs.getString(1).toLowerCase();
    plan = plan.replaceAll("\\s+", " ");
    plan = plan.replaceAll("/\\*[^\\*]*\\*/", "");
    plan = plan.replaceAll("\\s+", " ");
    plan = StringUtils.replaceAll(plan, "system_range(1, 1)", "dual");
    plan = plan.replaceAll("\\( ", "\\(");
    plan = plan.replaceAll(" \\)", "\\)");
    assertEquals(plan, sql);
}

Example 37 with Plan

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

the class TestOptimizations method testMinMaxNullOptimization.

private void testMinMaxNullOptimization() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    Random random = new Random(1);
    int len = getSize(50, 500);
    for (int i = 0; i < len; i++) {
        stat.execute("drop table if exists test");
        stat.execute("create table test(x int)");
        if (random.nextBoolean()) {
            int count = random.nextBoolean() ? 1 : 1 + random.nextInt(len);
            if (count > 0) {
                stat.execute("insert into test select null " + "from system_range(1, " + count + ")");
            }
        }
        int maxExpected = -1;
        int minExpected = -1;
        if (random.nextInt(10) != 1) {
            minExpected = 1;
            maxExpected = 1 + random.nextInt(len);
            stat.execute("insert into test select x " + "from system_range(1, " + maxExpected + ")");
        }
        String sql = "create index idx on test(x";
        if (random.nextBoolean()) {
            sql += " desc";
        }
        if (random.nextBoolean()) {
            if (random.nextBoolean()) {
                sql += " nulls first";
            } else {
                sql += " nulls last";
            }
        }
        sql += ")";
        stat.execute(sql);
        ResultSet rs = stat.executeQuery("explain select min(x), max(x) from test");
        rs.next();
        if (!config.mvcc) {
            String plan = rs.getString(1);
            assertContains(plan, "direct");
        }
        rs = stat.executeQuery("select min(x), max(x) from test");
        rs.next();
        int min = rs.getInt(1);
        if (rs.wasNull()) {
            min = -1;
        }
        int max = rs.getInt(2);
        if (rs.wasNull()) {
            max = -1;
        }
        assertEquals(minExpected, min);
        assertEquals(maxExpected, max);
    }
    conn.close();
}

Example 38 with Plan

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

the class TestOptimizations method testOrderedIndexes.

/**
 * Where there are multiple indices, and we have an ORDER BY, select the
 * index that already has the required ordering.
 */
private void testOrderedIndexes() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE my_table(K1 INT, K2 INT, " + "VAL VARCHAR, PRIMARY KEY(K1, K2))");
    stat.execute("CREATE INDEX my_index ON my_table(K1, VAL)");
    ResultSet rs = stat.executeQuery("EXPLAIN PLAN FOR SELECT * FROM my_table WHERE K1=7 " + "ORDER BY K1, VAL");
    rs.next();
    assertContains(rs.getString(1), "/* PUBLIC.MY_INDEX: K1 = 7 */");
    stat.execute("DROP TABLE my_table");
    // where we have two covering indexes, make sure
    // we choose the one that covers more
    stat.execute("CREATE TABLE my_table(K1 INT, K2 INT, VAL VARCHAR)");
    stat.execute("CREATE INDEX my_index1 ON my_table(K1, K2)");
    stat.execute("CREATE INDEX my_index2 ON my_table(K1, K2, VAL)");
    rs = stat.executeQuery("EXPLAIN PLAN FOR SELECT * FROM my_table WHERE K1=7 " + "ORDER BY K1, K2, VAL");
    rs.next();
    assertContains(rs.getString(1), "/* PUBLIC.MY_INDEX2: K1 = 7 */");
    conn.close();
}

Example 39 with Plan

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

the class TestOptimizations method testMultiColumnRangeQuery.

private void testMultiColumnRangeQuery() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE Logs(id INT PRIMARY KEY, type INT)");
    stat.execute("CREATE unique INDEX type_index ON Logs(type, id)");
    stat.execute("INSERT INTO Logs SELECT X, MOD(X, 3) " + "FROM SYSTEM_RANGE(1, 1000)");
    stat.execute("ANALYZE SAMPLE_SIZE 0");
    ResultSet rs;
    rs = stat.executeQuery("EXPLAIN SELECT id FROM Logs " + "WHERE id < 100 and type=2 AND id<100");
    rs.next();
    String plan = rs.getString(1);
    assertContains(plan, "TYPE_INDEX");
    conn.close();
}

Example 40 with Plan

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

the class TestOptimizations method testIndexUseDespiteNullsFirst.

private void testIndexUseDespiteNullsFirst() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE my_table(K1 INT)");
    stat.execute("CREATE INDEX my_index ON my_table(K1)");
    stat.execute("INSERT INTO my_table VALUES (NULL)");
    stat.execute("INSERT INTO my_table VALUES (1)");
    stat.execute("INSERT INTO my_table VALUES (2)");
    ResultSet rs;
    String result;
    rs = stat.executeQuery("EXPLAIN PLAN FOR SELECT * FROM my_table " + "ORDER BY K1 ASC NULLS FIRST");
    rs.next();
    result = rs.getString(1);
    assertContains(result, "/* index sorted */");
    rs = stat.executeQuery("SELECT * FROM my_table " + "ORDER BY K1 ASC NULLS FIRST");
    rs.next();
    assertNull(rs.getObject(1));
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs.next();
    assertEquals(2, rs.getInt(1));
    // ===
    rs = stat.executeQuery("EXPLAIN PLAN FOR SELECT * FROM my_table " + "ORDER BY K1 DESC NULLS FIRST");
    rs.next();
    result = rs.getString(1);
    if (result.contains("/* index sorted */")) {
        fail(result + " does not contain: /* index sorted */");
    }
    rs = stat.executeQuery("SELECT * FROM my_table " + "ORDER BY K1 DESC NULLS FIRST");
    rs.next();
    assertNull(rs.getObject(1));
    rs.next();
    assertEquals(2, rs.getInt(1));
    rs.next();
    assertEquals(1, rs.getInt(1));
    // ===
    rs = stat.executeQuery("EXPLAIN PLAN FOR SELECT * FROM my_table " + "ORDER BY K1 ASC NULLS LAST");
    rs.next();
    result = rs.getString(1);
    if (result.contains("/* index sorted */")) {
        fail(result + " does not contain: /* index sorted */");
    }
    rs = stat.executeQuery("SELECT * FROM my_table " + "ORDER BY K1 ASC NULLS LAST");
    rs.next();
    assertEquals(1, rs.getInt(1));
    rs.next();
    assertEquals(2, rs.getInt(1));
    rs.next();
    assertNull(rs.getObject(1));
    // TODO: Test "EXPLAIN PLAN FOR SELECT * FROM my_table ORDER BY K1 DESC NULLS FIRST"
    // Currently fails, as using the index when sorting DESC is currently not supported.
    stat.execute("DROP TABLE my_table");
    conn.close();
}