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

Thread: sorting error

Hybrid View

  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,555
    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 01: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
    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

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by ales View Post
    This never has been true. Not a big deal to show that:

    http://groups.google.com/group/comp....e=source&hl=en
    Thanks God we have people like you in the forum that knows better than Oracle knows about Oracle products.

    Why don't you check Metalink Note #345048.1?... try it, it wouldn't bite you
    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.

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    Talking

    Paul, despite of fact that i didn't know the note it says no word about implicit sorting when using group by. It only says it happens, but without order by no gurantee. This is the same the oracle docs says.

    Quotes from the said Metalink Note:
    If no order by clause is specified then the order of the rows retrieved depends on the method used to retrieve the rows from the database. In other words, it depends on the execution plan chosen.
    ... the group by clause has been enhanced to allow a hash algorithm rather than a sorting algorithm to group the data. Neither of these algorithms guaranteed the order the data would be returned in, the sort algorithm just 'happened' to order it the way wanted by chance.
    Strange thing. Plain english text and two people understand it in a reverse way. Could you please find the words that mention implicit sorting in group by and post it here? In my understanding, if something just happens it's not very "implicit". At least, I would never rely on it.

    Try the code I posted earlier and answer yourself if the query returns ouput in the order you expect.

    The impertinet irony and rolled eyes are excused.
    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
    thanks it helped
    lucky

  8. #8
    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

  9. #9
    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

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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??
    I need to do it urgently..
    As documented on Metalink the only cure is to actually add ORDER BY clause to all affected queries.

    But, "If you have a lot of queries to change, you can use one of the following init/spfile parameters in order to have the same Optimizer behaviour as older versions. But I have to insist that there is no guarantee optimizer will still choose the same sort operation to group the data. Even with these parameters, it's not sure the rows will be retrieved in the same order:
    "_gby_hash_aggregation_enabled" = false
    OR
    optimizer_features_enabled=9.2.0
    OR
    optimizer_features_enabled=8.1.7"

    I'm not suggesting to change your init/spfile initialization parameters, this is an option Oracle brings to the table but I would consider it just a last resort option.

    Hope this helps.
    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.

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