I don't think the use_concat() hint can work in this case.
It looks like you need your code to say something like:
select
*
from
t1
where
n1 = (decode(:b1,'ENTER-QUERY',n1,13))
to get a plan like this. (And you probably need n1 declared not null
to the get answer you expect). This was on 10.2, by the way, not
checked on 9.2
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3015 | 550K| 16 |
| 1 | CONCATENATION | | | | |
|* 2 | FILTER | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 3000 | 547K| 14 |
|* 4 | FILTER | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T1 | 15 | 2805 | 2 |
|* 6 | INDEX RANGE SCAN | T1_I1 | 15 | | 1 |
-----------------------------------------------------------------------
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
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@(protected)>
To: "Jonathan Lewis" <jonathan@(protected)"
<oracle-l@(protected)>
Sent: Tuesday, July 10, 2007 10:35 PM
Subject: RE: Doing battle with the CBO in 9i (9.2.0.6)
Is there a way to force a USE_CONCAT to do the union all? I tried giving it a
USE_CONCAT, but, it had no effect. The 9i manual mentions that it's costed, and
will only do the transformation if the cost is cheaper?
-Mark
--
Mark J. Bobak
Senior Database Administrator, System & Product Technologies
ProQuest
789 E. Eisenhower, Parkway, P.O. Box 1346
Ann Arbor MI 48106-1346
734.997.4059 or 800.521.0600 x 4059
mark.bobak@(protected)
www.proquest.com
www.csa.com
ProQuest...Start here.
--
http://www.freelists.org/webpage/oracle-l
