Oracle “索引rowid的表访问” 比 “用户rowid的表访问” 慢

嗨，汤姆，
匿名过程有一个类型的表rowid在一个列十亿 + 记录表，我打开一个光标选择rownum小于200000的表的rowid，然后通过限制每50k记录从表中获取一个光标批量收集。此外，我更新了大表上的两列，其光标rowid与表的实际rowid匹配。这里的更新不选择并行奴隶。所以我想提高过程的性能，所以修改类型使用唯一列而不是rowid，然后在where子句条件中更新具有唯一列匹配的表，而不是使用rowid。在这种情况下，查询将生成 “索引rowid的表访问”，而不是 “用户rowid的表访问”，从而进一步降低了性能。你能提出一种改善这种情况的方法吗？

--with ROWID
type a_iface is records( t_ROWID ROWID);
type t_iface is table of a_iface index by integer;
r_iface t_face;
cursor c1 is select rowid from big_table where rownum<200000;
begin

loop 
fetch c1 bulk collecct into r_iface limit 50000;
exit when r_iface.count=0;

forall i in 1..r_iface.count
update /*+PARALLLE(16) BIG_TABLE
SET COLA=VALUE1
, COLB=VALUE2
WHERE ROWID= r_iface(i).t_rowid;

...
复制

在进一步的尝试中，我使用了唯一的列索引。更新查询使用 “按索引rowid进行表访问”，但运行速度比上述ROWID过程慢。

--unique column
type r_iface is record(bigtable.col4%type default);
type t_iface is table of a_iface index by integer;
r_iface t_face;
cursor c1 is select col4 from big_table where rownum<200000;
begin

loop 

fetch c1 bulk collect into r_iface limit 50000;
exit when r_iface.count=0;

forall i in 1..r_iface.count
update /*+PARALLLE(16) BIG_TABLE
SET COLA=VALUE1
, COLB=VALUE2
WHERE col4= r_iface(i).col3;
复制

请建议提高表更新性能的方法。

问候，
Sreenivas

这是因为通过rowid访问一行比通过索引做更少的工作!

使用索引查找行时，数据库:

-在索引中搜索值
-查找匹配的相应行的rowid
-使用此rowid访问表

而使用rowid:

-直接进入有问题的行

作为一般原则，少做工作 => 更快。

下面的示例比较了PK查找与rowid。

请注意，使用主键:

-在计划中有一个额外的步骤 (索引唯一扫描)
-做更多的工作 (3个缓冲区而不是1个)

create table t ( pk primary key , stuff ) as 
  select level pk, lpad('x', 50, 'x') stuff from dual
  connect by level <= 1000;
  
set serveroutput off
alter session set statistics_level = all;

select rowid, t.* from t
where  pk = 1;

ROWID                PK   STUFF                                                
AAAbnqAAFAAAAbkAAA      1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                                 
SQL_ID  5r48nkxxtbyx0, child number 0                                                                             
-------------------------------------                                                                             
select rowid, t.* from t where  pk = 1                                                                            
                                                                                                                  
Plan hash value: 2701941032                                                                                       
                                                                                                                  
---------------------------------------------------------------------------------------------------------------   
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |   
---------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT            |              |      1 |        |      1 |00:00:00.01 |       3 |      1 |   
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |   
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0029813 |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |   
---------------------------------------------------------------------------------------------------------------   
                                                                                                                  
Predicate Information (identified by operation id):                                                               
---------------------------------------------------                                                               
                                                                                                                  
   2 - access("PK"=1)

select rowid, t.* from t
where  rowid = 'AAAbnqAAFAAAAbkAAA';

ROWID                PK   STUFF                                                
AAAbnqAAFAAAAbkAAA      1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                               
SQL_ID  6wr6jwyc15vfq, child number 0                                                           
-------------------------------------                                                           
select rowid, t.* from t where  rowid = 'AAAbnqAAFAAAAbkAAA'                                    
                                                                                                
Plan hash value: 921006707                                                                      
                                                                                                
---------------------------------------------------------------------------------------------   
| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
---------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT           |      |      1 |        |      1 |00:00:00.01 |       1 |   
|   1 |  TABLE ACCESS BY USER ROWID| T    |      1 |      1 |      1 |00:00:00.01 |       1 |   
---------------------------------------------------------------------------------------------
复制

所以你应该通过rowid进入，对吗？

不!

Rowwids可以改变，正如Tom在本文中讨论的那样:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=is-it-safe-to-use-rowid-to-locate-a-row

也就是说，在更新性能方面，有更好的技术。通常最快的方法是使用 “创建表作为选择” 重新创建表，并在那里进行 “更新”。或者你可以去DIY并行处理。

有关更多详细信息，请阅读以下文章:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:6407993912330
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542