In-memory tables

Read(905) Label: in-memory table, memory, len, keys, row, find, index,

An entity table can be imported into the memory to become a in-memory table, which is used like a normal table sequence:

 

A

1

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

2

=A1.open()

3

=A2.memory(;right(Name,6)=="Garcia")

4

=A3(4)

A2 opens composite table employees.ctx’s base table. In A3, T.memory(C,…;w) function filters out records that meets the condition w and generates a in-memory table containing employee records where the surname is Garcia. We can view a in-memory table as we view a table sequence. Here’s A3’s result:

Also like a table sequence, we can access records in a in-memory table by their sequence numbers. A4 gets the 4th record from the in-memory table, for example:

Besides using an entity table to generate a in-memory table, we can use cs.memory() function to generate one from a cursor:

 

A

1

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

2

=A1.open()

3

=A2.cursor(EID,Dept,Name; right(Name,6)=="Garcia")

4

=A3.memory()

5

=A4.len()

A3 selects from the employee records where surname is Garcia to geneate a cursor. A4 generates a in-memory table from this cursor:

Since the cursor contains only some of the fields in etable, the in-memory table generated from the cursor contains same fields, too. In A5, T.len() function gets the number of records in the in-memory table:

Use T.keys(K…) function to set the primary key for a in-memory table. Then use T.find(k) function and k.row(T) function to query or find records:

 

A

1

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

2

=A1.open()

3

=A2.memory(;right(Name,6)== "Garcia")

4

>A3.keys(EID)

5

=A3.find(8887)

6

=8887.row(A3)

Since primary key of the in-memory table is EID, the reset of the key in A4 can be skipped. A5 and A6 respectively find the specified record according to the primary key value 8889 and get the same result:

Then we can use T.index() function to create an index for the in-memory table to increase the query efficiency.

And we can use update and delete functions to update the in-memory table. Their uses are similar as they are with an entity table:

 

A

B

1

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

=A1.open().attach(etable)

2

=B1.memory(;right(Name,6)=="Garcia")

 

3

=[24,78,226].(~.row(A2))

=A2.select(Gender=="F").derive()

4

>A3(1).Gender="UNKNOWN"

>B3.run(Gender="Female")

5

>A2.delete(A3)

>A2.update(B3)

6

=B1.cursor(;right(Name,6)=="Garcia").fetch()

 

To find details of updating the in-memory table, we execute the cellset code step by step. Below is the in-memory table in A2:

A3 gets records where EID is 24, 78 and 226 from A2:

B3 selects records of female employees from A2 and copies the result with derive function:

A4 modifies the first record in A3:

Then we find that A2’s in-memory table is modified:

B4 modifies records in B3. But since B3’s table sequence is a copy, the modification won’t affect the original in-memory table. Execute B4 and we get data in B3 as follows:

Execute A5, and three records where EID is 24, 78 and 226 are deleted from B2’s in-memory table:

Execute B5 and records of female employees in the in-memory table are modified. Records where EID is 78 or 226 are returned to the in-memory table:

A6 again fetches data from the in-memory table:

The modification of the in-memory table doesn’t affect the corresponding entity table because a in-memory table is independent of the entity table from which it is generated.