Re: performance of joins

"derek" <dereksmi_at_gmail.com> wrote in message news:1117835109.852799.120550_at_z14g2000cwz.googlegroups.com...
>
> 3) I am pasting the results with the autotrace on at the end of this
> posting. I hope that you would be able to help me out with
> understanding the trace. I will be more than glad to send you the
> actual trace file if you require it to analyse more deeply. One
> surprising thing I noticed in the output of the auto-trace is "sorts"
> being used in the nested indexed join. What does that mean? Also in
> sort merge join number of sorts is 4 -- does it mean that the sorted
> tables are trashed due to lack of memory?
>
> 4) For the comment 4, yeah I think Oracle probably doesnot care abt
> optimising some unusual queries and ths might explain the results. I am
> attaching th autotrace summaries first followed by summaries from the
> trace file of tkprof.
>
>
> best regards,
> Derek Smith.
>
>

You seem to have missed the reply I sent to you under the "Non equi-join" thread.

Item 3: - with autotrace, you are going to see some statistics relating to recursive activity for parsing the statements. If you ran the statements twice and checked the stats on the second pass, the "sorts" value from autotrace would make more sense,
i.e. 0 for the NL, 2 for the SM.

Item 4: - Oracle is unable to 'not care', the code has to run. It has tried "very hard" to optimize the query. Think carefully about what your query actually has to do in the absence of an index:

    for each row in first table,

        compare with every row in second table.

Of course, with your example, the SM allows the average number of comparisons to be about half because Oracle can find the right starting point in the (sorted) second data set.

The query takes a lot of CPU.

In fact, the NL and SM joins are doing pretty much the same thing - but EITHER option could have been the more CPU intensive for a particularly odd data set. Oracle has a few generic routines - they are bound to be sub-optimal at times.

Equating logical I/O to CPU is often a fair indicator, but not when Oracle doesn't use logical I/O to acquire the data. In the SM join, Oracle has copied the data into local memory - but it's pretty much the same data (in the right order) probably using about the same amount of memory.

The amount of work is about the same, regardless of where the data comes from - in the SM it comes from local memory anmd chasing pointers, in the NL it comes from visits to buffered blocks and walking row lists.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005