DM8管理数组索引

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

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.

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.