Oracle 10g R2 data sorting issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle 10g R2 data sorting issue

  1. #1
    Join Date
    Jun 2005
    Location
    Ottawa, Ontario, Canada
    Posts
    18

    Question 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

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jun 2005
    Location
    Ottawa, Ontario, Canada
    Posts
    18
    slimdave,

    Is the information about these algorithm enhancements documented anywhere within the product / Metalink ?


    Thanks,
    Travis

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 .
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width