Re: PeopleSoft query

    I understand queries better than execution plans :-). If your query is massive, and it looks like it is, then scanning tables, however big they are, makes sense (if you want to ensure scalability, then you should partition them, but not try to use indexes). But when you scan tables, then accessing them AGAIN through an index kills you, especially if you do it multiple times, because in fact the indexed access has no added value since it takes you to rows you have already visited by scanning. I hope it makes sense? Therefore the sound approach is to take a brutal one, scan everything remorselessly, and while doing so use an analytical function (alright, cost isn't 0, it has to sort in the background but by and large it does it efficiently and here indexes can be used) so that you can wrap the result in a query and by testing the result of the analytical function know what you keep and what you discard. Subqueries that mutually depend on the result of each other as you have here mean slow death, as you can see.

SF

Karl Arao wrote:
> For additional info, this is the explain plan from DBMS_XPLAN.DISPLAY_CURSOR
>
>
> -- IF THE SQL GOES HASH JOIN THE RUNTIME FOR THIS SQL IS ESTIMATED 18HOURS
>
>

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