ITPub博客

首页 > 数据库 > 数据库开发技术 > rbo下通过rowid单行访问表的2种情况!

rbo下通过rowid单行访问表的2种情况!

原创 数据库开发技术 作者:warehouse 时间:2008-05-01 22:53:51 0 删除 编辑

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.

[@more@]

验证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来访问表的

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1003214/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5114460