The 6.0 version of Xpand offers the ability for a user to create a columnar index on their normal, row-based transactional tables. Creation of the columnar index improves execution for queries that require scanning of lots of data for a subset of columns of a table, and can be used in conjunction with the improvements to how aggregation queries are executed (also part of v6.0). This offers users the ability to run aggregation queries on their transactional data efficiently and orders of magnitude faster than with a row-based index.
Xpand only permits a single columnar index per table (but that index can contain multiple columns). Each columnar index includes independent storage for each column, and columnar indexes are always compressed.
To create a columnar index, you can either create an index explicitly on an existing table:
sql> CREATE INDEX J on T2 (J) USING COLUMNAR; |
Or include it in the CREATE TABLE statement:
sql> CREATE TABLE `t2` ( |
The UNIQUE option is not available for columnar indexes.
sql> CREATE UNIQUE INDEX J on T2 (J) USING COLUMNAR; ERROR 1 (HY000): [1027] Bad Arguments: Columnar index cannot be unique. |