Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Doing battle with the CBO in 9i (9.2.0.6)
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
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_at_il.proquest.com www.proquest.com www.csa.com ProQuest...Start here. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jul 10 2007 - 17:17:48 CDT
![]() |
![]() |