Say you are going to access a single row in the table via an index (where <indexed_set_of_columns> = value) - for example:


create table t ( x int, y int, z int );
create index t_idx on t(x,y);

and you issue:

select * from t where x=:x and y=:y;

and you expect 1 row. the number of LIOs will be:

height of index + 1 or 2 -- in general. that is, say the index has a height of three (root -> branch -> leaf node). That'll be 3 LIOs to find the key entry in the leaf block. That key entry will have a rowid -- we can use that rowid to then read the file/block the data is on. That'll be one LIO -- unless the row is migrated (or chained) then then will be an LIO to get the migrated row (or as many LIO's as it takes to get the entire chained row reassembled)..


But -- if your index was just on t(x) for example, you might see tons more LIO's to get the row -- because we have to skip back and forth between the index and the table to get all of the x/y values and check them out.

Lets take a look at an example:

ops$tkyte@ORA9IR2> /*
DOC>
DOC>drop table t;
DOC>
DOC>create table t
DOC>( x int,
DOC> y int,
DOC> z int,
DOC> a char(2000) default 'x',
DOC> b char(2000) default 'x',
DOC> c char(2000) default 'x',
DOC> d char(2000) default 'x',
DOC> e char(2000) default 'x',
DOC> f char(2000) default 'x',
DOC> g char(2000) default 'x',
DOC> h char(2000) default 'x',
DOC> i char(2000) default 'x',
DOC> j char(2000) default 'x',
DOC> k char(2000) default 'x',
DOC> l char(2000) default 'x',
DOC> m char(2000) default 'x',
DOC> n char(2000) default 'x',
DOC> o char(2000) default 'x',
DOC> p char(2000) default 'x'
DOC>)
DOC>/
DOC>
DOC>insert into t ( x,y,z )
DOC>select mod(rownum,100), rownum, rownum
DOC> from all_objects
DOC> where rownum <= 5000
DOC>/
DOC>create index t_idx1 on t(x,y);
DOC>create index t_idx2 on t(x);
DOC>*/
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> variable y number
ops$tkyte@ORA9IR2> exec :x := 1; :y := 1;

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @trace
ops$tkyte@ORA9IR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte@ORA9IR2> select /*+ index( t t_idx1 ) */ x,y,z from t where x = :x and y = :y;

X Y Z
---------- ---------- ----------
1 1 1

ops$tkyte@ORA9IR2> select /*+ index( t t_idx2 ) */ x,y,z from t where x = :x and y = :y;

X Y Z
---------- ---------- ----------
1 1 1

ops$tkyte@ORA9IR2> select /*+ index( t t_idx1 ) */ x,y,z,substr(p,1,1) from t where x = :x and y = :y;

X Y Z S
---------- ---------- ---------- -
1 1 1 x

ops$tkyte@ORA9IR2> select /*+ index( t t_idx2 ) */ x,y,z,substr(p,1,1) from t where x = :x and y = :y;

X Y Z S
---------- ---------- ---------- -
1 1 1 x


looking at the tkprof we can see what happened:


select /*+ index( t t_idx1 ) */ x,y,z from t where x = :x and y = :y

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=5 r=0 w=0 time=68 us)
1 INDEX RANGE SCAN T_IDX1 (cr=3 r=0 w=0 time=41 us)(object id 35623)

best case -- good use of index, 3 consistent reads (cr=) to get to the row in the index -- then just the lio's to retrieve the row from the table


********************************************************************************
select /*+ index( t t_idx2 ) */ x,y,z from t where x = :x and y = :y

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=89 r=0 w=0 time=389 us)
50 INDEX RANGE SCAN T_IDX2 (cr=3 r=0 w=0 time=87 us)(object id 35624)

here, a poor choice of indexing had us hit the table 50 times, to peek at Y to discover we didn't want that record. The extra CR's here are all about the hits on the table

********************************************************************************
select /*+ index( t t_idx1 ) */ x,y,z,substr(p,1,1) from t where x = :x and y = :y

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=9 r=0 w=0 time=82 us)
1 INDEX RANGE SCAN T_IDX1 (cr=3 r=0 w=0 time=42 us)(object id 35623)

here we see the effect of piecing the enire row back together -- I was massively chained (this is an unusual case, I made the row really really wide, you'll see this with LONGS and LONGS raws alot though)

********************************************************************************
select /*+ index( t t_idx2 ) */ x,y,z,substr(p,1,1) from t where x = :x and y = :y

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ#(35622) (cr=93 r=0 w=0 time=444 us)
50 INDEX RANGE SCAN OBJ#(35624) (cr=3 r=0 w=0 time=92 us)(object id 35624)

and the same sort of effect when we use the bad index


So, if you are doing simple table accesses and seeing high LIO's -- you probably have the wrong indexes in place (The indexes are not "fat enough", we have the visit the table lots to evaluate the predicate)