数组索引指在一个只包含单个数组成员的对象列上创建的索引。
数组索引定义语句
语法格式
CREATE ARRAY INDEX < 索引名> ON [< 模式名>.] < 表名> (< 索引列定义>)
使用说明
1) 暂不支持在水平分区表上创建数组索引；
2) 暂时不支持在有数组索引表上进行批量装载(数组索引失效的例外)；
3) 支持创建数组索引的对象只能包含数组一个成员。数组可以是DM静态数组、动态数组或者ORACLE兼容的嵌套表或VARRAY；
4) 数组项类型只能是可比较的标量类型，不支持复合类型、对象类型或大字段类型；
5) 临时表、垂直分区表不支持；
6) 数组索引不支持改名；
7) 数组索引列不支持改名；
8) 数组索引只能是单索引，不能为组合索引；
9) 不支持空值的检索
10) MPP环境不支持数组索引。
数组索引修改语句
数组索引修改语句与普通索引用法相同，请参考3.7节。与普通索引不同的是，数组索引不支持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: 17.012(ms). Execute id is 81. SQL> CREATE TYPE ARR_NUM2 IS TABLE OF NUMBER; --嵌套表 2 / executed successfully used time: 11.795(ms). Execute id is 82. SQL> CREATE TYPE ARR_NUM3 IS ARRAY NUMBER[]; --动态 2 / executed successfully used time: 14.784(ms). Execute id is 83. SQL> CREATE TYPE ARR_NUM4 IS ARRAY NUMBER[3]; --静态 2 / executed successfully used time: 14.017(ms). Execute id is 84. SQL> CREATE CLASS CLS1 AS V ARR_NUM1;END; 2 / executed successfully used time: 12.893(ms). Execute id is 85. SQL> CREATE TABLE TEST (C1 CLS1); executed successfully used time: 16.078(ms). Execute id is 87. SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(1,2,3))); affect rows 1 used time: 1.537(ms). Execute id is 88. SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(1,2))); affect rows 1 used time: 1.074(ms). Execute id is 89. SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(2,1))); affect rows 1 used time: 1.149(ms). Execute id is 90. SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(1,5))); affect rows 1 used time: 1.110(ms). Execute id is 91. SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(2,4))); affect rows 1 used time: 1.068(ms). Execute id is 92. SQL> INSERT INTO TEST VALUES(CLS1(ARR_NUM1(4,5,6))); affect rows 1 used time: 1.152(ms). Execute id is 93. SQL> commit; executed successfully used time: 17.231(ms). Execute id is 94. SQL> select * from test; LINEID C1 ---------- ------------------------ 1 JY.CLS1(ARR_NUM1(1,2,3)) 2 JY.CLS1(ARR_NUM1(1,2)) 3 JY.CLS1(ARR_NUM1(2,1)) 4 JY.CLS1(ARR_NUM1(1,5)) 5 JY.CLS1(ARR_NUM1(2,4)) 6 JY.CLS1(ARR_NUM1(4,5,6)) 6 rows got used time: 1.063(ms). Execute id is 95. SQL> CREATE ARRAY INDEX IDX ON TEST(C1); --创建数组索引 2 / executed successfully used time: 20.879(ms). Execute id is 97. SQL> SELECT * FROM TEST WHERE CONTAINS(C1,1,2,3); --使用数组索引查询 2 / LINEID C1 ---------- ------------------------ 1 JY.CLS1(ARR_NUM1(1,2,3)) 2 JY.CLS1(ARR_NUM1(1,2)) 3 JY.CLS1(ARR_NUM1(2,1)) 4 JY.CLS1(ARR_NUM1(1,5)) 5 JY.CLS1(ARR_NUM1(2,4)) used time: 3.400(ms). Execute id is 98. SQL> --嵌套表 2 DECLARE 3 X ARR_NUM2; 4 BEGIN 5 X := ARR_NUM2(); 6 X.EXTEND(3); 7 X(1) := 1; 8 X(2) := 2; 9 X(3) := 3; 10 SELECT * FROM TEST WHERE CONTAINS(C1,X); 11 END; 12 / LINEID C1 ---------- ------------------------ 1 JY.CLS1(ARR_NUM1(1,2,3)) 2 JY.CLS1(ARR_NUM1(1,5)) 3 JY.CLS1(ARR_NUM1(2,1)) 4 JY.CLS1(ARR_NUM1(1,2)) 5 JY.CLS1(ARR_NUM1(2,4)) used time: 7.179(ms). Execute id is 100. SQL> --动态数组 2 DECLARE 3 X ARR_NUM3; 4 BEGIN 5 X := NEW NUMBER [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 JY.CLS1(ARR_NUM1(1,2,3)) 2 JY.CLS1(ARR_NUM1(1,5)) 3 JY.CLS1(ARR_NUM1(2,1)) 4 JY.CLS1(ARR_NUM1(1,2)) 5 JY.CLS1(ARR_NUM1(2,4)) used time: 6.016(ms). Execute id is 102. SQL> --静态数组 2 DECLARE 3 X ARR_NUM4; 4 BEGIN 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 JY.CLS1(ARR_NUM1(1,2,3)) 2 JY.CLS1(ARR_NUM1(1,5)) 3 JY.CLS1(ARR_NUM1(2,1)) 4 JY.CLS1(ARR_NUM1(1,2)) 5 JY.CLS1(ARR_NUM1(2,4)) used time: 5.688(ms). Execute id is 104.
数组索引删除语句
数组索引删除语句与普通索引用法相同
SQL> drop index idx; executed successfully used time: 402.153(ms). Execute id is 106. SQL> SELECT * FROM TEST WHERE CONTAINS(C1,1,2,3); SELECT * FROM TEST WHERE CONTAINS(C1,1,2,3); [-3252]:Error in line: 1 The array index is not created on the column[C1]. used time: 1.016(ms). Execute id is 0.