创建索引

您可以通过 CREATE INDEX 语句为表创建索引。

背景信息

OceanBase 数据库支持在非分区表和分区表上创建索引。

本文主要介绍如何在非分区表上创建索引，分区表上索引的创建请参见 分区表的索引。

语法

OceanBase 数据库 Oracle 模式下，创建索引的 SQL 语法格式如下：

语法 1

通过 CREATE TABLE 创建索引。

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

语法 2

通过 CREATE INDEX 创建索引。

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...]

示例

示例 1

1. 创建 t_idx9 表。

   obclient> CREATE TABLE t_idx9(
               id NUMBER,
               name varchar2(18),
               c_date date,
               PRIMARY KEY (id),
               index idx9 (c_date)
             );
   Query OK, 0 rows affected
   

2. 查看 t_idx9 表的索引。

   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
   

示例 2

1. 创建 tbl1 表。

   obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(10));
   Query OK, 0 rows affected
   

2. 在 tbl1 表添加索引 tbl1_idx1。

   obclient> CREATE INDEX tbl1_idx1 ON tbl1 (col1, col2);
   Query OK, 0 rows affected
   

3. 查看 TBL1 表的索引。

   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