-
Oracle 10g R2 data sorting issue
The other day I imported the all of the schemas from our current Oracle 10.1.0.4 instance into a newly-created Oracle 10.2 instance. Now, when I query the new R2 instance, I'm noticing that the sort order for many of the numeric fields has been lost, as compared when querying the same fields in the 10.1.0.4 instance (with the data coming back as correctly sorted).
Have any of you noticed same / similar characteristics with the data in your R2 instances ?
Oracle 10.1.0.4 instance query and output :
SQL> select distinct T1."PROD_LINE" as c2,
2 T1."PROD_COST" as c4
3 from "IMPQC"."PRODUCT" T1
4 where (T1."PROD_LINE" = 'Back Packs');
C2 C4
-------------------- ----------
Back Packs 9
Back Packs 12
Back Packs 16
Back Packs 88
Oracle 10.2 instance query and output :
SQL> select T1."PROD_LINE" as c2,
2 T1."PROD_COST" as c4
3 from "IMPQC"."PRODUCT" T1
4 where (T1."PROD_LINE" = 'Back Packs');
C2 C4
-------------------- ----------
Back Packs 9
Back Packs 88
Back Packs 12
Back Packs 16
Am I missing something here completely ?? ... Thanks to any who can shed some light on this ...
TTYL,
Travis
-
there is no such thing as order in a heap table, rows can (and will) as you have seen come out in any order they choose.
If you want order you must use an ORDER by clause
-
10.2 has new (much faster) algorithms that are less likely to yield a sorted result. As Davey says if you want an order, you use ORDER BY.
-
slimdave,
Is the information about these algorithm enhancements documented anywhere within the product / Metalink ?
Thanks,
Travis
-
http://www.oracle.com/technology/dep...racle10gr2.pdf
Hash-based Aggregation
Data aggregation is a frequent operation in data warehousing and OLAP environments. Data is either aggregated on the fly or pre-computed and stored as materialized views. In either case, the time and resources needed to do the aggregation must be minimal. Oracle Database 10g Release 2 improves aggregation performance by using a hash-based method for aggregation replacing the sort-based approach used in previous releases. The hash-based scheme significantly improves the performance ... of data aggregation .
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|