Purpose
You can use this statement to create an index. An index is a database structure created for a table to sort data in one or more columns of the table in a specific order. It improves the query speed and reduces the performance overhead of database systems.
In the current version of OceanBase Database, indexes are classified into unique indexes and non-unique indexes. When you create an index, you can use the STORING(column_name_list) clause to specify the redundant columns in the indexed table. The redundant columns of an index table include the columns that you specified and the primary key columns if the table has a primary key or hidden primary key columns if the table does not have a primary key.
Note
In Oracle mode of OceanBase Database, if you drop any indexed column from a table, the index becomes invalid.
Syntax
CREATE [UNIQUE] INDEX index_name
ON table_name (index_col_name,...)
[index_type] [index_options]
index_type:
USING BTREE
index_options:
index_option [index_option...]
index_option:
GLOBAL | LOCAL
| COMMENT 'string'
| COMPRESSION [=] {NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 | ZLIB_1.0}
| BLOCK_SIZE [=] size
| STORING(column_name_list)
| VISIBLE | INVISIBLE
index_col_name:
column_name [(length)] [ASC | DESC]
column_name_list:
column_name [, column_name...]
Parameters
| Parameter | Description |
|---|---|
| index_name | The name of the index to be created. |
| table_name | The name of the table to which the index belongs. |
| index_col_name | The column name of the index. You can add ASC (ascending order) to the end of each column name. DESC (descending order) is not supported. Default value: ASC. Index-based sorting method: Data is first sorted by the value in the first column specified by index_col_name and by the value in the next column for the records with the same value as the first column. |
| column_name | The name of the column used to create the index. |
| index_type | The index type. Only USING BTREE is supported for you to create B-tree indexes. |
| UNIQUE | Specifies the index to a unique index. |
| index_option | The index option. Separate multiple index options with spaces. | | GLOBAL | LOCAL | Specifies whether the index is a global index or a local index. Default value: GLOBAL. | | COMMENT | The comment. | | COMPRESSION | The compression algorithm. | | BLOCK_SIZE | The microblock size. | | STORING | Specifies to store redundant columns in the indexed table to improve the query performance of the system. |
Examples
Create an index on the
tbl1table./* Create a table named tbl1.*/ obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(10)); Query OK, 0 rows affected /*Create an index on the tbl1 table.*/ obclient> CREATE INDEX tbl1_idx1 ON tbl1 (col1, col2); Query OK, 0 rows affected /*View the index on the tbl1 table.*/ obclient> SELECT * FROM USER_INDEXES WHERE table_name='TBL1'\G *************************** 1. row *************************** INDEX_NAME: TBL1_OBPK_1657184021525727 INDEX_TYPE: NORMAL TABLE_OWNER: SYS TABLE_NAME: TBL1 TABLE_TYPE: TABLE UNIQUENESS: UNIQUE COMPRESSION: ENABLED PREFIX_LENGTH: NULL TABLESPACE_NAME: NULL INI_TRANS: NULL MAX_TRANS: NULL INITIAL_EXTENT: NULL NEXT_EXTENT: NULL MIN_EXTENTS: NULL MAX_EXTENTS: NULL PCT_INCREASE: NULL PCT_THRESHOLD: NULL INCLUDE_COLUMN: NULL FREELISTS: NULL FREELIST_GROUPS: NULL PCT_FREE: NULL LOGGING: NULL BLEVEL: NULL LEAF_BLOCKS: NULL DISTINCT_KEYS: NULL AVG_LEAF_BLOCKS_PER_KEY: NULL AVG_DATA_BLOCKS_PER_KEY: NULL CLUSTERING_FACTOR: NULL STATUS: VALID NUM_ROWS: NULL SAMPLE_SIZE: NULL LAST_ANALYZED: NULL DEGREE: 1 INSTANCES: NULL PARTITIONED: NO TEMPORARY: NULL GENERATED: NULL SECONDARY: NULL BUFFER_POOL: NULL FLASH_CACHE: NULL CELL_FLASH_CACHE: NULL USER_STATS: NULL DURATION: NULL PCT_DIRECT_ACCESS: NULL ITYP_OWNER: NULL ITYP_NAME: NULL PARAMETERS: NULL GLOBAL_STATS: NULL DOMIDX_STATUS: NULL DOMIDX_OPSTATUS: NULL FUNCIDX_STATUS: NULL JOIN_INDEX: NO IOT_REDUNDANT_PKEY_ELIM: NULL DROPPED: NO VISIBILITY: VISIBLE DOMIDX_MANAGEMENT: NULL SEGMENT_CREATED: NULL ORPHANED_ENTRIES: NULL INDEXING: NULL AUTO: NULL *************************** 2. row *************************** INDEX_NAME: TBL1_IDX1 INDEX_TYPE: NORMAL TABLE_OWNER: SYS TABLE_NAME: TBL1 TABLE_TYPE: TABLE UNIQUENESS: NONUNIQUE COMPRESSION: ENABLED PREFIX_LENGTH: NULL TABLESPACE_NAME: NULL INI_TRANS: NULL MAX_TRANS: NULL INITIAL_EXTENT: NULL NEXT_EXTENT: NULL MIN_EXTENTS: NULL MAX_EXTENTS: NULL PCT_INCREASE: NULL PCT_THRESHOLD: NULL INCLUDE_COLUMN: NULL FREELISTS: NULL FREELIST_GROUPS: NULL PCT_FREE: NULL LOGGING: NULL BLEVEL: NULL LEAF_BLOCKS: NULL DISTINCT_KEYS: NULL AVG_LEAF_BLOCKS_PER_KEY: NULL AVG_DATA_BLOCKS_PER_KEY: NULL CLUSTERING_FACTOR: NULL STATUS: VALID NUM_ROWS: NULL SAMPLE_SIZE: NULL LAST_ANALYZED: NULL DEGREE: 1 INSTANCES: NULL PARTITIONED: NO TEMPORARY: NULL GENERATED: NULL SECONDARY: NULL BUFFER_POOL: NULL FLASH_CACHE: NULL CELL_FLASH_CACHE: NULL USER_STATS: NULL DURATION: NULL PCT_DIRECT_ACCESS: NULL ITYP_OWNER: NULL ITYP_NAME: NULL PARAMETERS: NULL GLOBAL_STATS: NULL DOMIDX_STATUS: NULL DOMIDX_OPSTATUS: NULL FUNCIDX_STATUS: NULL JOIN_INDEX: NO IOT_REDUNDANT_PKEY_ELIM: NULL DROPPED: NO VISIBILITY: VISIBLE DOMIDX_MANAGEMENT: NULL SEGMENT_CREATED: NULL ORPHANED_ENTRIES: NULL INDEXING: NULL AUTO: NULL 2 rows in setCreate a unique index on the
tbl2table./* Create a table named tbl2. */ obclient> CREATE TABLE tbl2 (col1 INT, col2 INT, col3 INT, PRIMARY KEY(col1)); Query OK, 0 rows affected /* Create a unique index on the tbl2 table. */ obclient> CREATE UNIQUE INDEX tbl2_idx2 ON tbl2(col2) STORING(col3); Query OK, 0 rows affected /* View the unique index on the tbl2 table. */ obclient> SELECT * FROM USER_INDEXES WHERE table_name='TBL2'\G *************************** 1. row *************************** INDEX_NAME: TBL2_OBPK_1657184907465223 INDEX_TYPE: NORMAL TABLE_OWNER: SYS TABLE_NAME: TBL2 TABLE_TYPE: TABLE UNIQUENESS: UNIQUE COMPRESSION: ENABLED PREFIX_LENGTH: NULL TABLESPACE_NAME: NULL INI_TRANS: NULL MAX_TRANS: NULL INITIAL_EXTENT: NULL NEXT_EXTENT: NULL MIN_EXTENTS: NULL MAX_EXTENTS: NULL PCT_INCREASE: NULL PCT_THRESHOLD: NULL INCLUDE_COLUMN: NULL FREELISTS: NULL FREELIST_GROUPS: NULL PCT_FREE: NULL LOGGING: NULL BLEVEL: NULL LEAF_BLOCKS: NULL DISTINCT_KEYS: NULL AVG_LEAF_BLOCKS_PER_KEY: NULL AVG_DATA_BLOCKS_PER_KEY: NULL CLUSTERING_FACTOR: NULL STATUS: VALID NUM_ROWS: NULL SAMPLE_SIZE: NULL LAST_ANALYZED: NULL DEGREE: 1 INSTANCES: NULL PARTITIONED: NO TEMPORARY: NULL GENERATED: NULL SECONDARY: NULL BUFFER_POOL: NULL FLASH_CACHE: NULL CELL_FLASH_CACHE: NULL USER_STATS: NULL DURATION: NULL PCT_DIRECT_ACCESS: NULL ITYP_OWNER: NULL ITYP_NAME: NULL PARAMETERS: NULL GLOBAL_STATS: NULL DOMIDX_STATUS: NULL DOMIDX_OPSTATUS: NULL FUNCIDX_STATUS: NULL JOIN_INDEX: NO IOT_REDUNDANT_PKEY_ELIM: NULL DROPPED: NO VISIBILITY: VISIBLE DOMIDX_MANAGEMENT: NULL SEGMENT_CREATED: NULL ORPHANED_ENTRIES: NULL INDEXING: NULL AUTO: NULL *************************** 2. row *************************** INDEX_NAME: TBL2_IDX2 INDEX_TYPE: NORMAL TABLE_OWNER: SYS TABLE_NAME: TBL2 TABLE_TYPE: TABLE UNIQUENESS: UNIQUE COMPRESSION: ENABLED PREFIX_LENGTH: NULL TABLESPACE_NAME: NULL INI_TRANS: NULL MAX_TRANS: NULL INITIAL_EXTENT: NULL NEXT_EXTENT: NULL MIN_EXTENTS: NULL MAX_EXTENTS: NULL PCT_INCREASE: NULL PCT_THRESHOLD: NULL INCLUDE_COLUMN: NULL FREELISTS: NULL FREELIST_GROUPS: NULL PCT_FREE: NULL LOGGING: NULL BLEVEL: NULL LEAF_BLOCKS: NULL DISTINCT_KEYS: NULL AVG_LEAF_BLOCKS_PER_KEY: NULL AVG_DATA_BLOCKS_PER_KEY: NULL CLUSTERING_FACTOR: NULL STATUS: VALID NUM_ROWS: NULL SAMPLE_SIZE: NULL LAST_ANALYZED: NULL DEGREE: 1 INSTANCES: NULL PARTITIONED: NO TEMPORARY: NULL GENERATED: NULL SECONDARY: NULL BUFFER_POOL: NULL FLASH_CACHE: NULL CELL_FLASH_CACHE: NULL USER_STATS: NULL DURATION: NULL PCT_DIRECT_ACCESS: NULL ITYP_OWNER: NULL ITYP_NAME: NULL PARAMETERS: NULL GLOBAL_STATS: NULL DOMIDX_STATUS: NULL DOMIDX_OPSTATUS: NULL FUNCIDX_STATUS: NULL JOIN_INDEX: NO IOT_REDUNDANT_PKEY_ELIM: NULL DROPPED: NO VISIBILITY: VISIBLE DOMIDX_MANAGEMENT: NULL SEGMENT_CREATED: NULL ORPHANED_ENTRIES: NULL INDEXING: NULL AUTO: NULL 2 rows in set