Oracle ROWID 方式访问数据库
转自 http://blog.csdn.net/leshami/article/details/7474308
和ROWNUM一样,ROWID是一个伪列,即是一个非用户定义的列,而又实际存储于数据库之中。每一个表都有一个ROWID列,一个ROWID值用于
唯一确定数据库表中的的一条记录。因此通过ROWID 方式来访问数据也是 Oracle 数据库访问数据的实现方式之一。一般情况下,ROWID方式的
访问一定以索引访问或用户指定ROWID作为先决条件,因为所有的索引访问方式最终都会转换为通过ROWID来访问数据记录。(注:index full
scan 与index fast full scan除外)由于Oracle ROWID能够直接定位一条记录,因此使用ROWID方式来访问数据,极大提高数据的访问效率。
- -->查看表上rowid
- scott@CNMMBO> select rowid,empno,ename from emp where deptno=20;
- ROWID EMPNO ENAME
- ------------------ ---------- ----------
- AAATTBAALAAAAuUAAA 7369 SMITH
- AAATTBAALAAAAuUAAD 7566 JONES
- AAATTBAALAAAAuUAAH 7788 SCOTT
- AAATTBAALAAAAuUAAK 7876 ADAMS
- AAATTBAALAAAAuUAAM 7902 FORD
- -->下面使用rowid来进行访
- -->以下演示中忽略了物理读,仅考虑逻辑读的情形
- scott@CNMMBO> set autot trace;
- scott@CNMMBO> select empno,ename from emp where rowid='AAATTBAALAAAAuUAAA';
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1116584662
- -----------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -----------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 22 | 1 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 22 | 1 (0)| 00:00:01 |
- -----------------------------------------------------------------------------------
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 1 consistent gets
- 0 physical reads
- --此时执行计划中操作1为TABLE
- ACCESS BY USER ROWID,注意此时为USER ROWID,表明由用户直接调用产生
- --统计信息中的consistent gets为1,即只需要根据rowid即可返回数据
- --使用多个rowid的情形
- scott@CNMMBO> select empno,ename from emp where rowid in ('AAATTBAALAAAAuUAAA','AAATTBAALAAAAuUAAD');
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1106538681
- ------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 22 | 1 (0)| 00:00:01 |
- | 1 | INLIST ITERATOR | | | | | |
- | 2 | TABLE ACCESS BY USER ROWID| EMP | 1 | 22 | 1 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 2 consistent gets
- 0 physical reads
- --上面的执行计划中出现了INLIST ITERATOR,即INLIST迭代,该操作说明其子操作多次重复时,会出现该操作。
- --由于我们使用了in运算,且传递了2个rowid,故出现INLIST迭代操作
- --迭代操作意味着条件中的对象列表一个接一个的迭代传递给子操作
- --此时统计信息中的consistent gets为2,因为一个rowid一次逻辑运算
- --下面来看看直接使用列来访问表的情形
- scott@CNMMBO> select empno,ename from emp where empno=7369;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2949544139
- --------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- --------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 10 | 1 (0)| 00:00:01 |
- | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00:00:01 |
- |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - access("EMPNO"=7369)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 2 consistent gets
- 0 physical reads
- --在通过谓词访问表时,此执行计划中使用了INDEX UNIQUE SCAN扫描方式
- --此时先实施INDEX UNIQUE SCAN,然后将该操作的结果传递给父操作TABLE ACCESS BY INDEX ROWID来定位记录
- --此时也给出了谓词信息:2 - access("EMPNO"=7369)
- --此时的逻辑读consistent gets的值是2,即一次读索引,一次通过rowid读表上的数据块
- --下面来看看直接使用列且使用in访问多条记录的情形
- scott@CNMMBO> select empno,ename from emp where empno in (7369,7566);
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 1899965127
- ---------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ---------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 2 | 20 | 2 (0)| 00:00:01 |
- | 1 | INLIST ITERATOR | | | | | |
- | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 20 | 2 (0)| 00:00:01 |
- |* 3 | INDEX RANGE SCAN | PK_EMP | 2 | | 1 (0)| 00:00:01 |
- ---------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access("EMPNO"=7369 OR "EMPNO"=7566)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 5 consistent gets
- 0 physical reads
- --此时执行计划中的索引扫描方式变成了索引范围扫描INDEX RANGE SCAN
- --由于存在in操作,因此此时在第2步也出现了迭代操作
- --此处的逻辑读consistent gets的值是5 ? 为什么是 5 呢? 下面来分析看看
- scott@CNMMBO> alter system flush shared_pool;
- scott@CNMMBO> alter system flush buffer_cache;
- scott@CNMMBO> set serveroutput off;
- scott@CNMMBO> set autot off;
- scott@CNMMBO> select /*+ gather_plan_statistics */ empno,ename from emp where empno in (7369,7566);
- EMPNO ENAME
- ---------- ----------
- 7369 SMITH
- 7566 JONES
- scott@CNMMBO> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
- PLAN_TABLE_OUTPUT
- ----------------------------------------------------------------------------
- SQL_ID 373xnw8s521t4, child number 0
- -------------------------------------
- select /*+ gather_plan_statistics */ empno,ename from emp where empno in (7369,7566)
- Plan hash value: 1899965127
- ----------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
- ----------------------------------------------------------------------------------------------------------
- | 1 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 5 | 2 |
- | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 2 | 2 |00:00:00.01 | 5 | 2 |
- |* 3 | INDEX RANGE SCAN | PK_EMP | 2 | 2 | 2 |00:00:00.01 | 3 | 1 |
- ----------------------------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 3 - access(("EMPNO"=7369 OR "EMPNO"=7566))
- --从上面的执行计划可知,2次索引扫描,2次rowid扫描,加上1次迭代,因此总的consistent gets为5。
- --与此类似的使用基于rowid的update操作,其性能同样高于直接使用列来实现update操作,此处不再演示
- --使用rowid删除重复记录的示例
- DELETE FROM emp e
- WHERE e.ROWID > (SELECT MIN( x.ROWID )
- FROM emp x
- WHERE x.empno = e.empno);
- --Author: Robinson Cheng
- --Blog : http://blog.csdn.net/robinson_0612
- --总结:
- 使用rowid访问数据时可以减少逻辑读的数量,因为一个rowid能唯一定位一条记录
- 尽管rowid能极大程度的提高数据的访问效率,然而由于其不易识别性(为十六进制)在大量数据访问时并不易于使用
更多参考
dbms_xplan之display_cursor函数的使用
小小菜鸟一枚