RE: Doing battle with the CBO in 9i (9.2.0.6)

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_at_jlcomp.demon.co.uk] 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_at_il.proquest.com> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>; "oracle-l" <oracle-l_at_freelists.org> 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_at_il.proquest.com
www.proquest.com
www.csa.com

ProQuest...Start here.



--
http://www.freelists.org/webpage/oracle-l