database(dbms) replicate sort order in ascending and descending from all data strcture for fast query?

[Danilo Santos]

database(dbms) replicate sort order in ascending and descending from all data strcture for fast query like one cache search?

h2database or others database order with each change or just in the request?

is there a command to define an order to be learned by the database so that queries by fear are faster?

[Evgenij Ryazanov]

Hello.

H2 currently can use index-sorted optimization only when there is a compatible index with the same order. But you can create indexes with different order on the same columns.

CREATE TABLE TEST(A INT, B INT);
CREATE INDEX IDX1 ON TEST(A, B);
CREATE INDEX IDX2 ON TEST(A, B DESC);

EXPLAIN SELECT * FROM TEST ORDER BY A;
> SELECT
>     "PUBLIC"."TEST"."A",
>     "PUBLIC"."TEST"."B"
> FROM "PUBLIC"."TEST"
>     /* PUBLIC.IDX1 */
> ORDER BY 1
> /* index sorted */

EXPLAIN SELECT * FROM TEST WHERE A = 1 ORDER BY A, B;
> SELECT
>     "PUBLIC"."TEST"."A",
>     "PUBLIC"."TEST"."B"
> FROM "PUBLIC"."TEST"
>     /* PUBLIC.IDX1: A = 1 */
> WHERE "A" = 1
> ORDER BY 1, 2
> /* index sorted */

EXPLAIN SELECT * FROM TEST WHERE A = 1 ORDER BY A, B DESC;
> SELECT
>     "PUBLIC"."TEST"."A",
>     "PUBLIC"."TEST"."B"
> FROM "PUBLIC"."TEST"
>     /* PUBLIC.IDX2: A = 1 */
> WHERE "A" = 1
> ORDER BY 1, 2 DESC
/* index sorted */

[Andrei Tokar]

We may even implement that optimization mentioned by Evgenij, regardless of the original index order (add case of exact opposite order, to be precise), because both direct and reverse iterators are available now on the underlying B-Tree (MVMap). Existence of NULL values in the order may complicate things somewhat thought.