RE: Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??

  • From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
  • To: 'Wolfgang Breitling' <breitliw@xxxxxxxxxxxxx>
  • Date: Fri, 5 Aug 2011 15:12:59 -0500

Odd it is ENABLED (_gby_hash_aggregation_enabled = TRUE) and 
optimizer_features_enabled=10.2.0.4 _THOUGH_ I thought I had disabled this due 
to bug 4604970 Wrong Results with 'HASH GROUP BY AGGREGATION ENABLED'.

(Oh, I see now - it was fixed in 10.2.0.3)

I did have it set at one point.

It is curious that it doesn't reflect a HASH GROUP BY I think...

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor@xxxxxxxxxxxxxxx<mailto:chris.taylor@xxxxxxxxxxxxxxx>

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.

From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx]
Sent: Friday, August 05, 2011 2:53 PM
To: Taylor, Chris David
Cc: 'oracle-l@xxxxxxxxxxxxx'
Subject: Re: Wow - has anyone used ROW_NUMBER() to get around using a UNION 
statement when UNION ALL doesn't work??

Do you by chance have hash_group_by disabled - because of legacy code which 
expects group by to render rows in order without an explicit order by ?

In case the answer is yes, how does the union perform if you enable it for this 
session / sql ?

On 2011-08-05, at 1:30 PM, Taylor, Chris David wrote:


My bad...appears it is a SORT UNIQUE.

Some info for the UNION statement for those interested.


Other related posts: