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:

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"=10 AND "T1"."N1"<=90)

As you might expect, we have joined the two tables, and 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"=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 benefit is often small, and 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:

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
;

select
	/*+
		qb_name(main)
		place_group_by(@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 example shows – 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 “full” use of the place_group_by hint included this number as a second parameter when checked the outline information from the execution plan.