数组索引指在一个只包含单个数组成员的对象列上创建的索引。
语法格式
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.