WL#5861: Use single value list partitions as index
Affects: Server-9.x
—
Status: Un-Assigned
For a list partitioned table where all partitions have a single value, it is possible to use the partitioning function as index. Example: CREATE TABLE t (a INT, b VARCHAR(63)...) PARTITION BY LIST (a) (PARTITION p0 VALUES IN (0), PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); INSERT INTO t VALUES (1, "row 1"), (0, "row 0")...; when implemented this should avoid filesort, and use the index given by partitioning instead: EXPLAIN SELECT * FROM t ORDER BY a; One could also remove the partitioning columns from the PK in the partitions handlers, making the PK shorter (which is good for InnoDB) in this case. Like: CREATE TABLE t (a INT, b INT, c VARCHAR(64)... PRIMARY KEY(a, b)) PARTITION BY LIST (a) (PARTITION p0 VALUES IN (0), PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); Would create the partitions handlers with the structure: CREATE TABLE t (a INT, b INT, c VARCHAR(64)... PRIMARY KEY(b)) Note: in LIST COLUMNS partitioning; no partition column can have the same value in two or more partitions!
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.