jump to navigation

Best Method To Select One Row From Small Table – Solution (Revolution 1) September 7, 2011

Posted by Richard Foote in Oracle Indexes, Small Indexes.
trackback

OK, time for some answers, although of course regular readers of this blog will already know the answer :)

When selecting one row from the small table as in the quiz in my previous post, the correct order is as follows:

1) PK access of an Index Organized Table.

This option only requires just the 1 consistent get (as the IOT only consists of just 1 block) and this consistent get is a “cheaper” consistent get examination, which in turn only requires the 1 latch. So 1 consistent get and 1 latch. For more info on this, see: http://richardfoote.wordpress.com/2009/05/27/indexes-and-small-tables-part-vii-cluster-one/.

2) Use of Unique Index With a Heap Table.

This option only requires 2 consistent gets (one to read the index leaf block and one to read the table block). There can only be a maximum of one row when a single equality predicate is specified as the index is Unique, which means that 2 consistent gets is the maximum necessary (there may only be the 1 consistent get if there are either no rows to be returned or if all required columns can be found in the index). Additionally, because the index is Unique, both consistent gets are the cheaper, 1 latch consistent get examinations. So that’s 2 consistent gets and 2 latches. For more information, see: http://richardfoote.wordpress.com/2009/05/13/indexes-and-small-tables-part-v-its-no-game/.

3) Use of Non-Unique Index With a Heap Table

This option requires at most 3 consistent gets (one to read the leaf block, one to access the table and possibly one more to perform an additional fetch operation and checking the leaf block again in case of more rows, possibly necessary as the index is Non-Unique and there could be more than one row that matches an equality predicate). Unfortunately, as the index is Non-Unique, the consistent gets are the full-blown consistent gets which requires the buffer block to be pinned/unpinned via 2 latch calls. If the last value is specified, then the additional fetch may be unnecessary and if the index contains all the necessary columns, then just 1 consistent get would be necessary. But in the example provided, we’re looking at typically 3 consistent gets and 6 latches. For more information, see: http://richardfoote.wordpress.com/2009/05/05/indexes-and-small-tables-part-iv-treefingers/.

4) Use of a Full Table Scan

This option requires as a minimum 4 consistent gets (as Oracle needs to not only access the one block containing the 42 rows, but additionally the table segment header multiple times in order to determine the extent map, segment HWM etc.). These additional consistent gets are not generally an issue as these overheads are negligible for a typical FTS of a typical table. But in this example, with a tiny table, these consistent gets makes the difference. Note also that all 4 consistent gets require the block to be pinned/unpinned and so require 2 latch gets each. So that’s 4 consistent gets and 8 latches, minimum, even if all the rows can fit in 1 table block. On its own, no big deal, but if this small lookup table is accessed 10,000 times a minute, that’s potentially a lot of extra CPU and contention when performing a FTS over the above options. For more info, see: http://richardfoote.wordpress.com/2009/04/16/indexes-on-small-tables-part-i-one-of-the-few/ and the other articles on Indexes on Small Tables.

To those that got it right, well done. To those that didn’t, hopefully you’ve learnt something useful.

The moral of the story, no table is too small to potentially benefit from an index, even if the table is only one block in size :)

New question and discussion tomorrow.

About these ads

Comments»

1. tonysleight - September 7, 2011

You were quite correct, my results were revealing.

I understand your calculations above, however, they do not compare with my test results. So, to start with I ran your example 1) above and using 11G R2 (Windows 32 bit version) I got the following:-

TESTDB01 USER > create table small (id number primary key, name varchar2(10)) organization index;

Table created.

TESTDB01 USER > insert into small select rownum, ‘BOWIE’ from dual connect by level <=100;

100 rows created.

TESTDB01 USER > commit;

Commit complete.

TESTDB01 USER > exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’SMALL’, method_opt=>’FOR ALL COLUMNS SIZE 1);
ERROR:
ORA-01756: quoted string not properly terminated

