南大通用GBase8s 常用SQL语句（五十二）

索引的双向遍历

如果对一列创建索引时不指定 ASC 或 DESC 关键字，则缺省情况下值以升序顺序存储；但是数据库服务器的双向遍历能力让您对一列仅创建一个索引并将该索引用于查询；这些查询指定结果以排序列的升序还是降序来排序。

由于此能力，是否将单列索引创建为升序或者降序索引无关紧要。不管您为索引选择哪种存储顺序，数据库服务器在处理查询时可按照升序或降序顺序来遍历该索引。

然而，如果您在表上创建一个复合索引，可能需要 ASC 和 DESC 关键字。例如，如果您希望输入其 ORDER BY 子句按照多个列排序并且按照不同的顺序对每个列排序的 SELECT 语句，并且您希望对此查询使用一个索引，则需要创建一个与 ORDER BY 列对应的复合索引。例如，假设您希望输入以下查询：

SELECT stock_num, manu_code, description, unit_price

        FROM stock ORDER BY manu_code ASC, unit_price DESC;

此查询通过 manu_code 列的值升序排序，然后按照 unit_price 列的值降序排序。要对此查询使用索引，您需要发出一个与 ORDER BY 子句的需求相对应的 CREATE INDEX 语句。例如，您可输入以下两个语句之一来创建索引：

CREATE INDEX stock_idx1 ON stock

        (manu_code ASC, unit_price DESC);

        CREATE INDEX stock_idx2 ON stock

        (manu_code DESC, unit_price ASC);

为此查询使用的复合索引（stock_idx1 或 stock_idx2）不能用于您使用 ORDER BY 子句为两列指定相同的排序方向的查询。例如，假设您希望输入以下查询：

SELECT stock_num, manu_code, description, unit_price

        FROM stock ORDER BY manu_code ASC, unit_price ASC;

        SELECT stock_num, manu_code, description, unit_price

        FROM stock ORDER BY manu_code DESC, unit_price DESC;

如果您希望使用复合索引来提高这些查询的性能，则需要输入以下 CREATE INDEX 语句之一。可使用两种已创建的索引之一（stock_idx3 或 stock_idx4 ）来提高先前的查询的性能。

CREATE INDEX stock_idx3 ON stock

        (manu_code ASC, unit_price ASC);

        CREATE INDEX stock_idx4 ON stock

        (manu_code DESC, unit_price DESC);

可在一列上创建不超过一个的升序索引和不超过一个的降序索引。由于数据库服务器的双向遍历能力，您仅需创建这些索引之一。同时创建与在 stock_num 列上的升序或者降序排序完全达到相同的结果。

在索引的表上执行 INSERT 或 DELETE 操作之后，索引项的数量在页里变化，表需要的索引页的数量可以取决于该索引是否指定升序或降序顺序。对于一些加载和 DML 操作，按降序排序的单个列或多列索引可能导致数据库服务器分配的索引页多于按升序排序的索引。

列组上索引数目的限制

你可以在列组上创建多个索引，提供到每个索引有一个唯一升序和降序列的组合。例如，要在 stock 表的 stock_num 和 manu_code 列上创建所有可能的索引，您能创建四个索引：

l 两个列上升序的 ix1 索引

l 两个列上降序的 ix2 索引

l ix3 索引在 stock_num 上升序以及在 manu_code 上降序

l ix4 索引在 stock_num 上降序以及在 manu_code 上升序

由于数据库服务器的双向遍历能力，你不需要创建这四个索引。您仅需创建两个索引：

l ix1 和 ix2 索引对于用户为两个列指定的相同的排序方向（升序或降序）的排序达到相同的结果，因此您仅需这对索引之一。

l ix3 和 ix4 索引对于用户为两个列（第一列上的升序和第二列上的降序或反之）指定的不同的排序方向的排序达到相同的结果。因此，您仅需创建这对索引之一。（另见 索引的双向遍历。）

如果每个索引都有不同的排列顺序，则 GBase 8s 还可以支持对同一升序和降序列组合的多个索引；请参阅 SET COLLATION 语句 。

使用运算符类

运算符类是用于查询优化和构建索引的与辅助存取方法相关的运算符集合。如果以下两个之一成立，则当创建索引时您必须指定运算符类：

l 对辅助存取方法不存在缺省运算符类。（用户定义的存取方法可提供非缺省运算符类。）

l 您希望使用与辅助存取方法提供的缺省运算符类不同的运算符类。

如果使用代替的存取方法，并且如果存取方法有一个缺省运算符类，则您能在此省略运算符类；但是如果您不指定运算符类并且辅助存取方法没有缺省运算符类，数据库服务器返回一个错误。有关更多信息，请参阅缺省运算符类。以下 CREATE INDEX 语句在将 abs_btree_ops 运算符类用于 cust_num 键的 cust_tab 表上创建一个 B-tree 索引：

CREATE INDEX c_num1_ix ON cust_tab (cust_num abs_btree_ops);

使用 access-method 子句

USING 子句为新的索引指定辅助存取方法。

使用 access-method 子句

辅助存取方法是执行索引所需的所有操作的一组例程，如创建、删除（drop）、插入、删除（delete）、更新和扫描。

数据库服务器提供以下辅助存取方法：

l 一般 B-tree 索引是内置辅助存取方法。

B-tree 索引有助于检索某一范围数据值的查询。数据库服务器实现此辅助存取方法并在系统目录表中将其注册为 btree 。

l R-tree 方法是注册的辅助存取方法。

R-tree 索引有助于搜索多维数据。数据库服务器在数据库的系统目录表中将此辅助存取方法注册为 rtree。R-tree 辅助存取方法对 UNIQUE 索引键无效。R-tree 索引不能都被集群。R-tree 索引可存储在非缺省页大小的 dbspace 中。有关 R-tree 索引的更多信息，请参阅 GBase 8s R-Tree 索引用户指南 。

l bts 方法是注册的辅助存取方法。

使用 bts 存取方法在存储于表的某个列中的文档存储库中执行词和语句的基本文本查询。要执行基本文本查询，请使用 bts 存取方法在文本列上创建索引，然后使用 bts_contains() 查询谓词函数和其它管理函数。有关 bts 存取方法的更多信息，请参阅Create the index by specifying the bts access method 。

您指定的存取方法必须在 sysams 系统目录表中注册。缺省辅助存取方法是 B-tree 。

如果存取方法是 B-tree ，则您仅能为每个升序或降序列的组合或使用运算符类的功能键创建一个索引。（此限制不适用于其它辅助存取方法。）缺省情况下，CREATE INDEX 创建一个一般 B-tree 索引。如果希望使用非B-tree 的辅助存取方法来创建索引，则您必须在 USING 子句中指定辅助存取方法的名称。

一些用户定义的存取方法作为 DataBlade 模块打包。一些 DataBlade 模块提供在创建它们时需要特定参数的索引。关于用户定义的存取方法的更多信息，请参阅您的辅助存取方法的文档或 DataBlade 模块。

以下（实现 R-tree 索引的数据库的）示例在包含不透明数据类型 point 的 location 列上创建 R-tree 索引，并在 location 上执行带有过滤器的查询。

CREATE INDEX loc_ix ON TABLE emp (location) USING rtree;

SELECT name FROM emp WHERE location N_equator_equals point('500, 0');

以下 CREATE INDEX 语句创建使用 fulltext 辅助存取方法的索引，它接受两个参数：WORD_SUPPORT 和 PHRASE_SUPPORT 。它为表 t 建立索引，它有两列：i，一个整数列，以及 data ， 一个 TEXT 列。

CREATE INDEX tx ON t(data)

        USING fulltext (WORD_SUPPORT='PATTERN',

        PHRASE_SUPPORT='MAXIMUM');