Search in sources :

Example 1 with IndexHints

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

the class TableFilter method getPlanSQL.

/**
 * Get the query execution plan text to use for this table filter.
 *
 * @param isJoin if this is a joined table
 * @return the SQL statement snippet
 */
public String getPlanSQL(boolean isJoin) {
    StringBuilder buff = new StringBuilder();
    if (isJoin) {
        if (joinOuter) {
            buff.append("LEFT OUTER JOIN ");
        } else {
            buff.append("INNER JOIN ");
        }
    }
    if (nestedJoin != null) {
        StringBuilder buffNested = new StringBuilder();
        TableFilter n = nestedJoin;
        do {
            buffNested.append(n.getPlanSQL(n != nestedJoin));
            buffNested.append('\n');
            n = n.getJoin();
        } while (n != null);
        String nested = buffNested.toString();
        boolean enclose = !nested.startsWith("(");
        if (enclose) {
            buff.append("(\n");
        }
        buff.append(StringUtils.indent(nested, 4, false));
        if (enclose) {
            buff.append(')');
        }
        if (isJoin) {
            buff.append(" ON ");
            if (joinCondition == null) {
                // need to have a ON expression,
                // otherwise the nesting is unclear
                buff.append("1=1");
            } else {
                buff.append(StringUtils.unEnclose(joinCondition.getSQL()));
            }
        }
        return buff.toString();
    }
    if (table.isView() && ((TableView) table).isRecursive()) {
        buff.append(table.getName());
    } else {
        buff.append(table.getSQL());
    }
    if (table.isView() && ((TableView) table).isInvalid()) {
        throw DbException.get(ErrorCode.VIEW_IS_INVALID_2, table.getName(), "not compiled");
    }
    if (alias != null) {
        buff.append(' ').append(Parser.quoteIdentifier(alias));
    }
    if (indexHints != null) {
        buff.append(" USE INDEX (");
        boolean first = true;
        for (String index : indexHints.getAllowedIndexes()) {
            if (!first) {
                buff.append(", ");
            } else {
                first = false;
            }
            buff.append(Parser.quoteIdentifier(index));
        }
        buff.append(")");
    }
    if (index != null) {
        buff.append('\n');
        StatementBuilder planBuff = new StatementBuilder();
        if (joinBatch != null) {
            IndexLookupBatch lookupBatch = joinBatch.getLookupBatch(joinFilterId);
            if (lookupBatch == null) {
                if (joinFilterId != 0) {
                    throw DbException.throwInternalError("" + joinFilterId);
                }
            } else {
                planBuff.append("batched:");
                String batchPlan = lookupBatch.getPlanSQL();
                planBuff.append(batchPlan);
                planBuff.append(" ");
            }
        }
        planBuff.append(index.getPlanSQL());
        if (!indexConditions.isEmpty()) {
            planBuff.append(": ");
            for (IndexCondition condition : indexConditions) {
                planBuff.appendExceptFirst("\n    AND ");
                planBuff.append(condition.getSQL());
            }
        }
        String plan = StringUtils.quoteRemarkSQL(planBuff.toString());
        if (plan.indexOf('\n') >= 0) {
            plan += "\n";
        }
        buff.append(StringUtils.indent("/* " + plan + " */", 4, false));
    }
    if (isJoin) {
        buff.append("\n    ON ");
        if (joinCondition == null) {
            // need to have a ON expression, otherwise the nesting is
            // unclear
            buff.append("1=1");
        } else {
            buff.append(StringUtils.unEnclose(joinCondition.getSQL()));
        }
    }
    if (filterCondition != null) {
        buff.append('\n');
        String condition = StringUtils.unEnclose(filterCondition.getSQL());
        condition = "/* WHERE " + StringUtils.quoteRemarkSQL(condition) + "\n*/";
        buff.append(StringUtils.indent(condition, 4, false));
    }
    if (scanCount > 0) {
        buff.append("\n    /* scanCount: ").append(scanCount).append(" */");
    }
    return buff.toString();
}
Also used : IndexLookupBatch(org.h2.index.IndexLookupBatch) StatementBuilder(org.h2.util.StatementBuilder) IndexCondition(org.h2.index.IndexCondition)

Example 2 with IndexHints

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

