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

Example 1 with Analyze

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

the class Parser method parsePrepared.

private Prepared parsePrepared() {
    int start = lastParseIndex;
    Prepared c = null;
    String token = currentToken;
    if (token.length() == 0) {
        c = new NoOperation(session);
    } else {
        char first = token.charAt(0);
        switch(first) {
            case '?':
                // read the ? as a parameter
                readTerm();
                // this is an 'out' parameter - set a dummy value
                parameters.get(0).setValue(ValueNull.INSTANCE);
                read("=");
                read("CALL");
                c = parseCall();
                break;
            case '(':
                c = parseSelect();
                break;
            case 'a':
            case 'A':
                if (readIf("ALTER")) {
                    c = parseAlter();
                } else if (readIf("ANALYZE")) {
                    c = parseAnalyze();
                }
                break;
            case 'b':
            case 'B':
                if (readIf("BACKUP")) {
                    c = parseBackup();
                } else if (readIf("BEGIN")) {
                    c = parseBegin();
                }
                break;
            case 'c':
            case 'C':
                if (readIf("COMMIT")) {
                    c = parseCommit();
                } else if (readIf("CREATE")) {
                    c = parseCreate();
                } else if (readIf("CALL")) {
                    c = parseCall();
                } else if (readIf("CHECKPOINT")) {
                    c = parseCheckpoint();
                } else if (readIf("COMMENT")) {
                    c = parseComment();
                }
                break;
            case 'd':
            case 'D':
                if (readIf("DELETE")) {
                    c = parseDelete();
                } else if (readIf("DROP")) {
                    c = parseDrop();
                } else if (readIf("DECLARE")) {
                    // support for DECLARE GLOBAL TEMPORARY TABLE...
                    c = parseCreate();
                } else if (readIf("DEALLOCATE")) {
                    c = parseDeallocate();
                }
                break;
            case 'e':
            case 'E':
                if (readIf("EXPLAIN")) {
                    c = parseExplain();
                } else if (readIf("EXECUTE")) {
                    c = parseExecute();
                }
                break;
            case 'f':
            case 'F':
                if (isToken("FROM")) {
                    c = parseSelect();
                }
                break;
            case 'g':
            case 'G':
                if (readIf("GRANT")) {
                    c = parseGrantRevoke(CommandInterface.GRANT);
                }
                break;
            case 'h':
            case 'H':
                if (readIf("HELP")) {
                    c = parseHelp();
                }
                break;
            case 'i':
            case 'I':
                if (readIf("INSERT")) {
                    c = parseInsert();
                }
                break;
            case 'm':
            case 'M':
                if (readIf("MERGE")) {
                    c = parseMerge();
                }
                break;
            case 'p':
            case 'P':
                if (readIf("PREPARE")) {
                    c = parsePrepare();
                }
                break;
            case 'r':
            case 'R':
                if (readIf("ROLLBACK")) {
                    c = parseRollback();
                } else if (readIf("REVOKE")) {
                    c = parseGrantRevoke(CommandInterface.REVOKE);
                } else if (readIf("RUNSCRIPT")) {
                    c = parseRunScript();
                } else if (readIf("RELEASE")) {
                    c = parseReleaseSavepoint();
                } else if (readIf("REPLACE")) {
                    c = parseReplace();
                }
                break;
            case 's':
            case 'S':
                if (isToken("SELECT")) {
                    c = parseSelect();
                } else if (readIf("SET")) {
                    c = parseSet();
                } else if (readIf("SAVEPOINT")) {
                    c = parseSavepoint();
                } else if (readIf("SCRIPT")) {
                    c = parseScript();
                } else if (readIf("SHUTDOWN")) {
                    c = parseShutdown();
                } else if (readIf("SHOW")) {
                    c = parseShow();
                }
                break;
            case 't':
            case 'T':
                if (readIf("TRUNCATE")) {
                    c = parseTruncate();
                }
                break;
            case 'u':
            case 'U':
                if (readIf("UPDATE")) {
                    c = parseUpdate();
                } else if (readIf("USE")) {
                    c = parseUse();
                }
                break;
            case 'v':
            case 'V':
                if (readIf("VALUES")) {
                    c = parseValues();
                }
                break;
            case 'w':
            case 'W':
                if (readIf("WITH")) {
                    c = parseWithStatementOrQuery();
                }
                break;
            case ';':
                c = new NoOperation(session);
                break;
            default:
                throw getSyntaxError();
        }
        if (indexedParameterList != null) {
            for (int i = 0, size = indexedParameterList.size(); i < size; i++) {
                if (indexedParameterList.get(i) == null) {
                    indexedParameterList.set(i, new Parameter(i));
                }
            }
            parameters = indexedParameterList;
        }
        if (readIf("{")) {
            do {
                int index = (int) readLong() - 1;
                if (index < 0 || index >= parameters.size()) {
                    throw getSyntaxError();
                }
                Parameter p = parameters.get(index);
                if (p == null) {
                    throw getSyntaxError();
                }
                read(":");
                Expression expr = readExpression();
                expr = expr.optimize(session);
                p.setValue(expr.getValue(session));
            } while (readIf(","));
            read("}");
            for (Parameter p : parameters) {
                p.checkSet();
            }
            parameters.clear();
        }
    }
    if (c == null) {
        throw getSyntaxError();
    }
    setSQL(c, null, start);
    return c;
}

Example 2 with Analyze

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

the class Analyze method analyzeTable.

/**
 * Analyze this table.
 *
 * @param session the session
 * @param table the table
 * @param sample the number of sample rows
 * @param manual whether the command was called by the user
 */
