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 😉

Advertisements

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

Like

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.

Like

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

Like

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.

Like

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.

Like

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.

Like

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.

Like

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.

Like

5. Brian Tkatch - May 28, 2009

Richard, as usual, fantastic.

Like

6. B. Polarski - May 28, 2009

Excellent series

Like

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.

Like

8. Richard Foote - May 31, 2009

Thanks guys for the nice feedback, much appreciated 🙂

Like

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.

Like

Richard Foote - June 9, 2009

Hi Tony

Glad the series has been of use 🙂

Like

10. IUDITH MENTZEL - June 15, 2017

Hello Mr.Foote,

First, thanks a lot for your excellent site, which I enjoy a lot already for a very long time 🙂

I have a question related to IOT-s:
Is it true that reading a row by the PK will always perform exactly 1 LIO ?
supposing, of course, that we have no chained rows.

Here is a small test that I performed:

create table test_tab (
currency_alpha_code varchar2(3) not null,
currency_numeric_code varchar2(3) not null,
currency_name varchar2(100) not null,
minor_unit integer,
primary key ( currency_alpha_code ) ,
unique ( currency_numeric_code )
) organization index;

insert into test_tab
WITH rws AS
( SELECT CHR(65 + i – 1)||TO_CHAR(j,’fm00′) code,
TO_CHAR(rownum,’fm000′) num,
DBMS_RANDOM.STRING(‘U’,30) nm,
MOD(rownum,5) + 1 minor
FROM (SELECT rownum i FROM DUAL CONNECT BY LEVEL <= 26),
(SELECT rownum j FROM DUAL CONNECT BY LEVEL ‘FOR ALL COLUMNS SIZE AUTO’,
CASCADE => TRUE,
ESTIMATE_PERCENT => 100);
end;
/

alter session set statistics_level = all
/

select * from test_tab where currency_alpha_code = ‘EUR’
/

CURRENCY_ALPHA_CODE CURRENCY_NUMERIC_CODE CURRENCY_NAME MINOR_UNIT
————————————————————————–
EUR 901 CURRENCY EURO 100

SELECT p.*
FROM
( SELECT * FROM v$sql
WHERE
PARSING_SCHEMA_NAME =
SYS_CONTEXT(‘USERENV’,’CURRENT_USER’)
AND UPPER(SQL_TEXT)
LIKE ‘%SELECT * FROM TEST_TAB%’
ORDER BY LAST_LOAD_TIME
) s,
TABLE ( DBMS_XPLAN.display_cursor (
s.sql_id, s.child_number, ‘IOSTATS LAST’) ) p
/

PLAN_TABLE_OUTPUT
————————————————————————————————————
SQL_ID 38n59j7v2mnh5, child number 0

select * from test_tab where currency_alpha_code = ‘EUR’

Plan hash value: 1479017752

————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | 2 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_3601337 | 1 | 1 | 1 |00:00:00.01 | 2 | 2 |
————————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“CURRENCY_ALPHA_CODE”=’EUR’)
————————————-

18 rows selected.

My question is:

Is there any logical explanation for why do we see BUFFERS=2 for this statement, instead of BUFFERS=1,
considering that the statement accesses the IOT’s PK index only ?

I performed a few similar tests for the SMALL table in your article and also for a modified version of it
as shown below, and in all these tests I still get a plan with BUFFERS = 1.

create table small (id number primary key, name varchar2(10) unique, long_name varchar2(100)) organization index;

insert into small select rownum, ‘BOWIE’||rownum, rpad(‘BOWIE’,50,’x’) from dual connect by level <=100;

alter session set statistics_level = all
/

select * from small where id = 42;

D NAME LONG_NAME
——————————————————————
42 BOWIE42 BOWIExxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

SELECT p.*
FROM
( SELECT * FROM v$sql
WHERE
PARSING_SCHEMA_NAME =
SYS_CONTEXT('USERENV','CURRENT_USER')
AND UPPER(SQL_TEXT)
LIKE '%SELECT * FROM SMALL%'
ORDER BY LAST_LOAD_TIME
) s,
TABLE ( DBMS_XPLAN.display_cursor (
s.sql_id, s.child_number, 'IOSTATS LAST') ) p
/

PLAN_TABLE_OUTPUT
—————————————————————————————————
SQL_ID fvfzyk69w9x5q, child number 0

select * from small where id = 42

Plan hash value: 3902163832

—————————————————————————————————
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
—————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_3600183 | 1 | 1 | 1 |00:00:00.01 | 1 |
—————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access("ID"=42)
————————————-

18 rows selected.

Currently I only have access to LiveSQL, so I could not use AUTOTRACE for checking, but I think that for SELECT statements, the value of BUFFERS statistics shown in the execution plan
is equivalent to the CONSISTENT GETS shown with AUTOTRACE.

Thanks a lot in advance and thanks once again for your excellent work !

Best Regards,
Iudith Mentzel

Like

Richard Foote - June 16, 2017

Hi Iudith

Unfortunately your demo hasn’t been formatted properly and is difficult to reproduce.

Before going too deep into this (pun fully intended), can you just first confirm your IOT is only 1 block in size and doesn’t have to read a root block first ?

Like

11. IUDITH MENTZEL - June 17, 2017

Hello Richard,

Thank you a lot for your so fast answer 🙂

I created a similar heap table, test_tab2, loaded with the same data as the IOT test_tab,
each of the two tables having a PK and a UK defined.

I have the following statistics:

select table_name, num_rows, blocks, avg_row_len
from user_tables
order by table_name
/

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
—————————————————
TEST_TAB 184 – 42
TEST_TAB2 184 5 42

2 rows selected.

select table_name, index_name, blevel, leaf_blocks, distinct_keys

from user_indexes

order by table_name, index_name
/

TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS
—————————————————————————–
TEST_TAB SYS_C003192888 0 1 184
TEST_TAB SYS_IOT_TOP_3627261 1 3 184
TEST_TAB2 SYS_C003192898 0 1 184
TEST_TAB2 SYS_C003192899 0 1 184

4 rows selected.

Looking at the statistics above, it is not clear to me why the BLEVEL is only positive for the IOT, and it is 0 for the other three indexes.

Your question pointed me exactly to what I have suspected when I first saw BUFFERS=2 in the execution plan for the IOT.

But, for the heap table, we also have the same result BUFFERS=2 when selecting a single row using the PK index,
which seems logical, because it is supposed to read (at least) one block for the index scan and one for the table access.

As by my intuition, the IOT was supposed to perform “one LIO less” than the heap table, because it does not need to access the table.

Also, it was not entirely clear for me whether the BUFFERS statistic in the execution plan does count all the blocks read from the index, or just the (single) leaf block.

Is it possible to have an index without a root block at all ?

This would indeed be a new discovery for me !

And, if so, then why doesn’t this happen for the IOT (index) as well ?
Is it because it has 3 leaf blocks, while the other indexes
have just 1 ?

There is nothing like learning from you :):)

Thanks a lot once again & Best Regards,
Iudith Mentzel

Like

Richard Foote - June 20, 2017

Hi Iudith

It’s indeed possible to have an index that consists of just a leaf block (blevel = 0). My demo in this article is such an index. However, if all the index entries can’t fit in the single leaf block (note that with an IOT, that generally includes all columns within the table), then you need a root block to determine which leaf block to visit to find the row of interest.

Like

12. IUDITH MENTZEL - June 21, 2017

Thank you so much Richard,
everything is clear now and a good lesson learned 🙂

Best Regards,
Iudith

Like


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

%d bloggers like this: