管理数组索引

数组索引指在一个只包含单个数组成员的对象列上创建的索引。

语法格式

CREATE ARRAY INDEX < 索引名> ON \[< 模式名>.\] < 表名> (< 索引列定义>)  

使用说明

1. 暂不支持在水平分区表上创建数组索引；
2. 暂时不支持在有数组索引表上进行批量装载(数组索引失效的例外)；
3. 支持创建数组索引的对象只能包含数组一个成员。数组可以是DM静态数组、动态数组或者ORACLE兼容的嵌套表或VARRAY；
4. 数组项类型只能是可比较的标量类型，不支持复合类型、对象类型或大字段类型；
5. 临时表不支持；
6. 数组索引不支持改名；
7. 数组索引列不支持改名；
8. 数组索引只能是单索引，不能为组合索引；
9. 不支持空值的检索
10. MPP环境不支持数组索引。

数组索引修改语句与普通索引用法相同，与普通索引不同的是，数组索引不支持NOSORT和ONLINE用法。

数组索引使用

使用数组索引进行查询，必须使用谓词CONTAINS。

语法格式

CONTAINS(< 索引列名>,)

或者

CONTAINS(< 索引列名>,arr\_var\_exp)

参数
val：必须为与对象列数组项相同或可转换的标量类型表达式。
arr_var_exp：必须为数组类型（DM静态数组、动态数组或者ORACLE兼容的嵌套表或VARRAY），其数组项类型必须与对象列数组项类型相同或可转换。

举例说明

SQL> CREATE TYPE ARR\_NUM1 IS VARRAY(1024) OF NUMBER;   --VARRAY数组
2   /
executed successfully
used time: 27.267(ms). Execute id is 13.
SQL> CREATE TYPE ARR\_NUM2 IS TABLE OF NUMBER;    --嵌套表
2   /
executed successfully
used time: 27.319(ms). Execute id is 14.
SQL> CREATE TYPE ARR\_NUM3 IS ARRAY NUMBER\[\];   --动态
2   /
executed successfully
used time: 20.404(ms). Execute id is 15.
SQL> CREATE TYPE ARR\_NUM4 IS ARRAY NUMBER\[3\];   --静态
2   /
executed successfully
used time: 18.836(ms). Execute id is 16.
SQL> CREATE CLASS CLS1 AS V ARR\_NUM1;END;
2   /
executed successfully
used time: 16.774(ms). Execute id is 17.

SQL> CREATE TABLE TEST (C1 CLS1);
executed successfully
used time: 21.077(ms). Execute id is 20.
SQL> INSERT INTO TEST VALUES(CLS1(ARR\_NUM1(1,2,3)));
affect rows 1

used time: 1.497(ms). Execute id is 21.
SQL> INSERT INTO TEST VALUES(CLS1(ARR\_NUM1(1,2)));
affect rows 1

used time: 0.598(ms). Execute id is 22.
SQL> INSERT INTO TEST VALUES(CLS1(ARR\_NUM1(2,1)));
affect rows 1

used time: 0.627(ms). Execute id is 23.
SQL> INSERT INTO TEST VALUES(CLS1(ARR\_NUM1(1,5)));
affect rows 1

used time: 0.555(ms). Execute id is 24.
SQL> INSERT INTO TEST VALUES(CLS1(ARR\_NUM1(2,4)));
affect rows 1

used time: 0.925(ms). Execute id is 25.
SQL> INSERT INTO TEST VALUES(CLS1(ARR\_NUM1(4,5,6)));
affect rows 1

used time: 0.908(ms). Execute id is 26.
SQL> CREATE ARRAY INDEX IDX ON TEST(C1);  --创建数组索引
executed successfully
used time: 36.684(ms). Execute id is 27.

SQL> SELECT \* FROM TEST WHERE CONTAINS(C1,1,2,3);  --使用数组索引查询

LINEID     C1
---------- ----------------------------
1          SYSDBA.CLS1(ARR\_NUM1(1,2,3))
2          SYSDBA.CLS1(ARR\_NUM1(1,2))
3          SYSDBA.CLS1(ARR\_NUM1(2,1))
4          SYSDBA.CLS1(ARR\_NUM1(1,5))
5          SYSDBA.CLS1(ARR\_NUM1(2,4))

used time: 3.133(ms). Execute id is 28.

嵌套表

SQL> DECLARE
2   X ARR\_NUM2;
3   BEGIN
4   X := ARR\_NUM2();
5   X.EXTEND(3);
6   X(1) := 1;
7   X(2) := 2;
8   X(3) := 3;
9   SELECT \* FROM TEST WHERE CONTAINS(C1,X);
10  END;
11  /

LINEID     C1
---------- ----------------------------
1          SYSDBA.CLS1(ARR\_NUM1(1,2,3))
2          SYSDBA.CLS1(ARR\_NUM1(1,5))
3          SYSDBA.CLS1(ARR\_NUM1(2,1))
4          SYSDBA.CLS1(ARR\_NUM1(1,2))
5          SYSDBA.CLS1(ARR\_NUM1(2,4))

used time: 7.699(ms). Execute id is 32.

动态数组

SQL> DECLARE
2   X ARR\_NUM3;
3   BEGIN
4   X := NEW NUMBER \[3\];
5   X\[1\]:= 1;
6   X\[2\]:= 2;
7   X\[3\]:= 3;
8   SELECT \* FROM TEST WHERE CONTAINS(C1,X);
9   END;
10  /

LINEID     C1
---------- ----------------------------
1          SYSDBA.CLS1(ARR\_NUM1(1,2,3))
2          SYSDBA.CLS1(ARR\_NUM1(1,5))
3          SYSDBA.CLS1(ARR\_NUM1(2,1))
4          SYSDBA.CLS1(ARR\_NUM1(1,2))
5          SYSDBA.CLS1(ARR\_NUM1(2,4))

used time: 5.921(ms). Execute id is 34.

静态数组

SQL> DECLARE
2   X ARR\_NUM4;
3   BEGIN
4   X\[1\]:= 1;
5   X\[2\]:= 2;
6   X\[3\]:= 3;
7   SELECT \* FROM TEST WHERE CONTAINS(C1,X);
8   END;
9   /

LINEID     C1
---------- ----------------------------
1          SYSDBA.CLS1(ARR\_NUM1(1,2,3))
2          SYSDBA.CLS1(ARR\_NUM1(1,5))
3          SYSDBA.CLS1(ARR\_NUM1(2,1))
4          SYSDBA.CLS1(ARR\_NUM1(1,2))
5          SYSDBA.CLS1(ARR\_NUM1(2,4))

used time: 5.954(ms). Execute id is 36.

数组索引删除语句

SQL> drop index idx;
executed successfully
used time: 70.830(ms). Execute id is 38.