Best Method To Select One Row From Small Table Quiz (Each Small Candle) September 5, 2011

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

Assume you have a tiny little table with just 42 rows (naturally) that all fit in one table block. Order the following options in order of “efficiency” (most efficient option first) when accessing just one of these rows:

1) Full Table Scan of Heap Table

2) PK access of an Index Organised Table

3) Index access of Heap Table via a Unique Index

4) Index access of Heap Table via a Non-Unique Index

If you think any of the options are the same, then you can order them as follows (example only):

1) Option 1

2) Option 2

2) Option 3

4) Option 4

Answer in the next few days …

Yes, any index must visit the table as there are required columns within the table that are not stored in the index (this is implied by the above options). The table has only ever contained 42 rows and the are no additional table blocks below the table HWM (not that this really makes a difference to the answer). To keep it simple, the column being queried has a NOT NULL constraint (although it doesn’t really matter, except for when you want to put a PK constraint on it such as with the IOT option).

1. Vincent Malgrat - September 6, 2011

I would say 2 > 3 > 4 > 1 in efficiency (less “consistent gets”)

Richard Foote - September 6, 2011

Hi Vincent

2. mwidlake - September 6, 2011

Hi Richard,

Can I just check – do you need to clarify if the row contains columns that are not in the indexes? ie oracle has to visit the actual row to get the data?

Cheers,

Martin

Richard Foote - September 6, 2011

Hi Martin

Yes, the row contains columns not in the index and we need to visit the table to get the row.

3. surya - September 6, 2011

Hi,

Option 1 ….

** naturally all rows fit in one block”

I dont feel other options will valid here if you have to access your data block , if goes with index, a root(hope root and branch leaf blocks will be only one) the cost may will be 1+1 =2 (index block + datablock)blocks for index access where in full tablescan will be as 1 block.

Let me know if my assumption is wrong

-Thanks
Surya

Richard Foote - September 6, 2011

Hi Surya

You need to list all 4 options in the correct order ;)

Note that a level 0 index consists only a single leaf block.

4. Marcus Mönnig - September 6, 2011

The 42 rows fit into one block, but are there empty blocks below the high water mark?

Richard Foote - September 6, 2011

Hi Marcus

Your lack of trust in me is hurtful ;)

No, there are no additional blocks below the HWM.

5. tonysleight - September 6, 2011

Does the table contain nulls?

tonysleight - September 6, 2011

My first question would be, how can you create a table that contains a single database block? There must be a minimum size that a table with a single column can be. However, block size is configurable, so, you could create a several tables all one block but each a different size.

Also, is it not true that an IOT is not treated like a table and does not have any blocks associated with it?

Richard Foote - September 6, 2011

Hi Tony

A guess now with deferred segment creation, the minimum size of a table can be 0 blocks (ie. there is no segment until the first row is inserted). However, if you have inserted a row, then the smallest table you can have is one with just the one block below the high water mark.

So this table in question is effectively as small as you can get with a table that actually contains rows. Assume the same block size for both table and indexes.

An IOT is treated like a table in that you can select from it and perform DML with it but it’s structurally an index. But an index that doesn’t point to any rows in another table segment.

So the question is not meant to asked in a manner in which the answer depends. The 4 options can be listed in a consistent order based on accessing 1 row from a 42 row table in which all rows reside in the 1 table block.

Richard Foote - September 6, 2011

Hi Tony

No, if it makes any difference to you :)

6. Radoslav Golian - September 6, 2011

I agree with Vincent..

1) Option 2 (1 consistent get = unique scan)
2) Option 3 (2 consistent gets = unique scan + table block)
3) Option 4 (3 consistent gets = 1 from index + 1 table block + 1 consistent read from index to check whether there are no more rows)
4) Option 1 – I think at least 4 cr (table segment header, table block, check whether there are no more rows)

Option 2 and Option 3 could be the same if the table has only one column, then we don’t have to go to the table in Option 3 and query could be answered from the index (1 consistent get)..

Richard Foote - September 6, 2011

7. Sunil - September 7, 2011

1. option 1
2. option 2
3. option 3
4. option 4

8. Martin Preiss - September 7, 2011

Hi Richard,

I think I saw the answers to your question in an interesting series of blog articles on “Indexes And Small Tables” two years ago (especially Part V). Must be somewhere near …

Regards

Martin

Richard Foote - September 7, 2011

Hi Martin

Well spotted :)

9. tonysleight - September 7, 2011

I think from what I have read the IOT and unique index are functionally similar, the difference being the IOT does not need to access the table data. The non unique index might have nulls and same column values and I think this index has to walk along the leaf blocks to the index the required row. The full table scan can read the data in a single block read as the whole table fits in a single block.

So, my guess would be option 2) followed by 3) followed by 1) followed by 4).

To check, I conducted a test, and the results were quite revealing. I’ll not post them unless they are different to your answers Richard when you post them.

Richard Foote - September 7, 2011

Hi Tony

Difference between a PK and an IOT is that the PK indeed refers to a table segment via the associated rowids while an IOT has the additional columns co-located within the index structure and so doesn’t need a table segment to reference (although you can have an overflow segment to cater for columns you may not want within the index).

Note that the Non-Unique index can’t have NULLs the same as with the Unique index if the entire index entry consists of NULLs.

With such a tiny table, the index is likewise tiny and consists of just one block as well so there’s no index leaf blocks it needs to walk along, there’s just the one.

I bet the tests were revealing ;)

10. Hans Gruijs - September 20, 2011

IOT and unique index scanare the fastest. Unique index scan looks a little bit faster than IOT.
Than non-unique index scan and slowest is FTS.

This supprises me. I would have expected FTS is the fastest as only one scan had to be executed and with an index 2.

OPTIE 1:

