Re: is this a bug, or a tkprof misunderatnding?

Doug Cha <dcha_at_shell2.ba.best.com> wrote:
>I have been doing some tuning and am somewhat surprised by
>the results of a ceratin set of tkprof reports. There
>are indexes on certain tables of x size (say 150k rows)
>and index range scans of it return something like 36million
>in the tkprof rows column. Am I reading this wrong?
>

 [ SQL, plan and statistic omitted ]

No, you're reading it correctly. There is no join condition between PSTREESELECT05 (L) and PSTREESELECT08 (L1), so a nested loop operation is occurring where for each of the 4124 rows returned by the index PS_PSTREESELECT05 on PSTREESELECT05, the inner loop returns all the rows returned by the condition L1.SELECTOR_NUM = 4625 ( I'm guessing that the index PSBPSTREESELECT08 is the index on that column ). I suspect there's about 7500 of them ( 3 million / 4000 ).

You might have problems optimising this one. Ideally, you want to drive the query from the conditions defined on PS_A_LED_RPTG_VW, however that's a view and the optimiser tends ( sometimes for very good reasons ) to join to views last and use them as filtering conditions. You might get some mileage by rewriting the query to avoid using the view.

Dave.
--
Remove the no-spam bit from my email address to reply. Received on Tue Oct 27 1998 - 08:43:50 CST