Re: How to force subquery being the first executed

On Wed, 27 Aug 2008 14:51:38 +0200, Peter Kallweit <p_kallweit_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

My bet is you are not posting a simplified version. You are also not posting the execution plan (unless some want people to believe there are few 'silver bullets' or generic ways to 'force' something, especially as you seem to imply the query can not be changed). This is all not very helpful and means it is crystal ball time again.

First of all, Tom Kyte demonstrates there can be a tremendous negative impact when the column involved in the subquery is both indexed and NULL *allowed*.
As the optimizer thinks the result of a query can be NULL, it won't use the index, even if NULL columns do not exist. As it is currently customary to allow NULL on *all* columns except for the primary key, this might be your issue.

Secondly there is a PUSHQ hint, which exactly does what you want: execute the subquery first. As this would involve modifying the query, probably you can't use it.

Enters: using stored outlines. However this won't work if the query contains hardcoded literals and those literals vary. Which it is important to post the exact query.

My 2 euro cents.

Let Barry Bulsara flame away at this response!!!!!!

-- 
Sybrand Bakker
Senior Oracle DBA