Re: Optimizing SQL to Reduce Table Scans

ct witter wrote on 04.01.2006 18:28:
> I am using 2 tables: an Order Header and Order Detail. The problem that
> I keep running into is for a request to pull some criteria based on the
> detail lines, then display all the lines for orders meeting that
> criteria.
>
> So I believe that the SQL is first executing to get all the line(s)
> that meet the criteria. Then it is going back through the table to
> gather the header and all detail lines that meet the previous criteria.
> This results in multiple table scans.
>
> Is this the correct way to do this? Is there a more efficient way?
>

Do not assume what Oracle is doing. Use EXPLAIN PLAN to get the execution plan then optimize it:

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm#PFGRF009

Thomas Received on Wed Jan 04 2006 - 11:41:15 CST