第七章 SQL命令 CREATE INDEX（二）

UNIQUE关键字

使用UNIQUE关键字，可以指定索引中的每条记录都有一个唯一的值。
更具体地说，这确保了索引(以及包含索引的表)中的两条记录不能具有相同的排序值。
默认情况下，大多数索引使用大写字符串排序(使搜索不区分大小写)。
在本例中，值“Smith”和“SMITH”被认为是相等的，而不是唯一的。
CREATE INDEX不能指定非默认索引字符串排序规则。
通过在类定义中定义索引，可以为各个索引指定不同的字符串排序规则。

可以更改名称空间的默认排序规则，使字段/属性在默认情况下区分大小写。
更改此选项需要重新编译命名空间中的所有类并重新构建所有索引。
转到Management Portal，选择Classes选项，为存储的查询选择名称空间，并使用Compile选项重新编译相应的类。
然后重建所有指数。
它们将区分大小写。

注意:当表的数据被其他用户访问时，不要重建索引。
这样做可能会导致不准确的查询结果。

BITMAP 关键字

使用BITMAP关键字，你可以指定这个索引将是位图索引。
位图索引由一个或多个位字符串组成，其中位位置表示行id，每个位值表示该行字段(或合并字段名字段的值)的特定值的存在(1)或不存在(0)。
SQL在插入、更新或删除数据时维护这些位置位(作为压缩位串);
在使用位图索引和使用常规索引之间，INSERT、UPDATE或DELETE操作的性能没有显著差异。
位图索引对于许多类型的查询操作都是非常高效的。
它们具有以下特点:

• 只能在表（类）中定义位图索引，这些表（类）使用系统分配的 RowID 和正整数值，或者当 IDKEY 基于类型为 %Integer 和 MINVAL 的单个属性时使用主键 IDKEY 来定义自定义 ID 值 > 0，或键入 %Numeric，其中 SCALE = 0 且 MINVAL > 0。

可以使用$SYSTEM.SQL.Util.SetOption()方法 SET status=$SYSTEM.SQL.Util.SetOption("BitmapFriendlyCheck",1,.oldval) 设置一个系统范围的配置参数，在编译时检查该限制，确定是否允许在%Storage.SQL中定义位图索引。
此检查仅适用于使用%Storage.SQL的类。
默认值是0。
可以使用$SYSTEM.SQL.Util.GetOption("BitmapFriendlyCheck")来确定该选项的当前配置。

只能为使用默认(%Storage.Persistent)结构的表定义位图索引。
具有复合键的表，例如子表，不能使用位图索引。
如果使用DDL(而不是使用类定义)来创建表，那么它就满足了这个要求，并且可以使用位图索引。

• 位图索引应该只在可能的不同字段值的数量有限且相对较小的情况下使用。
  例如，对于性别、国籍或时区字段，位图索引是一个很好的选择。
  位图不应该在具有UNIQUE约束的字段上使用。
  如果一个字段可以有超过10,000个不同的值，或者多个索引字段可以有超过10,000个不同的值，那么就不应该使用位图。
• 位图索引在WHERE子句中与逻辑AND和OR操作结合使用时非常有效。
  如果两个或多个字段通常被组合查询，那么为这些字段定义位图索引可能是有利的。

BITMAPEXTENT关键字

位图区段索引是表本身的位图索引。
SQL使用这个索引来提高COUNT(*)的性能，它返回表中记录(行)的数量。
一个表最多可以有一个位图扩展索引。
创建多个位图区段索引将导致一个带有%msg的SQLCODE -400错误ERROR #5445: Multiple Extent indices defined: DDLBEIndex

所有使用CREATE TABLE定义的表都会自动定义位图范围索引。
这个自动生成的索引被分配为索引名称DDLBEIndex和SQL MapName %%DDLBEIndex。
定义为类的表可以有位图范围索引，索引名称和SQL MapName为$ClassName。

可以使用CREATE BITMAPEXTENT INDEX向表中添加位图区段索引，或者重命名自动生成的位图区段索引。
指定的index-name应该是表的table-name对应的类名。
这将成为索引的SQL MapName。
不能指定字段名或WITH DATA子句。

以下示例使用索引名DDLBEIndex和SQL MapName Patient创建位图区索引。如果Sample.Patient已具有%%DDLBEIndex位图区索引，则此示例将该索引重命名为SQL MapName Patient：

  &sql(CREATE BITMAPEXTENT INDEX Patient ON TABLE Sample.Patient)
  WRITE !,"SQL code: ",SQLCODE

