DM7数组索引的概念是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

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

数组索引定义语句
语法格式
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>CREATETYPEARR_NUM1ISVARRAY(1024)OFNUMBER;--VARRAY数组2/executedsuccessfullyusedtime:17.012(ms).Executeidis81.SQL>CREATETYPEARR_NUM2ISTABLEOFNUMBER;--嵌套表2/executedsuccessfullyusedtime:11.795(ms).Executeidis82.SQL>CREATETYPEARR_NUM3ISARRAYNUMBER[];--动态2/executedsuccessfullyusedtime:14.784(ms).Executeidis83.SQL>CREATETYPEARR_NUM4ISARRAYNUMBER[3];--静态2/executedsuccessfullyusedtime:14.017(ms).Executeidis84.SQL>CREATECLASSCLS1ASVARR_NUM1;END;2/executedsuccessfullyusedtime:12.893(ms).Executeidis85.SQL>CREATETABLETEST(C1CLS1);executedsuccessfullyusedtime:16.078(ms).Executeidis87.SQL>INSERTINTOTESTVALUES(CLS1(ARR_NUM1(1,2,3)));affectrows1usedtime:1.537(ms).Executeidis88.SQL>INSERTINTOTESTVALUES(CLS1(ARR_NUM1(1,2)));affectrows1usedtime:1.074(ms).Executeidis89.SQL>INSERTINTOTESTVALUES(CLS1(ARR_NUM1(2,1)));affectrows1usedtime:1.149(ms).Executeidis90.SQL>INSERTINTOTESTVALUES(CLS1(ARR_NUM1(1,5)));affectrows1usedtime:1.110(ms).Executeidis91.SQL>INSERTINTOTESTVALUES(CLS1(ARR_NUM1(2,4)));affectrows1usedtime:1.068(ms).Executeidis92.SQL>INSERTINTOTESTVALUES(CLS1(ARR_NUM1(4,5,6)));affectrows1usedtime:1.152(ms).Executeidis93.SQL>commit;executedsuccessfullyusedtime:17.231(ms).Executeidis94.SQL>select*fromtest;LINEIDC1----------------------------------1JY.CLS1(ARR_NUM1(1,2,3))2JY.CLS1(ARR_NUM1(1,2))3JY.CLS1(ARR_NUM1(2,1))4JY.CLS1(ARR_NUM1(1,5))5JY.CLS1(ARR_NUM1(2,4))6JY.CLS1(ARR_NUM1(4,5,6))6rowsgotusedtime:1.063(ms).Executeidis95.SQL>CREATEARRAYINDEXIDXONTEST(C1);--创建数组索引2/executedsuccessfullyusedtime:20.879(ms).Executeidis97.SQL>SELECT*FROMTESTWHERECONTAINS(C1,1,2,3);--使用数组索引查询2/LINEIDC1----------------------------------1JY.CLS1(ARR_NUM1(1,2,3))2JY.CLS1(ARR_NUM1(1,2))3JY.CLS1(ARR_NUM1(2,1))4JY.CLS1(ARR_NUM1(1,5))5JY.CLS1(ARR_NUM1(2,4))usedtime:3.400(ms).Executeidis98.SQL>--嵌套表2DECLARE3XARR_NUM2;4BEGIN5X:=ARR_NUM2();6X.EXTEND(3);7X(1):=1;8X(2):=2;9X(3):=3;10SELECT*FROMTESTWHERECONTAINS(C1,X);11END;12/LINEIDC1----------------------------------1JY.CLS1(ARR_NUM1(1,2,3))2JY.CLS1(ARR_NUM1(1,5))3JY.CLS1(ARR_NUM1(2,1))4JY.CLS1(ARR_NUM1(1,2))5JY.CLS1(ARR_NUM1(2,4))usedtime:7.179(ms).Executeidis100.SQL>--动态数组2DECLARE3XARR_NUM3;4BEGIN5X:=NEWNUMBER[3];6X[1]:=1;7X[2]:=2;8X[3]:=3;9SELECT*FROMTESTWHERECONTAINS(C1,X);10END;11/LINEIDC1----------------------------------1JY.CLS1(ARR_NUM1(1,2,3))2JY.CLS1(ARR_NUM1(1,5))3JY.CLS1(ARR_NUM1(2,1))4JY.CLS1(ARR_NUM1(1,2))5JY.CLS1(ARR_NUM1(2,4))usedtime:6.016(ms).Executeidis102.SQL>--静态数组2DECLARE3XARR_NUM4;4BEGIN5X[1]:=1;6X[2]:=2;7X[3]:=3;8SELECT*FROMTESTWHERECONTAINS(C1,X);9END;10/LINEIDC1----------------------------------1JY.CLS1(ARR_NUM1(1,2,3))2JY.CLS1(ARR_NUM1(1,5))3JY.CLS1(ARR_NUM1(2,1))4JY.CLS1(ARR_NUM1(1,2))5JY.CLS1(ARR_NUM1(2,4))usedtime:5.688(ms).Executeidis104.

数组索引删除语句
数组索引删除语句与普通索引用法相同

SQL>dropindexidx;executedsuccessfullyusedtime:402.153(ms).Executeidis106.SQL>SELECT*FROMTESTWHERECONTAINS(C1,1,2,3);SELECT*FROMTESTWHERECONTAINS(C1,1,2,3);[-3252]:Errorinline:1Thearrayindexisnotcreatedonthecolumn[C1].usedtime:1.016(ms).Executeidis0.

看完上述内容，你们掌握DM7数组索引的概念是什么的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！