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

Example 6 with Analyze

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

the class Parser method parseAnalyze.

private Prepared parseAnalyze() {
    Analyze command = new Analyze(session);
    if (readIf("TABLE")) {
        Table table = readTableOrView();
        command.setTable(table);
    }
    if (readIf("SAMPLE_SIZE")) {
        command.setTop(readPositiveInt());
    }
    return command;
}

Example 7 with Analyze

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

the class Parser method parseExplain.

private Explain parseExplain() {
    Explain command = new Explain(session);
    if (readIf("ANALYZE")) {
        command.setExecuteCommand(true);
    } else {
        if (readIf("PLAN")) {
            readIf("FOR");
        }
    }
    if (isToken("SELECT") || isToken("FROM") || isToken("(") || isToken("WITH")) {
        Query query = parseSelect();
        query.setNeverLazy(true);
        command.setCommand(query);
    } else if (readIf("DELETE")) {
        command.setCommand(parseDelete());
    } else if (readIf("UPDATE")) {
        command.setCommand(parseUpdate());
    } else if (readIf("INSERT")) {
        command.setCommand(parseInsert());
    } else if (readIf("MERGE")) {
        command.setCommand(parseMerge());
    } else {
        throw getSyntaxError();
    }
    return command;
}

Example 8 with Analyze

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

the class DbUpgrade method upgrade.

private static void upgrade(ConnectionInfo ci, Properties info) throws SQLException {
    String name = ci.getName();
    String data = name + Constants.SUFFIX_OLD_DATABASE_FILE;
    String index = name + ".index.db";
    String lobs = name + ".lobs.db";
    String backupData = data + ".backup";
    String backupIndex = index + ".backup";
    String backupLobs = lobs + ".backup";
    String script = null;
    try {
        if (scriptInTempDir) {
            new File(Utils.getProperty("java.io.tmpdir", ".")).mkdirs();
            script = File.createTempFile("h2dbmigration", "backup.sql").getAbsolutePath();
        } else {
            script = name + ".script.sql";
        }
        String oldUrl = "jdbc:h2v1_1:" + name + ";UNDO_LOG=0;LOG=0;LOCK_MODE=0";
        String cipher = ci.getProperty("CIPHER", null);
        if (cipher != null) {
            oldUrl += ";CIPHER=" + cipher;
        }
        Connection conn = DriverManager.getConnection(oldUrl, info);
        Statement stat = conn.createStatement();
        String uuid = UUID.randomUUID().toString();
        if (cipher != null) {
            stat.execute("script to '" + script + "' cipher aes password '" + uuid + "' --hide--");
        } else {
            stat.execute("script to '" + script + "'");
        }
        conn.close();
        FileUtils.move(data, backupData);
        FileUtils.move(index, backupIndex);
        if (FileUtils.exists(lobs)) {
            FileUtils.move(lobs, backupLobs);
        }
        ci.removeProperty("IFEXISTS", false);
        conn = new JdbcConnection(ci, true);
        stat = conn.createStatement();
        if (cipher != null) {
            stat.execute("runscript from '" + script + "' cipher aes password '" + uuid + "' --hide--");
        } else {
            stat.execute("runscript from '" + script + "'");
        }
        stat.execute("analyze");
        stat.execute("shutdown compact");
        stat.close();
        conn.close();
        if (deleteOldDb) {
            FileUtils.delete(backupData);
            FileUtils.delete(backupIndex);
            FileUtils.deleteRecursive(backupLobs, false);
        }
    } catch (Exception e) {
        if (FileUtils.exists(backupData)) {
            FileUtils.move(backupData, data);
        }
        if (FileUtils.exists(backupIndex)) {
            FileUtils.move(backupIndex, index);
        }
        if (FileUtils.exists(backupLobs)) {
            FileUtils.move(backupLobs, lobs);
        }
        FileUtils.delete(name + ".h2.db");
        throw DbException.toSQLException(e);
    } finally {
        if (script != null) {
            FileUtils.delete(script);
        }
    }
}

Example 9 with Analyze

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

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