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