the class Table method getBestPlanItem.

/**
 * Get the best plan for the given search mask.
 *
 * @param session the session
 * @param masks per-column comparison bit masks, null means 'always false',
 *              see constants in IndexCondition
 * @param filters all joined table filters
 * @param filter the current table filter index
 * @param sortOrder the sort order
 * @param allColumnsSet the set of all columns
 * @return the plan item
 */
public PlanItem getBestPlanItem(Session session, int[] masks, TableFilter[] filters, int filter, SortOrder sortOrder, HashSet<Column> allColumnsSet) {
    PlanItem item = new PlanItem();
    item.setIndex(getScanIndex(session));
    item.cost = item.getIndex().getCost(session, null, filters, filter, null, allColumnsSet);
    Trace t = session.getTrace();
    if (t.isDebugEnabled()) {
        t.debug("Table      :     potential plan item cost {0} index {1}", item.cost, item.getIndex().getPlanSQL());
    }
    ArrayList<Index> indexes = getIndexes();
    IndexHints indexHints = getIndexHints(filters, filter);
    if (indexes != null && masks != null) {
        for (int i = 1, size = indexes.size(); i < size; i++) {
            Index index = indexes.get(i);
            if (isIndexExcludedByHints(indexHints, index)) {
                continue;
            }
            double cost = index.getCost(session, masks, filters, filter, sortOrder, allColumnsSet);
            if (t.isDebugEnabled()) {
                t.debug("Table      :     potential plan item cost {0} index {1}", cost, index.getPlanSQL());
            }
            if (cost < item.cost) {
                item.cost = cost;
                item.setIndex(index);
            }
        }
    }
    return item;
}
Also used : Trace(org.h2.message.Trace) Index(org.h2.index.Index) Constraint(org.h2.constraint.Constraint)

Example 3 with IndexHints

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

the class Parser method parseIndexHints.

private IndexHints parseIndexHints(Table table) {
    if (table == null) {
        throw getSyntaxError();
    }
    read("(");
    LinkedHashSet<String> indexNames = new LinkedHashSet<>();
    if (!readIf(")")) {
        do {
            String indexName = readIdentifierWithSchema();
            Index index = table.getIndex(indexName);
            indexNames.add(index.getName());
        } while (readIfMore(true));
    }
    return IndexHints.createUseIndexHints(indexNames);
}
Also used : LinkedHashSet(java.util.LinkedHashSet) DropIndex(org.h2.command.ddl.DropIndex) Index(org.h2.index.Index) CreateIndex(org.h2.command.ddl.CreateIndex) ValueString(org.h2.value.ValueString)

Example 4 with IndexHints

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

the class Parser method readTableFilter.

