This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Dec 25, 2012 1:22 PM by sql_coder Go to original post RSS
  • 15. Re: GROUP BY CUBE
    Solomon Yakobson Guru
    Currently Being Moderated
    Ikrischer wrote:
    But you know we talk about: http://docs.oracle.com/cd/E11882_01/server.112/e26088/operators005.htm#SQLRF51164 ?
    So you are referring to SET operator<font color=red set=3>s</font>. OK, where does it say it returns ordered rows? All I can see is distinct_ and distinct doesn't guarantee sorted.

    SY.
  • 16. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    Well yes, half of the post we talk about a set operator or if you want set operators. There is an example with dual from me, you can try it out if you want, even I know one example is not a prove. Would be interessting, if you have an example, that it not ordered.
    Solomon Yakobson wrote:
    OK, where does it say it returns ordered rows? All I can see is distinct_ and distinct doesn't guarantee sorted.
    I agree, DISTINCT does not guarantee it is sorted, same with GROUP BY. But we talk about UNION and only about the first column, not a sort over all columns. I already told you about the oracle documents, I am not allowed to public them (copy rights). That is why I ask if someone else has access to them.

    ikrischer
  • 17. Re: GROUP BY CUBE
    Solomon Yakobson Guru
    Currently Being Moderated
    Ikrischer wrote:
    But we talk about UNION and only about the first column, not a sort over all columns. I already told you about the oracle documents, I am not allowed to public them (copy rights).
    Again and for the last time only ORDER BY guarantees order. I don't know if certification documents are prepared by Oracle or are "outsourced". In any case if it states UNION returns ordered set - it lies. And below is an example where rows returned by UNION are not sorted:
    SQL> select  *
      2    from  tbl
      3  /
    
             N
    ----------
             1
             1
            99
            99
             2
             2
            55
            55
    
    8 rows selected.
    
    SQL>  select  n
      2     from  tbl
      3     where n in (55,2)
      4   union
      5    select  n
      6      from  tbl
      7      where n in (1,99)
      8  /
    
             N
    ----------
             1
            55
            99
             2
    
    SQL> 
    As you can see set returned by UNION is not ordered. Why? Because table was created with PARALLEL:
    create table tbl(n number)
    parallel
    /
    Therefore execution plan:
    SQL> explain plan for
      2   select  n
      3     from  tbl
      4     where n in (55,2)
      5   union
      6    select  n
      7      from  tbl
      8      where n in (1,99)
      9  /
    
    Explained.
    
    SQL> @?\rdbms\admin\utlxpls
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------
    Plan hash value: 2433233639
    
    -------------------------------------------------------------------------------------
    | Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT         |          |     8 |   104 |     6  (67)| 00:00:01 |
    |   1 |  PX COORDINATOR          |          |       |       |            |          |
    |   2 |   PX SEND QC (RANDOM)    | :TQ10001 |     8 |   104 |     6  (67)| 00:00:01 |
    |   3 |    SORT UNIQUE           |          |     8 |   104 |     6  (67)| 00:00:01 |
    |   4 |     PX RECEIVE           |          |       |       |            |          |
    |   5 |      PX SEND HASH        | :TQ10000 |       |       |            |          |
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------
    |   6 |       UNION-ALL          |          |       |       |            |          |
    |   7 |        PX BLOCK ITERATOR |          |     4 |    52 |     2   (0)| 00:00:01 |
    |*  8 |         TABLE ACCESS FULL| TBL      |     4 |    52 |     2   (0)| 00:00:01 |
    |   9 |        PX BLOCK ITERATOR |          |     4 |    52 |     2   (0)| 00:00:01 |
    |* 10 |         TABLE ACCESS FULL| TBL      |     4 |    52 |     2   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       8 - filter("N"=2 OR "N"=55)
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------
      10 - filter("N"=1 OR "N"=99)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    27 rows selected.
    
    SQL> 
    And without parallel:
    SQL> select  *
      2    from  tbl
      3  /
    
             N
    ----------
             1
             1
            99
            99
             2
             2
            55
            55
    
    8 rows selected.
    
    SQL>  select  n
      2     from  tbl
      3     where n in (55,2)
      4   union
      5    select  n
      6      from  tbl
      7      where n in (1,99)
      8  /
    
             N
    ----------
             1
             2
            55
            99
    
    SQL>  
    Now it is sorted since execution plan is:
    SQL> explain plan for
      2   select  n
      3     from  tbl
      4     where n in (55,2)
      5   union
      6    select  n
      7      from  tbl
      8      where n in (1,99)
      9  /
    
    Explained.
    
    SQL> @?\rdbms\admin\utlxpls
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------
    Plan hash value: 880278506
    
    ----------------------------------------------------------------------------
    | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |      |     8 |   104 |     8  (63)| 00:00:01 |
    |   1 |  SORT UNIQUE        |      |     8 |   104 |     8  (63)| 00:00:01 |
    |   2 |   UNION-ALL         |      |       |       |            |          |
    |*  3 |    TABLE ACCESS FULL| TBL  |     4 |    52 |     3   (0)| 00:00:01 |
    |*  4 |    TABLE ACCESS FULL| TBL  |     4 |    52 |     3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - filter("N"=2 OR "N"=55)
       4 - filter("N"=1 OR "N"=99)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    21 rows selected.
    
    SQL> 
    SY.
  • 18. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    Thanks for that example SY. As I said before, I was surprised myself, but looks like the documents you get to pass the Oracle exams are wrong and that is not funny. I searched fo them and you can find some one them in the net, even if older versions you can see what I mean.

    http://www-is.offis.uni-oldenburg.de/sqlkurs/sql/pdf/Les15.pdf
    http://www.ohm-hochschule.de/fileadmin/Rechenzentrum/Virtuohm/Vorlesungen/Hopf/SQL1.pdf

    ikrischer
  • 19. Re: GROUP BY CUBE
    Solomon Yakobson Guru
    Currently Being Moderated
    Ikrischer wrote:
    As I said before, I was surprised myself, but looks like the documents you get to pass the Oracle exams are wrong and that is not funny.
    Not funny indeed. But you still owe Frank a very nice german beer as a late christmas present :D.

    SY.
  • 20. Re: GROUP BY CUBE
    sql_coder Newbie
    Currently Being Moderated
    that is true, not only because of this, Frank always helps in a very friendly way, he deserves more then one. All I need is a way to send it to him. Who is responsible for the Oracle exam documents, so I can tell them about the problem ?

    ikrischer
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points