Re: 0,08 secs + 0,12 secs = 2 minutes when joined
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 27 Apr 2009 09:47:53 -0700 (PDT)
Message-ID: <2e61bd62-6439-4e7c-9b03-d3d0ac82ca18_at_u39g2000pru.googlegroups.com>
On Apr 27, 12:29 pm, Sébastien de Mapias <sglrig..._at_gmail.com> 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
Date: Mon, 27 Apr 2009 09:47:53 -0700 (PDT)
Message-ID: <2e61bd62-6439-4e7c-9b03-d3d0ac82ca18_at_u39g2000pru.googlegroups.com>
On Apr 27, 12:29 pm, Sébastien de Mapias <sglrig..._at_gmail.com> 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
If the filter condition on tab1 is indexed then I would try converting tab1 an inline view that applied the filter condition on tab1 and see what the plan changes to.
HTH -- Mark D Powell -- Received on Mon Apr 27 2009 - 11:47:53 CDT