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

Example 36 with Explain

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

the class TestOptimizations method testUseCoveringIndex.

private void testUseCoveringIndex() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TABLE_A(id IDENTITY PRIMARY KEY NOT NULL, " + "name VARCHAR NOT NULL, active BOOLEAN DEFAULT TRUE, " + "UNIQUE KEY TABLE_A_UK (name) )");
    stat.execute("CREATE TABLE TABLE_B(id IDENTITY PRIMARY KEY NOT NULL,  " + "TABLE_a_id BIGINT NOT NULL,  createDate TIMESTAMP DEFAULT NOW(), " + "UNIQUE KEY TABLE_B_UK (table_a_id, createDate), " + "FOREIGN KEY (table_a_id) REFERENCES TABLE_A(id) )");
    stat.execute("INSERT INTO TABLE_A (name)  SELECT 'package_' || CAST(X as VARCHAR) " + "FROM SYSTEM_RANGE(1, 100)  WHERE X <= 100");
    stat.execute("INSERT INTO TABLE_B (table_a_id, createDate)  SELECT " + "CASE WHEN table_a_id = 0 THEN 1 ELSE table_a_id END, createDate " + "FROM ( SELECT ROUND((RAND() * 100)) AS table_a_id, " + "DATEADD('SECOND', X, NOW()) as createDate FROM SYSTEM_RANGE(1, 50000) " + "WHERE X < 50000  )");
    stat.execute("CREATE INDEX table_b_idx ON table_b(table_a_id, id)");
    stat.execute("ANALYZE");
    ResultSet rs = stat.executeQuery("EXPLAIN ANALYZE SELECT MAX(b.id) as id " + "FROM table_b b JOIN table_a a ON b.table_a_id = a.id GROUP BY b.table_a_id " + "HAVING A.ACTIVE = TRUE");
    rs.next();
    assertContains(rs.getString(1), "/* PUBLIC.TABLE_B_IDX: TABLE_A_ID = A.ID */");
    rs = stat.executeQuery("EXPLAIN ANALYZE SELECT MAX(id) FROM table_b GROUP BY table_a_id");
    rs.next();
    assertContains(rs.getString(1), "/* PUBLIC.TABLE_B_IDX");
    conn.close();
}

Example 37 with Explain

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

the class TestOptimizations method testConstantTypeConversionToColumnType.

private void testConstantTypeConversionToColumnType() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations;IGNORECASE=TRUE");
    Statement stat = conn.createStatement();
    stat.executeUpdate("CREATE TABLE test (x int)");
    ResultSet resultSet;
    resultSet = stat.executeQuery("EXPLAIN SELECT x FROM test WHERE x = '5'");
    assertTrue(resultSet.next());
    // String constant '5' has been converted to int constant 5 on
    // optimization
    assertTrue(resultSet.getString(1).endsWith("X = 5"));
    stat.execute("drop table test");
    conn.close();
}

Example 38 with Explain

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

the class TestTableEngines method checkPlan.

private void checkPlan(Statement stat, String sql) throws SQLException {
    ResultSet rs = stat.executeQuery("EXPLAIN " + sql);
    assertTrue(rs.next());
    String plan = rs.getString(1);
    assertEquals(normalize(sql), normalize(plan));
}

Example 39 with Explain

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

the class TestSpatial method testMemorySpatialIndex.

/**
 * Test the in the in-memory spatial index
 */
private void testMemorySpatialIndex() throws SQLException {
    deleteDb("spatial");
    Connection conn = getConnection(URL);
    Statement stat = conn.createStatement();
    stat.execute("create memory table test(id int primary key, polygon geometry)");
    stat.execute("create spatial index idx_test_polygon on test(polygon)");
    stat.execute("insert into test values(1, 'POLYGON ((1 1, 1 2, 2 2, 1 1))')");
    stat.execute("insert into test values(2, null)");
    ResultSet rs;
    // an query that can not possibly return a result
    rs = stat.executeQuery("select * from test " + "where polygon && 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry " + "and polygon && 'POLYGON ((10 10, 10 20, 20 20, 10 10))'::Geometry");
    assertFalse(rs.next());
    rs = stat.executeQuery("explain select * from test " + "where polygon && 'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry");
    rs.next();
    if (config.mvStore) {
        assertContains(rs.getString(1), "/* PUBLIC.IDX_TEST_POLYGON: POLYGON &&");
    }
    // TODO equality should probably also use the spatial index
    // rs = stat.executeQuery("explain select * from test " +
    // "where polygon = 'POLYGON ((1 1, 1 2, 2 2, 1 1))'");
    // rs.next();
    // assertContains(rs.getString(1),
    // "/* PUBLIC.IDX_TEST_POLYGON: POLYGON =");
    // these queries actually have no meaning in the context of a spatial
    // index, but
    // check them anyhow
    stat.executeQuery("select * from test where polygon > " + "'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry");
    stat.executeQuery("select * from test where polygon < " + "'POLYGON ((1 1, 1 2, 2 2, 1 1))'::Geometry");
    rs = stat.executeQuery("select * from test " + "where intersects(polygon, 'POLYGON ((1 1, 1 2, 2 2, 1 1))')");
    assertTrue(rs.next());
    rs = stat.executeQuery("select * from test " + "where intersects(polygon, 'POINT (1 1)')");
    assertTrue(rs.next());
    rs = stat.executeQuery("select * from test " + "where intersects(polygon, 'POINT (0 0)')");
    assertFalse(rs.next());
    stat.execute("drop table test");
    conn.close();
    deleteDb("spatial");
}