BITSLICE 关键字

使用BITSLICE关键字，可以指定此索引将是位片索引。位片索引专门用于计算中使用的数字数据。位片索引将每个数值数据值表示为二进制位串。位片索引不是使用布尔标志来索引数值数据值(就像在位图索引中那样)，而是为每个数值创建一个位串，为每个记录创建一个单独的位串。这是一种高度专门化的索引类型，应该仅用于快速聚合计算。例如，以下内容将是位切片索引的候选对象：

SELECT SUM(Salary) FROM Sample.Employee

可以为字符串数据字段创建位片索引，但位片索引将这些数据值表示为规范数字。换句话说，任何非数字字符串(如“abc”)都将被索引为0。这种类型的位片索引可用于快速计数具有字符串字段值的记录，而不计算那些为空的记录。

不应在WHERE子句中使用位片索引，因为SQL查询优化器不使用位片索引。

使用INSERT、UPDATE或DELETE操作填充和维护位片索引比使用位图索引或常规索引慢得多。在频繁更新的字段上使用多个位片索引和/或使用位片索引可能具有显著的性能代价。

位片索引只能用于系统分配的行ID为正整数值的记录。位片索引只能用于单个字段名。不能指定WITH DATA子句。

重建索引

使用CREATE INDEX语句创建索引会自动构建索引。但是，在某些情况下，可能希望显式重新生成索引。

注意：如果其他用户正在访问表的数据，则在重建索引时必须采取其他步骤。如果不这样做，可能会导致查询结果不准确。有关更多详细信息，请参阅在活动系统上构建索引。

可以按如下方式构建/重新构建索引：
- 使用构建索引SQL命令。
- 使用管理门户重建指定类(表)的所有索引。
- 使用%BuildIndices()方法。

要重建非活动表的所有索引，请执行以下操作：

  SET status = ##class(myschema.mytable).%BuildIndices()

默认情况下，此命令在重建索引之前清除索引。可以覆盖此清除默认值，并使用%PurgeIndices()方法显式清除指定的索引。如果对一定范围的ID值调用%BuildIndices()，则默认情况下 IRIS不会清除索引。

还可以清除/重建指定的索引：

  SET status = ##class(myschema.mytable).%BuildIndices($ListBuild("NameIDX","SpouseIDX"))

如上所述，如果索引损坏，可能需要清除/重建索引，或者更改索引的区分大小写。要重新压缩位图索引，请使用%SYS.Maint.Bitmap方法，而不是清除/重建。

示例

下面的嵌入式SQL示例创建了一个名为Fred的表，然后在Fred表的Lastword和Firstword字段上创建了一个名为“FredIndex”的索引(通过从提供的名称“Fred_Index”中去掉标点)。

ClassMethod CreateIndex()
{
    &sql(
        CREATE TABLE Fred 
        (
            TESTNUM     INT NOT NULL,
            FIRSTWORD   CHAR (30) NOT NULL,
            LASTWORD    CHAR (30) NOT NULL,
            CONSTRAINT FredPK PRIMARY KEY (TESTNUM)
        )
    )
    if SQLCODE = 0 { 
        w !,"表创建" 
    } elseif SQLCODE = -201 { 
        w !,"表已经存在" 
    } else { 
        w !,"SQL表创建错误代码: ",SQLCODE
        q 
    }
    &sql(
        CREATE INDEX Fred_Index
        ON TABLE Fred
        (
            LASTWORD,FIRSTWORD
        )
    )
    if SQLCODE = -324 {
        w !,"索引已经存在" 
        q 
    } elseif SQLCODE = 0 { 
        w !,"创建索引" 
    } else { 
        w !,"SQL索引创建错误代码是: ",SQLCODE 
        q 
    }
}

下面的示例在Staff表的City字段上创建了一个名为“CityIndex”的索引:

CREATE INDEX CityIndex ON Staff (City)

下面的示例在Staff表的EmpName字段上创建了一个名为“EmpIndex”的索引。
UNIQUE约束用来避免在字段中有相同值的行:

CREATE UNIQUE INDEX EmpIndex ON TABLE Staff (EmpName)

下面的示例在Purchases表的SKU字段上创建一个名为“SKUIndex”的位图索引。
BITMAP关键字表示这是位图索引:

CREATE BITMAP INDEX SKUIndex ON TABLE Purchases (SKU)