第六章 SQL命令 CREATE INDEX（一）

为表创建索引。

大纲

CREATE [UNIQUE | BITMAP | BITMAPEXTENT | BITSLICE ] INDEX index-name
        ON [TABLE] table-name
       (field-name, ...)
       [AS index-class-name [ (parameter-name = parameter_value, ... ) ] ]
       [WITH DATA  (datafield-name, ...)]

参数

• UNIQUE - 可选的——一个约束，确保表中不会有两行索引中所有字段的值相同。不能为位图或位片索引指定此关键字。UNIQUE关键字后面可以跟(或被)CLUSTERED或NONCLUSTERED关键字替换。
  这些关键字是no-ops;
  它们是为了与其他供应商兼容而提供的。
• BITMAP - 可选—表示创建位图索引。
  位图索引允许对具有少量不同值的字段进行快速查询。
• BITMAPEXTENT - 可选-表示应该创建位映射范围索引。
  一个表最多只能创建一个位映射范围索引。
  BITMAPEXTENT中没有指定字段名。
• BITSLICE - 可选—创建位片索引。
  位片索引可以非常快速地计算某些表达式，比如求和和范围条件。
  这是一种专门化的索引类型，只能用于解决非常特定的问题。
• index-name - 定义的索引。名称是一个标识符。
• table-name - 为其定义索引的现有表的名称。不能为视图创建索引。表名可以是限定的(schema.table)，也可以是非限定的(Table)。未限定的表名采用缺省模式名。
• field-name - 用作索引基础的一个或多个字段名。字段名必须用括号括起来。多个字段名称用逗号分隔。每个字段名后面可以跟一个ASC或DESC关键字。这些关键词是禁止操作；提供它们是为了与其他供应商兼容。
• AS index-class-name - 可选—定义索引的类，可选地后跟圆括号，圆括号中包含一对或多对逗号分隔的参数名称和关联值。
• WITH DATA (datafield-name) - 可选—要定义为索引数据属性的一个或多个字段名。字段名必须用括号括起来。多个字段名称用逗号分隔。指定位图或位片索引时，不能指定WITH DATA子句。

描述

CREATE INDEX在命名表的指定字段(或多个字段)上创建排序索引。IRIS使用索引来提高查询操作的性能。 IRIS在INSERT、UPDATE和DELETE操作期间自动维护索引，这种索引维护可能会对这些数据修改操作的性能产生负面影响。

可以使用CREATE INDEX命令或通过将索引定义添加到类定义来创建索引，可以使用DROP INDEX命令删除索引。

CREATE INDEX可用于创建以下三种类型的索引中的任何一种：
- 常规索引(Type=index)：指定CREATE INDEX(用于非唯一值)或CREATE UNIQUE INDEX(用于唯一值)。
- 位图索引(Type=bitmap):指定CREATE bitmap index。
- bitslice索引(Type=bitslice):指定CREATE bitslice index。

还可以使用%Dictionary.IndexDefinition类定义索引。

可以使用CREATE INDEX向分片表添加索引。

权限与锁

CREATE INDEX命令属于特权操作。
用户必须具有%ALTER_TABLE管理权限才能执行CREATE INDEX。
如果不这样做，将导致%msg的SQLCODE -99错误，User 'name' does not have %ALTER_TABLE privileges。
如果拥有适当的权限，可以使用GRANT命令为用户或角色分配%ALTER_TABLE权限。
管理权限是特定于名称空间的。

用户必须对指定的表具有%ALTER权限。
如果用户是表的Owner(创建者)，则自动授予该用户对该表的%ALTER权限。
否则，用户必须被授予%ALTER权限。
如果不这样做，将导致一个SQLCODE -99错误，其中%msgUser 'name' does not have required %ALTER privilege needed to change the table definition for 'Schema.TableName'。
可以通过调用%CHECKPRIV命令来确定当前用户是否具有%ALTER权限。
可以使用GRANT命令为指定的表分配%ALTER权限。

