Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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
share|improve this answer
    
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
add comment

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)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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