CREATE INDEX 语句

本文介绍如何使用CREATE INDEX语句来创建表的索引,包括不同类型的索引及其使用方法。索引能够显著提升数据库查询性能,文章还涵盖了索引的参数设置、使用注意事项及权限要求。
说明

此语句用于创建指定表的索引。索引可以提高数据库的性能。

语法

CREATE[VIRTUAL][UNIQUE][CLUSTERED]INDEXindex-name
ON[owner.]table-name
(column-name[ASC|DESC],...
|function-name(argument[,...])AScolumn-name)
[{IN|ON}dbspace-name]

参数

VIRTUAL 关键字VIRTUAL 关键字主要由索引顾问使用。在索引顾问对查询计划进行求值期间以及使用 PLAN 函数时,虚拟索引将模拟真实物理索引的属性。可以结合使用虚拟索引和 PLAN 函数以充分利用索引在性能方面的提高,从而避免创建真实索引这一通常又耗时又消耗资源的过程。

虚拟索引对其它连接是不可见的,并且在关闭连接时删除。在对查询的实际执行计划进行求值时不使用虚拟索引,所以不会对性能造成影响。

虚拟索引的列数限制为四列。

有关详细信息,请参见启动索引顾问索引顾问概述

CLUSTERED 关键字CLUSTERED 属性使表行按照与索引对应的大概键顺序来存储。当服务器尝试保留键顺序时,不保证实施总体群集。

如果存在聚簇索引,LOAD TABLE 语句便按照索引键的顺序在表中插入行,而 INSERT 语句将按照键顺序的定义尝试将新行添加到包含相邻行的表页上。

有关详细信息,请参见使用聚簇索引

UNIQUE 关键字UNIQUE 属性确保表中不会有两行的值在所有索引列中相同。每个索引键必须是唯一的,或者至少在一列中包含 NULL。

表的唯一约束和唯一索引是有区别的。唯一索引的列可以为 NULL,而唯一约束的列不能为 NULL。外键可以引用主键或者具有唯一约束的列,但不能引用具有唯一索引的列,因为唯一索引可以包括多个 NULL 实例。

ASC | DESC 选项除非显式指定降序 (DESC),否则列以升序 (increasing) 排序。不管索引是升序排列还是降序排列,在执行升序或降序 ORDER BY 操作时都会使用索引。但是,如果通过混合的升序和降序属性来执行 ORDER BY,则仅当索引是用同样的升序和降序属性创建的时才使用索引。

function-name 参数function-name 参数为内置函数创建索引。CREATE INDEX 语句的这种格式是执行以下操作的便捷方法:

  1. 向表中添加名为 column-name 的计算列。该列通过指定为内置函数的 COMPUTE 子句以及任何所指定的参数来定义。该列的数据类型依函数的返回类型而定。

  2. 填充表中现有行的计算列。

  3. 创建列的索引。

删除索引不会造成相关联的列被删除。CREATE INDEX 的这一格式不可用于所声明的临时表。

有关列计算列的详细信息,请参见使用计算列

IN | ON 子句缺省情况下,索引放在它的表所在的同一个数据库文件中。通过指定用来存放索引的 dbspace 名称,可以将索引放在单独的数据库文件中。此特性主要用于帮助大型数据库克服文件大小限制。

有关限制的详细信息,请参见大小和数量限制

用法

CREATE INDEX 语句在指定表的指定列上创建排序索引。索引自动用于改进向数据库发出的查询的性能,以及通过 ORDER BY 子句对查询排序。索引一旦创建,在 SQL 语句中就不会再引用它,除非要校验 (VALIDATE INDEX) 或删除 (DROP INDEX) 它。

不能创建视图索引。

  • 索引所有权在 CREATE INDEX 语句中无法指定索引所有者。索引总是归表的所有者所拥有。索引名称对每个所有者必须是唯一的。

  • 不存在视图索引无法为视图创建索引。

  • 索引名称空间每个索引的名称对给定的表必须是唯一的。

  • 排它表使用只要 CREATE INDEX 影响了当前正由其它连接使用的表,就会禁止该语句。CREATE INDEX 可能很耗时,服务器在处理该语句时不会处理引用同一个表的请求。

  • 自动创建的索引Adaptive Server Anywhere 自动创建主键和唯一约束的索引。这些自动创建的索引保存在表所在的同一数据库文件中。

权限

必须是表的所有者,或者有 DBA 权限或 REFERENCES 权限。

副作用

自动提交。为内置函数创建索引也会生成一个检查点。

更新列统计信息。

另请参见
标准和兼容性
  • SQL/92供应商扩展。

  • SQL/99供应商扩展。

  • SybaseAdaptive Server Enterprise 的 CREATE INDEX 语句比 Adaptive Server Anywhere 的复杂。虽然在 Adaptive Server Anywhere 中允许使用 Adaptive Server Enterprise 语法,但一些子句和关键字会被忽略。

    • Adaptive Server Enterprise 11.5 的完整语法如下:

      CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]
      INDEXindex-name
      ON[[database.]owner.]table_name
      (column_name[,column_name], ...)
      [WITH{
      {FILLFACTOR|MAX_ROWS_PER_PAGE}=x,
      CONSUMERS=x,
      ...IGNORE_DUP_KEY,
      ...SORTED_DATA,
      [IGNORE_DUP_ROW|ALLOW_DUP_ROW]
      }]
      [ONsegment_name]

    Adaptive Server Anywhere 允许使用(通过忽略)以下关键字:

    • FILLFACTOR

    • IGNORE_DUP_KEY

    • SORTED_DATA

    • IGNORE_DUP_ROW

    • ALLOW_DUP_ROW

    在 Adaptive Server Enterprise 和 Adaptive Server Anywhere 中,索引的物理放置方式是不同的。Adaptive Server Anywhere 支持 ON segment-name 子句,但 segment-name 引用 dbspace。

    Adaptive Server Anywhere 中的唯一索引允许包含 NULL,其它方面都相同。Adaptive Server Enterprise 中的唯一索引允许最多一个空值的行,不允许重复的行。

    对于 Adaptive Server Anywhere 和 Enterprise,给定表的索引名称必须都是唯一的。

