Examples with Analyze org.h2.command.ddl.Analyze used on opensource projects

Example 16 with Analyze

use of org.h2.command.ddl.Analyze 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 17 with Analyze

use of org.h2.command.ddl.Analyze 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 18 with Analyze

use of org.h2.command.ddl.Analyze in project h2database by h2database.

the class TestOptimizations method testDistinctOptimization.

private void testDistinctOptimization() throws SQLException {
    deleteDb("optimizations");
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, " + "NAME VARCHAR, TYPE INT)");
    stat.execute("CREATE INDEX IDX_TEST_TYPE ON TEST(TYPE)");
    Random random = new Random(1);
    int len = getSize(10000, 100000);
    int[] groupCount = new int[10];
    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
    for (int i = 0; i < len; i++) {
        prep.setInt(1, i);
        prep.setString(2, "Hello World");
        int type = random.nextInt(10);
        groupCount[type]++;
        prep.setInt(3, type);
        prep.execute();
    }
    ResultSet rs;
    rs = stat.executeQuery("SELECT TYPE, COUNT(*) FROM TEST " + "GROUP BY TYPE ORDER BY TYPE");
    for (int i = 0; rs.next(); i++) {
        assertEquals(i, rs.getInt(1));
        assertEquals(groupCount[i], rs.getInt(2));
    }
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT DISTINCT TYPE FROM TEST " + "ORDER BY TYPE");
    for (int i = 0; rs.next(); i++) {
        assertEquals(i, rs.getInt(1));
    }
    assertFalse(rs.next());
    stat.execute("ANALYZE");
    rs = stat.executeQuery("SELECT DISTINCT TYPE FROM TEST " + "ORDER BY TYPE");
    for (int i = 0; i < 10; i++) {
        assertTrue(rs.next());
        assertEquals(i, rs.getInt(1));
    }
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT DISTINCT TYPE FROM TEST " + "ORDER BY TYPE LIMIT 5 OFFSET 2");
    for (int i = 2; i < 7; i++) {
        assertTrue(rs.next());
        assertEquals(i, rs.getInt(1));
    }
    assertFalse(rs.next());
    rs = stat.executeQuery("SELECT DISTINCT TYPE FROM TEST " + "ORDER BY TYPE LIMIT -1 OFFSET 0 SAMPLE_SIZE 3");
    // must have at least one row
    assertTrue(rs.next());
    for (int i = 0; i < 3; i++) {
        rs.getInt(1);
        if (i > 0 && !rs.next()) {
            break;
        }
    }
    assertFalse(rs.next());
    conn.close();
}