How to force subquery being the first executed

External Since: Aug 27, 2008 Posts: 1

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

External Since: Dec 20, 2007 Posts: 289

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