TESTDB01 USER > exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’SMALL’, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

TESTDB01 USER > set autotrace on
TESTDB01 USER > select * from small where id = 42;

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

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 2598631370

—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 9 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_151370 | 1 | 9 | 0 (0)| 00:00:01 |
—————————————————————————————-

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

1 – access("ID"=42)

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

TESTDB01 USER >

The difference between my test results and your test results for the IOT access is 48 recursive calls and an extra 8 consistent gets. What could be the cause of the recursive calls?

My test results also differed from your solutions by quite a wide margin on each test case. There were also lots of recursive calls and also some memory sorts added which I think inflated my results.

I would be interested to know from where these extra gets and calls are originating.

My test results are quite lengthy (I apologise now!).

uconp020 SYSTEM  > 
uconp020 SYSTEM  > drop table t1;

Table dropped.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > create table t1 (c1 varchar2(2)) PCTFREE 0;

Table created.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > insert into t1 select to_char(to_number('1','99')+ level) from dual connect by level < 43;

42 rows created.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > commit;

Commit complete.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'T1',method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > 
uconp020 SYSTEM  > select blocks from user_tables where table_name = 'T1';

    BLOCKS                                                                                                                          
----------                                                                                                                          
         5                                                                                                                          

1 row selected.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > set autotrace on
uconp020 SYSTEM  >   select * from t1 where c1 = '21';

C1                                                                                                                                  
--                                                                                                                                  
21                                                                                                                                  

1 row selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |     3 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("C1"='21')                                                                                                            


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

uconp020 SYSTEM  > 
uconp020 SYSTEM  > set autotrace off
uconp020 SYSTEM  > 
uconp020 SYSTEM  > create index i_t1 on t1 (c1);

Index created.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > analyze index i_t1 validate structure;

Index analyzed.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'T1',method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > select height, lf_blks, br_blks, lf_rows, distinct_keys, rows_per_key from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS    LF_ROWS DISTINCT_KEYS ROWS_PER_KEY                                                              
---------- ---------- ---------- ---------- ------------- ------------                                                              
         1          1          0         42            42            1                                                              

1 row selected.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > select blocks from user_tables where table_name = 'T1';

    BLOCKS                                                                                                                          
----------                                                                                                                          
         5                                                                                                                          

1 row selected.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > alter system flush buffer_cache;

System altered.

uconp020 SYSTEM  > alter system flush shared_pool;

System altered.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > set autotrace on
uconp020 SYSTEM  >   select * from t1 where c1 = '21';

C1                                                                                                                                  
--                                                                                                                                  
21                                                                                                                                  

1 row selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2966378588                                                                                                         
                                                                                                                                    
-------------------------------------------------------------------------                                                           
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                           
-------------------------------------------------------------------------                                                           
|   0 | SELECT STATEMENT |      |     1 |     3 |     1   (0)| 00:00:01 |                                                           
|*  1 |  INDEX RANGE SCAN| I_T1 |     1 |     3 |     1   (0)| 00:00:01 |                                                           
-------------------------------------------------------------------------                                                           
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - access("C1"='21')                                                                                                            


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

uconp020 SYSTEM  > set autotrace off
uconp020 SYSTEM  > 
uconp020 SYSTEM  > drop index i_t1;

Index dropped.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > create unique index ui_t1 on t1(c1);

Index created.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > analyze index ui_t1 validate structure;

Index analyzed.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'T1',method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > select blocks from user_tables where table_name = 'T1';

    BLOCKS                                                                                                                          
----------                                                                                                                          
         5                                                                                                                          

1 row selected.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > select height, lf_blks, br_blks, lf_rows, distinct_keys, rows_per_key from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS    LF_ROWS DISTINCT_KEYS ROWS_PER_KEY                                                              
---------- ---------- ---------- ---------- ------------- ------------                                                              
         1          1          0         42            42            1                                                              

1 row selected.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > alter system flush buffer_cache;

System altered.

