Table Access by Rowid 有時候會花費很長時間

通過ROWID通路表的時候, 通常是先根據索引找到某個範圍的ROWID,然後再根據ROWID反查表去獲得滿足其他條件的資料。　通常反查的實體讀會比較多。　如果這個反查的實體讀特别大并且沒有其他更有效的方式過濾傳回結果的時候，應該考慮檢查使用的索引是否不合适

官方解釋如下：

8.2.3.2 How Table Access by Rowid Works

To access a table by rowid, the database performs multiple steps.

The database does the following:

1. Obtains the rowids of the selected rows, either from the statement 

   WHERE

    clause or through an index scan of one or more indexes

   Table access may be needed for columns in the statement not present in the index.

2. Locates each selected row in the table based on its rowid

8.2.3.3 Table Access by Rowid: Example

This example demonstrates rowid access of the 

hr.employees

 table.

Assume that you run the following query:

SELECT * 
FROM   employees 
WHERE  employee_id > 190;
           

Step 2 of the following plan shows a range scan of the 

emp_emp_id_pk

 index on the 

hr.employees

 table. The database uses the rowids obtained from the index to find the corresponding rows from the 

employees

 table, and then retrieve them. The 

BATCHED

 access shown in Step 1 means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.

--------------------------------------------------------------------------------
|Id| Operation                           | Name     |Rows|Bytes|Cost(%CPU)|Time|
--------------------------------------------------------------------------------
| 0| SELECT STATEMENT                    |             |  |    |2(100)|        |
| 1|  TABLE ACCESS BY INDEX ROWID BATCHED|EMPLOYEES    |16|1104|2  (0)|00:00:01|
|*2|   INDEX RANGE SCAN                  |EMP_EMP_ID_PK|16|    |1  (0)|00:00:01|
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID">190)