1 2 Previous Next 20 Replies Latest reply: Dec 25, 2012 3:22 PM by sql_coder Go to original post RSS
      • 15. Re: GROUP BY CUBE
        Solomon Yakobson
        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
          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
            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
              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
                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
                  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