6 Replies Latest reply: Nov 16, 2012 4:09 PM by Frank Kulash RSS

    Cube , Rollup

    969779
      Hi

      what is the difference between Cube and Rollup

      and what's the difference if I say for example :

      Group by ( job_id, Division_id) or I say : Group by (Division_id, Job_id) ;


      ML.
        • 1. Re: Cube , Rollup
          BluShadow
          Finding the documentaiton hard to search today?

          Rollup and Cube: http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1669
          Rollup: http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1612
          Group by (Aggregate functions): http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref962
          • 2. Re: Cube , Rollup
            sb92075
            Mr.lonely wrote:
            Hi

            what is the difference between Cube and Rollup

            and what's the difference if I say for example :

            Group by ( job_id, Division_id) or I say : Group by (Division_id, Job_id) ;


            ML.
            http://www.oracle.com/pls/db112/search?remark=quick_search&word=cube&partno=

            http://www.oracle.com/pls/db112/search?word=rollup&partno=
            • 3. Re: Cube , Rollup
              969779
              hi,
              I could do that my self , but i thought some one tells the diff , explain it to me in easy way here in the forum .... any way thanks for the help !

              ML.
              • 4. Re: Cube , Rollup
                Frank Kulash
                Hi,
                Mr.lonely wrote:
                hi,
                I could do that my self ...
                Great! Then you don't have to go throught the trouble of posting a message on this forum. The documentation is more reliable, and more comprehensive, than the answers you get here. The manual can be faster, and it's never sarcastic.

                This forum is a great place for posting questions when the documentation isn't clear, or seems to be contradictory. For example: "The SQL language 11.2 manual gives this example: ... When I tried ths ... I expected the output to be ... but instead I got ... Why? The manual even says ...".

                This forum is also a good place to ask questions about how to use features. For example: "This query with GROUP BY ROLLUP ... seems to do the same thing as this one using GROUPING SETS .... Are there any situations in which these 2 queries would not produce the same results? Is one of them better that the other in this case? I tried to to the same thing using CUBE, like this ... but I ggot this error: .... Are there any guidlines for when ROLLUP is better than GROUPING SETS, and when GROUPING SETS is better than ROLLUP?
                • 5. Re: Cube , Rollup
                  969779
                  I found in the book I study that :

                  The group sets offers better performance than Cube ! , Therefore you should use GROUPING SETS rather than CUBE wherever possible. so I noticed that it's really good to use Grouping sets. :).
                  • 6. Re: Cube , Rollup
                    Frank Kulash
                    Hi,
                    Mr.lonely wrote:
                    I found in the book I study that :

                    The group sets offers better performance than Cube ! ,
                    CUBE generates all possible totals. If you really need to create all possible totals, then CUBE is the best way to do it.
                    If you don't really need to create all possible totals, then CUBE is not the best way to do it. In particular, if you find yourself using the GROUPING function in a HAVING clause to discard some totals, then you're doing something wrong. If you don't need all possible combinations, then using CUBE to generate them, and then using HAVING to get rid of some of them, isn't very efficient. Better to use GROUPING sets (or ROLLUP) to only generate the totals you need in the first palce.
                    Therefore you should use GROUPING SETS rather than CUBE wherever possible. so I noticed that it's really good to use Grouping sets. :).
                    I agree. I use GROUPING SETS a lot more than ROLLUP. I don't think I've ever used CUBE for real work, only for experiments to see what it does.