Quiz Night

Inspired by Martin Widlake’s series on IOTs, I thought I’d throw out this little item. In the following, run against 10.2.0.3, tables t3 and t4 are index organized tables, in the same tablespace, with a primary key of (id1, id2) in that order.

SQL> desc t3
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID1                           NOT NULL NUMBER
 ID2                           NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 V2                                     VARCHAR2(40)
 PADDING                                VARCHAR2(500)

SQL> desc t4
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 ID1                           NOT NULL NUMBER
 ID2                           NOT NULL NUMBER
 V1                                     VARCHAR2(40)
 V2                                     VARCHAR2(40)
 PADDING                                VARCHAR2(500)

SQL> select index_name, column_name
  2  from user_ind_columns
  3  where table_name in ('T3','T4')
  4  order by table_name, index_name, column_position;

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
T3_PK                ID1
                     ID2

T4_PK                ID1
                     ID2

4 rows selected.

SQL> truncate table t3;

Table truncated.

SQL> truncate table t4;

Table truncated.

SQL> insert into t3 select * from t1;

2000 rows created.

SQL> insert into t4 select * from t1;

2000 rows created.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly
SQL> select max(v1) from t3;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2110918630

---------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     3 |  2003 |
|   1 |  SORT AGGREGATE       |       |     1 |     3 |       |
|   2 |   INDEX FAST FULL SCAN| T3_PK |  2000 |  6000 |  2003 |
---------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

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

SQL> select max(v1) from t4;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4293386624

---------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |     3 |  2003 |
|   1 |  SORT AGGREGATE       |       |     1 |     3 |       |
|   2 |   INDEX FAST FULL SCAN| T4_PK |  2000 |  6000 |  2003 |
---------------------------------------------------------------

Note
-----
   - cpu costing is off (consider enabling it)

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

The question – how come, following a truncate and with an index fast full scan in both cases, the number of consistent gets varies so much between the two tables ? You may also wonder why the cost is so high for a fast full scan on such a small amount of data.

In case it helps, here’s another little bit of information:

  1  select
  2     index_name, blevel, leaf_blocks,
  3     avg_leaf_blocks_per_key         avg_lpk,
  4     avg_data_blocks_per_key         avg_dpk,
  5     clustering_factor               cluf
  6  from
  7     user_indexes
  8  where
  9     table_name in ('T3','T4')
 10  order by
 11*    index_name
SQL> /

INDEX_NAME  BLEVEL LEAF_BLOCKS    AVG_LPK    AVG_DPK    CLUF
---------- ------- ----------- ---------- ---------- -------
T3_PK            1           8          1          1    2000
T4_PK            1          12          1          1    2000