Examples with IndexCondition org.h2.index.IndexCondition used on opensource projects

Example 1 with IndexCondition

use of org.h2.index.IndexCondition in project h2database by h2database.

the class ExpressionColumn method createIndexConditions.

@Override
public void createIndexConditions(Session session, TableFilter filter) {
    TableFilter tf = getTableFilter();
    if (filter == tf && column.getType() == Value.BOOLEAN) {
        IndexCondition cond = IndexCondition.get(Comparison.EQUAL, this, ValueExpression.get(ValueBoolean.TRUE));
        filter.addIndexCondition(cond);
    }
}

Example 2 with IndexCondition

use of org.h2.index.IndexCondition in project h2database by h2database.

the class BaseIndex method getCostRangeIndex.

/**
 * Calculate the cost for the given mask as if this index was a typical
 * b-tree range index. This is the estimated cost required to search one
 * row, and then iterate over the given number of rows.
 *
 * @param masks the IndexCondition search masks, one for each column in the
 *            table
 * @param rowCount the number of rows in the index
 * @param filters all joined table filters
 * @param filter the current table filter index
 * @param sortOrder the sort order
 * @param isScanIndex whether this is a "table scan" index
 * @param allColumnsSet the set of all columns
 * @return the estimated cost
 */
protected final long getCostRangeIndex(int[] masks, long rowCount, TableFilter[] filters, int filter, SortOrder sortOrder, boolean isScanIndex, HashSet<Column> allColumnsSet) {
    rowCount += Constants.COST_ROW_OFFSET;
    int totalSelectivity = 0;
    long rowsCost = rowCount;
    if (masks != null) {
        for (int i = 0, len = columns.length; i < len; i++) {
            Column column = columns[i];
            int index = column.getColumnId();
            int mask = masks[index];
            if ((mask & IndexCondition.EQUALITY) == IndexCondition.EQUALITY) {
                if (i == columns.length - 1 && getIndexType().isUnique()) {
                    rowsCost = 3;
                    break;
                }
                totalSelectivity = 100 - ((100 - totalSelectivity) * (100 - column.getSelectivity()) / 100);
                long distinctRows = rowCount * totalSelectivity / 100;
                if (distinctRows <= 0) {
                    distinctRows = 1;
                }
                rowsCost = 2 + Math.max(rowCount / distinctRows, 1);
            } else if ((mask & IndexCondition.RANGE) == IndexCondition.RANGE) {
                rowsCost = 2 + rowCount / 4;
                break;
            } else if ((mask & IndexCondition.START) == IndexCondition.START) {
                rowsCost = 2 + rowCount / 3;
                break;
            } else if ((mask & IndexCondition.END) == IndexCondition.END) {
                rowsCost = rowCount / 3;
                break;
            } else {
                break;
            }
        }
    }
    // If the ORDER BY clause matches the ordering of this index,
    // it will be cheaper than another index, so adjust the cost
    // accordingly.
    long sortingCost = 0;
    if (sortOrder != null) {
        sortingCost = 100 + rowCount / 10;
    }
    if (sortOrder != null && !isScanIndex) {
        boolean sortOrderMatches = true;
        int coveringCount = 0;
        int[] sortTypes = sortOrder.getSortTypes();
        TableFilter tableFilter = filters == null ? null : filters[filter];
        for (int i = 0, len = sortTypes.length; i < len; i++) {
            if (i >= indexColumns.length) {
                // more of the order by columns.
                break;
            }
            Column col = sortOrder.getColumn(i, tableFilter);
            if (col == null) {
                sortOrderMatches = false;
                break;
            }
            IndexColumn indexCol = indexColumns[i];
            if (!col.equals(indexCol.column)) {
                sortOrderMatches = false;
                break;
            }
            int sortType = sortTypes[i];
            if (sortType != indexCol.sortType) {
                sortOrderMatches = false;
                break;
            }
            coveringCount++;
        }
        if (sortOrderMatches) {
            // "coveringCount" makes sure that when we have two
            // or more covering indexes, we choose the one
            // that covers more.
            sortingCost = 100 - coveringCount;
        }
    }
    // If we have two indexes with the same cost, and one of the indexes can
    // satisfy the query without needing to read from the primary table
    // (scan index), make that one slightly lower cost.
    boolean needsToReadFromScanIndex = true;
    if (!isScanIndex && allColumnsSet != null && !allColumnsSet.isEmpty()) {
        boolean foundAllColumnsWeNeed = true;
        for (Column c : allColumnsSet) {
            if (c.getTable() == getTable()) {
                boolean found = false;
                for (Column c2 : columns) {
                    if (c == c2) {
                        found = true;
                        break;
                    }
                }
                if (!found) {
                    foundAllColumnsWeNeed = false;
                    break;
                }
            }
        }
        if (foundAllColumnsWeNeed) {
            needsToReadFromScanIndex = false;
        }
    }
    long rc;
    if (isScanIndex) {
        rc = rowsCost + sortingCost + 20;
    } else if (needsToReadFromScanIndex) {
        rc = rowsCost + rowsCost + sortingCost + 20;
    } else {
        // The (20-x) calculation makes sure that when we pick a covering
        // index, we pick the covering index that has the smallest number of
        // columns (the more columns we have in index - the higher cost).
        // This is faster because a smaller index will fit into fewer data
        // blocks.
        rc = rowsCost + sortingCost + columns.length;
    }
    return rc;
}