uconp020 SYSTEM  > alter system flush shared_pool;

System altered.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > set autotrace on
uconp020 SYSTEM  >   select * from t1 where c1 = '21';

C1                                                                                                                                  
--                                                                                                                                  
21                                                                                                                                  

1 row selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3323832122                                                                                                         
                                                                                                                                    
---------------------------------------------------------------------------                                                         
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                                         
---------------------------------------------------------------------------                                                         
|   0 | SELECT STATEMENT  |       |     1 |     3 |     0   (0)| 00:00:01 |                                                         
|*  1 |  INDEX UNIQUE SCAN| UI_T1 |     1 |     3 |     0   (0)| 00:00:01 |                                                         
---------------------------------------------------------------------------                                                         
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - access("C1"='21')                                                                                                            


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

uconp020 SYSTEM  > set autotrace off
uconp020 SYSTEM  > 
uconp020 SYSTEM  > drop index ui_t1;

Index dropped.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > drop table t1;

Table dropped.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > create table t1 (c1, constraint t1_pk primary key (c1)) organization index as select to_char(to_number('1','99')+level) from dual connect by level < 43;

Table created.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > analyze index t1_pk validate structure;

Index analyzed.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > exec dbms_stats.gather_table_stats(ownname=>'SYSTEM',tabname=>'T1',method_opt=>'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > select blocks from user_tables where table_name = 'T1';

    BLOCKS                                                                                                                          
----------                                                                                                                          
                                                                                                                                    

1 row selected.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > select height, lf_blks, br_blks, lf_rows, distinct_keys, rows_per_key from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS    LF_ROWS DISTINCT_KEYS ROWS_PER_KEY                                                              
---------- ---------- ---------- ---------- ------------- ------------                                                              
         1          1          0         42            42            1                                                              

1 row selected.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > alter system flush buffer_cache;

System altered.

uconp020 SYSTEM  > alter system flush shared_pool;

System altered.

uconp020 SYSTEM  > 
uconp020 SYSTEM  > set autotrace on
uconp020 SYSTEM  >   select * from t1 where c1 = '21';

C1                                                                                                                                  
----------------------------------------                                                                                            
21                                                                                                                                  

1 row selected.


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 426392120                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------                                                         
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                                         
---------------------------------------------------------------------------                                                         
|   0 | SELECT STATEMENT  |       |     1 |     3 |     0   (0)| 00:00:01 |                                                         
|*  1 |  INDEX UNIQUE SCAN| T1_PK |     1 |     3 |     0   (0)| 00:00:01 |                                                         
---------------------------------------------------------------------------                                                         
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - access("C1"='21')                                                                                                            


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

uconp020 SYSTEM  > set autotrace off
uconp020 SYSTEM  > 
uconp020 SYSTEM  > spool off;
Richard Foote - September 7, 2011

Hi Tony

OK, you need to run the examples multiple times so that the results are not “tainted” by recursive calls associated with parsing the statements, the possible creation of baselines and the such. These will perform additional work behind the scenes which will impact the results.

To guarantee a level playing field between options, you need to run them each a few times so that you finally get to the point where there are no recursive calls. These recursive calls only occur when they get executed the first few times (assuming the plans remain cached in the shared_pool), which becomes a non-issue when you have an environment that executes statements many times (as one would hope).

In most of my examples, I generally rerun a statement to get rid of the recursive calls, else the picture becomes cloudy and comparisons invalid if some executions perform recursive statements and others don’t.

tonysleight - September 7, 2011

you are quite correct!

Thank you for correcting my delusions!

After running queries more than once sequentially, my results agree with your totals completely, even down to the FTS at 8 gets which is of the same order of magnitude as your prediction of a minimum of 4.

2. Richard Foote - September 9, 2011

Hi Tony

No worries :)

Actually, your last comment as just given me an idea for an interesting little puzzle I can use on a new quiz. Thank-you !!


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,822 other followers

%d bloggers like this: