[ORALCE]SQL 优化案例之 TABLE ACCESS BY USER ROWID

ROWID访问数据行最快，在实际应用中可以采用

生成测试数据：

drop table tx1 purge;
create table tx1 as select * from dba_objects;
update tx1 set object_id=rownum;
commit;

set autotrace traceonly
set linesize 160
exec dbms_stats.gather_table_stats('SYS','TX1',estimate_percent =>100,method_opt=>'for all indexed columns',cascade=>true);
SQL> select rowid from TX1 where object_id=188;

ROWID
------------------
AAASixAABAABP8bAA2

测试：

SQL> set autotrace traceonly
SQL> select * from TX1 where object_id=188 and rowid='AAASixAABAABP8bAA2';


Execution Plan
----------------------------------------------------------
Plan hash value: 561004343

-----------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    1 |   132 |    1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY USER ROWID| TX1  |    1 |   132 |    1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=188)


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

结论：　用ROWID访问，只有一个逻辑读，没有物理读，效率极高，在实际开发中一定场合，可以采用