Oracle listagg forces SORT (GROUP BY) execution plan

oracleperformance

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?

Best Answer

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
Related Question