Example 3 with IndexCondition

use of org.h2.index.IndexCondition in project h2database by h2database.

the class IndexCursor method prepare.

/**
 * Prepare this index cursor to make a lookup in index.
 *
 * @param s Session.
 * @param indexConditions Index conditions.
 */
public void prepare(Session s, ArrayList<IndexCondition> indexConditions) {
    this.session = s;
    alwaysFalse = false;
    start = end = null;
    inList = null;
    inColumn = null;
    inResult = null;
    inResultTested = null;
    intersects = null;
    // don't use enhanced for loop to avoid creating objects
    for (IndexCondition condition : indexConditions) {
        if (condition.isAlwaysFalse()) {
            alwaysFalse = true;
            break;
        }
        // lookups, each such lookup will perform an own table scan.
        if (index.isFindUsingFullTableScan()) {
            continue;
        }
        Column column = condition.getColumn();
        if (condition.getCompareType() == Comparison.IN_LIST) {
            if (start == null && end == null) {
                if (canUseIndexForIn(column)) {
                    this.inColumn = column;
                    inList = condition.getCurrentValueList(s);
                    inListIndex = 0;
                }
            }
        } else if (condition.getCompareType() == Comparison.IN_QUERY) {
            if (start == null && end == null) {
                if (canUseIndexForIn(column)) {
                    this.inColumn = column;
                    inResult = condition.getCurrentResult();
                }
            }
        } else {
            Value v = condition.getCurrentValue(s);
            boolean isStart = condition.isStart();
            boolean isEnd = condition.isEnd();
            boolean isIntersects = condition.isSpatialIntersects();
            int columnId = column.getColumnId();
            if (columnId >= 0) {
                IndexColumn idxCol = indexColumns[columnId];
                if (idxCol != null && (idxCol.sortType & SortOrder.DESCENDING) != 0) {
                    // if the index column is sorted the other way, we swap
                    // end and start NULLS_FIRST / NULLS_LAST is not a
                    // problem, as nulls never match anyway
                    boolean temp = isStart;
                    isStart = isEnd;
                    isEnd = temp;
                }
            }
            if (isStart) {
                start = getSearchRow(start, columnId, v, true);
            }
            if (isEnd) {
                end = getSearchRow(end, columnId, v, false);
            }
            if (isIntersects) {
                intersects = getSpatialSearchRow(intersects, columnId, v);
            }
            // an X IN(..) condition, unless the X IN condition can use the index.
            if ((isStart || isEnd) && !canUseIndexFor(inColumn)) {
                inColumn = null;
                inList = null;
                inResult = null;
            }
            if (!session.getDatabase().getSettings().optimizeIsNull) {
                if (isStart && isEnd) {
                    if (v == ValueNull.INSTANCE) {
                        // join on a column=NULL is always false
                        alwaysFalse = true;
                    }
                }
            }
        }
    }
    if (inColumn != null) {
        start = table.getTemplateRow();
    }
}

Example 4 with IndexCondition

use of org.h2.index.IndexCondition 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 5 with IndexCondition

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