Examples with TableFilter org.h2.table.TableFilter used on opensource projects

Example 21 with TableFilter

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

the class TableFilter method getBestPlanItem.

/**
 * Get the best plan item (index, cost) to use use for the current join
 * order.
 *
 * @param s the session
 * @param filters all joined table filters
 * @param filter the current table filter index
 * @param allColumnsSet the set of all columns
 * @return the best plan item
 */
public PlanItem getBestPlanItem(Session s, TableFilter[] filters, int filter, HashSet<Column> allColumnsSet) {
    PlanItem item1 = null;
    SortOrder sortOrder = null;
    if (select != null) {
        sortOrder = select.getSortOrder();
    }
    if (indexConditions.isEmpty()) {
        item1 = new PlanItem();
        item1.setIndex(table.getScanIndex(s, null, filters, filter, sortOrder, allColumnsSet));
        item1.cost = item1.getIndex().getCost(s, null, filters, filter, sortOrder, allColumnsSet);
    }
    int len = table.getColumns().length;
    int[] masks = new int[len];
    for (IndexCondition condition : indexConditions) {
        if (condition.isEvaluatable()) {
            if (condition.isAlwaysFalse()) {
                masks = null;
                break;
            }
            int id = condition.getColumn().getColumnId();
            if (id >= 0) {
                masks[id] |= condition.getMask(indexConditions);
            }
        }
    }
    PlanItem item = table.getBestPlanItem(s, masks, filters, filter, sortOrder, allColumnsSet);
    item.setMasks(masks);
    // The more index conditions, the earlier the table.
    // This is to ensure joins without indexes run quickly:
    // x (x.a=10); y (x.b=y.b) - see issue 113
    item.cost -= item.cost * indexConditions.size() / 100 / (filter + 1);
    if (item1 != null && item1.cost < item.cost) {
        item = item1;
    }
    if (nestedJoin != null) {
        setEvaluatable(true);
        item.setNestedJoinPlan(nestedJoin.getBestPlanItem(s, filters, filter, allColumnsSet));
        // TODO optimizer: calculate cost of a join: should use separate
        // expected row number and lookup cost
        item.cost += item.cost * item.getNestedJoinPlan().cost;
    }
    if (join != null) {
        setEvaluatable(true);
        do {
            filter++;
        } while (filters[filter] != join);
        item.setJoinPlan(join.getBestPlanItem(s, filters, filter, allColumnsSet));
        // TODO optimizer: calculate cost of a join: should use separate
        // expected row number and lookup cost
        item.cost += item.cost * item.getJoinPlan().cost;
    }
    return item;
}

Example 22 with TableFilter

use of org.h2.table.TableFilter 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();
}

Example 23 with TableFilter

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

the class TableFilter method prepareJoinBatch.

/**
 * Attempt to initialize batched join.
 *
 * @param jb join batch if it is already created
 * @param filters the table filters
 * @param filter the filter index (0, 1,...)
 * @return join batch if query runs over index which supports batched
 *         lookups, {@code null} otherwise
 */
public JoinBatch prepareJoinBatch(JoinBatch jb, TableFilter[] filters, int filter) {
    assert filters[filter] == this;
    joinBatch = null;
    joinFilterId = -1;
    if (getTable().isView()) {
        session.pushSubQueryInfo(masks, filters, filter, select.getSortOrder());
        try {
            ((ViewIndex) index).getQuery().prepareJoinBatch();
        } finally {
            session.popSubQueryInfo();
        }
    }
    // For globally top table filter we don't need to create lookup batch,
    // because currently it will not be used (this will be shown in
    // ViewIndex.getPlanSQL()). Probably later on it will make sense to
    // create it to better support X IN (...) conditions, but this needs to
    // be implemented separately. If isAlwaysTopTableFilter is false then we
    // either not a top table filter or top table filter in a sub-query,
    // which in turn is not top in outer query, thus we need to enable
    // batching here to allow outer query run batched join against this
    // sub-query.
    IndexLookupBatch lookupBatch = null;
    if (jb == null && select != null && !isAlwaysTopTableFilter(filter)) {
        lookupBatch = index.createLookupBatch(filters, filter);
        if (lookupBatch != null) {
            jb = new JoinBatch(filter + 1, join);
        }
    }
    if (jb != null) {
        if (nestedJoin != null) {
            throw DbException.throwInternalError();
        }
        joinBatch = jb;
        joinFilterId = filter;
        if (lookupBatch == null && !isAlwaysTopTableFilter(filter)) {
            // createLookupBatch will be called at most once because jb can
            // be created only if lookupBatch is already not null from the
            // call above.
            lookupBatch = index.createLookupBatch(filters, filter);
            if (lookupBatch == null) {
                // the index does not support lookup batching, need to fake
                // it because we are not top
                lookupBatch = JoinBatch.createFakeIndexLookupBatch(this);
            }
        }
        jb.register(this, lookupBatch);
    }
    return jb;
}

Example 24 with TableFilter

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

the class JoinBatch method start.

private void start() {
    // initialize current row
    current = new JoinRow(new Object[filters.length]);
    // initialize top cursor
    Cursor cursor;
    if (batchedSubQuery) {
        assert viewTopFutureCursor != null;
        cursor = get(viewTopFutureCursor);
    } else {
        // setup usual index cursor
        TableFilter f = top.filter;
        IndexCursor indexCursor = f.getIndexCursor();
        indexCursor.find(f.getSession(), f.getIndexConditions());
        cursor = indexCursor;
    }
    current.updateRow(top.id, cursor, JoinRow.S_NULL, JoinRow.S_CURSOR);
    // we need fake first row because batchedNext always will move to the
    // next row
    JoinRow fake = new JoinRow(null);
    fake.next = current;
    current = fake;
}

Example 25 with TableFilter

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

the class MVSpatialIndex method findByGeometry.

@Override
public Cursor findByGeometry(TableFilter filter, SearchRow first, SearchRow last, SearchRow intersection) {
    Session session = filter.getSession();
    if (intersection == null) {
        return find(session, first, last);
    }
    Iterator<SpatialKey> cursor = spatialMap.findIntersectingKeys(getKey(intersection));
    TransactionMap<SpatialKey, Value> map = getMap(session);
    Iterator<SpatialKey> it = map.wrapIterator(cursor, false);
    return new MVStoreCursor(session, it);
}