DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: sorting error

  1. #11
    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.

  2. #12
    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

  3. #13
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Arguing with a well known issue documented in Metalink?

    Can't believe it. You can't read -or didn't even bother to read - either, huh?

    Let me quote from the Metalink note, may be if pre-digested you will be able to see it a little clearly.

    "The GROUP BY clause still performs sort operations but in version 10G, new internal Sort algorithms have been introduced which do not guarantee the order of data retrieval and may change the order for returned rows"

    "Sort operation used in 9.2.0 optimizer is Sort by group while 10g optimizer is using HASH GROUP BY operation. Each sort algorithm orders data in a different way"

    Please stop confusing people. It's Okay to say "I don't know" or "I stand corrected"

    Have a nice and productive life.
    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. #14
    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.

  5. #15
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Quote Originally Posted by PAVB
    Arguing with a well known issue documented in Metalink?.
    No, I don't. That's misunderstanding. What I argue with is this:
    Quote Originally Posted by PAVB
    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.
    I do insist this is not true. There never has been implicit order by with group by. Of course group by uses internal sorting mechanism and final output may or may not be affected by it, but group by without order by has never guaranted the order of output. The oracle docs says it too and the mentioned Metalink note says it as well:
    Applies to:
    Oracle Server - Enterprise Edition - Version: 6.0.0.0 to 10.2.0.1
    ...
    The only way to enforce the order of data in a query is to specify an order by clause.
    Quote Originally Posted by PAVB
    Let me quote from the Metalink note, may be if pre-digested you will be able to see it a little clearly.

    "The GROUP BY clause still performs sort operations but in version 10G, new internal Sort algorithms have been introduced which do not guarantee the order of data retrieval and may change the order for returned rows"

    "Sort operation used in 9.2.0 optimizer is Sort by group while 10g optimizer is using HASH GROUP BY operation. Each sort algorithm orders data in a different way"
    Yes, I understand it well. And what? Do they say there's implicit order by with group by? No. They say group by internally sorts. It sorts differently in 9i and 10g. Do they say group by makes order by for you? No.

    Please stop confusing people. It's Okay to say "I don't know" or "I stand corrected"
    I say you confuse people with the mystical implicit order by.

    Please be so kind and launch in 9i the testing code I posted. SQL*Plus does not bite believe me or not.
    Then, please, post the result of the final query here and comment how it conform with the "implicit order by with group by" idea. Show us how it "sorted the output by the GROUP BY condition even if no ORDER BY was explicitily specified.".

    Until you post the result here I find further discussion pointless.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  6. #16
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Okay... you asked for it...
    Code:
    SQL>
    SQL> select version from v$instance;
    
    VERSION
    -----------------
    9.2.0.4.0
    
    SQL>
    SQL> create table the_table (
      2     pk1 int,
      3     pk2 int,
      4     nk1 int not null,
      5     nk2 int not null,
      6     primary key(pk1,pk2)
      7  )
      8  ;
    
    Table created.
    
    SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2);
    
    Index created.
    
    SQL> insert into the_table values (1, 1, 2, 2 );
    
    1 row created.
    
    SQL> insert into the_table values (2, 1, 1, 1 );
    
    1 row created.
    
    SQL> analyze table the_table compute statistics;
    
    Table analyzed.
    
    SQL>
    SQL> explain plan for select nk1, nk2, pk1, pk2, count(*) a_count
    from the_table  2
      3  group by nk1, nk2, pk1, pk2;
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display);
    
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    -----------------------------------------------------------------------
    | Id  | Operation            |  Name          | Rows  | Bytes | Cost  |
    -----------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |                |     2 |    16 |     1 |
    |   1 |  SORT GROUP BY NOSORT|                |     2 |    16 |     1 |
    |   2 |   INDEX FULL SCAN    | THE_TABLE_IDX  |     2 |    16 |     1 |
    -----------------------------------------------------------------------
    
    Note: cpu costing is off
    
    10 rows selected.
    
    SQL>


    Just wish you do not start arguing about "... BY NOSORT" 'cause that would show you don't understand index pre-sorting either.

    Here is a little piece of advice... Rule of Holes #1 states "when you find yourself in a hole the single most important thing you must do is to stop digging" so, stop digging.

    Please don't take it personally but I'm done with you, this is the last time I'm answering 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.

  7. #17
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    ROFL
    You tweaked the script to make it not to show the results! I don't believe my eyes. Where's the final select? You replaced it by explain plan to hide the truth! Who is in the hole then? Stop digging and stop spreading fog!

    OK, I launch the whole thing for you, if you're running scared ...

    Code:
    SQL>select version from v$instance;
    
    VERSION
    -----------------
    9.2.0.6.0
    
    SQL>create table the_table (
      2     pk1 int,
      3     pk2 int,
      4     nk1 int not null,
      5     nk2 int not null,
      6     primary key(pk1,pk2)
      7  )
      8  /
    
    Table created.
    
    SQL>create index the_table_idx on the_table(pk1,nk1,pk2,nk2)
      2  /
    
    Index created.
    
    SQL>insert into the_table values (1, 1, 2, 2 );
    
    1 row created.
    
    SQL>
    SQL>insert into the_table values (2, 1, 1, 1 );
    
    1 row created.
    
    SQL>
    SQL>analyze table the_table compute statistics;
    
    Table analyzed.
    
    SQL>
    SQL>set autotrace on explain
    SQL>
    SQL>select nk1, nk2, pk1, pk2, count(*) a_count
      2  from the_table
      3  group by nk1, nk2, pk1, pk2
      4  ;
    
           NK1        NK2        PK1        PK2    A_COUNT
    ---------- ---------- ---------- ---------- ----------
             2          2          1          1          1
             1          1          2          1          1
    
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=16)
       1    0   SORT (GROUP BY NOSORT) (Cost=1 Card=2 Bytes=16)
       2    1     INDEX (FULL SCAN) OF 'THE_TABLE_IDX' (NON-UNIQUE) (Cost=
              1 Card=2 Bytes=16)
    We can see now if the "implicit order by" came into play and sorted the result in the expected order .

    Quote Originally Posted by PAVB
    Please don't take it personally but I'm done with you, this is the last time I'm answering you.
    That's OK, I don't mind it and I don't take it personally. The only thing I regret is the lost time I spent preaching to a deaf guy. I hope somebody else find the thread useful ...
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  8. #18
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    thanks both of u..
    @PAVB---i mentioned earlier that setting optimizer_features_enable alleviated my problem..
    I also read abt this parameter "_gby_hash_aggregation_enabled" in metalink but it was not shown by "show parameter _gby_hash_aggregation_enabled" command..
    So, for the time being, I m using optimizer_features_enable=9.2.0
    lucky

  9. #19
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Glad it worked out Lucky.
    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.

  10. #20
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    but still i m not able to see the setting of parameter _gby_hash_aggregation_enabled as show parameter does not show anything when show parameter _gby_hash_aggregation_enabled is executed...
    can sb tell me how could it be done??
    lucky

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