How to force subquery being the first executed

Hi %,

I've a question about 'leading' the optimizer. To make it not too simple, it should work from 9.2.0.4 up to 11.1.0.6.

I'm selecting from a heavy view (many joins, big result set), using a subquery to restrict the result:

   select *
     from heavyView
    where keys in (select keys from simpleView);

In most cases the query starts with complete execution of the heavyView and is then restricting the result using the subquery. This results in a very bad performance.

However, from the application logic I know, that the subquery returns only a few rows.
Therefore I want the query to start with the subquery, and then going with the result rows into the main view. Indexes to support accessing the main view in this way are available.

How can I enforce this execution order?

Sadly, changing the subquery into a join and using a 'leading' or 'ordered' hint is not possible.

Regards
Peter Received on Wed Aug 27 2008 - 07:51:38 CDT