Examples with SortOrder org.h2.result.SortOrder used on opensource projects

Example 1 with SortOrder

use of org.h2.result.SortOrder 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 2 with SortOrder

use of org.h2.result.SortOrder in project h2database by h2database.

the class HashIndex method getCost.

@Override
public double getCost(Session session, int[] masks, TableFilter[] filters, int filter, SortOrder sortOrder, HashSet<Column> allColumnsSet) {
    for (Column column : columns) {
        int index = column.getColumnId();
        int mask = masks[index];
        if ((mask & IndexCondition.EQUALITY) != IndexCondition.EQUALITY) {
            return Long.MAX_VALUE;
        }
    }
    return 2;
}

Example 3 with SortOrder

use of org.h2.result.SortOrder in project h2database by h2database.

the class Aggregate method initOrder.

private SortOrder initOrder(Session session) {
    int size = orderByList.size();
    int[] index = new int[size];
    int[] sortType = new int[size];
    for (int i = 0; i < size; i++) {
        SelectOrderBy o = orderByList.get(i);
        index[i] = i + 1;
        int order = o.descending ? SortOrder.DESCENDING : SortOrder.ASCENDING;
        sortType[i] = order;
    }
    return new SortOrder(session.getDatabase(), index, sortType, null);
}

Example 4 with SortOrder

use of org.h2.result.SortOrder 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 SortOrder

use of org.h2.result.SortOrder in project ignite by apache.

the class GridSqlQueryParser method processSortOrder.

/**
 * @param sortOrder Sort order.
 * @param qry Query.
 */
private void processSortOrder(SortOrder sortOrder, GridSqlQuery qry) {
    if (sortOrder == null)
        return;
    int[] indexes = sortOrder.getQueryColumnIndexes();
    int[] sortTypes = sortOrder.getSortTypes();
    for (int i = 0; i < indexes.length; i++) {
        int colIdx = indexes[i];
        int type = sortTypes[i];
        qry.addSort(new GridSqlSortColumn(colIdx, (type & SortOrder.DESCENDING) == 0, (type & SortOrder.NULLS_FIRST) != 0, (type & SortOrder.NULLS_LAST) != 0));
    }
}