I’ve made use of the function a few times in the past, for example in this posting on the dangers of using reverse key indexes, but every time I’ve mentioned it I’ve only been interested in the “leaf blocks per key” option. There are actually four different variations of the function, relevant to different types of index and controlled by setting a flag parameter to one of 4 different values.
The call to sys_op_lbid() take 3 parameters: index (or index [sub]partition object id, a flag vlaue, and a table “rowid”, where the flag value can be one of L, R, O, or G. The variations of the call are as follows:
- L – the function will return the row directory address (i.e. something that look like a rowid) of the first index entry in the leaf block that holds the index entry for the referenced table rowid. The effect of this is that the number of distinct values returned by calling the function for every row in the table is equal to the number of index leaf blocks which current hold an active entry.
- R – Relevant only to bitmap indexes; the function will return the row directory address of the bitmap index entry for the referenced table rowid. The effect of this is that the number of distinct values returned by calling the function for every row in the table is equal to the number of index entries in the bitmap index.
- O – Relevent only to the primary key index of an index organized table with an overflow. The function is used with a non-key column instead of a rowid and returns a rowid that corresponds to the row directory entry in the overflow segment. An interesting detail of the overflow entries is that there is an “nrid” (next rowid) pointer in the primary key index entry that does not get deleted when all the columns in the related overflow entry are set null – so you can delete all the data from the overflow (set every overflow column in every row to null) and the primary key clustering factor would not change.
- G – Relevent only to secondary indexes on an index organized table. Like the L and R options this function takes a rowid (which is a special case for IOTs) as one of its inputs and uses the block guess from the secondary index to construct a row directory entry for the first entry in the primary key leaf block that corresponds to that block guess. This serves two purposes – it allows Oracle to calculate the clustering factor of the secondary index (as you walk the secondary index in order how much do you jump around the leaf blocks of the primary key), and it allows Oracle to produce the pct_direct_access figure for the secondary index by joining the secondary index to the primary key index on primary key, and comparing the ‘G’ result for the secondary with the ‘L’ result from the primary, which gives a count of the number of times the guess is correct.
These observations can be confirmed by gathering stats on different structures with trace enabled, and doing a couple of block dumps. For reference the following is just a simple script to create an index organized table with overflow and secondary index:
rem rem Script: sys_op_lbid_2.sql rem Author: Jonathan Lewis rem Dated: Dec 2018 rem create table t1( id constraint t1_pk primary key, v1 , v2 , v3 , padding ) organization index pctthreshold 2 overflow as with generator as ( select rownum id from dual connect by level <= 1e4 -- > comment to avoid WordPress format issue ) select rownum, lpad(rownum,30), lpad(rownum,30), lpad(rownum,40), rpad('x',100,'x') from generator v1, generator v2 where rownum <= 1e4 -- > comment to avoid WordPress format issue ; create index t1_i1 on t1(v3); alter session set sql_trace true; begin dbms_stats.gather_table_stats( ownname => null, tabname => 'T1', method_opt => 'for all columns size 1' ); end; / alter session set sql_trace false; select object_id, object_name from user_objects order by object_id ;
The significance of the query for object_id and data_object_id shows up in the trace file (and subsequent dumps) when Oracle uses one or other of the values in its SQL and rowid construction.
Here are the interesting SQL statements generated as the stats are gathered – but cosmetically altered to be reader-friendly. In order they are:
- Stats for primary key of IOT: using the ‘L’ option for counting leaf blocks and the ‘O’ option for the clustering factor into overflow segment.
- Stats for secondary index of IOT: using the ‘L’ option for counting leaf blocks and the ‘G’ option for the clustering factor into the primary key index
- Calculate pct_direct_access: the ‘L’ option gives the actual leaf block in the primary key index, the ‘G’ option gives the leaf block guessed by the secondary index
select /*+ index(t,t1_pk) */ count(*) as nrw, count(distinct sys_op_lbid(351334,'L',t.rowid)) as nlb, null as ndk, (sys_op_lbid(351334,'O',V1),1) as clf from t1 t where id is not null ; select /*+ index(t,t1_i1) */ count(*) as nrw, count(distinct sys_op_lbid(351335,'L',t.rowid)) as nlb, null as ndk, sys_op_countchg(sys_op_lbid(351335,'G',t.rowid),1) as clf from t1 t where v3 is not null ; select case when count(*) = 0 then 100 else round( count( case when substr(gdba,7,9)=substr(lbid,7,9) then 1 else null end )/count(*)*100 ) end from ( select /*+ ordered use_hash(i.t1 t2) index_ffs(t2,t1_pk) */ sys_op_lbid(351334,'L',t2.rowid) lbid, gdba from ( select /*+ index_ffs(t1,t1_i1) */ sys_op_lbid(351335,'G',t1.rowid) gdba, t1.ID from t1 t1 ) i, ` t1 t2 where i.id = t2.id ) ;
The strange substr(,7,9) that appears in the join between the primary key index and the secondary index is needed because the ‘G’ option uses the object_id of the table to turn an absolute block guess into a rowid while the ‘L’ option is using the data_object_id of the primary key index to turn its block addrss into a rowid. (This means there may be variants of this SQL for IOTs using partitioning.)
Comments and related questions are welcome.