发表于: 2008.05.01 22:53
分类: 性能优化
出处: http://warehouse.itpub.net/post/777/460919
---------------------------------------------------------------
RBO Path 1: Single Row by Rowid
This access path is available only if the statement's WHERE clause identifies the selected rows by rowid or with the CURRENT OF CURSOR embedded SQL syntax supported by the Oracle precompilers. To execute the statement, Oracle accesses the table by rowid.
验证1：
This access path is available only if the statement's WHERE clause identifies the selected rows by rowid
SQL> select tt.* , rowid from tt;
ID NA ROWID
---------- -- ------------------
1 a AAAEG4AAEAAAAAMAAA
2 b AAAEG4AAEAAAAAMAAB
3 c AAAEG4AAEAAAAAMAAC
4 d AAAEG4AAEAAAAAMAAD
5 e AAAEG4AAEAAAAAMAAE
6 f AAAEG4AAEAAAAAQAAA
1000 h AAAEG4AAEAAAAAQAAB
已选择7行。
SQL> set autotrace on
SQL> select * from tt where rowid='AAAEG4AAEAAAAAMAAC';
ID NA
---------- --
3 c
执行计划
----------------------------------------------------------
Plan hash value: 3020126379
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 1 | 6 | 1 (0)| 00:00:0
1 |
| 1 | TABLE ACCESS BY USER ROWID| TT | 1 | 6 | 1 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
---
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
470 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)
1 rows processed
SQL> select /*+ rule */ * from tt where rowid='AAAEG4AAEAAAAAMAAC';
ID NA
---------- --
3 c
执行计划
----------------------------------------------------------
Plan hash value: 3020126379
-------------------------------------------
| Id | Operation | Name |
-------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY USER ROWID| TT |
-------------------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
470 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)
1 rows processed
验证2：
with the CURRENT OF CURSOR embedded SQL syntax supported by the Oracle precompilers. To execute the statement, Oracle accesses the table by rowid.
SQL> declare
2 cursor c1 is select *from tt for update;
3 v_tt tt%rowtype;
4 begin
5 for i in c1 loop
6 update tt set name=name||'test' where current of c1;
7 end loop;
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> SET AUTOTRACE OFF
SQL> SELECT SQL_TEXT FROM V$SQL WHERE SQL_ID IN (
2 select SQL_ID from v$sql_plan where options like '%ROWID%'
3 AND OBJECT_OWNER='XYS');
SQL_TEXT
--------------------------------------------------------------------------------
UPDATE TT SET NAME=NAME||'test' WHERE ROWID = :B1
select * from tt where rowid='AAAEG4AAEAAAAAMAAC'
select /*+ rule */ * from tt where rowid='AAAEG4AAEAAAAAMAAC'
SQL>
通过试验发现（上面的查询结果UPDATE TT SET NAME=NAME||'test' WHERE ROWID = :B1）正如doc所言潜入current of cursor子句的sql oracle在处理时是通过rowid来访问表的
