Indexes And Small tables Part VII (Cluster One)

OK, almost getting to the end here 😉

As discussed previously, despite popular opinion, an index can be just that little bit more efficient than a FTS when accessing very small tables, even if  all rows in the table exist in the one table block. And a small efficiency multiplied by a large number can potentially add up and make a noticeable difference.

As we’ve seen, a unique index on such a small table accessing a specific row of interest need only perform one consistent read on the index block and one consistent read on the table block for a grand total of 2 consistent reads, with both consistent gets being the cheaper examinations variety. Not bad,  not too bad at all and somewhat cheaper than an equivalent FTS.

However, as I hinted and as many of you have already commented, we can go one step further still in reducing the overheads of such queries on small tables by potentially storing all the columns in the one, non-heap table structure.

One option is to create an Index Organized Table (IOT), storing all columns within a single index structure and thereby eliminating the need to visit a table segment at all.

Following on from the previous demo, let’s recreate the table as an IOT and populate it with the same data:

SQL> drop table small;
 
Table dropped.
 
SQL> create table small (id number primary key, name varchar2(10)) organization index;
 
Table created.
 
SQL> insert into small select rownum, ‘BOWIE’ from dual connect by level <=100;
 
100 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’SMALL’, method_opt=>’FOR ALL COLUMNS SIZE 1′);
 
PL/SQL procedure successfully completed.

 

If we now run our query again:

 

SQL> select * from small where id = 42;

 
        ID NAME
---------- ----------
        42 BOWIE
 
Execution Plan
------------------------------------------
|Id|Operation         |Name              |
------------------------------------------
| 0|SELECT STATEMENT  |                  |
|*1| INDEX UNIQUE SCAN|SYS_IOT_TOP_68376 |
------------------------------------------

Statistics
-------------------------------------------
  0  recursive calls
  0  db block gets
  1  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

 

We notice that we have now reduced the number of consistent gets down to just one.

Not only is it just one consistent get but if we look at the type of consistent get by running the following query in another session before/after our SELECT above:

SQL> select name, value from v$sesstat s, v$statname n where s.statistic#=n.statistic# and sid = 141 and name like ‘consistent gets%’;
 

NAME                           VALUE
----------------------------- ------
consistent gets                32842
consistent gets - examination   6694

 

SQL> select name, value from v$sesstat s, v$statname n where s.statistic#=n.statistic# and sid = 141 and name like ‘consistent gets%’;

 
NAME                           VALUE
----------------------------- ------
consistent gets                32843 (+1)
consistent gets - examination   6695 (+1)

 

We also notice that it’s the cheaper, one latch consistent gets examination.

So we’ve now reduced our overheads down to just one consistent get and just the one latch get as well. It doesn’t really get much cheaper than that.

IOT are one of those under used options in Oracle that really should be considered used a lot more than they are. Yes they can be problematic when used inappropriately (especially if you need to create several secondary indexes) but for scenarios such as this they can be very useful.

I plan to discuss the advantages and disadvantages of IOT in future posts.

 

Another posssible option to improve things in our little demo is to create a Hash Cluster (as commented by Piet):

SQL> create cluster small_cluster (id number) size 100 single table hashkeys 200;
 
Cluster created.
 
SQL> create table small_tab (id number, name varchar2(100)) cluster small_cluster(id);
 
Table created.
 
SQL> insert into small_tab select rownum, ‘BOWIE’ from dual connect by level <=100;
 
100 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’SMALL_TAB’, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE 1′);
 
PL/SQL procedure successfully completed.
 
SQL> select * from small_tab where id = 42; 

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

Execution Plan
---------------------------------------
| Id  | Operation         | Name      |
---------------------------------------
|   0 | SELECT STATEMENT  |           |
|*  1 |  TABLE ACCESS HASH| SMALL_TAB |
---------------------------------------

Statistics
-------------------------------------------
  0  recursive calls
  0  db block gets
  1  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

 

Note again we have got our consistent gets down to just one as Oracle can simply determine the correct hash key based on the specified ID value and go directly to the table block containing the row of interest. Note however, this will not be a consistent get – examination, but the more expensive 2 latch and pin the block variety.

However, if you now create a unique index on the ID column:

SQL> create unique index small_tab_i on small_tab(id);

Index created.

And re-run the query:

SQL> select * from small_tab where id = 42; 

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

Execution Plan
---------------------------------------
| Id  | Operation         | Name      |
---------------------------------------
|   0 | SELECT STATEMENT  |           |
|*  1 |  TABLE ACCESS HASH| SMALL_TAB |
---------------------------------------

Statistics
-------------------------------------------
  0  recursive calls
  0  db block gets
  1  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

 

Oracle will still use the hash to determine the location of the row of interest but because it now knows it will only retrieve the one row, will do so with only a consistent get – examination.  Back to one consistent get and only one latch get as well.

Again, clusters and all their various types and forms will be discussed in future blog entries.

Perhaps our good old familar heap table might not always be the best and most efficient option when creating these small tables (or even larger tables for that matter).

But for now the key message from this series is that any table, no matter how small can potentially benefit from being indexed. There really is no such thing as a table that’s too small to benefit from an index.Yes the difference might be small and of no real consequence but then again for larger database environments the overall savings might all add up and surprise. Note that the associated costs of having such indexes are also likely to be relatively small so perhaps it might just be worthwhile indexing those small tables after all 😉