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

Example 31 with Index

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

the class Select method getSortIndex.

/**
 * Get the index that matches the ORDER BY list, if one exists. This is to
 * avoid running a separate ORDER BY if an index can be used. This is
 * specially important for large result sets, if only the first few rows are
 * important (LIMIT is used)
 *
 * @return the index if one is found
 */
private Index getSortIndex() {
    if (sort == null) {
        return null;
    }
    ArrayList<Column> sortColumns = New.arrayList();
    for (int idx : sort.getQueryColumnIndexes()) {
        if (idx < 0 || idx >= expressions.size()) {
            throw DbException.getInvalidValueException("ORDER BY", idx + 1);
        }
        Expression expr = expressions.get(idx);
        expr = expr.getNonAliasExpression();
        if (expr.isConstant()) {
            continue;
        }
        if (!(expr instanceof ExpressionColumn)) {
            return null;
        }
        ExpressionColumn exprCol = (ExpressionColumn) expr;
        if (exprCol.getTableFilter() != topTableFilter) {
            return null;
        }
        sortColumns.add(exprCol.getColumn());
    }
    Column[] sortCols = sortColumns.toArray(new Column[0]);
    if (sortCols.length == 0) {
        // sort just on constants - can use scan index
        return topTableFilter.getTable().getScanIndex(session);
    }
    ArrayList<Index> list = topTableFilter.getTable().getIndexes();
    if (list != null) {
        int[] sortTypes = sort.getSortTypesWithNullPosition();
        for (Index index : list) {
            if (index.getCreateSQL() == null) {
                // can't use the scan index
                continue;
            }
            if (index.getIndexType().isHash()) {
                continue;
            }
            IndexColumn[] indexCols = index.getIndexColumns();
            if (indexCols.length < sortCols.length) {
                continue;
            }
            boolean ok = true;
            for (int j = 0; j < sortCols.length; j++) {
                // the index and the sort order must start
                // with the exact same columns
                IndexColumn idxCol = indexCols[j];
                Column sortCol = sortCols[j];
                if (idxCol.column != sortCol) {
                    ok = false;
                    break;
                }
                if (SortOrder.addExplicitNullPosition(idxCol.sortType) != sortTypes[j]) {
                    ok = false;
                    break;
                }
            }
            if (ok) {
                return index;
            }
        }
    }
    if (sortCols.length == 1 && sortCols[0].getColumnId() == -1) {
        // special case: order by _ROWID_
        Index index = topTableFilter.getTable().getScanIndex(session);
        if (index.isRowIdIndex()) {
            return index;
        }
    }
    return null;
}

Example 32 with Index

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

the class Select method queryDistinct.

private void queryDistinct(ResultTarget result, long limitRows) {
    // limitRows is never 0 here
    if (limitRows > 0 && offsetExpr != null) {
        int offset = offsetExpr.getValue(session).getInt();
        if (offset > 0) {
            limitRows += offset;
        }
    }
    int rowNumber = 0;
    setCurrentRowNumber(0);
    Index index = topTableFilter.getIndex();
    SearchRow first = null;
    int columnIndex = index.getColumns()[0].getColumnId();
    int sampleSize = getSampleSizeValue(session);
    while (true) {
        setCurrentRowNumber(rowNumber + 1);
        Cursor cursor = index.findNext(session, first, null);
        if (!cursor.next()) {
            break;
        }
        SearchRow found = cursor.getSearchRow();
        Value value = found.getValue(columnIndex);
        if (first == null) {
            first = topTableFilter.getTable().getTemplateSimpleRow(true);
        }
        first.setValue(columnIndex, value);
        Value[] row = { value };
        result.addRow(row);
        rowNumber++;
        if ((sort == null || sortUsingIndex) && limitRows > 0 && rowNumber >= limitRows) {
            break;
        }
        if (sampleSize > 0 && rowNumber >= sampleSize) {
            break;
        }
    }
}

Example 33 with Index

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

the class Select method prepare.

