Edit Question - KnowledgeHub

Title Body

I'm getting poor performance from DISTINCT. The explain plan indicates that it is doing SORT (GROUP BY) which doesn't sound right. I would expect some kind of HASH aggregation to produce much better result. Is there a hint to tell oracle to use HASH for DISTINCT rather than sort? I've used /*+ USE_HASH_AGGREGATION */ in similar situations, but it is not working for DISTINCT. So this is my original query: SELECT count(distinct userid) n, col FROM users GROUP BY col; users has 30M rows, each userid is there 12 times. This query takes 70 seconds. Now we rewrite it as SELECT count(userid) n, col FROM (SELECT distinct userid, col FROM users) GROUP BY col And it takes 40 seconds. Now add the hint to do hash instead of sort: SELECT count(userid) n, col FROM (SELECT /*+ USE_HASH_AGGREGATION */ distinct userid, col FROM users) GROUP BY col and it takes 10 seconds. If somebody can explain to me why this is happening or how I can beat the first simple query into working as good as the 3rd one, that would be fantastic. The reason I care about query simplicity is because these queries are actually generated. Plans: 1) Slow: ---------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | Used-Tmp| -------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 5 |00:01:12.01 | 283K| 292K| | | | | | 1 | SORT GROUP BY | | 1 | 5 | 5 |00:01:12.01 | 283K| 292K| 194M| 448K| 172M (0)| 73728 | | 2 | TABLE ACCESS FULL| USER

Tags (comma-separated) Cancel