public static void analyzeTable(Session session, Table table, int sample, boolean manual) {
    if (table.getTableType() != TableType.TABLE || table.isHidden() || session == null) {
        return;
    }
    if (!manual) {
        if (session.getDatabase().isSysTableLocked()) {
            return;
        }
        if (table.hasSelectTrigger()) {
            return;
        }
    }
    if (table.isTemporary() && !table.isGlobalTemporary() && session.findLocalTempTable(table.getName()) == null) {
        return;
    }
    if (table.isLockedExclusively() && !table.isLockedExclusivelyBy(session)) {
        return;
    }
    if (!session.getUser().hasRight(table, Right.SELECT)) {
        return;
    }
    if (session.getCancel() != 0) {
        // if the connection is closed and there is something to undo
        return;
    }
    Column[] columns = table.getColumns();
    if (columns.length == 0) {
        return;
    }
    Database db = session.getDatabase();
    StatementBuilder buff = new StatementBuilder("SELECT ");
    for (Column col : columns) {
        buff.appendExceptFirst(", ");
        int type = col.getType();
        if (type == Value.BLOB || type == Value.CLOB) {
            // can not index LOB columns, so calculating
            // the selectivity is not required
            buff.append("MAX(NULL)");
        } else {
            buff.append("SELECTIVITY(").append(col.getSQL()).append(')');
        }
    }
    buff.append(" FROM ").append(table.getSQL());
    if (sample > 0) {
        buff.append(" LIMIT ? SAMPLE_SIZE ? ");
    }
    String sql = buff.toString();
    Prepared command = session.prepare(sql);
    if (sample > 0) {
        ArrayList<Parameter> params = command.getParameters();
        params.get(0).setValue(ValueInt.get(1));
        params.get(1).setValue(ValueInt.get(sample));
    }
    ResultInterface result = command.query(0);
    result.next();
    for (int j = 0; j < columns.length; j++) {
        Value v = result.currentRow()[j];
        if (v != ValueNull.INSTANCE) {
            int selectivity = v.getInt();
            columns[j].setSelectivity(selectivity);
        }
    }
    db.updateMeta(session, table);
}

Example 3 with Analyze

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

the class Session method commit.

/**
 * Commit the current transaction. If the statement was not a data
 * definition statement, and if there are temporary tables that should be
 * dropped or truncated at commit, this is done as well.
 *
 * @param ddl if the statement was a data definition statement
 */
public void commit(boolean ddl) {
    checkCommitRollback();
    currentTransactionName = null;
    transactionStart = 0;
    if (transaction != null) {
        // TODO should not rely on locking
        if (!locks.isEmpty()) {
            for (Table t : locks) {
                if (t instanceof MVTable) {
                    ((MVTable) t).commit();
                }
            }
        }
        transaction.commit();
        transaction = null;
    }
    if (containsUncommitted()) {
        // need to commit even if rollback is not possible
        // (create/drop table and so on)
        database.commit(this);
    }
    removeTemporaryLobs(true);
    if (undoLog.size() > 0) {
        // commit the rows when using MVCC
        if (database.isMultiVersion()) {
            ArrayList<Row> rows = New.arrayList();
            synchronized (database) {
                while (undoLog.size() > 0) {
                    UndoLogRecord entry = undoLog.getLast();
                    entry.commit();
                    rows.add(entry.getRow());
                    undoLog.removeLast(false);
                }
                for (Row r : rows) {
                    r.commit();
                }
            }
        }
        undoLog.clear();
    }
    if (!ddl) {
        // do not clean the temp tables if the last command was a
        // create/drop
        cleanTempTables(false);
        if (autoCommitAtTransactionEnd) {
            autoCommit = true;
            autoCommitAtTransactionEnd = false;
        }
    }
    int rows = getDatabase().getSettings().analyzeSample / 10;
    if (tablesToAnalyze != null) {
        for (Table table : tablesToAnalyze) {
            Analyze.analyzeTable(this, table, rows, false);
        }
        // analyze can lock the meta
        database.unlockMeta(this);
    }
    tablesToAnalyze = null;
    endTransaction();
}

Example 4 with Analyze

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

the class TestOptimizations method testAnalyzeLob.

private void testAnalyzeLob() throws Exception {
    Connection conn = getConnection("optimizations");
    Statement stat = conn.createStatement();
    stat.execute("create table test(v varchar, b binary, cl clob, bl blob) as " + "select ' ', '00', ' ', '00' from system_range(1, 100)");
    stat.execute("analyze");
    ResultSet rs = stat.executeQuery("select column_name, selectivity " + "from information_schema.columns where table_name='TEST'");
    rs.next();
    assertEquals("V", rs.getString(1));
    assertEquals(1, rs.getInt(2));
    rs.next();
    assertEquals("B", rs.getString(1));
    assertEquals(1, rs.getInt(2));
    rs.next();
    assertEquals("CL", rs.getString(1));
    assertEquals(50, rs.getInt(2));
    rs.next();
    assertEquals("BL", rs.getString(1));
    assertEquals(50, rs.getInt(2));
    stat.execute("drop table test");
    conn.close();
}

Example 5 with Analyze

use of org.h2.command.ddl.Analyze in project ignite by apache.

the class GridH2TableSelfTest method checkQueryPlan.

/**
     * Check query plan to correctly select index.
     *
     * @param conn Connection.
     * @param sql Select.
     * @param search Search token in result.
     * @throws SQLException If failed.
     */
private void checkQueryPlan(Connection conn, String sql, String search) throws SQLException {
    try (Statement s = conn.createStatement()) {
        try (ResultSet r = s.executeQuery("EXPLAIN ANALYZE " + sql)) {
            assertTrue(r.next());
            String plan = r.getString(1);
            assertTrue("Execution plan for '" + sql + "' query should contain '" + search + "'", plan.contains(search));
        }
    }
}