@Override
public void prepare() {
    if (isPrepared) {
        // sometimes a subquery is prepared twice (CREATE TABLE AS SELECT)
        return;
    }
    if (SysProperties.CHECK && !checkInit) {
        DbException.throwInternalError("not initialized");
    }
    if (orderList != null) {
        sort = prepareOrder(orderList, expressions.size());
        orderList = null;
    }
    ColumnNamer columnNamer = new ColumnNamer(session);
    for (int i = 0; i < expressions.size(); i++) {
        Expression e = expressions.get(i);
        String proposedColumnName = e.getAlias();
        String columnName = columnNamer.getColumnName(e, i, proposedColumnName);
        // if the name changed, create an alias
        if (!columnName.equals(proposedColumnName)) {
            e = new Alias(e, columnName, true);
        }
        expressions.set(i, e.optimize(session));
    }
    if (condition != null) {
        condition = condition.optimize(session);
        for (TableFilter f : filters) {
            // left outer join child on p = pc where c is null;
            if (!f.isJoinOuter() && !f.isJoinOuterIndirect()) {
                condition.createIndexConditions(session, f);
            }
        }
    }
    if (isGroupQuery && groupIndex == null && havingIndex < 0 && filters.size() == 1) {
        if (condition == null) {
            Table t = filters.get(0).getTable();
            ExpressionVisitor optimizable = ExpressionVisitor.getOptimizableVisitor(t);
            isQuickAggregateQuery = isEverything(optimizable);
        }
    }
    cost = preparePlan(session.isParsingCreateView());
    if (distinct && session.getDatabase().getSettings().optimizeDistinct && !isGroupQuery && filters.size() == 1 && expressions.size() == 1 && condition == null) {
        Expression expr = expressions.get(0);
        expr = expr.getNonAliasExpression();
        if (expr instanceof ExpressionColumn) {
            Column column = ((ExpressionColumn) expr).getColumn();
            int selectivity = column.getSelectivity();
            Index columnIndex = topTableFilter.getTable().getIndexForColumn(column, false, true);
            if (columnIndex != null && selectivity != Constants.SELECTIVITY_DEFAULT && selectivity < 20) {
                // the first column must be ascending
                boolean ascending = columnIndex.getIndexColumns()[0].sortType == SortOrder.ASCENDING;
                Index current = topTableFilter.getIndex();
                // if another index is faster
                if (columnIndex.canFindNext() && ascending && (current == null || current.getIndexType().isScan() || columnIndex == current)) {
                    IndexType type = columnIndex.getIndexType();
                    // indexes don't work
                    if (!type.isHash() && (!type.isUnique() || columnIndex.getColumns().length > 1)) {
                        topTableFilter.setIndex(columnIndex);
                        isDistinctQuery = true;
                    }
                }
            }
        }
    }
    if (sort != null && !isQuickAggregateQuery && !isGroupQuery) {
        Index index = getSortIndex();
        Index current = topTableFilter.getIndex();
        if (index != null && current != null) {
            if (current.getIndexType().isScan() || current == index) {
                topTableFilter.setIndex(index);
                if (!topTableFilter.hasInComparisons()) {
                    // in(select ...) and in(1,2,3) may return the key in
                    // another order
                    sortUsingIndex = true;
                }
            } else if (index.getIndexColumns() != null && index.getIndexColumns().length >= current.getIndexColumns().length) {
                IndexColumn[] sortColumns = index.getIndexColumns();
                IndexColumn[] currentColumns = current.getIndexColumns();
                boolean swapIndex = false;
                for (int i = 0; i < currentColumns.length; i++) {
                    if (sortColumns[i].column != currentColumns[i].column) {
                        swapIndex = false;
                        break;
                    }
                    if (sortColumns[i].sortType != currentColumns[i].sortType) {
                        swapIndex = true;
                    }
                }
                if (swapIndex) {
                    topTableFilter.setIndex(index);
                    sortUsingIndex = true;
                }
            }
        }
    }
    if (!isQuickAggregateQuery && isGroupQuery && getGroupByExpressionCount() > 0) {
        Index index = getGroupSortedIndex();
        Index current = topTableFilter.getIndex();
        if (index != null && current != null && (current.getIndexType().isScan() || current == index)) {
            topTableFilter.setIndex(index);
            isGroupSortedQuery = true;
        }
    }
    expressionArray = expressions.toArray(new Expression[0]);
    isPrepared = true;
}

