DM7数组索引的概念是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
我们提供的服务有：成都网站设计、成都做网站、
外贸网站建设、微信公众号开发、网站优化、网站认证、
迎泽ssl等。为上1000+企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的
迎泽网站制作公司数组索引指在一个只包含单个数组成员的对象列上创建的索引。
数组索引定义语句
语法格式
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.
看完上述内容，你们掌握DM7数组索引的概念是什么的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注创新互联-成都网站建设公司行业资讯频道，感谢各位的阅读！
网页标题：DM7数组索引的概念是什么-创新互联
文章路径：
http://cdkjz.cn/article/disjej.html