• 不能在从持久类投射的表上使用CREATE INDEX，除非表类定义包含[DdlAllowed]。
  否则，操作将失败，出现SQLCODE -300错误，并且%msg DDL not enabled for class 'Schema.tablename'。
• 不能在部署的持久类映射的表上使用CREATE INDEX。
  此操作失败，出现SQLCODE -400错误，并显示%msg Unable to execute DDL that modifies a deployed class: 'classname'。

CREATE INDEX语句获取表名上的表级锁。
这可以防止其他进程修改表的数据。
这个锁在CREATE INDEX操作结束时自动释放。
CREATE INDEX在相应的类定义上维护一个锁，直到创建索引操作(包括索引数据的填充)完成。

在创建索引时，表不能被其他进程以EXCLUSIVE模式或SHARE模式锁定。
在被锁定的表上尝试CREATE INDEX操作会导致SQLCODE -110错误，并带有%msg，如下所示: Unable to acquire exclusive table lock for table 'Sample.MyTest'。

仅支持兼容性选项

SQL仅接受以下``CREATE INDEX选项用于解析目的，以帮助将现有SQL代码转换为 SQL。
这些选项不提供任何实际的功能。

CLUSTERED | NONCLUSTERED owner.catalog. ASC | DESC

下面的例子展示了这些no-op关键字的位置:

CREATE UNIQUE CLUSTERED INDEX index-name
  ON TABLE owner.catalog.schema.table
   (field1 ASC, field2 DESC)

索引名称

索引的名称在给定的表中必须是唯一的。
索引名称遵循标识符约定，受以下限制。
默认情况下，索引名是简单的标识符;
索引名称可以是分隔的标识符。
索引名不能超过128个字符。
索引名不区分大小写。

IRIS使用提供的名称(它引用为“SqlName”)在类和全局中生成相应的索引属性名称。
此索引属性名称仅包含字母和数字字符(字母和数字)，长度最多为96个字符。
为了生成索引属性名， IRIS首先从提供的SqlName中去掉标点字符，然后生成一个只有96个字符(或更少)的唯一标识符来创建唯一的索引属性名。
- 索引名称可以与字段、表或视图名称相同，但不建议重复。
- 索引属性名(在标点剥离后)必须是唯一的。
如果指定了重复的SQL索引名，系统将生成SQLCODE -324错误。
如果指定的SQL索引名称仅在标点字符方面与现有SQL索引名称不同， IRIS将最后一个字符替换为大写字母(以“a”开头)，以创建唯一的索引属性名称。
因此，可以(尽管不建议)创建仅在标点字符上不同的SQL索引名。
- 索引属性名必须以字母开头。
因此，索引名的第一个字符或去掉初始标点字符后的第一个字符必须是字母。
有效的字母是通过$ZNAME测试的字符。
如果SQL索引名称的第一个字符是一个标点符号字符(%或_)，第二个字符是一个数字， IRIS会附加一个小写的“n”作为剥离后的索引属性名称的第一个字符。
- 索引名可能比31个字符长得多，但是前31个字母数字字符不同的索引名更容易处理。

管理门户SQL接口Catalog Details显示每个索引的SQL索引名称(SQL Map name)和相应的索引属性名称(index name)。

当试图创建与现有索引同名的索引时，将会发生什么呢?

现有索引

默认情况下， IRIS拒绝创建与该表的现有索引同名的索引，并发出SQLCODE -324错误。
要确定当前设置，调用$SYSTEM.SQL.CurrentSettings()，它为现有索引设置显示一个Allow DDL CREATE INDEX for existing index。
默认值是0，这是该选项的推荐设置。
如果该选项设置为1, IRIS将从类定义中删除现有索引，然后通过执行CREATE index重新创建它。
它从CREATE index中指定的表中删除指定的索引。
此选项允许删除/重新创建UNIQUE约束索引(不能使用DROP index命令完成)。
要删除/重新创建主键索引，请参考ALTER TABLE命令。

