RE: Doing battle with the CBO in 9i (9.2.0.6)
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jul 2007 19:25:37 -0400
Jonathan,
I tried something like:
select ... from table where :x = 'xxx'
union all
select ... from table where not :x='xxx' and fk_col_id = :y;
And that gave me a similar plan, but I was not allowed to assign that plan,
using outlines. The assignment apparently worked, as changes were reflected in
ol$ and ol$hints, but the plan was ignored.
I'll try yours tomorrow, but if it results in the same plan, and I suspect it
will, I think the result will be the same.
-Mark
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx]
Sent: Tue 7/10/2007 6:17 PM
To: Bobak, Mark; oracle-l
Subject: 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
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>; "oracle-l"
<oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxx
www.proquest.com
www.csa.com
ProQuest...Start here.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
