Re: 0,08 secs + 0,12 secs = 2 minutes when joined
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 27 Apr 2009 13:31:56 -0500
Message-ID: <QYmJl.15610$pr6.10767_at_flpi149.ffdc.sbc.com>
Michael Austin 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 13:31:56 -0500
Message-ID: <QYmJl.15610$pr6.10767_at_flpi149.ffdc.sbc.com>
Michael Austin wrote:
> 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
>>
> > > Make sure that any columns in the WHERE clause or JOIN ON clause are > properly indexed.
can you provide us the EXPLAIN PLAN for the "full" query? This will also help you determine where it is spending most of its time.. Received on Mon Apr 27 2009 - 13:31:56 CDT