private TableFilter readTableFilter() {
    Table table;
    String alias = null;
    if (readIf("(")) {
        if (isSelect()) {
            Query query = parseSelectUnion();
            read(")");
            query.setParameterList(new ArrayList<>(parameters));
            query.init();
            Session s;
            if (createView != null) {
                s = database.getSystemSession();
            } else {
                s = session;
            }
            alias = session.getNextSystemIdentifier(sqlCommand);
            table = TableView.createTempView(s, session.getUser(), alias, query, currentSelect);
        } else {
            TableFilter top;
            top = readTableFilter();
            top = readJoin(top);
            read(")");
            alias = readFromAlias(null);
            if (alias != null) {
                top.setAlias(alias);
                ArrayList<String> derivedColumnNames = readDerivedColumnNames();
                if (derivedColumnNames != null) {
                    top.setDerivedColumns(derivedColumnNames);
                }
            }
            return top;
        }
    } else if (readIf("VALUES")) {
        table = parseValuesTable(0).getTable();
    } else {
        String tableName = readIdentifierWithSchema(null);
        Schema schema = getSchema();
        boolean foundLeftBracket = readIf("(");
        if (foundLeftBracket && readIf("INDEX")) {
            // Sybase compatibility with
            // "select * from test (index table1_index)"
            readIdentifierWithSchema(null);
            read(")");
            foundLeftBracket = false;
        }
        if (foundLeftBracket) {
            Schema mainSchema = database.getSchema(Constants.SCHEMA_MAIN);
            if (equalsToken(tableName, RangeTable.NAME) || equalsToken(tableName, RangeTable.ALIAS)) {
                Expression min = readExpression();
                read(",");
                Expression max = readExpression();
                if (readIf(",")) {
                    Expression step = readExpression();
                    read(")");
                    table = new RangeTable(mainSchema, min, max, step, false);
                } else {
                    read(")");
                    table = new RangeTable(mainSchema, min, max, false);
                }
            } else {
                Expression expr = readFunction(schema, tableName);
                if (!(expr instanceof FunctionCall)) {
                    throw getSyntaxError();
                }
                FunctionCall call = (FunctionCall) expr;
                if (!call.isDeterministic()) {
                    recompileAlways = true;
                }
                table = new FunctionTable(mainSchema, session, expr, call);
            }
        } else if (equalsToken("DUAL", tableName)) {
            table = getDualTable(false);
        } else if (database.getMode().sysDummy1 && equalsToken("SYSDUMMY1", tableName)) {
            table = getDualTable(false);
        } else {
            table = readTableOrView(tableName);
        }
    }
    ArrayList<String> derivedColumnNames = null;
    IndexHints indexHints = null;
    // for backward compatibility, handle case where USE is a table alias
    if (readIf("USE")) {
        if (readIf("INDEX")) {
            indexHints = parseIndexHints(table);
        } else {
            alias = "USE";
            derivedColumnNames = readDerivedColumnNames();
        }
    } else {
        alias = readFromAlias(alias);
        if (alias != null) {
            derivedColumnNames = readDerivedColumnNames();
            // if alias present, a second chance to parse index hints
            if (readIf("USE")) {
                read("INDEX");
                indexHints = parseIndexHints(table);
            }
        }
    }
    // inherit alias for CTE as views from table name
    if (table.isView() && table.isTableExpression() && alias == null) {
        alias = table.getName();
    }
    TableFilter filter = new TableFilter(session, table, alias, rightsChecked, currentSelect, orderInFrom++, indexHints);
    if (derivedColumnNames != null) {
        filter.setDerivedColumns(derivedColumnNames);
    }
    return filter;
}
Also used : RangeTable(org.h2.table.RangeTable) TruncateTable(org.h2.command.ddl.TruncateTable) CreateTable(org.h2.command.ddl.CreateTable) FunctionTable(org.h2.table.FunctionTable) CreateLinkedTable(org.h2.command.ddl.CreateLinkedTable) Table(org.h2.table.Table) DropTable(org.h2.command.ddl.DropTable) Query(org.h2.command.dml.Query) DropSchema(org.h2.command.ddl.DropSchema) CreateSchema(org.h2.command.ddl.CreateSchema) Schema(org.h2.schema.Schema) ValueString(org.h2.value.ValueString) IndexHints(org.h2.table.IndexHints) RangeTable(org.h2.table.RangeTable) TableFilter(org.h2.table.TableFilter) Expression(org.h2.expression.Expression) ValueExpression(org.h2.expression.ValueExpression) FunctionTable(org.h2.table.FunctionTable) FunctionCall(org.h2.expression.FunctionCall) Session(org.h2.engine.Session)

Aggregations

Index (org.h2.index.Index)2 ValueString (org.h2.value.ValueString)2 LinkedHashSet (java.util.LinkedHashSet)1 CreateIndex (org.h2.command.ddl.CreateIndex)1 CreateLinkedTable (org.h2.command.ddl.CreateLinkedTable)1 CreateSchema (org.h2.command.ddl.CreateSchema)1 CreateTable (org.h2.command.ddl.CreateTable)1 DropIndex (org.h2.command.ddl.DropIndex)1 DropSchema (org.h2.command.ddl.DropSchema)1 DropTable (org.h2.command.ddl.DropTable)1 TruncateTable (org.h2.command.ddl.TruncateTable)1 Query (org.h2.command.dml.Query)1 Constraint (org.h2.constraint.Constraint)1 Session (org.h2.engine.Session)1 Expression (org.h2.expression.Expression)1 FunctionCall (org.h2.expression.FunctionCall)1 ValueExpression (org.h2.expression.ValueExpression)1 IndexCondition (org.h2.index.IndexCondition)1 IndexLookupBatch (org.h2.index.IndexLookupBatch)1 Trace (org.h2.message.Trace)1