Hi Tom,
Anonymous procedure having a type for table rowid on one column for a billion plus record table, i open a cursor selecting rowid of the table for rownum less than 200000, then i fetch a cursor bulk collecting from the table by limiting every 50k records. Further i update two columns on the big table having cursor rowid match with actual rowid of the table. Here the update doesn't pick parallel slaves. So i want to improve the performance of the procedure, so modified the type to use unique column instead of rowid, then update the table having unique column match in where clause condition instead of using rowid. In this case query is generating "table access by index rowid" against "table access by user rowid" and further diminished the performance. Could you suggest a way to improve the situation?
--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;
...
In further attempt i used unique column index. The update query uses "table access by index rowid" but it runs slower than above ROWID procedure.
--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;
Kindly suggest ways to improve the performance of the table update.
Regards,
Sreenivas
That's because accessing a row by rowid does less work then via an index!
When looking up a row using an index, the database:
- Searches the index for the value
- Looks up the rowid for the corresponding row(s) that match
- Accesses the table using this rowid
Whereas using rowid:
- Goes directly to the row in question
As a general principle, doing less work => faster.
The example below compares a PK lookup vs rowid.
Notice that using the primary key:
- Has an extra step in the plan (index unique scan)
- Does more work (3 buffers instead of 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 |
---------------------------------------------------------------------------------------------
So you should access by rowid then, right?
Nooooooooo!
Rowids can change, as Tom discusses in this article:
https://asktom.oracle.com/pls/asktom/asktom.search?tag=is-it-safe-to-use-rowid-to-locate-a-row That said, when it comes to update performance there are better techniques. Often the fastest is to re-create the table using "create table as select", doing the "update" there. Or you could go for DIY parallel processing.
Read these articles for more details:
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