Re: 0,08 secs + 0,12 secs = 2 minutes when joined

From: joel garry <>
Date: Mon, 27 Apr 2009 14:35:29 -0700 (PDT)
Message-ID: <>

On Apr 27, 9:29 am, Sébastien de Mapias <> wrote:
> Hi,
> I have a query that looks like:
> select ...
> from tab1,
>       tab2,
>       tab3,
>       tab4,
>       view
> where <filter on tab1 that reduces result set to 1 row>
>   <+ many other clauses...>
>   and view.xx = tab1.xx
>   and view.yy = tab2.yy
>   and view.zz = tab2.zz
> If I remove (comment out) the view and the where clauses lines
> related to it, the response time is immediate; if I run the
> select on the view alone, with "view.xx = value", "view.yy = value"...
> as returned by the previous statement, the response time is
> also immediate.
> => I'd like the optimizer to see that computing my first result
> set, and then joining the view on these xx/yy/zz resulting from
> my "first" statement should be optimal, but the whole query
> together takes more than 2 minutes to complete... I've tried to
> inline the view, and used many hints, trying to obtain the same
> plan as when the queries are run individually, without success.
> Should I post everything in details in order to be more explicit
> or can you tell me already there are ways I should explore ?
> Thanks a lot.
> Sébastien

I believe the concept you want to google on is "Predicate pushing." That should display a Jonathan Lewis link that shows what kind of stuff we'd want to see to help you, and an informative asktom oramag link.

In your situation, I expect you'll find what you were trying to do with the inline view is related to this - Mark's suggestion may lead to that.

Here's a more general mini-faq on what to post here, but you've made a good start telling us what you've tried:

Always remember to post detailed version, platform and option information, sometimes this kind of question will lead directly to an "oh, that patchset has a bug with such-and-such in partitioning."


-- is bogus.
"It's a matter of some surprise to me that the idea never occurred to
any of the great philosophers or mathematicians." - Richard Dawkins
Wouldn't want to shatter any cosmic spheres, I suppose.
Received on Mon Apr 27 2009 - 16:35:29 CDT

Original text of this message