How does SQL Index works in Cache?

Question

Question

#Caché #Ensemble #InterSystems IRIS #InterSystems IRIS for Health

Could any one tell me, how the index works in cache db, Consider for example, I have a table called "Employee" and the fields as "EMPID, EMPNAME, EMPAGE" and I am having index for EMPID as IDX_EMPID and trying to get the record using the following query as

SELECT * FROM EMPLOYEE WHERE EMPID="005"

and the table data be like

EMPID EMPNAME EMPAGE

001 ABC 20

002 AAA 21

003 ABB 23

004 BBB 20

005 BDF 24

006 EEE 22

Will SQL Engine directly hit the "005" record using ^EMPLOYEEI global, if so how it is happening, are they using B-Tree structure?

Please explain

Thanks in Advance

Discussion (2)1

Log in or sign up to continue

score 0 · Answer 1 · 2024-07-11T07:45:05-04:00

in SMP > System Explorer > SQL you place your query and tab SHOW PLAN tells you the best approach with lowest cost -
As any data in IRIS (Caché,...) are stored in Globals B+Tree structure always applies by design.

score 1 · Answer 2 · 2024-07-11T11:00:08-04:00

As Robert said, the data and indexes are both stored in global trees. The structure of the trees for indices and the trees for general data storage are different, though, and that's where the efficiency comes from.

The data will be stored in the ^EMPLOYEED global. You'll see a bunch of data in that global like:

^EMPLOYEED(1) = $lb("001","ABC",20)

^EMPLOYEED(2) = $ib("002","AAA",21)

Those are lists of every property of that class, indexed by the ID column.

The indexes, on the other hand, will look more like:

^EMPLOYEEI("IDX_EMPID","001",1)

^EMPLOYEEI("IDX_EMPID","002",2)

This allows for a much more efficient search to find the ID of the records that match your search criteria, similar to if you opened a terminal and put in:

write $ORDER(^EMPLOYEEI("IDX_EMPID","001",""))

Which would give you row 1, then it could more easily look up row 1 in the original without having to go through every record in the table in order searching for it.

In your system management portal, you can run your query and check the query plan, then run it again telling it to ignore indexes like this:

SELECT * FROM %IGNOREINDEX * EMPLOYEE WHERE EMPID="005"

This will tell it to ignore all indexes on the table. You can check that query plan and compare it to the original to see the differences in how the query finds data and how well it performs.