Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Car.

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

Answered by: Tom Kyte - Last updated: March 20, 2007 - 10:49 am UTC

Category: Database - Version: 10.2

Viewed 100+ 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 we 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.

and you rated our response

  (4 ratings)

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

Reviews

Excellent

March 20, 2007 - 11:10 am UTC

Reviewer: Car Elcaro

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

Bravo!

March 20, 2007 - 11:19 am UTC

Reviewer: Joe from Devon, PA

Great explanation!!

GOOD EXPLANATION

March 20, 2007 - 3:37 pm UTC

Reviewer: DHARANIDHAR from VIZAG


CLEAR EXPLANATION

March 21, 2007 - 12:32 pm UTC

Reviewer: rao from USA

Nice explanation for understanding