关于索引组织表IOT

摘自oracle高效设计：

create table t
( a int,
b int,
c int,
primary key (a,b)
)
organization index;

create index t_idx_c on t(c);

set autotrace traceonly explain

set autotrace traceonly explain
select a, b from t where c = 55;

执行计划
----------------------------------------------------------
Plan hash value: 2046116080

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| T_IDX_C | 1 | 39 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

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

1 - access("C"=55)

Note
-----
- dynamic sampling used for this statement

注意执行计划没有table access by rowid，IOT表的逻辑rowid位于索引结构中，
主键值已经存在rowid里面。

修改c为非NULL，执行：

SELECT ROWID, t.* FROM t;

执行计划
----------------------------------------------------------
Plan hash value: 339099436

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 9595 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | T_IDX_C | 101 | 9595 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

可以发现作索引全扫描T_IDX_C 就可以获得结果集。