Group By

In an earlier article on Cost Based Query Transformation, I showed an example of “complex view merging” where the optimizer turned an in-line aggregate view into a simple join with late aggregation.

In 11g, the optimizer can do the opposite transformation using a mechanism known as “Group by Placement”.

Here’s a simple SQL statement, with the associated execution plan from 10g:

rem
rem     Script:         group_by_11g.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Feb 2008
rem

select 
        t1.v1, sum(t2.n2)
from    t1, t2
where 
        t2.n1 between 10 and 90
and     t1.n1 = t2.n1
group by 
        t1.v1
;

--------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  1236 |   232K|       |   176 |
|   1 |  HASH GROUP BY      |      |  1236 |   232K|  1784K|   176 |
|*  2 |   HASH JOIN         |      |  7639 |  1439K|       |    30 |
|*  3 |    TABLE ACCESS FULL| T2   |  1236 |  9888 |       |    14 |
|*  4 |    TABLE ACCESS FULL| T1   |  1236 |   223K|       |    14 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="T2"."N1")
   3 - filter("T2"."N1">=10 AND "T2"."N1"<=90) -- > comment added to avoid WordPress format issue
   4 - filter("T1"."N1">=10 AND "T1"."N1"<=90) -- > comment added to avoid WordPress format issue

As you might expect we have joined the two tables then done a large scale aggregation (using the 10g “hash group by”mechanism). But here’s what happens in 11g (11.1.0.6) for this sample data set and query:

--------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |  3000 |   591K|       |   142 |
|   1 |  HASH GROUP BY        |          |  3000 |   591K|  1272K|   142 |
|*  2 |   HASH JOIN           |          |  3000 |   591K|       |    44 |
|   3 |    VIEW               | VW_GBC_5 |   200 |  3400 |       |    29 |
|   4 |     HASH GROUP BY     |          |   200 |  2400 |       |    29 |
|*  5 |      TABLE ACCESS FULL| T2       |  1236 | 14832 |       |    14 |
|   6 |    TABLE ACCESS FULL  | T1       |  3000 |   541K|       |    14 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="ITEM_1")
   5 - filter("T2"."N1"<=90 AND "T2"."N1">=10)

In this case we have aggregated data from t2 before joining to t1 because the optimizer has decided the volume selected from t2 is so large that aggregating before joining would be cheaper than joining before aggregating.

This is a fantastic trick – I’ve often seen examples where it would obviously be nice to rewrite a query to do this sort of thing but the performance is so small that it’s not worth the effort involved in rewriting and testing the code. Now it can happen automatically.

Of course, as always, the optimizer arithmetic may be inaccurate – so Oracle has supplied hints to allow you force the transformation when it doesn’t happen and stop the transformation if it does happen. For example:

prompt  ===================================
prompt  Hinting to force group by placement
prompt  ===================================
 
select
        /*+
                qb_name(main)
                place_group_by(@main(t1@main))
        */
        t1.v1, sum(t2.n2)
from    t1, t2
where
        t2.n1 between 10 and 50
and     t1.n1 = t2.n1
group by
        t1.v1
;

prompt  ===================================
prompt  Hinting to block group by placement
prompt  ===================================

select
        /*+
                qb_name(main)
                no_place_group_by(@main(t2@main))
        */
        t1.v1, sum(t2.n2)
from    t1, t2
where
        t2.n1 between 10 and 90
and     t1.n1 = t2.n1
group by
        t1.v1
;

prompt  =======================================
prompt  Hinting to double up group by placement
prompt  =======================================
 
select
        /*+
                qb_name(main)
                place_group_by(@main (t1@main) (t2@main))
        */
        t1.v1, sum(t2.n2)
from    t1, t2
where
        t2.n1 between 10 and 50
and     t1.n1 = t2.n1
group by
        t1.v1
;

You’ll notice that I’ve used the qb_name() hint in these examples – this makes it easier to control the details in more complex cases.

The place_group_by and no_place_group_by hints are a little more subtle than my examples show – and I look forward to seeing them documented – so I would be a little cautious about using them. You’ll see in my example execution plan that there is a reference to an internal view called vw_gbc_5. The name relates in some way to the order of the optimizer’s processing, and I found that the proper use of the place_group_by hint included the number as a second parameter when I checked the outline information from the execution plan.

Another little detail that I haven’t yet investigated is that whenever t2 was subject to group by placement the internal view had a name of the form vw_gbc_N while the internal view relating to group by placement of t1 had a name of the form vw_gbf_N.