RE: Plea for Query Tuning Help
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <charlottejanehammond@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Sep 2006 12:38:23 -0400
Charlotte,
What does the plan show? Can you see if the subquery is being merged?
Did you try w/ the NO_MERGE hint in the subquery?
Like this:
SELECT *
FROM NASTY_VIEW
WHERE ID = (SELECT /*+ NO_MERGE */ id FROM my_table WHERE ROWNUM=1);
-Mark
--
Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning
"A human being should be able to change a diaper, plan an invasion,
butcher a hog, conn a ship, design a building, write a sonnet, balance
accounts, build a wall, set a bone, comfort the dying, take orders, give
orders, cooperate, act alone, solve equations, analyze a new problem,
pitch manure, program a computer, cook a tasty meal, fight efficiently,
die gallantly. Specialization is for insects." --Robert A. Heinlein
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Charlotte Hammond
Sent: Tuesday, September 12, 2006 12:11 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Plea for Query Tuning Help
Hi all,
I have a very complicated view, NASTY_VIEW.
SELECT *
FROM NASTY_VIEW
WHERE ID = :bind
This is fast (< 1 second). CBO cost = 400.
Fantastic.
However if I try
SELECT *
FROM NASTY_VIEW
WHERE ID = (SELECT id FROM my_table WHERE ROWNUM=1)
This takes "forever" (well >> hours). my_table is a simple table and id
is the primary key. CBO cost = 22000. Not fantastic. (SELECT id FROM
my_table WHERE
ROWNUM=1) happens in the blink of an eye if run alone.
The execution plans are very different and it looks as if Oracle has
attempted to merge the subquery into the top-level select. Similar
things happen if I use a join instead of a sub-query.
How can I prevent this happening so that it "looks"
like the first "fantastic" query? I've tried messing with hints
(NO_MERGE and PUSH_SUBQ) but I don't know enough to do so effectively.
Statistics are full and up to date. This is Oracle 9.2.0.6.
Any suggestions would be greatfully received!
Thank you
Charlotte
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
