Re: two different explain plans

In article <388F22EA.76FC8DF4_at_hotmail.com>,   s_c_99_at_hotmail.com wrote:
> I have been asked to identify efficiency
> of a SQL statement. I am trying to change
> few things and see the execution plan.
>
> Well, the good news is that I can successfully
> use sql_trace and tkprof utilities to get an
> output. But the bad news is that I am getting
> confused with how to compare two different
> execution plans of same query?
>
> They still are doing full table scans.
> One uses sorts and hash joins.
> and other uses index joins.
>
> what is the difference between hash join and
> index join? How does sorting make difference?
> How can I interpret and compare two execution plans
> on same query?
>
> Thanks.
>
> S_C_99_at_hotmail.com
>

Since you have a tkprof output look at the IO statistics for the explained SQL. If one of the two runs shows much lower IO then it almost surely ran faster and is the better choice.

As far as sorting goes, sorting is a lot of work and requires additional IO unless the total quanity of data to be sorted will fit in the sort_area_size.

When comparing explain plans for queries that produce the same output you may find the cost and cardinality columns of interest.

You have three ways to do a join in Oracle: Nested loops
Hash
Sort/Merge

Sort/Merge generally reads one table, usually by full table scan, then sorts it; reads the other table then sorts it into the same order; and finally merges the two sort outputs together. Generally a sort/merge is done if no index exists to support the join or the CBO thinks that one time through the table and a sort will involve less IO than retrieving the data via multiple index retrievels to the same block.

Hash joins became available with 7.3 and instead of sorting the data Oracle hashs it into memory. Then when it reads the second table and hashs it. If if finds a row from the first table there you have a match otherwise no match and continue on to next row. Hash joins work best when one table is a fraction of the size of the other and all the join results can be kept in memory. If the result set is too large to keep in memory Oracle starts paging the hash area blocks in and out lowering the efficiency of the join.

The nested loop logic is get a row from first table and then do something to get row from second table, then get the next row from first table and repeat whatever plan that was used to access the second table until all rows in the first table are exhaused.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 27 2000 - 13:10:59 CST