Accessing a composite table

The f.create() function is used to open a composite table saved as a file without the need of specifying a parameter:

 

A

B

1

=file("D:/file/dw/employees.ctx")

 

2

=A1.create()

=A2.cursor().fetch()

3

=A2.attach(stable)

=A3.cursor().fetch()

4

 

=A3.cursor(EID,Dept,Gender,Name,OCount,OAmount).fetch()

A2 opens composite table’s base table with create function. In A3, T.attach function opens an attached table by specifying its name. B2 retrieves data from the composite table’s base table:

The retrieved data is the data populated to the composite table.

B3 retrieves data from the attached table stable:

The data retreived from an attached table can contain fields of the base table. B4, for example, retrieves data containing fields of the base table while generating a cursor:

In the above, T'.cursor() function is used to retrieve data from entity table. We can also use T'.cursor(C1, C2…; w) function to specify the fields we want through parameters C1, C2… or the records we desire using a query condition w when retrieving data from an entity table:

 

A

B

1

=file("D:/file/dw/employees.ctx")

 

2

=A1.create()

=A2.attach(stable)

3

=A2.cursor(EID,Name,Dept)

=A3.fetch()

4

=A2.cursor(;right(Name,1)=="e")

=A4.fetch()

5

=B2.cursor(EID,Name,OCount,OAmount;OAmount>5000)

=A5.fetch()

A3 retrieves only three fields from the base table. B3 fetches data as follows:

A4 specifies a query condition, which is getting records where the employee names end with letter e. Here’s B4’s result:

A5 retieves from stable the order records where OAmount is greater than 5000. Here’s B5’s result:

When a composite table contains a large amount of data, we can perform segmental retrieval. But first we need to split the composite table and store it by segment. Actually a composite table is by default stored by segment. To disable the auto-segmental-storage mode, we use @u option in create function. But the automatical segmentation may not satisfy our real-world needs, so we need to specify the field according to which the entity tables in a composite table are split when segmental storage is needed. Let’s look at how to store data in and retrieve data from an enity table by segment:

 

A

B

1

=file("D:/file/dw/employees.ctx")

=A1.create()

2

=B1.attach(stable)

 

3

=file("D:/file/dw/orders.ctx")

=A3.create()

4

=B3.attach(otable)

 

5

=A2.cursor(;;1:3).fetch()

=A2.cursor(;;2:3).fetch()

6

=A4.cursor(;;1:3).fetch()

=A4.cursor(;;2:3).fetch()

A2 retrieves the attached table stable from the composite table file employees.ctx. Both A5 and B5 retrieve one segment of stable from 3 segments. A5 retrieves the first segment and B5 retrieves the second segment. Below are the retrieved segments:

By comparing the results with A4’s employee data in the previous example, we find that the last seller ID in the first segment, 32767, and the first seller ID in the second segment,32769, are neigbours.This shows the segmental retrieval traverses and reads all records only once.

A4 opens the attached table otable. Both A6 and B6 retrieve one segment of otable from 3 segments. A6 retrieves the first and B6 retrieves the second. Below are the retrived segments:

Because otable is a large table, the records in each segment is a lot more. As the composite table orders.ctx is stored by segment by EID, records of the same seller will be put into one segment. Thus the last record of one segment and the first record of the next segment belong to different seller. Comparing the two segments with those retrieved in B5 and B6, we find that records in corresponding segment in two composite tables are different. For example, EID in the second segment’s first record in employees table is 32769 while EID in the second segment’s first record in orders table is 32670.

When the computation involves entity tables in different composite tables, we use multicursor to ensure the synchronization of their segmentation:

 

A

B

1

=file("D:/file/dw/employees.ctx")

=file("D:/file/dw/orders.ctx")

2

=A1.create()

=B1.create()

3

=A2.attach(stable)

=B2.attach(otable)

4

=A3.cursor@m(;;3)

=B3.cursor(;;A4)

5

=joinx(A4:s,EID;B4:o,EID)

=A5.fetch()

6

=A3.cursor@m(;;3)

