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.trackback
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 …
UPDATE: just to clarify based on comments already made.
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).
Richard Foote's Oracle Blog 
I would say 2 > 3 > 4 > 1 in efficiency (less “consistent gets”)
LikeLike
Hi Vincent
Thanks for your answer. Interesting …
LikeLike
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
LikeLike
Hi Martin
Yes, the row contains columns not in the index and we need to visit the table to get the row.
LikeLike
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
LikeLike
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.
LikeLike
Nah…. No more answers before asking some questions… 😉
The 42 rows fit into one block, but are there empty blocks below the high water mark?
LikeLike
Hi Marcus
Your lack of trust in me is hurtful 😉
No, there are no additional blocks below the HWM.
LikeLike
Does the table contain nulls?
LikeLike
I should have thought a little more carefully about this question before posting my previous comment.
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?
So, my answer to your question would be it depends…
LikeLike
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.
LikeLike
Hi Tony
No, if it makes any difference to you 🙂
LikeLike
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)..
LikeLike
Hi Radaslav
Thanks for your answer. Interesting …
LikeLike
1. option 1
2. option 2
3. option 3
4. option 4
LikeLike
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
LikeLike
Hi Martin
Well spotted 🙂
LikeLike
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.
LikeLike
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 😉
LikeLike
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>
LikeLike
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.
LikeLike
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
LikeLike