jump to navigation

Indexes And Small tables Part VII (Cluster One) May 27, 2009

Posted by Richard Foote in Index Organized Tables, Oracle Indexes, Small Indexes.
trackback

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 ;)

About these ads

Comments»

1. Christian Mallwitz - May 27, 2009

I’m really enjoying your posts!

One questions: I have a materialized view that runs some complex SQL to generate a lookup table with a two column PK and one column payload. The table is bigger than one block. I was trying to actually generate the materialized view as an IOT because the Oracle documentation indicates I can pass an appropriate storage clause to the create materialized view statement. But all my attempts failed. Could you provide an example?

Thanks
Christian Mallwitz

Richard Foote - May 27, 2009

Hi Christian

I need to see your specific example and what errors you experience to be sure how best to advice (feel free to post more details here or send me an email). Certainly the suggestion by Stew might be a viable solution although it depends on how you want to refresh the thing.

2. Jonathan Lewis - May 27, 2009

Richard,
A note on the single table hash cluster. If you create a unique index on the hash key, and if there are no hash collisions in a cluster block then the consistent get will be an examination since (a) Oracle knows that there is only a single row to collect and (b) the structure of the block will be so rigidly defined that Oracle will know the actual rowid, not just the block id, of the target row.

(The block has a state flag that records an A in the fourth place if both conditions are met – and the non-collision condition is a common reason for failure).

Richard Foote - May 27, 2009

Hi Jonathan

Thank you very much for the note. It fits in so well with what I’ve been discussing that I’ve extended the example in the blog piece to include it. It’s yet another example of how a unique index can make that little bit of difference.

3. Stew Ashton - May 27, 2009

Christian,

The only way I was able to do this was :
1) create the IOT, then
2) create the materialized view with the ON PREBUILT TABLE clause.

Just a general warning : a complete refresh of an IOT materialized view will always do conventional inserts because direct path inserts are incompatible with an index structure; this will generate much more UNDO than you would usually expect.

Richard Foote - May 27, 2009

Hi Stew

Another warning is if you want to completely refresh a MV within a refresh group as Oracle is forced to effectively use a delete command to ensure consistency and the ability to rollbackall the changes within the group. Can be very expensive.

4. Alberto Dell'Era - May 27, 2009

And of course, even if the rows are so few and small to fit in one block, looking for “the” row on the table block means to scan all of them, while looking for “the” row on the index block is a quick binary search :)

Actually, I would consider the hypothesis that the “consistent get – examination” shortcut is possible only because the unique index block can be visited in O(log(N)) time, thus very quickly, while holding the latch – O(log(N)) being a combination of the index entries being ordered and the fact that there is only one of them that can satisfy the predicate.

Richard Foote - May 27, 2009

Hi Alberto

Absolutely. Finding a specific index entry would be very quick compared to scanning all the rows in a table block as would visiting the table with a rowid in hand and hence knowing the exact row directory of interest. Holding onto the buffer chain latch during these scans is not likely to therefore cause undue contention as a result, making an examination get a viable alternative.

And the CPU savings associated with reading these blocks more efficiently than one can during a FTS can all add up.

5. Brian Tkatch - May 28, 2009

Richard, as usual, fantastic.

6. B. Polarski - May 28, 2009

Excellent series

7. PdV - May 29, 2009

Richard, Jonathan, others,

Thanks for some excellent info.

Even today I came across another system with a few small-ish unindexed tables (and queried 10K+ times in a 15min spreport). Pointing them to your blog for “reference” is a great Help.

IOTs should be a no-brainer in many cases, and a bit of publicity for them wont hurt.

I’m looking forward to your series on (hash)clusters. I’m barely scratching the surface of those myself and I find there is a lot I need to know to get it just-right. If only things were a bit more simple sometimes.

8. Richard Foote - May 31, 2009

Thanks guys for the nice feedback, much appreciated :)

9. Tony Johnson - June 1, 2009

Richard,

Thanks for the excellent series. You have pointed out many examples that should be of benefit to architects around the world. I have fought the index battle more times than I care to recall over my career and this series will help in the future to (hopefully) minimize that exercise in the future.

Richard Foote - June 9, 2009

Hi Tony

Glad the series has been of use :)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,915 other followers

%d bloggers like this: