outer join order
We have a query which does something similar to
a.id (+) = b.id
and b.id (+) = c.id
and c.other_id (+) = d.other_id
and expensive_function (arbitrary_parameter, d.column) >= arbitrary_number
It's my understanding that the only valid path given all the outer joins is to access table D first, in this case with a full table scan since there are no other predicates. Unfortunately given this order the query will probably never return since it needs to execute an expensive function on every row in the relativly large table.
Is it possible to somehow change the path to filter D last? If I understand outer join restrictions correctly I don't think so, but I'm a little hazy so I thought I'd ask. So far with a few quick ORDERED tests nothing works.
whatever order the tables are accessed in, the outer joins mean that the function is going to execute for every row in d anyway -- that's what they do of course.
It might be possible to avoid execution of the expensive function for some rows by either ...
i) puttinga function-based index on the d column of ...
create index my_index on d (expensive_function (arbitrary_parameter, d.column))
.. although that might not be a viable alternative.
... or ...
ii) avoiding execution of the function for some rows based on the values of d.column and arbitrary_parameter. For example, if you knew that if in al cases for arbitrary_parameter > d.column, then ...
NOT(expensive_function (arbitrary_parameter, d.column) >= arbitrary_number)
.. you could change the predicate to ...
This would avoid both the expensive_function's execution time and the context switch to PL/SQL for some portion of your rows.
when (arbitrary_parameter > d.column)
expensive_function (arbitrary_parameter, d.column)
end >= arbitrary_number
... or ...
iii) expressing expensive_function as SQL.
Thanks I figured D was stuck at every row without an additional predicate.
I thought about function based indexes but that's not really an option since the inputs are arbitrary. Eg, when the user runs the query, he's going to give arbitrary_parameter and arbitrary_number. Since those values aren't known ahead of time they can't be indexed for.
expensive_function is basically a poor mans version of spatial functions. If spatial were an option this query probably wouldn't be a problem, but that is a whole other issue.
I'll look into the case statement, that may be able to cut down on the executions but I'm not sure how much total benefit there would be.
Thanks for the reply.
Click Here to Expand Forum to Full Width