Table reads versus index reads

Posted by gdb390 on 04-Jun-2019 13:15

We have a table AD with 1564 records

The table has several indexes. One index is the index ADADRESKEY with 1 field ADRES (ascending).

All indexes have been rebuild.

We start the database with proserve command 

We execute the below query :

for each ad no-lock where ad.adres = "def" : 

  display naam adres.

end.

There is one record that is shown.

Table statistics show the following numbers : 

First execution : table reads AD : 2 , Index reads ADADRESKEY : 3 

Second (and all after that) execution : table reads AD : 1, index reads ADADRESKEY : 3 

Two questions :

*  where is the number two coming from in the first execution ( has this something to do with the loading of schema information ?) 

* where does the number 3 comes from in the index reads ?

Posted by gus bjorklund on 05-Jun-2019 14:08

1 index read for index root block,

1 index read for the leaf block to locate the record with key value "def",

1 index read of leaf block on next iteration of for each to determine that there are no more matches.

note that if the current cursor is at the last entry in a leaf block, the an additional read of root block will be needed to read the next leaf block, so that can sometimes add 2 more reads, depending on index type and query.

All Replies

Posted by George Potemkin on 04-Jun-2019 20:16

>  where is the number two coming from in the first execution

First run reads a template record.

> where does the number 3 comes from in the index reads ?

Index ADADRESKEY is unique, isn't it? And some records were deleted, right? Then index tree contains "index entry locks".

Posted by gdb390 on 05-Jun-2019 13:19

thanks for the answer george

the index ADADRESKEY is not unique , not primary

are those entry locks not "released" with an index rebuild ?

Posted by gus bjorklund on 05-Jun-2019 13:40

if the index is not unique, then placeholder locks are not needed since there can be multiple entries with the same key value.

an index rebuild will remove placeholders. so will an index compact which can be run online and is usually quite fast.=

Posted by gdb390 on 05-Jun-2019 13:49

thus the question remains why there are 3 reads on the index ADADRESKEY

Posted by gus bjorklund on 05-Jun-2019 14:08

1 index read for index root block,

1 index read for the leaf block to locate the record with key value "def",

1 index read of leaf block on next iteration of for each to determine that there are no more matches.

note that if the current cursor is at the last entry in a leaf block, the an additional read of root block will be needed to read the next leaf block, so that can sometimes add 2 more reads, depending on index type and query.

Posted by gdb390 on 05-Jun-2019 14:11

thanks Gus !

This thread is closed