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

Example 86 with Index

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

the class TestMVStore method testIndexSkip.

private void testIndexSkip() {
    MVStore s = openStore(null, 4);
    MVMap<Integer, Integer> map = s.openMap("test");
    for (int i = 0; i < 100; i += 2) {
        map.put(i, 10 * i);
    }
    Cursor<Integer, Integer> c = map.cursor(50);
    // skip must reset the root of the cursor
    c.skip(10);
    for (int i = 70; i < 100; i += 2) {
        assertTrue(c.hasNext());
        assertEquals(i, c.next().intValue());
    }
    assertFalse(c.hasNext());
    for (int i = -1; i < 100; i++) {
        long index = map.getKeyIndex(i);
        if (i < 0 || (i % 2) != 0) {
            assertEquals(i < 0 ? -1 : -(i / 2) - 2, index);
        } else {
            assertEquals(i / 2, index);
        }
    }
    for (int i = -1; i < 60; i++) {
        Integer k = map.getKey(i);
        if (i < 0 || i >= 50) {
            assertNull(k);
        } else {
            assertEquals(i * 2, k.intValue());
        }
    }
    // skip
    c = map.cursor(0);
    assertTrue(c.hasNext());
    assertEquals(0, c.next().intValue());
    c.skip(0);
    assertEquals(2, c.next().intValue());
    c.skip(1);
    assertEquals(6, c.next().intValue());
    c.skip(20);
    assertEquals(48, c.next().intValue());
    c = map.cursor(0);
    c.skip(20);
    assertEquals(40, c.next().intValue());
    c = map.cursor(0);
    assertEquals(0, c.next().intValue());
    assertEquals(12, map.keyList().indexOf(24));
    assertEquals(24, map.keyList().get(12).intValue());
    assertEquals(-14, map.keyList().indexOf(25));
    assertEquals(map.size(), map.keyList().size());
}

Example 87 with Index

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

the class TestTempTableCrash method test.

private static void test() throws Exception {
    Connection conn;
    Statement stat;
    System.setProperty("h2.delayWrongPasswordMin", "0");
    System.setProperty("h2.check2", "false");
    FilePathRec.register();
    System.setProperty("reopenShift", "4");
    TestReopen reopen = new TestReopen();
    FilePathRec.setRecorder(reopen);
    String url = "jdbc:h2:rec:memFS:data;PAGE_SIZE=64;ANALYZE_AUTO=100";
    // String url = "jdbc:h2:" + RecordingFileSystem.PREFIX +
    // "data/test;PAGE_SIZE=64";
    Class.forName("org.h2.Driver");
    DeleteDbFiles.execute("data", "test", true);
    conn = DriverManager.getConnection(url, "sa", "sa");
    stat = conn.createStatement();
    Random random = new Random(1);
    long start = System.nanoTime();
    for (int i = 0; i < 10000; i++) {
        long now = System.nanoTime();
        if (now > start + TimeUnit.SECONDS.toNanos(1)) {
            System.out.println("i: " + i);
            start = now;
        }
        int x;
        x = random.nextInt(100);
        stat.execute("drop table if exists test" + x);
        String type = random.nextBoolean() ? "temp" : "";
        // String type = "";
        stat.execute("create " + type + " table test" + x + "(id int primary key, name varchar)");
        if (random.nextBoolean()) {
            stat.execute("create index idx_" + x + " on test" + x + "(name, id)");
        }
        if (random.nextBoolean()) {
            stat.execute("insert into test" + x + " select x, x " + "from system_range(1, " + random.nextInt(100) + ")");
        }
        if (random.nextInt(10) == 1) {
            conn.close();
            conn = DriverManager.getConnection(url, "sa", "sa");
            stat = conn.createStatement();
        }
    }
    conn.close();
}

Example 88 with Index

use of org.h2.index.Index 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 89 with Index

use of org.h2.index.Index 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 90 with Index

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