=B3.cursor(;;A6)

7

=joinx(A6:s,EID;B6:o,EID)

=A7.groups(s.EID:EID;count(~):Count, sum(o.Amount):Sum)

A3 and B3 retrieve two entity tables stable and otable repectively from two composite tables. In A4, T.cursor@m(;;n) function splits stable into n segments to generate a multicursor. B4 splits entity table otable according to A4’s multicursor. Then in A5, joinx function joins the synchronized multicursors in A4 and B4. Here’s the join result in B5:

According to the same way of segmentation, segments in the two cursors match completely.

B7 re-generates multicursors to calculate number of orders and sales amount for each seller:

A comparison with stable’s OCount and OAmount fields in the above shows the joining result is correct.

Since an entity table can be retrieved in segments, the cs.joinx(C:…, T:K:…, x:F,…;…;n) function also applies to it. In the function, entity table T replaces the segmentable bin file f to join with data in a cursor.

We can create an index for an entity table according to a certain condition. An index-based query will be much more effcient. For example:

 

A

B

1

=file("D:/file/dw/employees.ctx")

=A1.create()

2

=B1.cursor().fetch()

 

3

 

=now()

4

=B1.cursor(;[8136,11247,83372,264,8223,826, 32758,1341,6255,1983].contain(EID))

=A4.fetch()

5

 

=interval@ms(B3, now())

6

>B1.index(idx, [8136,11247,83372,264,8223, 826,32758,1341,6255,1983].contain(EID);EID)

=now()

7

=B1.icursor(;[8136,11247,83372,264,8223, 826,32758,1341,6255,1983].contain(EID),idx)

=A7.fetch()

8

 

=interval@ms(B6, now())

Line 1 and line 2 open the base table, or the entity table employees, in the composite table employees.ctx. Line 4 queries the corresponding 10 records in employees. B4 fetches the records as follows:

In A6, T.index(I, w; C,…) function creates an index for employees, and names the index idx and generates it according to the key EID. The index file will be generated and the file name be handled automatically when the code is executed. In A7, T.icursor(C,…; w, I) function performs query by the index. B7 gets the same result as B4 does. Without specifying C,… fields, the function will delete entity table T’s index I; without parameter I, it will delete T’s all indexes.

B5 and B8 calculate how long it takes the two methods to query the 100 records:

 

An index makes the query faster. So it can also speed up a query over a row-based composite table.

If the key of a composite table is a serial byte field, we can use a part of the key value as the segmenting condition. This offers us more information through the key and increase efficiency. For example:

 

A

B

C

D

1

=file("D:/file/dw/students.ctx")

=create(ID,City,School, SID)

 

 

2

for 20

="C"/A2

=20+rand(31)

 

3

 

for C2

=B2/"_S"/B3

=rand(4001)+1000

4

 

 

=k(A2,B3)

 

5

 

 

for D3

=k(C5:2)

6

 

 

 

>B1.insert(0,C4+D5,B2, C3,D5)

7

=A1.create@y(#ID:4,City, School,SID;ID:2)

>A7.append(B1.cursor())

=A1.create().cursor(;;10: 200).fetch()

=A1.create().cursor(;;11: 200).fetch()

From line 2 to line 6, data for the composite table is generated and stored in B1’s table sequence. The data consists of several fields – ID, City, School and SID, and includes students records from 20 cities, which are C1~C20. Each city includes 20~50 schools and each school has 1000~5000 students. The ID field contains 4-byte serial byte values, in which the first byte is city code, the second is school code, and the third and the fourth are student ID. Here is B1’s data:

A7 creates the composite table’s base table using f.create(C:b, …; K:b) function. The function shows the number of bytes in serial byte key values and specifies the number of bytes for segmentation. The serial byte key ID has 4 bytes and the first two bytes are used for segmentation.

As the first byte of ID field is city and the second is school, which is specified during the data generation phase, the segmentation is performed by school.

Here’s data in C7:

Data in D7:

In the two neigboring segments, the last record in the 10th segment and first record in the 11th segment belong to two different schools.