Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL faster than SQL!

andyschwarzJul 2 2015 — edited Jul 6 2015

Hi Folks

I really try to avoid complex code, and I perfer SQL to PL/SQL whenever possible. Normally this not not just easier, but also much faster.

I now have an issue where this is not the case.
When I UPDATE data in a table with a simple UPDATE ... WHERE command, it takes twice as much time as a PL/SQL update over the ROWID.

I get the exact timing when I select FOR UPDATE.


Even just identifying 1 single row, a SELECT for UPDATE takes twice the time than a simple SELECT. (FULL table scan)

So I belief the overhead is due to the LOCK ORACLE put's on every row touched.
Am I right on this? Can someone point me to the documentation on this? Is there an ORACLE statistic where I can see the plus in ressource consumption?

See my example below. This creates a table with 100'000'000 rows with no index. Any index would reduce the number of rows "touched" and make the test less obvious.

Timing will differ on every system but the effect should be alike.
I'm using ORACLE 11.2.0.4.5 on Solaris 10 x64

Thanks for feedback.
Andy

Example:

create table join1
(my_pk number,
key_1  number,
key_2  number,
key_3  number,
key_4  number,
key_5  number,
key_6  number,
key_7  number,
key_8  number,
key_9  number,
key_10  number,
data    varchar2(1000));


insert into join1 select * from (
WITH data1 AS
(SELECT level key_1, level + 23351 key_2, level + 56512 key_3, level + 21 key_4, level + 99433 key_5
           FROM DUAL
     CONNECT BY LEVEL <= 10000)
select rownum my_pk,
        a.key_1, a.key_2,  a.key_3, a.key_4, a.key_5, b.key_1 key_6, b.key_2 key_7,  b.key_3 key_8, b.key_4 key_9, b.key_5 key_10
       ,'Just to fill data in .... ' datax
from data1 A
cross join data1 B
);

commit;

begin
DBMS_STATS.GATHER_TABLE_STATS (
   ownname          => user,
   tabname          => 'JOIN1',
   estimate_percent => 10,
   block_sample     => TRUE,
   degree           => 12,  
   cascade          => TRUE);
end;
/

Tests:

select * from join1 where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352;
-- 7 sec

select * from join1 where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352
for update;
-- 13sec

update join1
set data = data||'#'
where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352;
rollback;
-- 13sec

update join1
set data = data||'#'
where rowid in (select rowid from join1 where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352);
rollback;
-- 13sec


DECLARE
  TYPE rowid_type              IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
  tab_rowid                    rowid_type;
  lv_rows_updated              number :=0;

  CURSOR my_cursor IS
    SELECT rowid rid FROM join1 where key_1=500 and key_2=23851 and key_3=57012 and key_4=521 and key_6=1 and key_7=23352;

BEGIN
  OPEN my_cursor;

  FETCH my_cursor
        BULK COLLECT INTO tab_rowid
        LIMIT 10000;

  FORALL lv_row IN tab_rowid.FIRST .. tab_rowid.LAST
         update join1 set data = data||'#' where  rowid = tab_rowid(lv_row);

      lv_rows_updated := SQL%ROWCOUNT;
   ROLLBACK;

  CLOSE my_cursor;
END;
/

-- 7sec

This post has been answered by Jonathan Lewis on Jul 3 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 3 2015
Added on Jul 2 2015
36 comments
8,678 views