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@xxxxxxxxxxx>
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

Other related posts: