Re: How to force subquery being the first executed

On Aug 27, 8:51 am, Peter Kallweit <p_kallw..._at_arcor.de> wrote:
> 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

Without the actual explain plan or any supporting detail for the tables/indexes available there is no solid reliable way to answer your question. The CBO is going to solve the query based on the statistics for the referenced objects and the plan options available to it based on how the query is written.

Run an explain plan and then look in your SQL manual to see if any of the following hints might be of use in obtaining the plan you want:

NO_MERGE causes Oracle not to merge mergeable views. PUSH_SUBQ causes non-merged subqueries to be evaluated at the earliest possible step in the execution plan

But what you can do depends on how the SQL is written and what the statistics tell the CBO. Rewriting the query so that the view code and the query are one statement might provide more options.

HTH -- Mark D Powell -- Received on Wed Aug 27 2008 - 08:35:54 CDT