Re: 10gR2 Upgrade .. Watch out

I see from the bug number that one of the suggested workarounds is to set the optimizer_features_enable to a 10.1 level

Since the problem relates to "order by elimination", an alternative is to disable it for the query, for example (using the test case in the quoted bug):

select

     /*+ qb_name(main) no_eliminate_oby(@main) */
     col1,
     sum(col2) tot
from     order_test
where     col2 = '1'
group by     col1
order by     2 desc

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

> ------------------------------
>
> From: "GovindanK" <gkatteri_at_fastmail.fm>
> Subject: 10gR2 Upgrade .. Watch out
> Date: Wed, 27 Dec 2006 14:00:49 -0800

> 3. And Recently
> SELECT name, SUM(basic) total FROM TEST WHERE basic=20 GROUP BY name ORDER BY
> 2 DESC did not work;
> If you use SELECT name, SUM(basic) total FROM TEST WHERE basic BETWEEN 20 AND
> 20 GROUP BY name ORDER BY 2 DESC then it works!!!
> Patch number is 5415881.

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