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

From: Sébastien de Mapias <sglrigaud_at_gmail.com>
Date: Mon, 27 Apr 2009 09:29:56 -0700 (PDT)
Message-ID: <70e70aab-f650-4c41-b847-c4ff724644d8_at_w31g2000prd.googlegroups.com>

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 Received on Mon Apr 27 2009 - 11:29:56 CDT