index和rowid的一点关系！

唯一index和非唯一index的index entry中尽管都存在rowid，但是其实有一点细微的差别，在唯一index中index_column就是index_column, 数据通过index_column排序；而在非唯一index中index_column其实是index_key+rowid，如果index_key相同，那么会使用rowid排序。

[@more@]

Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.
--=======================================================
SQL> select /*+ index(a idx_tt) */ a.*,rowid from tt a where id<=2;

ID NAME AGE ROWID
---------- ---------- ---------- ------------------
1 a AAAD9yAAEAAAAAOAAA
1 a AAAD9yAAEAAAAAQAAC
2 a AAAD9yAAEAAAAAOAAB
2 a AAAD9yAAEAAAAAQAAD

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

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

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

2 - access("ID"<=2)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
695 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1002465/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1002465/