You can use the CREATE INDEX statement to create an index on a table.
Background
OceanBase Database allows you to create an index on both partitioned and non-partitioned tables. An index can be either a local index or a global index. In addition, it can either be a unique index or a normal index. A local unique index on a partitioned table must include a partitioning key of the table.
This topic describes how to create an index on a non-partitioned table.
Syntax
In Oracle mode, use the following syntax to create an index:
Statement 1
Use the CREATE TABLE statement to create an index.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
(table_definition_list) [table_option_list] [partition_option] [AS] select;
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
LIKE table_name;
table_definition:
column_definition
| [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
| [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
| {INDEX | KEY} [index_name] index_desc
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[DEFAULT const_value] [AUTO_INCREMENT]
[NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
index_desc:
(column_desc_list) [index_type] [index_option_list]
column_desc_list:
column_desc [, column_desc ...]
column_desc:
column_name [(length)] [ASC | DESC]
index_type:
USING BTREE
index_option_list:
index_option [ index_option ...]
index_option:
[GLOBAL | LOCAL]
| block_size
| compression
| STORING(column_name_list)
| comment
Statement 2
Use the CREATE INDEX statement to create an index.
CREATE [UNIQUE][hint_options] 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...]
Examples
Example 1
Create a table named
t_idx1.obclient> CREATE TABLE t_idx9( id NUMBER, name varchar2(18), c_date date, PRIMARY KEY (id), index idx9 (c_date) ); Query OK, 0 rows affectedQuery the indexes on the
t_idx1table.obclient> SELECT INDEX_NAME,INDEX_TYPE, TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='T_IDX9'; +------------------------------+------------+-------------+------------+------------+------------+ | INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | TABLE_TYPE | UNIQUENESS | +------------------------------+------------+-------------+------------+------------+------------+ | T_IDX9_OBPK_1651134874777227 | NORMAL | SYS | T_IDX9 | TABLE | UNIQUE | | IDX9 | NORMAL | SYS | T_IDX9 | TABLE | NONUNIQUE | +------------------------------+------------+-------------+------------+------------+------------+ 2 rows in set
Example 2
Create a table named
tbl1.obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(10)); Query OK, 0 rows affectedAdd the
tbl1_idx1index on thetbl1table.obclient> CREATE INDEX tbl1_idx1 ON tbl1 (col1, col2); Query OK, 0 rows affectedView the index on the
TBL1table.obclient> SELECT * FROM USER_INDEXES WHERE table_name='TBL1'\G *************************** 1. row *************************** INDEX_NAME: TBL1_OBPK_1651137140305717 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 *************************** 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 2 rows in set
Example 3
Create a table named
tbl3.obclient> CREATE TABLE tbl3(col1 INT PRIMARY KEY,col2 VARCHAR(10)); Query OK, 0 rows affectedAdd an index named
tbl3_idx3in thetbl3table, and set the degree of parallelism (DOP) to5.obclient> CREATE /*+ PARALLEL(10) */ INDEX tbl3_idx3 ON tbl3(col1, col2); Query OK, 0 rows affected