在管理门户、系统管理、配置、SQL和对象设置、SQL中，可以通过选择“忽略冗余DDL语句”复选框，在系统范围内设置此选项(以及其他类似的创建、更改和删除选项)。

但是，即使将此选项设置为允许重新创建现有索引，如果表包含数据，则不能重新创建Primary Key IDKEY索引。
尝试这样做会产生SQLCODE -324错误。

表明

必须指定现有表的名称。

• 如果table-name是一个不存在的表，则CREATE INDEX失败，出现SQLCODE -30错误，并将%msg设置为Table 'SQLUSER.MYTABLE' does not exist。
• 如果table-name是视图，则CREATE INDEX失败，出现SQLCODE -30错误，并将%msg设置为Attempt to CREATE INDEX 'My_Index' on view SQLUSER.MYVIEW failed. Indices only supported for tables, not views。

创建索引修改表的定义;
如果没有更改表定义的权限，则CREATE INDEX将失败，出现SQLCODE -300错误，并将%msg设置为DDL not enabled for class 'schema.tablename'。

字段名称

必须指定至少一个要索引的字段名。
指定一个字段名或用逗号分隔的字段名列表，这些字段名用括号括起来。
在索引定义中允许并保留重复的字段名。
指定多个字段可能会提高GROUP BY操作的性能，例如，先按州分组，然后再按每个州中的城市分组。
通常，应该避免在一个或多个具有大量重复数据的字段上建立索引。
例如，在人员数据库中，在Name字段上建立索引是合适的，因为大多数名称都是惟一的。
在State字段上建立索引(在大多数情况下)是不合适的，因为存在大量重复的数据值。
指定的字段必须在表中定义，或者在表的持久类的超类中定义。
(当然，所有的类都必须经过编译。)
指定不存在的字段将产生SQLCODE -31错误。

除了普通的数据字段，还可以使用CREATE INDEX来创建索引:

• 在SERIAL字段(%Counter字段)上。
• 在IDENTITY字段上。
• 在集合的ELEMENTS或KEYS值上。

不能在流值字段上创建索引。

如果一个IDKEY字段(属性)是SQL Computed，则不能创建具有多个IDKEY字段的索引。
这个限制不适用于单个字段IDKEY索引。
因为索引中的多个IDKEY字段是用“||”(双竖条)字符分隔的，所以不能在IDKEY字段数据中包含这个字符串。

嵌入对象中的字段(%SerialObject)

要在嵌入式对象中创建字段索引，需要在引用该嵌入式对象的表(%Persistent类)中创建索引。
在CREATE INDEX中，字段名指定了表(%Persistent object)中引用字段的名称(通过下划线连接到嵌入对象(%SerialObject)中的字段名)，如下面的示例所示:

CREATE INDEX StateIdx ON TABLE Sample.Person (Home_State)

这里Home是Sample.Person中的一个字段，它引用嵌入式对象Sample.Address，其中包含State字段。

只有那些与持久类引用属性相关联的嵌入对象记录才会被索引。不能直接索引%SerialObject属性。

索引类名

此可选语法允许用户使用SQL指定函数索引的类和参数。

SQL示例如下:

CREATE INDEX HistIdx ON TABLE Sample.Person (MedicalHistory) AS %iFind.Index.Basic (LANGUAGE='en', LOWER=1)

WITH DATA子句

指定此子句可能允许仅通过读取索引来解析查询，这将极大地减少磁盘I/O量，从而提高性能。

如果field-name使用字符串排序，你应该在field-name和WITH DATA datfield -name中指定相同的字段;
这允许检索未排序的值，而不必转到主映射。
如果field-name中的值不使用字符串排序规则，那么在WITH DATA datfield -name中指定该字段没有任何好处。

可以在WITH DATA datfield -name中指定未被索引的字段。
这允许从索引中满足更多的查询，而不需要访问主映射。
权衡是你想维护多少个索引;
而向索引中添加数据会使索引变得更大，这会减慢不需要数据的操作。

可以在WITH DATA DATA -name中指定在表的持久化类的超类中定义的字段。