Skip to Main Content
  • Questions
  • "Table access by index rowid" is slower than "table access by user rowid"

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Sreenivasa Rao.

Asked: July 18, 2018 - 2:48 pm UTC

Last updated: July 20, 2018 - 10:22 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

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

and Chris said...

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

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.