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