示例

为 employee 表创建一个有两列的索引。

CREATE INDEX employee_name_index
ON employee
( emp_lname, emp_fname )

为 sales_order_items 表的 prod_id 列创建索引。

CREATE INDEX item_prod
ON sales_order_items
( prod_id )

使用 SORTKEY 函数为 product 表的 description 列创建索引,排序顺序根据俄语归类而定。该语句有一个副作用,向表中添加一个计算列 desc_ru。

CREATE INDEX ix_desc_ru
ON product (
 SORTKEY( description, 'rusdict' )
 AS desc_ru )
<think>好的,我现在需要处理用户关于达梦数据库创建索引的SQL语句示例的请求。首先,我要回顾之前的对话历史,确保理解上下文。用户之前询问了GBase 8s的create index示例,现在转向达梦数据库,需要类似的指导。 用户提到参考了站内引用[1],其中主要介绍了达梦数据库的基础语法,但用户明确指出该引用仅供参考,实际需求是关于创建索引的示例。因此,我需要专注于达梦数据库特有的语法,而不是重复之前GBase 8s的内容。 接下来,我需要确认达梦数据库CREATE INDEX语法结构。达梦数据库兼容标准SQL,但可能有自己的扩展和特定选项。例如,ONLINE选项、STORAGE子句、压缩功能等,这些都是达梦可能支持的。同时,需要注意索引类型,如唯一索引、函数索引、复合索引等,这些都是常见的,但具体语法可能有所不同。 还要考虑用户可能的进阶需求,比如并行创建、表空间指定、索引维护等。需要提供不同场景下的示例,帮助用户全面了解。此外,用户可能关心性能优化,比如填充因子、并行度设置,这些在示例中加入会有帮助。 需要验证语法是否正确,确保示例能够在达梦数据库中实际运行。比如,达梦是否支持CLUSTER INDEX,或者是否有不同的聚簇索引语法。可能需要进行快速查阅或依靠已有知识库确认。 另外,用户之前的回答中提到了GBase 8s的注意事项,如索引命名规范、复合索引列顺序、ONLINE模式等,这些在达梦数据库中是否有对应或不同的实现方式?需要指出达梦特有的注意事项,比如STORAGE子句的使用,或者压缩选项。 最后,生成相关问题,确保覆盖用户可能后续提出的疑问,如删除索引、性能优化、查看索引信息等。这有助于用户进一步探索和解决问题。</think>以下是在达梦数据库(DM8)中创建索引的SQL语句示例及技术说明: ### 标准索引创建 ```sql -- 单列B树索引(默认类型) CREATE INDEX idx_emp_name ON employee(ename); -- 多列复合索引(最多支持32列) CREATE INDEX idx_order_info ON orders(customer_id, order_date DESC); ``` ### 高级索引类型 ```sql -- 唯一索引(自动校验数据唯一性) CREATE UNIQUE INDEX uidx_emp_id ON employee(empno); -- 函数索引(支持表达式计算) CREATE INDEX idx_dept_upper ON department(UPPER(dept_name)); -- 位图索引(适用于低基数列) CREATE BITMAP INDEX bidx_emp_gender ON employee(gender); -- 全文索引(文本检索专用)[^1] CREATE CONTEXT INDEX ctx_idx_resume ON employee(resume) LEXER DEFAULT_LEXER; ``` ### 性能优化参数 ```sql -- 指定表空间和并行度 CREATE INDEX idx_sales_amount ON sales(amount) STORAGE(ON "INDEX_TS") PARALLEL 4; -- 在线创建索引(避免锁表) CREATE INDEX idx_cust_phone ONLINE ON customer(mobile); -- 压缩索引(减少存储空间) CREATE INDEX idx_log_content ON sys_log(content) COMPRESS LEVEL 3; ``` ### 系统级管理 ```sql -- 查看索引元数据 SELECT * FROM DBA_INDEXES WHERE TABLE_NAME='EMPLOYEE'; -- 重建索引(维护索引性能) ALTER INDEX idx_emp_name REBUILD ONLINE; ``` $$ \text{索引选择策略公式:} \\ \text{索引收益} = \frac{\text{查询频率} \times \text{筛选率}}{\text{维护成本} + \text{存储成本}} $$ ### 最佳实践建议 1. 时间类型字段推荐使用`DESC`排序创建索引[^1] 2. 组合索引遵循"最左前缀"原则(如:$(a,b,c)$ 索引可支持 $a$、$a,b$、$a,b,c$ 查询) 3. 超过2000万行的大表建议使用`ONLINE`模式创建 4. 定期执行`STAT 100 ON TABLE_NAME`更新统计信息
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值