Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

What is the concept of DM7 array index

2025-01-17 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

What is the concept of DM7 array index? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

An array index is an index created on an object column that contains only a single array member.

Array index definition statement

Grammatical format

CREATE ARRAY INDEX

< 索引名>

ON [

< 模式名>

.]

< 表名>

(

< 索引列定义>

)

instructions

1) creation of array indexes on horizontal partitioned tables is not supported

2) batch loading on an array index table is not supported for the time being (with the exception of array index failure)

3) objects that support the creation of an array index can contain only one member of the array. Arrays can be DM static arrays, dynamic arrays, or ORACLE compatible nested tables or VARRAY

4) Array item types can only be comparable scalar types, and compound types, object types or large field types are not supported.

5) temporary tables and vertical partitioned tables are not supported

6) Array index does not support renaming

7) Array index columns do not support renaming

8) an array index can only be a single index, not a combined index

9) the retrieval of null values is not supported

10) Array indexing is not supported in MPP environment.

Array index modification statement

Array index modification statements are used in the same way as normal indexes, please refer to Section 3.7. Unlike normal indexes, array indexes do not support NOSORT and ONLINE usage.

Array index usage

To query using an array index, you must use the predicate CONTAINS.

Grammatical format

CONTAINS (

< 索引列名>

,)

Or

CONTAINS (

< 索引列名>

, arr_var_exp)

Parameters.

Val: must be a scalar type expression that is the same or convertible as an array item of an object column.

Arr_var_exp: must be an array type (DM static array, dynamic array, or ORACLE compatible nested table or VARRAY) whose array item type must be the same or convertible as the object array item type.

Give examples to illustrate

SQL > CREATE TYPE ARR_NUM1 IS VARRAY (1024) OF NUMBER;-- VARRAY array 2 / executed successfullyused time: 17.012 (ms). Execute id is 81.SQL > CREATE TYPE ARR_NUM2 IS TABLE OF NUMBER;-- nested table 2 / executed successfullyused time: 11.795 (ms). Execute id is 82.SQL > CREATE TYPE ARR_NUM3 IS ARRAY NUMBER [];-dynamic 2 / executed successfullyused time: 14.784 (ms). Execute id is 83.SQL > CREATE TYPE ARR_NUM4 IS ARRAY NUMBER [3];-static 2 / executed successfullyused time: 14.017 (ms). Execute id is 84.SQL > CREATE CLASS CLS1 AS V ARR_NUM1;END;2 / executed successfullyused time: 12.893 (ms). Execute id is 85.SQL > CREATE TABLE TEST (C1 CLS1); executed successfullyused time: 16.078 (ms). Execute id is 87.SQL > INSERT INTO TEST VALUES (CLS1 (ARR_NUM1); affect rows 1used time: 1.537 (ms). Execute id is 88.SQL > INSERT INTO TEST VALUES (CLS1 (ARR_NUM1 (1rem 2)); affect rows 1used time: 1.074 (ms). Execute id is 89.SQL > INSERT INTO TEST VALUES (CLS1 (ARR_NUM1 (2jue 1)); affect rows 1used time: 1.149 (ms). Execute id is 90.SQL > INSERT INTO TEST VALUES (CLS1 (ARR_NUM1 (1rem 5)); affect rows 1used time: 1.110 (ms). Execute id is 91.SQL > INSERT INTO TEST VALUES (CLS1 (ARR_NUM1 (2jue 4)); affect rows 1used time: 1.068 (ms). Execute id is 92.SQL > INSERT INTO TEST VALUES (CLS1 (ARR_NUM1); affect rows 1used time: 1.152 (ms). Execute id is 93.SQL > commit;executed successfullyused time: 17.231 (ms). Execute id is 94.SQL > select * from test LINEID C1-1 JY.CLS1 (ARR_NUM1 (1) ) 2 JY.CLS1 (ARR_NUM1 (1)) 3 JY.CLS1 (ARR_NUM1 (2)) 4 JY.CLS1 (ARR_NUM1 (1)) 5 JY.CLS1 (ARR_NUM1 (2)) 6 JY.CLS1 (ARR_NUM1) 6 rows gotused time: 1.063 (ms). Execute id is 95.SQL > CREATE ARRAY INDEX IDX ON TEST (C1);-- create array index 2 / executed successfullyused time: 20.879 (ms). Execute id is 97.SQL > SELECT * FROM TEST WHERE CONTAINS (C1 recorder 1pm 2jue 3) -- query 2 / LINEID C1 using array index-1 JY.CLS1 (ARR_NUM1 3) 2 JY.CLS1 (ARR_NUM1 (1)) 3 JY.CLS1 (ARR_NUM1 (2)) 4 JY.CLS1 (ARR_NUM1 (1)) 5 JY.CLS1 (ARR_NUM1 (2)) used time: 3.400 (ms). Execute id is 98.SQL >-- nested table 2 DECLARE3 X ARR_NUM2;4 BEGIN5 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 END X); 11 END 12 / LINEID C1-1 JY.CLS1 (ARR_NUM1 3) 2 JY.CLS1 (ARR_NUM1 (1)) 3 JY.CLS1 (ARR_NUM1 (2)) 4 JY.CLS1 (ARR_NUM1 (1)) 5 JY.CLS1 (ARR_NUM1 (2)) used time: 7.179 (ms). Execute id is 100.SQL >-- dynamic array 2 DECLARE3 X ARR_NUM3;4 BEGIN5 X: = NEW NUMBER [3]; 6 X [1]: = 1 7 X [2]: = 2 8 X [3]: = 3 9 X * FROM TEST WHERE CONTAINS (C1MagneX); 10 END 11 / LINEID C1-1 JY.CLS1 (ARR_NUM1 3) 2 JY.CLS1 (ARR_NUM1 (1)) 3 JY.CLS1 (ARR_NUM1 (2)) 4 JY.CLS1 (ARR_NUM1 (1)) 5 JY.CLS1 (ARR_NUM1 (2)) used time: 6.016 (ms). Execute id is 102.SQL >-- static array 2 DECLARE3 X ARR_NUM4;4 BEGIN5 X [1]: = 1 FROM TEST WHERE CONTAINS 6 X [2]: = 2 X 7 X [3]: = 3 8 X * FROM TEST WHERE CONTAINS (C1 MaginX); 9 END 10 / LINEID C1-1 JY.CLS1 (ARR_NUM1 3) 2 JY.CLS1 (ARR_NUM1 (1)) 3 JY.CLS1 (ARR_NUM1 (2)) 4 JY.CLS1 (ARR_NUM1 (1)) 5 JY.CLS1 (ARR_NUM1 (2)) used time: 5.688 (ms). Execute id is 104.

Array index delete statement

Array index delete statements are used in the same way as normal indexes

SQL > drop index idx;executed successfullyused time: 402.153 (ms). Execute id is 106.SQL > SELECT * FROM TEST WHERE CONTAINS; SELECT * FROM TEST WHERE CONTAINS; [- 3252]: Error in line: 1The array index is not created on the column [C1]. Used time: 1.016 (ms). Execute id is 0. After reading the above, have you mastered the concept of DM7 array index? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report