1 2 Previous Next 20 Replies Latest reply: Dec 25, 2012 3:22 PM by sql_coder RSS

    GROUP BY CUBE

    sql_coder
      Hello,

      is there any difference between those two querries ?

      SELECT columnA, columnB, COUNT(*)
      FROM table1
      GROUP BY CUBE(columnA, columnB)
      ;

      SELECT columnA, columnB, COUNT(*)
      FROM table1
      GROUP BY CUBE(columnB, columnA)
      ;

      I know it matters when using ROLLUP, but does the order of the columns has any impact in the GROUP BY CUBE statement ?
        • 1. Re: GROUP BY CUBE
          Frank Kulash
          Hi,

          No, those two queries are the same.

          When you run them, you may notice that order of rows in the output is different, but without an ORDER BY clause, the order of rows has no meaning.
          • 2. Re: GROUP BY CUBE
            sql_coder
            hello Frank Kulash,

            thanks for the answer. by the way ROLLUP will sort a query without ORDER BY when not wrong...
            • 3. Re: GROUP BY CUBE
              Frank Kulash
              Hi,
              Ikrischer wrote:
              hello Frank Kulash,

              thanks for the answer. by the way ROLLUP will sort a query without ORDER BY when not wrong...
              Sorry, I don;t understand what you mean.

              Oracle does not guarantee the order of rows in any result set unless there is an ORDER BY clause (or CONNECT BY, but that's a separate topic).
              You may notice that, in a particular version, on a particular platform, the results of GROUP BY are always in some particular order, even though there is no ORDER BY clause. Don't depend on it. That's exactly the kind of behavior that may change in a different version. If you want the output to be in a specific order, then use an ORDER BY clause.
              • 4. Re: GROUP BY CUBE
                sql_coder
                the resultset in a GROUP BY statement without an ORDER BY clause has no particular order, no doubt. but a I think the behaviour of GROUP BY ROLLUP is different....
                • 5. Re: GROUP BY CUBE
                  sql_coder
                  I think when you use UNION in Oracle, you have an order without an ORDER BY clause as well.....
                  • 6. Re: GROUP BY CUBE
                    padders
                    The point is not that the data was not implcitly ordered it is that it is not guaranteed and you should not rely on such behaviour.
                    • 7. Re: GROUP BY CUBE
                      sql_coder
                      Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
                      • 8. Re: GROUP BY CUBE
                        Frank Kulash
                        Hi,
                        Ikrischer wrote:
                        Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
                        Is Oracle really telling you that? Post a quote and a link to the relevant docuemntation.
                        Do you mean that someone from Oracle is literally saying that directly to you, e.g., in correspondence regarding a Service Request? Post it, if you can.
                        • 9. Re: GROUP BY CUBE
                          sql_coder
                          Hello Frank,

                          yes, it is in my Oracle documents I have for the SQL Expert exam. Not sure if you have access to it. I am not allowed to upload the document, but it is from Oracle. The following behaviour is for UNION, INTERCEPT and MINUS, not for UNION ALL.

                          "By default, the output is sorted in ascending order of the first column of the SELECT clause."


                          try this out:

                          SELECT 2 Sort_Column, 'a test' Text_to_order FROM dual
                          UNION
                          SELECT 3, 'about Set Operators' FROM dual
                          UNION
                          SELECT 1, 'This is' FROM dual
                          ;

                          IKrischer

                          Edited by: Ikrischer on Dec 16, 2012 6:27 PM
                          • 10. Re: GROUP BY CUBE
                            sql_coder
                            Hello Frank Kulash,

                            are you able to have a look into the Oracle SQL expert documents, if not let me know please. And when I am wrong (what is always possible) I am willing to send you a very nice german beer as a late christmas present....

                            ikrischer
                            • 11. Re: GROUP BY CUBE
                              Solomon Yakobson
                              Ikrischer wrote:
                              Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
                              Wrong! Oracle never told you that. Oracle users (some of them posted that in various Oracle SQL expert documents) stated that. And it was true at the time. Why? because UNION implies unique rows and in older versions it was done by using sort. In newer (I believe starting 10.2) Oracle can use hash. Anyway, here is a link to Tom Type's blog. Pay attention, among other things, to:

                              a) Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!

                              b) The only way to retrieve data from the database in some sorted order is to include an ORDER BY on your query. There is no substitute for ORDER BY.

                              And most importantly to:

                              Does UNION do a sort or is it the same as GROUP BY?

                              Union is a distinct operation and <font color=red>may or may not sort in order to distinct</font>. In any case, the sort it does would be a <font color=red>binary sort, not a character set sort</font> (eg: does not sort the same as order by would necessarily). Throw in <font color=red>parallel operations, partitioning</font> and all bets are off._*

                              SY.
                              • 12. Re: GROUP BY CUBE
                                sql_coder
                                Solomon Yakobson wrote:
                                Ikrischer wrote:
                                Oracle is telling me, when using a UNION operator the result set is ordered by the first column. so why should I not relay on it ?
                                Wrong! Oracle never told you that.
                                Before I got the Oracle documents to pass the exam 1Z0-047 (valid against 10g and 11g) I would agree, I never thought a query with a SET operator is sorted, I was surprised myself. But those documents (copyright Oracle Corporation) are telling me in the guidelines for SET operators, it is sorted by the fist column. I know an Index, etc. can just make it look like it is sorted, thats why I tried it with dual. So what do you want me to believe ? Do you have access to those documents yourself ?

                                ikrischer
                                • 13. Re: GROUP BY CUBE
                                  Solomon Yakobson
                                  Ikrischer wrote:
                                  I never thought a query with a SET operator is sorted, I was surprised myself.
                                  First of all SET is a function, not an operator. Secondly, query with SET function is not sorted. SET function apples to nested table, not to a query. I can write something like where SET is used as select-list expression:
                                  SQL> create or replace
                                    2    type SalList
                                    3      as table of number(7,2)
                                    4  /
                                  
                                  Type created.
                                  
                                  SQL> select  deptno,
                                    2          cast(collect(sal) as SalList) SalList
                                    3    from  emp
                                    4    group by deptno
                                    5  /
                                  
                                      DEPTNO SALLIST
                                  ---------- ---------------------------------------------
                                          10 SALLIST(2450, 5000, 1300)
                                          20 SALLIST(800, 3000, 800, 3000, 2975)
                                          30 SALLIST(1500, 1500, 1250, 1500, 1250, 1250)
                                  
                                  SQL>  select  deptno,
                                    2          set(cast(collect(sal) as SalList)) SalList
                                    3    from  emp
                                    4    group by deptno
                                    5  /
                                  
                                      DEPTNO SALLIST
                                  ---------- ------------------------------
                                          10 SALLIST(2450, 5000, 1300)
                                          20 SALLIST(800, 3000, 2975)
                                          30 SALLIST(1500, 1250)
                                  
                                  SQL> 
                                  SET eliminated duplicates, but do you see salary set sorted? Anyway, I think you are talking about:
                                  SELECT  *
                                    FROM  TABLE(SET(nested_table))
                                  /
                                  And this again will eliminate dups but not necessarily by sorting:
                                  SQL> select * from table(set(NumList(3,2,1,3,3,2,1,1,2)));
                                  
                                  COLUMN_VALUE
                                  ------------
                                             3
                                             2
                                             1
                                  
                                  SQL>  
                                  I have no idea if exam 1Z0-047 states that or not. I assume you misinterpreted what it states.

                                  SY.
                                  • 14. Re: GROUP BY CUBE
                                    sql_coder
                                    Solomon Yakobson wrote:
                                    Ikrischer wrote:
                                    I never thought a query with a SET operator is sorted, I was surprised myself.
                                    First of all SET is a function, not an operator. Secondly, query with SET function is not sorted. SET function apples to nested table, not to a query. I can write something like where SET is used as select-list expression:
                                    But you know we talk about: http://docs.oracle.com/cd/E11882_01/server.112/e26088/operators005.htm#SQLRF51164 ?

                                    ikrischer
                                    1 2 Previous Next