INDEXES

Indexes enable you to a quickly look up data in table columns.

By default, local unique BTREE indexes are created based on the primary key for each PROTECTED or PUBLIC dataset.

Additionally, there are instances when:

• There are unnecessary columns in the primary key.
• The few columns on which queries can be beneficial are missing from indexing (outside the primary keys) .

To overcome these issues, you can provide instructions to create non-primary key indexes.

Note:

Column groups:

• Support BTREE index.
• Don't allow different indexes of the same type with the same columns in same orders.
• Allow different indexes of different types with the same columns.
• Allow different indexes with the same columns in different orders.

Syntax

index_block ::= INDEXES
                                           '['
                                              { pk_index_statement | skip_pk_index_statement | 
                                                create_index_statement }[ create_index_statement ] ...
                                           ']';
pk_index_statement ::= CREATE INDEX ON PRIMARYKEY FOR ALL DATASETS [ EXCEPT table_list ] ;
skip_pk_index_statement ::= SKIP CREATE INDEX ON PRIMARYKEY FOR ALL DATASETS [ EXCEPT table_list ] ;
create_index_statement ::= CREATE [ index_type ] unique_spec [ scope_spec ] INDEX identifier ON table_name column_list ;
table_list ::= '[' table_name (, table_name) ']'
index_type ::= BTREE
unique_spec ::= UNIQUE | NONUNIQUE
scope_spec ::= LOCAL | GLOBAL       //default LOCAL

Example

INDEXES
[
 SKIP CREATE INDEX ON PRIMARYKEY FOR ALL DATASETS EXCEPT [PROMOTION_D];
 CREATE NON-UNIQUE INDEX city_index ON CUST_D[CUST_CITY, COUNTRY_ID];
 CREATE UNIQUE GLOBAL INDEX UN_INDEX ON CUST_D[CUST_ID, CUST_FIRST_NAME];
]