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

Sébastien de Mapias

unread,

Apr 27, 2009, 12:29:56 PM4/27/09

to

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

Mark D Powell

unread,

Apr 27, 2009, 12:47:53 PM4/27/09

to

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 --

Michael Austin

unread,

Apr 27, 2009, 1:49:20 PM4/27/09

to

> S�bastien
>

Make sure that any columns in the WHERE clause or JOIN ON clause are
properly indexed.

Michael Austin

unread,

Apr 27, 2009, 2:31:56 PM4/27/09

to

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..

joel garry

unread,

Apr 27, 2009, 5:35:29 PM4/27/09

to

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: http://dbaoracle.net/readme-cdos.htm

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."

jg
--
@home.com 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
http://www3.signonsandiego.com/stories/2009/apr/20/1c20dawkins202019/
Wouldn't want to shatter any cosmic spheres, I suppose.

Jonathan Lewis

unread,

Apr 30, 2009, 2:11:30 AM4/30/09

to

"S�bastien de Mapias" <sglr...@gmail.com> wrote in message
news:70e70aab-f650-4c41...@w31g2000prd.googlegroups.com...

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.

================================

You've done the right analysis, and just need to find the set
of hints that enforces your strategy. I've made some comments
and a suggestion at the following URL:

http://jonathanlewis.wordpress.com/2009/04/28/strategic-hints/

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html