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?
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
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
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
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:
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)