Oracle-oracle以rowid定位数据效率

Question

oracle以rowid定位数据与用主键去定位，效率差别如何？

Answer 1

rowid 即是单个row在Oracle数据库中的具体位置: (object id, relative file id, block within file, row within block).
通过主键定位某个row, 先找到这个row在B*树索引中存的rowid, 在通过rowid定位.
所以通过主键定位恰好多了一个查找索引的过程.

试验如下:
SQL> select * from a where rowid='AADZfIAABAABiEpAAA';

A B

1

Execution Plan

Plan hash value: 2574054659

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 38 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY USER ROWID| A | 1 | 38 | 1 (0)| 00:00:01 |

Statistics

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

SQL> select * from a where a=1;

A B

1

Execution Plan

Plan hash value: 1822123981

| Id | Operation | Name | Rows | Bytes | Cost (%U)| Time |

| 0 | SELECT STATEMENT | | 1 | 26 | 10)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| A | 1 | 26 | 10)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | SYS_C001590291 | 1 | | 10)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access("A"=1)

Statistics

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