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 (key_part,...)
[index_type] [index_options]
index_type:
USING BTREE
index_options:
index_option [index_option...]
index_option:
GLOBAL | LOCAL
| COMMENT 'string'
| BLOCK_SIZE [=] size
| STORING(column_name_list)
| VISIBLE | INVISIBLE
key_part:
{index_col_name [(length)] | (expr)} [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. |
| key_part | Creates a normal or function-based index. |
| 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. By default, the columns are sorted in ascending order. Index-based sorting method: Data is first sorted by the values in the first column of index_col_name and by the values in the next column for the records with the same values in the first column. |
| expr | A valid function-based index expression. A Boolean expression, such as c1=c1, is allowed.
NoticeCurrently, you cannot create function-based indexes on generated columns in OceanBase Database. For more information about the expressions supported by function-based indexes, see System functions supported by function-based indexes. |
| 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 options. Multiple index options are separated with spaces. |
| GLOBAL | LOCAL | Specifies whether the index is a global index or a local index. Default value: GLOBAL. |
| COMMENT | The comment. |
| BLOCK_SIZE | The microblock size. |
| STORING | Specifies to store copies of some columns in the index 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 setCreate an index named
i1based on thec1+1function on thet1_functable.obclient> CREATE TABLE t1_func(c1 INT, c2 INT); Query OK, 0 rows affected obclient> CREATE INDEX i1 ON t1_func(c1+1); Query OK, 0 rows affected obclient> SELECT index_name,index_type,table_owner,table_name,uniqueness FROM USER_INDEXES WHERE table_name='T1_FUNC'; +------------+-----------------------+-------------+------------+------------+ | INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS | +------------+-----------------------+-------------+------------+------------+ | I1 | FUNCTION-BASED NORMAL | SYS | T1_FUNC | NONUNIQUE | +------------+-----------------------+-------------+------------+------------+ 2 rows in set