Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL sometimes performs faster than a single SQL (Oracle 11g, RHEL AS4)
PL/SQL sometimes performs faster than a single SQL [message #434746] Thu, 10 December 2009 08:42 Go to next message
prajjwal
Messages: 28
Registered: January 2008
Location: kolkata
Junior Member

Hi,
It really amazes me when I see PL/SQL is sometimes miles faster than one SQL. Am I messing up somewhere?
I would give an example. I tried this on a production server having very high end configurations. Very imporantly I was very sure about the fact that the server was not overloaded.

I had two tables having structures

CREATE TABLE T1 (
COL1 NUMBER,
COL2 VARCHAR2(30),
.
.
.
); --row count approximately 200 millon, no indexes present still

CREATE TABLE T2 (
ROW_ID ROWID,
.
.
); --row count approximately 20k



--This took about 12 hours to complete
delete from T1 where rowid in (select row_id from T2);


--This took about 5 minutes to complete
begin
for i in (select row_id from t2)
loop
delete from t1 where rowid=i.row_id;
end loop;
end;
/


Regards,
Prajjwal
Re: PL/SQL sometimes performs faster than a single SQL [message #434748 is a reply to message #434746] Thu, 10 December 2009 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you want to know what's going on there you'd need to trace both sessions.

However this test is a bit too artifical for my liking. You should never have a table that stores rowids, for the simple reason that they can change.
icon6.gif  Re: PL/SQL sometimes performs faster than a single SQL [message #434755 is a reply to message #434746] Thu, 10 December 2009 09:28 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member

The SQL statement is doing FULL table scan where the PL/SQL does a direct read on the ROWID.
Razz

[Updated on: Thu, 10 December 2009 09:32]

Report message to a moderator

Re: PL/SQL sometimes performs faster than a single SQL [message #434832 is a reply to message #434746] Fri, 11 December 2009 02:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's something wrong with the execution of your delete statement. Is it possible people were using the table at the point you deleted it?

It's also probable that your pl/sql solution ran quicker because the SQL one had stuck most of the blocks from T2 into the buffer cache already.

Here's an example. I only generate 10,000,000 rows in the main table, and 1000 in the child. The first delete (by sql) took 4,000ms
After rolling back, the pl./sql delete took 63ms - a vast improvement.

After rolling that back, I re-did the sql delete and with blocks cached, it took 32ms - about half the time of the pl/sql one.


set timing off

drop table test_109;
drop table test_110;

CREATE TABLE Test_109 (COL1 NUMBER, COL2 VARCHAR2(30));

--row count approximately 200 millon, no indexes present still

CREATE TABLE Test_110 (ROW_ID ROWID); --row count approximately 20k

insert into test_109 (select level,'Level '||level from dual connect by level <= 10000000);

insert into test_110 select rowid from test_109 where mod(col1,10000)=1;

begin 
  dbms_stats.gather_table_stats(ownname => null
                               ,tabname => 'TEST_109');

  dbms_stats.gather_table_stats(ownname => null
                               ,tabname => 'TEST_110');
end;
/

commit;

set timing on

delete from test_109 where rowid in (select row_id from test_110);

rollback;

--This took about 5 minutes to complete
begin
for i in (select row_id from test_110)
loop
delete from test_109 where rowid=i.row_id;
end loop;
end;
/
rollback;

delete from test_109 where rowid in (select row_id from test_110);

rollback;


Previous Topic: attendence register
Next Topic: How to load data from an excel file to a table
Goto Forum:
  


Current Time: Mon Dec 05 21:22:09 CST 2016

Total time taken to generate the page: 0.07294 seconds