Here’s how to use index() function.
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, for the key of table sequence T. 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.
Options:
@s |
Create a multilevel tree-structure index and ignore parameter n if the TSeq’s key is for data ordering |
Parameters:
T |
A table sequence with a key |
n |
Index length |
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 squence’s key; the length is 10 |
Related functions:
Description:
Create an index for the key of a memory table.
Syntax:
T.index(n)
Note:
The function creates an index whose length is n for the key of 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 searching according to the primary key. The function assumes there is only one primary key in records.
Parameters:
T |
A memory table with only one primary key |
n |
The length of index |
Return value:
A memory table
Example:
|
A |
|
1 |
=file("D:\\emp3.ctx") |
|
2 |
=A1.create(#EID,NAME;EID) |
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 a memory table from A5’s attached table |
9 |
=A8.keys(EID) |
Set EID field as the memory table’s key |
10 |
=A8.index(10) |
Create an index whose length is 10 |
Description:
Create an index file for an entity table.
Syntax:
T.index(I:h,w;C,…;F,…)
Note:
The function creates index I for records meeting filtering condition w in entity table T according to keys C,…, which should not the field(s) inheriting from the parent table. The parameter w can be omitted; 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 index will be a complex one consisting of subindexes of all data zones when T is a homo-name files group. 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. 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.
Parameters:
T |
An entity 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 |
Options:
@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:
Boolean value
Example:
|
A |
|
1 |
=file("D:\\emp1.ctx") |
|
2 |
=A1.create(#EID,NAME;EID) |
Create a composite table where EID field is the key by which records are segmented |
3 |
=demo.cursor("select EID,NAME from employee where EID< 10") |
|
4 |
=A2.append(A3) |
Append cursor records 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 cursor records to entity table table1 |
8 |
=A7.index(test_index1,GENDER=="F";DEPT;) |
Create an index test_index1 by DEPT field |
9 |
=A5.index(test_index3,["F"].contain(GENDER);DEPT;) |
Use the contain syntax to create index test_index3 by DEPT field |
10 |
=A5.index(idx1:10;DEPT;) |
Create index idx1 with the length of 10 by DEPT field |
11 |
=A5.index(test_index3) |
Delete index test_index3 |
12 |
=A5.index() |
Delete all indexes |
13 |
=A5.index(test_index,GENDER=="F";DEPT;GENDER) |
Via the index, get records with only the indexed field DEPT and GENDER field |
14 |
=A7.index@w(test_index,GENDER=="F";DEPT;) |
Create a full-table index based on DEPT |
15 |
=A14.icursor (;like(DEPT,"*ale*"),test_index).fetch() |
Use like(“*X*”) style search
|
16 |
=file("test.ctx":[1,2,3]) |
Return a homo-name files group |
17 |
=A16.create(#EID,NAME,GENDER,DEPT;;EID%3+1) |
Generate a multi-zone composite table |
18 |
=demo.cursor("select EID,NAME,GENDER,DEPT,GENDER from employee") |
|
19 |
=A17.append@x(A18) |
A18 corresponds to multiple data zones of A17, so the zone expresson EID%3+1 needs to be calculated at each indexing |
20 |
=A19.index(test_index4,GENDER=="F";DEPT;) |
Create index test_index4 under data zone 1,2,3 respectively |
21 |
=A5.index@2(test_index3) |
Auto-load and maintain the second-level index |
23 |
=A5.index@3(test_index3) |
Auto-load and maintain the third-level index |
24 |
=A5.index@0(test_index3) |
Release the index |