Indexes On Small Tables Part III (Another Brick In The Wall Part III)

So far, in Part I and Part II of this little series, we’ve looked at how Oracle performs a Full Table Scan (FTS) when accessing a small table. With very small tables, Oracle needs to still access the table segment header and perform a number of fetch operations, even if the table is as small as you can get with all the rows in the table residing in one table block and even if we’re only interested in accessing the one row. In the little example we went through, Oracle still needs to access 2 distinct table blocks and perform 4 consistent get operations to read this one row of interest.

OK, let’s now finally introduce an index into the discussion.  To begin with, we’ll just create a standard, Non-Unique index on the ID column.

SQL> create index small_id_i on small(id);

Index created.

Now surely, there would be no real benefit in creating such an index on such a tiny table. This would be a separate index segment from the existing table segment so that if Oracle were to ever use the index to retrieve rows, it would need to visit both the index and table segments.

How can that possibly be more efficient than reading directly from the only table block in the table segment that contains rows ?

Well, let’s see what happens:

SQL> select * from small where id = 42;

        ID NAME
---------- -----
        42 BOWIE

--------------------------------------------
|Id|Operation                   |Name      |
--------------------------------------------
| 0|SELECT STATEMENT            |          |
| 1| TABLE ACCESS BY INDEX ROWID|SMALL     |
|*2|  INDEX RANGE SCAN          |SMALL_ID_I|
--------------------------------------------

Statistics
-------------------------------------------
  0  recursive calls
  0  db block gets
  3  consistent gets
  0  physical reads
  0  redo size
465  bytes sent via SQL*Net to client
396  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
  1  rows processed

Well, we notice 2 very interesting points.

Firstly, the CBO does indeed decide to use the index, even though the table is so tiny.

Secondly, we notice that the actual number of consistent gets has actually reduced from 4 with the FTS down to just 3.  The index scan does indeed require less consistent gets than the equivalent FTS.

If we look at the consistent get stats via another session before and after the SELECT staement as we did in Part I, we can again confirm only 3 consistent gets were necessary:

SQL> SELECT n.name, s.value FROM v$sesstat s, v$statname n
WHERE s.statistic# =n.statistic# AND s.sid = 134 AND n.name LIKE ‘consistent%’;

NAME                            VALUE
----------------------------- -------
consistent gets                   110
consistent gets - examination      25

And again after the SELECT on the small table:

SQL> SELECT n.name, s.value FROM v$sesstat s, v$statname n
WHERE s.statistic# =n.statistic# AND s.sid = 134 AND n.name LIKE ‘consistent%’;

 
NAME                            VALUE
----------------------------- -------
consistent gets                   113 (+3)
consistent gets - examination      25

The consistent gets indeed increased by 3 rather than 4 but again as in the FTS example, none of these consistent gets were the cheaper “consistent gets – examination” (to be discussed later).

But why did the index scan only require 3 consistent gets ? Again, by flushing the buffer cache and tracing the session, we get the necessary information to answer the question:

SQL> alter system flush buffer_cache;

System altered.

SQL> exec dbms_monitor.session_trace_enable(waits=> true);

PL/SQL procedure successfully completed.

SQL> select * from small where id = 42;

        ID NAME
---------- -----
        42 BOWIE

SQL> exec dbms_monitor.session_trace_disable();

PL/SQL procedure successfully completed.

If we now look at an example trace file:
=====================
PARSING IN CURSOR #2 len=33 dep=0 uid=88 oct=3 lid=88 tim=24452815463 hv=2225904586 ad=’23bdd320′ sqlid=’7pjs08q2at6ya’
select * from small where id = 42
END OF STMT
PARSE #2:c=0,e=7613,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=24452815457
EXEC #2:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=24452815593
WAIT #2: nam=’SQL*Net message to client’ ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=12446 tim=24452815631
WAIT #2: nam=’db file sequential read’ ela= 17035 file#=7 block#=118026 blocks=1 obj#=99587 tim=24452832933
WAIT #2: nam=’db file sequential read’ ela= 5592 file#=7 block#=117898 blocks=1 obj#=99586 tim=24452838643
FETCH #2:c=0,e=23057,p=2,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=24452838728
WAIT #2: nam=’SQL*Net message from client’ ela= 394 driver id=1111838976 #bytes=1 p3=0 obj#=99586 tim=24452839197
FETCH #2:c=0,e=22,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=24452839275
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=99586 op=’TABLE ACCESS BY INDEX ROWID SMALL (cr=3 pr=2 pw=2 time=0 us cost=2 size=9 card=1)’
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=99587 op=’INDEX RANGE SCAN SMALL_ID_I (cr=2 pr=1 pw=1 time=0 us cost=1 size=0 card=1)’
WAIT #2: nam=’SQL*Net message to client’ ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=99586 tim=24452839399

The critical part here is the first “db file sequential read” wait event. By looking at the obj#=99587, we can determine this refers to the SMALL_ID_I index segment. By looking at the associated file#=7 block#=118026, a block dump reveals this to be the index leaf block containing the index row entries. Notice how we do not actually access the index segment header at all prior to accessing the leaf block of the index.

The following “db file sequential read” wait event on obj#=99586 corresponds to the table segment and file#=7 blockblock#=117898 corresponds to the table block containing the rows. So the first FETCH uses 2 consistent reads (cr=2) to return the first (and in this case only) row of interest (r=1) by first reading the index leaf block and then reading the table data block containing the row of interest.

Oracle then performs another FETCH to read the leaf block again to determine whether there are any other rows of interest (cr=1) of which there are none (r=0).

There is no PK or Unique constraint on the ID column in table and the index is Non-Unique so there is no possible way for Oracle to know there is indeed only one row of interest hence why Oracle needs to again visit the leaf block just in case there are multiple rows that have an ID = 42.

So that’s our 3 consistent gets, 1 to read the leaf block, 1 to read the table block and 1 to again read the index block again due to a SQL*PLUS quirk to perform a secondary fetch operation for non-unique scans as the first fetch only checks and fetches for one row.

Now in our specific example, that’s a saving of 1 consistent get and effectively 2 latch gets by using the Non-Unique index vs. performing the FTS on this effectively 1 block table. The savings would of course be more substantial if the small table had more than just the one data block containing rows.

Now you might well say, is it really worth the effort, what’s 1 consistent get in the grand scheme of things. Well, that’s 1 consistent get each and every time such a query needs to access this specific table. And this is just one table, you may have many such lookup tables in your databases. In heavily used applications, such lookup tables can potentially be accessed many millions of times per hour. Now all this can add up to potentially many millions of reduced consistent gets, many millions of reduced latch requests, which all in turn can add up to considerable CPU savings as well as the significantly reduced contention issues, increasing the general scalability of your applications.

And as we’ll see, we can actually do a lot better than saving just 1 consistent get by using an index to access such small tables …

However, the key question at this point is why doesn’t Oracle have to visit the index segment header as it does with the table segment during a FTS and how does Oracle know where to find the index block it needs ?

That will be answered in the next instalment coming soon …