--- title: "创建索引 | OceanBase 文档中心" description: 创建索引 您可以通过 CREATE INDEX 语句为表创建索引。 背景信息 OceanBase 数据库支持在非分区表和分区表上创建索引。 本文主要介绍如何在非分区表上创建索引，分区表上索引的创建请参见 分区表的索引 。 语法 OceanBase 数据库 Oracle 模式下，创建索引的 SQL 语法格式如下： 语法… image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*P8CuR4UJ_FkAAAAAAAAAAAAADiGDAQ/original) OceanBase 数据库分布式版 - V 3.2.3 企业版 LTS # 创建索引 更新时间：2023-07-19 13:03:07 您可以通过 `CREATE INDEX` 语句为表创建索引。 ## 背景信息 OceanBase 数据库支持在非分区表和分区表上创建索引。 本文主要介绍如何在非分区表上创建索引，分区表上索引的创建请参见 [分区表的索引](https://www.oceanbase.com/docs/enterprise-oceanbase-database-cn-10000000000357626)。 ## 语法 OceanBase 数据库 Oracle 模式下，创建索引的 SQL 语法格式如下： ### 语法 1 通过 `CREATE TABLE` 创建索引。 ```sql 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` 创建索引。 ```sql 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` 表。 ```sql 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` 表的索引。 ```sql 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` 表。 ```sql obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(10)); Query OK, 0 rows affected ``` 2. 在 `tbl1` 表添加索引 `tbl1_idx1`。 ```sql obclient> CREATE INDEX tbl1_idx1 ON tbl1 (col1, col2); Query OK, 0 rows affected ``` 3. 查看 `TBL1` 表的索引。 ```sql 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 ``` 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线