This content has been marked as final. Show 6 replies
Finding the documentaiton hard to search today?
Rollup and Cube: http://docs.oracle.com/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1669
Group by (Aggregate functions): http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref962
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) ;
Mr.lonely wrote: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.
I could do that my self ...
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?
Mr.lonely wrote:CUBE generates all possible totals. If you really need to create all possible totals, then CUBE is the best way to do it.
I found in the book I study that :
The group sets offers better performance than Cube ! ,
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.