index()
Here’s how to use index() functions.
T.index()
Description:
Create an index table for the key of a table sequence.
Syntax:
T.index(n)
Note:
The function creates an index table, whose length is n, on the basic key of table sequence T. Records with same time key value could be placed at the same index address. The index table will be cleared if n=0, or when the table sequence’s key is reset; the index table’s length will be automatically identified if n isn’t supplied. An index table can help speed up the key-based data query process. To create an index table, we assume that there is only one key for the records, otherwise error will be reported.
Parameter:
|
T |
A table sequence with a key |
|
n |
Index length |
Option:
|
@s |
Create a multilevel tree-structure index and ignore parameter n if the table sequence’s basic key is serial byte values |
|
@m |
Enable parallel processing |
|
@n |
Create an ordinal-number index for the table sequence; the index is used for numberizing the foreign key. Foreign key numberization requires that foreign key values of the fact table correspond to ordinal numbers of the dimension table’s records; the ordinal number key can be omitted when using the index, and ignore parameter n when the option works. The option is not fit for a table sequence containing a time key |
Return value:
Table sequence
Example:
|
|
A |
|
|
1 |
=demo.query("select EID,NAME,SALARY from EMPLOYEE where EID<4") |
|
|
2 |
=A1.keys(EID) |
Set EID as A1’s key |
|
3 |
=A1.index(10) |
Create an index table for the table sequence’s key; the length is 10 |
|
4 |
=A1.index@m(100) |
Create an index table using parallel processing |
Create an ordinal-number index:
|
|
A |
|
|
1 |
=connect("demo").query("SELECT * FROM CITIES") |
Return a table sequence
|
|
2 |
=connect("demo").query("SELECT * FROM STATECAPITAL").keys(STATEID) |
Return a table sequence and set EID as the key
|
|
3 |
=A2.index@n() |
Create an ordinal-number index for A2’s table sequence |
|
4 |
=A1.switch(STATEID,A3) |
CITIES table acts as the fact table and STATECAPITAL as the dimension table; convert values of STATEID, the CITIES’s key, to corresponding referencing records in STATECAPITAL. The index table on foreign key STATEID is used during the computing process, so the ordinal number key STATEID is omitted from the expression, which is equivalent to A1.switch(STATEID,A3:#) |
Related function:
T.index( n )
Description:
Create an index on the key of an in-memory table.
Syntax:
T.index(n)
Note:
The function creates an index whose length is n for the key of in-memory table T. Parameter n can be omitted if the key contains serial byte values. The index facilitates data searching when we need to perform multiple searches according to the primary key. The function assumes there is only one primary key in records.
Parameter:
|
T |
An in-memory table with only one primary key |
|
n |
The length of index |
Option:
|
@m |
Create the index with parallel processing |
|
@n |
Create index on the ordinal number key, which can be absent but should be present when there is the time key |
|
@s |
Create index on the serial byte key |
Return value:
An in-memory table
Example:
|
|
A |
|
|
1 |
=file("D:\\emp3.ctx") |
|
|
2 |
=A1.create(#EID,NAME) |
Create a composite table’s base table |
|
3 |
=demo.cursor("select EID,NAME from employee where EID< 10") |
|
|
4 |
=A2.append(A3) |
|
|
5 |
=A2.attach(table3,#GENDER) |
Add an attached table to the base table |
|
6 |
=demo.cursor("select EID,GENDER from employee where EID< 10") |
Return a cursor |
|
7 |
=A5.append(A6) |
Append cursor records to the attached table |
|
8 |
=A5.memory() |
Generate an in-memory table from A5’s attached table |
|
9 |
=A8.keys(EID) |
Set EID field as the in-memory table’s key |
|
10 |
=A8.index(10) |
Create an index whose length is 10 |
|
11 |
=demo.cursor("select * from employee").memory() |
Return an in-memory table |
|
12 |
=A12.keys@t(EID,HIREDATE) |
Set EID as the basic key and HIERDATE as the time key for the in-memory table |
|
13 |
=A12.index@n() |
Create index for the in-memory table |
T.index( I:h,w;C,…;F,… )
Description:
Create an index file for an entity table or a multi-zone composite table.
Syntax:
T.index(I:h,w;C,…;F,…)
Note:
The function creates index I for records meeting filtering condition w, which can be absent, in entity table or multi-zone composite table T according to keys C,…, which should not the field(s) inheriting from the parent table. It creates an index for each zone table when parameter T is a multi-zone composite table.
Create a hash index with the average length as h if parameter h is present. The key(s) C,… and index name I are indispensable for creating the index. The index name should be unique, and an index file with the unique name will be automatically generated when the index creation is successfully executed.
The function deletes the corresponding index when there is only one parameter I; delete all indexes of T if all parameters are absent. A hash index can be only used for performing equivalence query and supports the contain syntax.
Parameter F is the field name in the entity table or multi-zone composite table. When it is present, record the field into the index file. We can only get the indexed fields C and F field when using the index to retrieve records. The greatest aspect of such an index is that we can quickly find values of F field through values of C. If parameter F is absent, the index will record the location information of all the fields in the entity table.
Parameter:
|
T |
An entity table or multi-zone composite table |
|
I |
Index name |
|
w |
Filtering condition; retrieve the whose set if the parameter is absent |
|
C |
The field for which an index is created |
|
h |
Index length |
|
F |
Field name in an entity table; can be omitted |
Option:
|
@2 |
Auto-load and maintain the second-level index when only parameter I is present |
|
@3 |
Auto-load and maintain the third-level index when only parameter I is present |
|
@0 |
Close the index to release resources when only parameter I is present |
|
@w |
Create a full-table index based on a certain column and support like(“*X*”) style search |
Return value:
An entity table or multi-zone composite table
Example:
|
|
A |
|
|
1 |
=file("emp1.ctx") |
|
|
2 |
=A1.create(#EID,NAME) |
Create base table for A1’s composite table |
|
3 |
=demo.cursor("select EID,NAME from employee where EID< 10") |
|
|
4 |
=A2.append(A3) |
Append records of A3’s cursor to the base table |
|
5 |
=A2.attach(table1,DEPT,GENDER) |
Add attached table table1 to the base table |
|
6 |
=demo.cursor("select EID,DEPT,GENDER from employee where EID< 10") |
|
|
7 |
=A5.append(A6) |
Append records of A6’s cursor to attached table table1 |
|
8 |
=A7.index(test_index1,GENDER=="F";DEPT;) |
Create index test_index1 on DEPT field; the index file name is emp1.ctx_table1_test_index1 |
|
9 |
=A5.index(test_index3,["F"].contain(GENDER);DEPT;) |
Use contain syntax to create index test_index3 on DEPT field; the index file name is emp1.ctx_table1_test_index3 |
|
10 |
=A5.index(idx1:10;DEPT;) |
Create index idx1 with the length of 10 using DEPT field; the index file name is emp1.ctx_table1_idx1 |
|
11 |
=A5.index(test_index3) |
Delete index test_index3 |
|
12 |
=A5.index() |
Delete all indexes |
|
13 |
=A5.index(test_index,GENDER=="F";DEPT;GENDER) |
Parameter F is present, and only indexed field DEPT and GENDER field can be retrieved through the index |
|
14 |
=A7.index@w(test_index4,GENDER=="F";DEPT;) |
Create a full-text index according to DEPT field |
|
15 |
=A14.icursor (;like(DEPT,"*ale*"),test_index).fetch() |
Use like(“*X*”) style query
|
|
16 |
=A5.index@2(test_index3) |
Auto-load and maintain the second-level index |
|
17 |
=A5.index@3(test_index3) |
Auto-load and maintain the third-level index |
|
18 |
=A5.index@0(test_index3) |
Release the index |
Ø When T is a multi-zone composite table:
|
|
A |
|
|
1 |
=file("test.ctx":[1,2,3]) |
|
|
2 |
=A1.create@y(#EID,NAME,GENDER,DEPT;EID%3+1) |
Generate a multi-zone composite table |
|
3 |
=connect("demo").cursor("select EID,NAME,GENDER,DEPT,GENDER from employee") |
Return a cursor |
|
4 |
=A2.append@x(A3) |
Append records of A3’s cursor to A2’s multi-zone composite table |
|
5 |
=A4.index(test_index4,GENDER=="F";DEPT;) |
Create an index for each zone table
|
T.index( I:h,w;C,… )
Description:
Create one or multiple non-primary-key-based indexes for an in-memory table.
Syntax:
T.index(I:h,w;C,…)
Note:
Treat column(s) C,… as the key and create index I for records meeting condition w , which can be omitted, on the key. When parameter h is present, create a hash index whose average length is h.
The function creates one or multiple non-primary-key-based indexes for in-memory table T during creating a cursor.
Parameter:
|
T |
An in-memory table |
|
I |
Index name |
|
w |
Filtering condition; read the whole set when it is absent |
|
C |
The field(s) on which index is created |
|
h |
Index length |
Return value:
An in-memory table
Example:
|
|
A |
|
|
1 |
=demo.cursor("select * from SCORES ") |
|
|
2 |
=file("SCORES_ClassTwo.ctx") |
Create a composite table file |
|
3 |
=A2.create@y(#CLASS,#STUDENTID,SUBJECT,SCORE) |
Create the composite table’s base table, where CLASS,#STUDENTID is the composite table’s key |
|
4 |
=A3.append@i(A1) |
Append data of A1’s cursor to the base table |
|
5 |
=A4.memory() |
Generate an in-memory table |
|
6 |
=A5.index(index1:10,CLASS =="Class one";SCORE) |
Create a non-primary-key-based index for A5’s in-memory table |