sorting error
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: sorting error

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    sorting error

    we have recently migrated data from 9i to 10g..
    platform MS server 2003 on both sides..
    oracle 6i forms and reports both sides..

    Earlier, data in forms and reports was displayed in sorted order..But After shifting to new server, data is not displayed in sorted order.
    How could it be corrected?
    lucky

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Do you mean the output is not sorted at all? Then use the ORDER BY clause in queries.
    If output is kind of sorted but the order is wrong, check the NLS_ parameters and if possible compare them with those on the old box.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Check the code.

    Until Ora9i when you had a GROUP BY clause ORDER BY was implicit meaning Oracle would sort the output by the GROUP BY condition even if no ORDER BY was explicitily specified.

    Starting on Ora10g if you want to ensure the output sorted you have to include both GROUP BY and ORDER BY clauses.
    Last edited by PAVB; 03-18-2009 at 02:22 PM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    thanks it helped
    lucky

  5. #5
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    this requires changing the coding of all forma and reports of my application dat i cant afford..Is there any way or parameter setting that allows implicit order by alongwith group by??
    I need to do it urgently..
    lucky

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Quote Originally Posted by PAVB View Post
    Until Ora9i when you had a GROUP BY clause ORDER BY was implicit meaning Oracle would sort the output by the GROUP BY condition even if no ORDER BY was explicitily specified.
    This never has been true. Not a big deal to show that:

    http://groups.google.com/group/comp....e=source&hl=en
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  7. #7
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    well momentarily, i got the solution by setting optimizer_features_enable='9.2.0'....
    but i m willing to use the optimizer features of 10g later on...
    is there any other way??
    lucky

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Quote Originally Posted by mahajanakhil198 View Post
    this requires changing the coding of all forma and reports of my application dat i cant afford..Is there any way or parameter setting that allows implicit order by alongwith group by??
    No, there isn't. Implicit ORDER BY with GROUP BY is a myth.

    Quote from 9i docs:
    http://download.oracle.com/docs/cd/B...3a.htm#2066158

    The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.
    Obviously, omitting the ORDER BY was a developer mistake. If you want sorted output you have to sort it explicitly. It apply to queries with GROUP BY or without it.

    Changing the optimizer_features_enable is the only thing that might help. I hope it gives you enough time to adjust the queries.
    Perhaps others here have some idea ...
    Good luck.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  9. #9
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    myth????
    here is the code..
    SQL> select * from test;

    A B
    ---------- ----------
    3 200
    2 300
    8 100
    5 400
    1 300
    4 500
    8 200
    5 300
    4 600
    2 300
    3 500
    2 100
    7 300

    13 rows selected.

    SQL> select a,sum(b) from test
    2 group by a;

    A SUM(B)
    ---------- ----------
    1 300
    2 700
    3 700
    4 1100
    5 700
    7 300
    8 300

    7 rows selected.
    lucky

  10. #10
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Well, sometimes it works. But it doesn't work always. Launch the code bellow and post here if the output was sorted according the group by clause in ascending order. Mine wasn't in 9i nor in 10g.

    Code:
    create table the_table (
       pk1 int,
       pk2 int,
       nk1 int not null,
       nk2 int not null,
       primary key(pk1,pk2)
    )
    /
    create index the_table_idx on the_table(pk1,nk1,pk2,nk2)
    /
    insert into the_table values (1, 1, 2, 2 );
    
    insert into the_table values (2, 1, 1, 1 );
    
    analyze table the_table compute statistics;
    
    set autotrace on explain
    
    select nk1, nk2, pk1, pk2, count(*) a_count
    from the_table
    group by nk1, nk2, pk1, pk2
    ;
    Ales
    The whole difference between a little boy and an adult man is the price of toys

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