Re: How to force subquery being the first executed

On Aug 27, 2:58 pm, sybra..._at_hccnet.nl wrote:
> On Wed, 27 Aug 2008 14:51:38 +0200, 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
>
> 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- Hide quoted text -
>
> - Show quoted text -

Let's not judge you on just this one helpful response shortly after a posting tantrum where your professionalism was questioned, let's judge you on all the posts to come from now on. Most remaining subscribers to this group would like a lot more professionalism and technical input and a lot lot less ad hominem comment.

Barry Received on Wed Aug 27 2008 - 10:33:23 CDT