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 ?
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.
> 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".
thanks for the answer george
the index ADADRESKEY is not unique , not primary
are those entry locks not "released" with an index rebuild ?
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.=
thus the question remains why there are 3 reads on the index ADADRESKEY
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.
thanks Gus !