Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Car.

Asked: March 20, 2007 - 10:26 am UTC

Last updated: March 20, 2007 - 10:49 am UTC

Version: 10.2

Viewed 1000+ times

You Asked

Tom, I curious to know, may be this is stupid question. Why SQL that can be done with simple SQL (join, subquery) version is faster compared with the same SQL using procedure call (ie to replace subquery). Please explain me the technical stuff behind that.

Thanks.

and Tom said...

in general, the more you let the database do, the less the database HAS to do.

Let us say you want to join table T1 with 100,000 records to table T2 with 500,000 records (each row in T1 joins to about 5 rows in T2)

suppose you do this in code:

for x in ( select * from T1 )
loop
   for y in ( select * from T2 where T2.key = x.key )
   loop
      ....
   end loop;
end loop;


so, that is 100,000 one row fetches from T1. And each time we do that, we run a query against T2 - we run that query 100,000 times. Every time we run it - it takes time to just set up and execute the query. And even worse - the optimizer would likely use an index on T2.KEY (finding 5 rows out of 500,000). So, we would do 100,000 index range scans.....


As opposed to:

select * from t1, t2 where t1.key = t2.key


Now, indexes - they will be smartly ignored - two nice full scans - a hash join, very efficient.

If we did it procedurally - the most efficient plan would not be available to the optimizer.


Think of it this way. Your boss could micromanage you and tell you bit by bit what to do. And a lot of it would seem repetitive to you. By the time you were done - you would see that there was a really efficient way to accomplish what you just did in a fraction of the time - HAD YOU BEEN TOLD what the goal was, not how to do it.

The database works in the same way - tell it the goal - not how to do it and let it decide how best to attack the problem.

Rating

  (4 ratings)

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

Comments

Excellent

Car Elcaro, March 20, 2007 - 11:10 am UTC

Clear explanation as always. Many many thanks.
Regard, Car.

Bravo!

Joe, March 20, 2007 - 11:19 am UTC

Great explanation!!

GOOD EXPLANATION

DHARANIDHAR, March 20, 2007 - 3:37 pm UTC


CLEAR EXPLANATION

rao, March 21, 2007 - 12:32 pm UTC

Nice explanation for understanding

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library