09:31:54 SQL> create table test ( id number, naam varchar2( 30));

Table created.

Elapsed: 00:00:00.03
09:32:02 SQL> begin for i in 1 .. 42 loop insert into test values ( i, lpad(‘a’,25,’a’)||i); end loop; end;
09:32:10 2 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
09:32:11 SQL> begin
09:33:28 2 DBMS_STATS.gather_schema_stats
09:33:28 3 ( ownname => ‘CORUSDBA’
09:33:28 4 , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
09:33:28 5 , method_opt => ‘for all columns size skewonly’
09:33:28 6 , Degree => DBMS_STATS.AUTO_DEGREE
09:33:28 7 , cascade => true
09:33:28 8 );
09:33:28 9 end;
09:33:28 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.05
09:34:10 SQL> declare l varchar2(30); begin for i in 1 .. 1000000 loop select naam into l from test where id = 32; end loop; end;
09:34:26 2 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:45.60
09:35:13 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:44.89

OPTIE 3:

09:37:38 SQL> create unique index i1 on test(id);

Index created.

Elapsed: 00:00:00.01
09:38:18 SQL> begin
09:38:20 2 DBMS_STATS.gather_schema_stats
09:38:20 3 ( ownname => ‘CORUSDBA’
09:38:20 4 , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
09:38:20 5 , method_opt => ‘for all columns size skewonly’
09:38:20 6 , Degree => DBMS_STATS.AUTO_DEGREE
09:38:20 7 , cascade => true
09:38:20 8 );
09:38:20 9 end;
09:38:20 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.51
09:38:23 SQL> set autotrace on explain
09:38:34 SQL> select naam from test where id = 32;

NAAM
——————————
aaaaaaaaaaaaaaaaaaaaaaaaa32

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 1312709105

————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 31 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 31 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | I1 | 1 | | 0 (0)| 00:00:01 |
————————————————————————————

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

2 – access(“ID”=32)

09:38:36 SQL> set autotrace off
09:38:51 SQL> declare l varchar2(30); begin for i in 1 .. 1000000 loop select naam into l from test where id = 32; end loop; end;
09:38:56 2 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.60
09:39:24 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:26.06

OPTIE 4:

09:39:51 SQL> drop index i1;

Index dropped.

Elapsed: 00:00:00.04

09:40:07 SQL> create index i1 on test(id);

Index created.

Elapsed: 00:00:00.01
09:40:12 SQL> begin
09:40:27 2 DBMS_STATS.gather_schema_stats
09:40:27 3 ( ownname => ‘CORUSDBA’
09:40:27 4 , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
09:40:27 5 , method_opt => ‘for all columns size skewonly’
09:40:27 6 , Degree => DBMS_STATS.AUTO_DEGREE
09:40:27 7 , cascade => true
09:40:27 8 );
09:40:27 9 end;
09:40:27 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.49
09:40:36 SQL> set autotrace on explain
09:40:39 SQL> select naam from test where id = 32;

NAAM
——————————
aaaaaaaaaaaaaaaaaaaaaaaaa32

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 2111324582

————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 31 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 1 (0)| 00:00:01 |
————————————————————————————

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

2 – access(“ID”=32)

09:40:40 SQL> set autotrace off
09:40:46 SQL> declare l varchar2(30); begin for i in 1 .. 1000000 loop select naam into l from test where id = 32; end loop; end;
09:40:46 2 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:33.06
09:41:20 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.56

OPTIE 2:

09:41:57 SQL> CREATE TABLE test2
09:42:28 2 ( id number
09:42:28 3 , naam varchar2(30)
09:42:28 4 , CONSTRAINT pk_test PRIMARY KEY (id)
09:42:28 5 )
09:42:28 6 ORGANIZATION INDEX
09:42:28 7 /

Table created.

Elapsed: 00:00:00.01
09:42:29 SQL> begin for i in 1 .. 42 loop insert into test2 values ( i, lpad(‘a’,25,’a’)||i); end loop; end;
09:42:39 2 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
09:42:39 SQL> begin
09:42:45 2 DBMS_STATS.gather_schema_stats
09:42:45 3 ( ownname => ‘CORUSDBA’
09:42:45 4 , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
09:42:45 5 , method_opt => ‘for all columns size skewonly’
09:42:45 6 , Degree => DBMS_STATS.AUTO_DEGREE
09:42:45 7 , cascade => true
09:42:45 8 );
09:42:45 9 end;
09:42:45 10 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.53
09:42:48 SQL> set autotrace on explain
09:42:53 SQL> select naam from test2 where id = 32;

NAAM
——————————
aaaaaaaaaaaaaaaaaaaaaaaaa32

1 row selected.

Elapsed: 00:00:00.03

Execution Plan
———————————————————-
Plan hash value: 352268188

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

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

1 – access(“ID”=32)

09:42:56 SQL> set autotrace off
09:43:01 SQL> declare l varchar2(30); begin for i in 1 .. 1000000 loop select naam into l from test2 where id = 32; end loop; end;
09:43:05 2 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.84
09:43:32 SQL> /

PL/SQL procedure successfully completed.

Elapsed: 00:00:25.36
09:43:58 SQL>

Richard Foote - September 20, 2011

Hi Hans

Hopefully I’ve helped explain why the FTS is the most expensive of the options :)

Note during most FTS, the extra few consistent gets to access the segment header matters little (a FTS scan of a 500M table for example).

However, for an index scan, a few extra consistent gets could potentially double the overall costs of using the index. That’s why Oracle is far more cleverer in determining the location of the index root block, which is where all the action begins, without having to access the index segment header.

Hans Gruijs - September 21, 2011

Hi Richard,
Thanks for the explanation, this makes sense.
In my case a FTS has 5 consistent gets and the index scan only 2.
Regards, Hans