Re: Why the monstrous SORT?

Brandon,

>From the responses you probably already know that the number of rows
sorted comes from number of times the view is used multiplied by number of rows in the view.

If you still struggling with this query I have couple words to say. If not can you please share how did you resolve the issue.

Let me try to help.

The situation looks like the CBO miscalculated the cost of using nested loops over the in-line view. In spite the fact that it needs to access AR_DOC_LINE using full scan and resort the results many times. If 10053 trace is available verify why CBO preferred nested loops over hash join. Run explain plan. Does it properly estimate cardinality of AR_DOC_LINE and selectivity of OE_CONTROL. Verify what is estimated cardinality of the in-line view.

If you want, I'd like to take a look at the 10053 trace and/or explain plan.

Otherwise, here are my suggestions in the order of the level of effectiveness.

1. In 9i and up you can materialize the view results using "with" clause, that way execution should not be needing to resort the results and do a full scan over and over again.
2. Try to force hash join or at least merge join on this view.
3. Hint CBO to put AR_DOC_LINE first in the join order with LEADING hint inside the in-line view. That should decrease number of scans to one for each time view is accessed.
4. In 9i putting AR_DOC_LINE table in keep cache might help.
5. Check if you can move OE_CONTROL out of the in-line view or rewrite the query that won't need in-line view.
   • Vladimir

On 7/7/05, Allen, Brandon <Brandon.Allen_at_oneneck.com> wrote:
> Steve, yes you're right about adding the != and dropping the whole MINUS clause. That cuts the query and execution plan in half, but unfortunately it doesn't cut out that much work because most of the work (the huge SORT) was being done in the top half, so I still have to find a way to minimize that huge SORT operation. It does cut out a significant amount of work though so I will forward the recommendation to the developers.
>
> Thanks!
>
>
>

--
http://www.freelists.org/webpage/oracle-l