Re: Does Oracle create temporary indexes for sub queries?

Dean said:

>If A or B are not straight-up tables with indexes, but they are
>subqueries for which no index exists, is there any cost advantage to
>performing an order-by clause on the select statements at the end of A
>and B? I guess I am assuming that if I do the order-by, then there will
>be a temp index avaiable, if I do order-by A.F1, A.F2 or B.F1, B.F2.

>There - clear as mud!

As I understand it, if the tables are different sizes the CBO will build an in-memory hash table of the smaller table, then scan the larger table and probe the hash. If the hash table doesn't fit in memory, things will slow down as it gets dumped to a temporary segment.  My unscientific wild guess is that any subquery that would hash would benefit from being told to sort, so it would do a sort merge instead, then not have to sort again to join the subqueries. But the CBO is an amazing and subtle thing... and in many cases, adding a real index would be slower than just scanning a table.

Of course, I'm talking out of my butt under the mud, so as Daniel implied, you should try it for your case and see what the plan says. Then don't believe the plan and run a trace.

jg

--
@home.com is bogus.
Doing what to who?  http://en.wikipedia.org/wiki/Brix