Example 34 with Index

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

the class Database method removeSchemaObject.

/**
 * Remove an object from the system table.
 *
 * @param session the session
 * @param obj the object to be removed
 */
public void removeSchemaObject(Session session, SchemaObject obj) {
    int type = obj.getType();
    if (type == DbObject.TABLE_OR_VIEW) {
        Table table = (Table) obj;
        if (table.isTemporary() && !table.isGlobalTemporary()) {
            session.removeLocalTempTable(table);
            return;
        }
    } else if (type == DbObject.INDEX) {
        Index index = (Index) obj;
        Table table = index.getTable();
        if (table.isTemporary() && !table.isGlobalTemporary()) {
            session.removeLocalTempTableIndex(index);
            return;
        }
    } else if (type == DbObject.CONSTRAINT) {
        Constraint constraint = (Constraint) obj;
        Table table = constraint.getTable();
        if (table.isTemporary() && !table.isGlobalTemporary()) {
            session.removeLocalTempTableConstraint(constraint);
            return;
        }
    }
    checkWritingAllowed();
    lockMeta(session);
    synchronized (this) {
        Comment comment = findComment(obj);
        if (comment != null) {
            removeDatabaseObject(session, comment);
        }
        obj.getSchema().remove(obj);
        int id = obj.getId();
        if (!starting) {
            Table t = getDependentTable(obj, null);
            if (t != null) {
                obj.getSchema().add(obj);
                throw DbException.get(ErrorCode.CANNOT_DROP_2, obj.getSQL(), t.getSQL());
            }
            obj.removeChildrenAndResources(session);
        }
        removeMeta(session, id);
    }
}

Example 35 with Index

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

the class Merge method merge.

/**
 * Merge the given row.
 *
 * @param row the row
 */
protected void merge(Row row) {
    ArrayList<Parameter> k = update.getParameters();
    for (int i = 0; i < columns.length; i++) {
        Column col = columns[i];
        Value v = row.getValue(col.getColumnId());
        Parameter p = k.get(i);
        p.setValue(v);
    }
    for (int i = 0; i < keys.length; i++) {
        Column col = keys[i];
        Value v = row.getValue(col.getColumnId());
        if (v == null) {
            throw DbException.get(ErrorCode.COLUMN_CONTAINS_NULL_VALUES_1, col.getSQL());
        }
        Parameter p = k.get(columns.length + i);
        p.setValue(v);
    }
    // try and update
    int count = update.update();
    // if update fails try an insert
    if (count == 0) {
        try {
            targetTable.validateConvertUpdateSequence(session, row);
            boolean done = targetTable.fireBeforeRow(session, null, row);
            if (!done) {
                targetTable.lock(session, true, false);
                targetTable.addRow(session, row);
                session.getGeneratedKeys().confirmRow(row);
                session.log(targetTable, UndoLogRecord.INSERT, row);
                targetTable.fireAfterRow(session, null, row, false);
            }
        } catch (DbException e) {
            if (e.getErrorCode() == ErrorCode.DUPLICATE_KEY_1) {
                // possibly a concurrent merge or insert
                Index index = (Index) e.getSource();
                if (index != null) {
                    // verify the index columns match the key
                    Column[] indexColumns = index.getColumns();
                    boolean indexMatchesKeys = true;
                    if (indexColumns.length <= keys.length) {
                        for (int i = 0; i < indexColumns.length; i++) {
                            if (indexColumns[i] != keys[i]) {
                                indexMatchesKeys = false;
                                break;
                            }
                        }
                    }
                    if (indexMatchesKeys) {
                        throw DbException.get(ErrorCode.CONCURRENT_UPDATE_1, targetTable.getName());
                    }
                }
            }
            throw e;
        }
    } else if (count != 1) {
        throw DbException.get(ErrorCode.DUPLICATE_KEY_1, targetTable.getSQL());
    }
}