Re: SQL question: GROUP BY and MAX

In article <MPG.1575b08d1ad51125989c3f_at_mayonews.mayo.edu>, Paul Tiseo says...
>
>In article <9eg8q3$np8$1_at_news.tue.nl>, hidders_at_REMOVE.THIS.win.tue.nl
>says...
>> SELECT a
>> FROM tabA
>> GROUP BY a
>> HAVING COUNT(*) = 5;
>>
>> This is equal to:
>>
>> SELECT DISTINCT a
>> FROM tabA r1
>> WHERE ( SELECT COUNT(*)
>> FROM tabA r2
>> WHERE r1.a = r2.a ) = 5;
>
> Would there/should there be performance differences, geenrally
>speaking? or, would most advanced db's SQL optimizers reduce it to the
>same operations? Thanks.
>--

Not today. Here is Oracle816, first statement:

Plan Table

| Operation                 |  Name    |  Rows | Bytes|  Cost  |
 ----------------------------------------------------------------
| SELECT STATEMENT          |          |   194 |    6K|    401 |
|  FILTER                   |          |       |      |        |
|   SORT GROUP BY NOSORT    |          |   194 |    6K|    401 |
|    INDEX FULL SCAN        |HZ_PARTIE |  128K |    4M|    401 |
----------------------------------------------------------------

Second one:

Plan Table

| Operation                 |  Name    |  Rows | Bytes|  Cost  |
 ----------------------------------------------------------------
| SELECT STATEMENT          |          |    1K|   41K |    406 |
|  SORT UNIQUE NOSORT       |          |    1K|   41K |    406 |
|   INDEX FULL SCAN         |HZ_PARTIE |    1K|   42K |    401 |
|    SORT AGGREGATE         |          |     1 |   34 |        |
|     INDEX RANGE SCAN      |HZ_PARTIE |     7 |  238 |      1 |
----------------------------------------------------------------

Please ignore the miniscule cost difference -- the actual times are 0.68 vs. 2.4 sec on a table with 100K records.

In short, cost optimisers are not that bad at reducing the cost, but are poor when it comes to figuring out semantically equivalent sql expressions. Received on Sun Jul 22 2001 - 01:25:46 CEST