1 2 Previous Next 22 Replies Latest reply on Oct 4, 2018 3:30 AM by DeepakShenoy

    cube vs roll-up vs grouping set

    user13328581

      Hello experts;

       

      I have the following sample data below and the code i am working on as well

       

      with t (namereq, amt, group_name) as

      (

      select 'A1', 2, 'A' from dual

      union all

      select 'A2', 3, 'A' from dual

      union all

      select 'A3', 4, 'A' from dual

      union all

      select 'B1', 3, 'B' from dual

      union all

      select 'B2', 5, 'B' from dual

      )

       

      desired output below

       

      Namereq    Amt         Group_name

      A1                 2           A

      A2                 3           A

      A3                 4           A

      Subtotal        9           A

      B1                 3           B

      B2                 5           B

      Subtotal        8           B

      Total              17        null

       

       

      Code created so far

       

      with t (namereq, amt, group_name) as

      (

      select 'A1', 2, 'A' from dual

      union all

      select 'A2', 3, 'A' from dual

      union all

      select 'A3', 4, 'A' from dual

      union all

      select 'B1', 3, 'B' from dual

      union all

      select 'B2', 5, 'B' from dual

      )

        select namereq

            ,sum(amt) as amt

            ,group_name

          from t

          group by rollup (group_name, namereq)

          order by group_name

       

       

      However, I decided to adapt the code to use grouping sets to get the desired output but i am getting some strange error

       

      with t (namereq, amt, group_name) as

      (

      select 'A1', 2, 'A' from dual

      union all

      select 'A2', 3, 'A' from dual

      union all

      select 'A3', 4, 'A' from dual

      union all

      select 'B1', 3, 'B' from dual

      union all

      select 'B2', 5, 'B' from dual

      )

       

       

      select namereq

               ,group_name

              ,sum(amt)

              ,GROUPING_ID(group_name, namereq) AS grouping_id

             ,GROUP_ID() AS group_id

      from t

      group by grouping sets(namereq,  CUBE (group_name, namereq))

      ORDER BY namereq, group_name;

       

      error below

      ORA-00904: "SYS_TBL_$1$"."NAMEREQ": invalid identifier

       

      Also, which is better to use roll-up or cube or grouping-sets in the above scenario

        • 1. Re: cube vs roll-up vs grouping set
          mathguy

          Here's the theory (the way I understand it, anyway):

           

          You have a table (real or the result of other computations), with one or more dimension columns and a quantity column. You want to show the rows from the table, plus additional "total quantity" rows at different levels, grouping by various dimension columns or groups of dimension columns.

           

          CUBE is the most general operator; it computes totals by ALL combinations of dimensions. If you have n dimensions, this means 2^n different kinds of totals.

           

          GROUP BY GROUPING SETS allows you to select ANY subset of such combinations.

           

          GROUP BY ROLLUP allows you to select only specific kinds of combinations: You have a HIERARCHY of dimensions, such as "sum sales by employee", then "sum sales by department" and then "sum sales by industry". While GROUP BY ROLLUP is specialized (so you can't do EVERYTHING with it, as you can with GROUP BY GROUPING SETS), the syntax is simpler, and I suspect the execution may also be faster (working in a special case, where the totals follow a hierarchy, allows optimizations that are simply not possible in a CUBE/GROUP BY GROUPING SETS environment).

           

          Now:

           

          My question to you is, since obviously what you are after is totals by a hierarchy of dimensions (first group by NAMEREQ, then by GROUP_NAME), GROUP BY ROLLUP is exactly the right tool. So, WHY are you looking to change it to GROUP BY GROUPING SETS?

           

          It is possible that you have a good reason, but I don't think you stated it.

          1 person found this helpful
          • 2. Re: cube vs roll-up vs grouping set
            Frank Kulash

            Hi,

            user13328581 wrote:

             

             

             

            ... However, I decided to adapt the code to use grouping sets to get the desired output but i am getting some strange error

             

            with t (namereq, amt, group_name) as

            (

            select 'A1', 2, 'A' from dual

            union all

            select 'A2', 3, 'A' from dual

            union all

            select 'A3', 4, 'A' from dual

            union all

            select 'B1', 3, 'B' from dual

            union all

            select 'B2', 5, 'B' from dual

            )

             

             

            select namereq

            ,group_name

            ,sum(amt)

            ,GROUPING_ID(group_name, namereq) AS grouping_id

            ,GROUP_ID() AS group_id

            from t

            group by grouping sets(namereq, CUBE (group_name, namereq))

            ORDER BY namereq, group_name;

             

            error below

            ORA-00904: "SYS_TBL_$1$"."NAMEREQ": invalid identifier

             

             

            I think that's a bug.  I get the same error (in Oracle 12.2.0.1.0) when I use a WITH clause, like you did, but no error when I create a real table t.

             

            Also, which is better to use roll-up or cube or grouping-sets in the aboveario

            As Mathguy pointed out in reply #1, ROLLUP suits your requirements exactly.  What advantage do you see in using CUBE, GROUPING SETS, or any combination of methods in this case?

            • 3. Re: cube vs roll-up vs grouping set
              user13328581

              I would like to use CUBE, grouping sets because it contains GROUPING_ID, group_id which i need to use to display "subtotal" instead of the null like rollup  does and i thought it will be more efficient instead of trying to use case statement to get the null replaced by "subtotal". Also, i was looking at gathering statistics to see which is more efficient from a performance perspective.

               

              @ frank, I also get the strange error(bug) as well when used with the with like that in

               

              Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

              PL/SQL Release 11.2.0.4.0 - Production

              "CORE 11.2.0.4.0 Production"

              TNS for 64-bit Windows: Version 11.2.0.4.0 - Production

              NLSRTL Version 11.2.0.4.0 - Production

              • 4. Re: cube vs roll-up vs grouping set
                jaramill

                Frank Kulash wrote:

                 

                Hi,

                user13328581 wrote:

                 

                 

                 

                ... However, I decided to adapt the code to use grouping sets to get the desired output but i am getting some strange error

                 

                with t (namereq, amt, group_name) as

                (

                select 'A1', 2, 'A' from dual

                union all

                select 'A2', 3, 'A' from dual

                union all

                select 'A3', 4, 'A' from dual

                union all

                select 'B1', 3, 'B' from dual

                union all

                select 'B2', 5, 'B' from dual

                )

                 

                 

                select namereq

                ,group_name

                ,sum(amt)

                ,GROUPING_ID(group_name, namereq) AS grouping_id

                ,GROUP_ID() AS group_id

                from t

                group by grouping sets(namereq, CUBE (group_name, namereq))

                ORDER BY namereq, group_name;

                 

                error below

                ORA-00904: "SYS_TBL_$1$"."NAMEREQ": invalid identifier

                 

                 

                I think that's a bug. I get the same error (in Oracle 12.2.0.1.0) when I use a WITH clause, like you did, but no error when I create a real table t.

                 

                 

                Same here Frank, and I'm one sub-version lower than you:

                 

                 

                SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 2 12:46:17 2018
                
                Copyright (c) 1982, 2016, Oracle.  All rights reserved.
                
                Last Successful login time: Tue Oct 02 2018 12:46:14 -04:00
                
                Connected to:
                Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                Advanced Analytics and Real Application Testing options
                
                      ,fd.group_name
                           *
                ERROR at line 22:
                ORA-00904: "SYS_TBL_$1$"."NAMEREQ": invalid identifier
                
                Elapsed: 00:00:00.04
                12:46:26 capacity@GGBLTD01>
                
                • 5. Re: cube vs roll-up vs grouping set
                  mathguy

                  GROUPING_ID is availble with GROUP BY ROLLUP. Did you try it? I am not sure you really need GROUP_ID when you GROUP BY ROLLUP (most likely, if you do then the query is written incorrectly), but it should also work with GROUP BY ROLLUP.

                   

                  If you have difficulty when you try (which I doubt), show the desired output and we'll help you with that.

                  • 6. Re: cube vs roll-up vs grouping set
                    mathguy

                    Frank Kulash wrote:

                     

                    I think that's a bug. I get the same error (in Oracle 12.2.0.1.0) when I use a WITH clause, like you did, but no error when I create a real table t.

                     

                     

                    Interesting! I ran into the same error in a completely different context recently, and the issue there too was WITH clause vs. real table. (And, as discussed at the end of that thread, I believe that was the second time I had run into it... and after trying something out now, I believe the same thought crossed my mind the first time, but not the second. I'll give it a try right after this.)

                    Re: Error I don't understand... may be caused by rogue query transformation?

                     

                    The thought that just crossed my mind: What if we use the old form of the WITH clause, where are not allowed to declare column names immediately after the name of the CTE, and instead we must give those names in the SELECT statement defining the CTE?

                     

                    Change the top of the query to

                     

                    with t as

                    (

                    select 'A1' as namereq, 2 as amt, 'A' as group_name from dual

                     

                    - then the query works with the WITH clause!

                     

                    This suggests that the bug must exist in Oracle's implementation of the WITH clause with column names declared after the CTE name. This was introduced in Oracle 11.2, to support recursive CTE. Moreover, the bug seems to manifest itself only in the presence of "advanced" forms of aggregation (however defined).

                     

                    I am not a paying customer, so I don't have access to MOS, either to check whether a bug report exists on this issue, to see if a fix exists, or to log a bug report if it is not already known.

                    1 person found this helpful
                    • 7. Re: cube vs roll-up vs grouping set
                      Frank Kulash

                      Hi,

                      user13328581 wrote:

                       

                      I would like to use CUBE, grouping sets because it contains GROUPING_ID, group_id which i need to use to display "subtotal" instead of the null like rollup does

                      You can use GROUPING_ID and/or GROUP_ID with ROLLUP if you want to, as the query below shows, but you don't need either.  I find GROUPING easier to use, like ths:

                      SELECT    CASE

                                     WHEN  GROUPING (namereq)    = 0  THEN namereq

                                     WHEN  GROUPING (group_name) = 0  THEN ' Subtotal'

                                                                      ELSE '  Total'

                                END                                AS n

                      ,         SUM (amt)  AS amt

                      ,         group_name

                      ,         GROUPING_ID (group_name, namereq)  AS grping_id

                      ,         GROUP_ID ()                        AS grp_id

                      FROM      t

                      GROUP BY  ROLLUP (group_name, namereq)

                      ORDER BY  group_name, namereq

                      ;

                      Output:

                      N                AMT G  GRPING_ID     GRP_ID

                      --------- ---------- - ---------- ----------

                      A1                 2 A          0          0

                      A2                 3 A          0          0

                      A3                 4 A          0          0

                      Subtotal          9 A          1          0

                      B1                 3 B          0          0

                      B2                 5 B          0          0

                      Subtotal          8 B          1          0

                        Total           17            3          0

                      By the way, don't use function names (such as GROUPING_ID) as column aliases; it's just asking for confusion.

                       

                      and i thought it will be more efficient instead of trying to use case statement to get the null replaced by "subtotal".

                      That's a good thought, but I don't see how you can avoid using a CASE expression (which is different from a CASE statement).  How would you convert the NUMBER returned by GROUPING_ID into either namereq, 'Subtotal' or 'Total'?  (There are ways, but I can't believe they're more efficient than CASE.)

                       

                      Also, i was looking at gathering statistics to see which is more efficient from a performance perspective.

                      I haven't actually tested it, but I'd bet CUBE won't be any more efficient.  It does more than ROLLUP, so it probably costs more, and then there's the additional cost of discarding the extra stuff it did that you never wanted in the first place.

                      GROUPING SETS might be just as efficient as ROLLUP, but no faster, and ROLLUP is easier to use.

                       

                      EDIT: The output above is supposed to have a single space before 'Subtotal', but this site keeps removing it.  It displays two spaces before 'Total', exactly as I wanted, but I can't get just 1 space before 'Subtotal'.  Does anyone know how to do that?  I'm going to start another thread.

                      1 person found this helpful
                      • 8. Re: cube vs roll-up vs grouping set
                        jaramill

                        You are correct sir!  It works on 12.1.0.2.0

                         

                        SQL

                         

                        with datum as
                             (
                              select 'A1' as namereq, 2 as amt, 'A' as group_name from dual union all
                              select 'A2' as namereq, 3 as amt, 'A' as group_name from dual union all
                              select 'A3' as namereq, 4 as amt, 'A' as group_name from dual union all
                              select 'B1' as namereq, 3 as amt, 'B' as group_name from dual union all
                              select 'B2' as namereq, 5 as amt, 'B' as group_name from dual
                             )
                            ,final_datum as
                             (
                              select namereq
                                    ,sum(amt)                         as amt
                                    ,group_name
                                    ,grouping_id(group_name, namereq) as my_grouping_id
                                    ,group_id()                       as my_group_id
                                from datum d1
                               group by grouping sets(
                                                      namereq
                                                     ,cube(group_name, namereq)
                                                     )
                              )
                        select fd.namereq
                              ,fd.amt
                              ,fd.group_name
                          from final_datum fd
                         order by fd.namereq    asc
                                 ,fd.group_name asc;
                        

                         

                        OUTPUT

                         

                        SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 2 13:10:31 2018
                        
                        Copyright (c) 1982, 2016, Oracle.  All rights reserved.
                        
                        Last Successful login time: Tue Oct 02 2018 13:10:36 -04:00
                        
                        Connected to:
                        Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                        With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
                        Advanced Analytics and Real Application Testing options
                        
                        A1          2 A
                        A1          2
                        A1          2
                        A2          3 A
                        A2          3
                        A2          3
                        A3          4 A
                        A3          4
                        A3          4
                        B1          3 B
                        B1          3
                        B1          3
                        B2          5 B
                        B2          5
                        B2          5
                                    9 A
                                    8 B
                                   17
                        
                        18 rows selected.
                        
                        Elapsed: 00:00:00.10
                        13:10:41 capacity@GGBLTQ01>
                        
                        • 9. Re: cube vs roll-up vs grouping set
                          mathguy

                          I just edited my post to add a link to the "other" thread, where the same issue arose.

                          • 10. Re: cube vs roll-up vs grouping set
                            jaramill

                            mathguy wrote:

                             

                            I just edited my post to add a link to the "other" thread, where the same issue arose.

                            Thanks.

                             

                            Your initial reply has made me a bit hesitant to use the columns feature of the WITH clause.

                            • 11. Re: cube vs roll-up vs grouping set
                              mathguy

                              I find that the feature makes code much more readable. I am a big fan of declaring things before defining them, to keep things separate. (You can tell the preference for C things!)

                               

                              I will continue to use it, but I will try to remember, when I see this kind of error, to try moving the column names within the definition. I see this as similar to SQL Developer's inability to handle characters like ? and {} in the PATTERN clause of MATCH_RECOGNIZE; I won't avoid using SQL Developer just for that reason, but whenever something seems to not work, I remember why that is and I run the query in SQL*Plus. I see this as a similar kind of issue.

                              • 12. Re: cube vs roll-up vs grouping set
                                Frank Kulash

                                Hi,

                                 

                                Curiouser and curiouser!

                                 

                                If you use the aliases in both places, there's no error:

                                WITH t (namereq, amt, group_name) as

                                (

                                select 'A1' AS namereq, 2 AS amt, 'A' AS group_name from dual

                                union all

                                select 'A2', 3, 'A' from dual

                                union all

                                select 'A3', 4, 'A' from dual

                                union all

                                select 'B1', 3, 'B' from dual

                                union all

                                select 'B2', 5, 'B' from dual

                                )

                                select namereq

                                         ,group_name

                                        ,sum(amt)

                                        ,GROUPING_ID(group_name, namereq) AS grouping_id

                                       ,GROUP_ID() AS group_id

                                from t

                                group by grouping sets(namereq,  CUBE (group_name, namereq))

                                ORDER BY namereq, group_name;

                                The aliases must match.  If the aliases after the WITH keyword aren't exactly the same as the aliases in the SELECT clause, then the "ORA-00904: "SYS_TBL_$1$"."NAMEREQ": invalid identifier" error occurs

                                1 person found this helpful
                                • 13. Re: cube vs roll-up vs grouping set
                                  mathguy

                                  Yes - I already made this observation in the other thread (I added a link to it in my earlier Reply, #6).

                                  • 14. Re: cube vs roll-up vs grouping set
                                    jaramill

                                    mathguy wrote:

                                     

                                    I find that the feature makes code much more readable. I am a big fan of declaring things before defining them, to keep things separate. (You can tell the preference for C things!)

                                     

                                    I will continue to use it, but I will try to remember, when I see this kind of error, to try moving the column names within the definition. I see this as similar to SQL Developer's inability to handle characters like ? and {} in the PATTERN clause of MATCH_RECOGNIZE; I won't avoid using SQL Developer just for that reason, but whenever something seems to not work, I remember why that is and I run the query in SQL*Plus. I see this as a similar kind of issue.

                                    Oh I totally agree on the readable part, just have to keep in mind what version of 12 I'm on.

                                    1 2 Previous Next