4

I have the following query

SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
, listagg(type, ', ') within group (order by null) types 
FROM test
group by id

Both type and id are short strings. The execution plan for when I comment out listagg and leave count aggregation in is a HASH GROUP BY (even without the hint) and works fast. With the listagg aggregation Oracle always chooses SORT GROUP BY which is an order of magnitude slower. Is there any reason for that?

3

The queries are an order of magnitude different !

The access paths themselves shouldn't have such an impact but you're comparing the simplest aggregation function (COUNT(*)) to one of the most complex (LISTAGG) !

Furthermore, you have specified an ORDER BY clause in your LISTAGG, this will force Oracle to sort, which explains the optimizer decision to ignore your hint (this hint is also undocumented as far as I can tell).

If you wish to compare the different access paths, use the exact same query with different hints, eg:

SELECT /*+ USE_HASH_AGGREGATION */ id
--, count(id)
FROM test
group by id

and

SELECT /*+ NO_USE_HASH_AGGREGATION */ id
--, count(id)
FROM test
group by id
4
  • how is listagg complex? It just concatenates values. Also i don't think there is any way to not specify order by in listagg. I think saying 'order by null' is the way to tell it not to sort. – MK01 Jul 10 '12 at 15:04
  • I didn't know that ORDER BY was mandatory. It doesn't change the fact that LISTAGG must maintain an ordered list of values in each group whereas COUNT(id) must maintain a single number. Can you post the explain plan of both queries? – Vincent Malgrat Jul 10 '12 at 15:25
  • use_hash_aggregation is in fact documented (and I believe it was even in 2012 when this answer was written). The order by clause within listagg is within each group after the groups have already been identified - this has nothing to do with how the grouping operation itself is performed. And the type of aggregation has nothing to do with access paths, what is that about? Sorry, but I personally don't find anything in this answer to be useful; -1 from me. – mathguy Mar 20 at 15:37
  • @mathguy: from my understanding, the use_hash_aggregation is used by the Oracle optimizer tools, but I can't find much in the documentation as of today. At the time I was asking about the access path because the type of aggregation may change the optimizer plan. The COUNT(id) for instance could use an index on the IDcolumn whereas the other query needs to access the base table. – Vincent Malgrat Mar 24 at 7:51
0

From my observations, Oracle (at least as of 11.2) has several limitations on when it will use HASH GROUP BY, even with the USE_HASH_AGGREGATION hint, based on the aggregate functions you use.

My suspicion is that Oracle has whitelisted certain functions such as COUNT, MIN, MAX, AVG, etc. that can be used in an aggregate query and still use hash aggregation.

Most other aggregate functions seem to force a SORT GROUP BY operation:

  • User-defined aggregate functions
  • The FIRST and LAST aggregates (e.g., MAX(...) KEEP (DENSE_RANK FIRST ORDER BY ...)), because they have an explicit ORDER BY in them that cannot be removed.
  • LISTAGG (as @MK01 has shown)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.