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.
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]
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 column name used to create the index. |
| index_type | The index type. Only USING BTREE is supported for you to create B-tree indexes. |
| UNIQUE | The 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. |
| COMPRESSION | The compression algorithm. |
| BLOCK_SIZE | The size of the micro block. |
| 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 tbl1 table.
/*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 indexes on the tbl1 table.*/
obclient> SELECT * FROM USER_INDEXES WHERE table_name='TBL1';
+----------------------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
| INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | UNIQUENESS | COMPRESSION | PREFIX_LENGTH | TABLESPACE_NAME | INI_TRANS | MAX_TRANS | INITIAL_EXTENT | NEXT_EXTENT | MIN_EXTENTS | MAX_EXTENTS | PCT_INCREASE | PCT_THRESHOLD | INCLUDE_COLUMN | FREELISTS | FREELIST_GROUPS | PCT_FREE | LOGGING | BLEVEL | LEAF_BLOCKS | DISTINCT_KEYS | AVG_LEAF_BLOCKS_PER_KEY | AVG_DATA_BLOCKS_PER_KEY | CLUSTERING_FACTOR | STATUS | NUM_ROWS | SAMPLE_SIZE | LAST_ANALYZED | DEGREE | INSTANCES | PARTITIONED | TEMPORARY | GENERATED | SECONDARY | BUFFER_POOL | FLASH_CACHE | CELL_FLASH_CACHE | USER_STATS | DURATION | PCT_DIRECT_ACCESS | ITYP_OWNER | ITYP_NAME | PARAMETERS | GLOBAL_STATS | DOMIDX_STATUS | DOMIDX_OPSTATUS | FUNCIDX_STATUS | JOIN_INDEX | IOT_REDUNDANT_PKEY_ELIM | DROPPED | VISIBILITY | DOMIDX_MANAGEMENT | SEGMENT_CREATED |
+----------------------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
| TBL1_OBPK_1652951802621413 | NORMAL | SYS | TBL1 | TABLE | UNIQUE | ENABLED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VALID | NULL | NULL | NULL | 1 | NULL | NO | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NO | NULL | NO | VISIBLE | NULL | NULL |
| TBL1_IDX1 | NORMAL | SYS | TBL1 | TABLE | NONUNIQUE | ENABLED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | VALID | NULL | NULL | NULL | 1 | NULL | NO | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NO | NULL | NO | VISIBLE | NULL | NULL |
+----------------------------+------------+-------------+------------+------------+------------+-------------+---------------+-----------------+-----------+-----------+----------------+-------------+-------------+-------------+--------------+---------------+----------------+-----------+-----------------+----------+---------+--------+-------------+---------------+-------------------------+-------------------------+-------------------+--------+----------+-------------+---------------+--------+-----------+-------------+-----------+-----------+-----------+-------------+-------------+------------------+------------+----------+-------------------+------------+-----------+------------+--------------+---------------+-----------------+----------------+------------+-------------------------+---------+------------+-------------------+-----------------+
2 rows in set