Einzelsatzzugriff

Und schon wieder eine Quizfrage des Herrn Foote:

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

In den Kommentaren wurden gleich ein paar berechtigte Nachfragen gestellt, aber ich tu mal so, als sei die Frage völlig harmlos:

1. FTS

create table t42 tablespace test_ts
as
select rownum id
     , 'Ziggy' col2
  from dual
connect by level <= 42;

exec dbms_stats.gather_table_stats(user, 'T42')

select *
  from t42
 where id = 1;

   ID COL2
----- -----
    1 Ziggy

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T42  |     1 |     9 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=1)


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

2. non-unique Index

create index t42_idx on t42(id);

--> Query

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     9 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T42     |     1 |     9 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T42_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("ID"=1)


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

3. Unique Index

create unique index t42_uidx on t42(id);

--> Query

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |     9 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T42      |     1 |     9 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | T42_UIDX |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access("ID"=1)


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

4. IOT-Zugriff

create table t42 (id, col2, constraint t42pk primary key(id)) organization index tablespace test_ts
as
select rownum id
     , 'Ziggy' col2
  from dual
connect by level <= 42

exec dbms_stats.gather_table_stats(user, 'T42')

--> Query

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

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

   1 - access("ID"=1)


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

5. Single Table Hash Cluster

Nicht in der Fragestellung enthalten, aber halbwegs zum Thema passend...

create cluster t_cluster
(id number(2,0))
tablespace test_ts
hash is id hashkeys 64;

create table t42
( id number(2,0)
, col2 varchar2(10) )
cluster t_cluster (id);

insert into t42
select rownum id
     , 'Ziggy' col2
  from dual
connect by level <= 42;

--> Query

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     0   (0)|
|*  1 |  TABLE ACCESS HASH| T42  |     1 |     9 |            |
---------------------------------------------------------------

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

   1 - access("ID"=1)


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

Also keine Überraschungen bei den Platzierungen:

1. IOT (und HashCluster) mit 1 consistent get für den Zugriff auf einen einzelnen Datensatz (+ 1 Latch)
2. unique Index Zugriff mit 2 consistent gets (+ 2 Latches; nur 2 - also 1 pro LIO -, da es sich um consistent get - examinations)
3. non-unique Index Zugriff mit 3 consistent gets (+ 6 Latches)
4. FTS mit 4 consistent get (+ 8 Latches)

Warum das so ist, hat der Herr Foote übrigens gelegentlich schon mal in seinem Blog erläutert:

• beim non-unique Index sind zwei Fetches erforderlich: einer zum Lesen des Ergebnisses und einer, um sicher zu gehen, dass nicht noch ein Satz für den gegebenen Wert folgt
• beim unique Index kann sich Oracle die zweite Fetch-Operation sparen, da nicht mehr als ein Satz zum Schlüssel vorliegen kann 
• auch zum FTS findet sich dort ein Artikel - und wahrscheinlich wären die beiden übrigen Artikel der fraglichen Serie Indexes And Small Tables auch noch verlinkungswürdig - falls das ein Wort sein sollte...

Nachtrag 07.09.2011: Inzwischen hat Richard Foote die Antwort auf seine Fragen gegeben und dabei auch die angesprochenen älteren Artikel verlinkt. Interessant sind auch noch die dort angegebenen Latch-Zahlen, die ich oben im Ergebnis ergänzt habe.