The determining as to which will run faster are a bit too complex to say that one syntax will always run faster than the other. However, using DISTINCT is better SQL and the fact that the query about which this question was asked runs faster using a GROUP BY makes me think that there are partitioning or index issues.
I ran similar queries to the 2 Gordon lists below and actually got identical explain plans on them on one table and different plans on another table. One was range partitioned and one was not. The fact remains though, that the GROUP BY clause was not the problem in the original post and if the $Table variable had been assigned a valid value, it would have worked fine. On Fri, 2003-01-10 at 10:54, Ronald J Kimball wrote: > On Fri, Jan 10, 2003 at 10:25:43AM -0500, [EMAIL PROTECTED] wrote: > > I just tried this command in Oracle: > > select status_flag from status_table group by status_flag > > > > and it produced the same output as > > select distinct status_flag from status_table > > > > I suspect, though I haven't confirmed this, that the two SQL commands are > > optimized to the same strategy by Oracle. I'm not really sure why you would > > want to use GROUP BY rather than a DISTINCT clause here. (This is under > > Oracle 8.1.7, so YMMV with other databases.) > > With Oracle 8.1.7, using EXPLAIN PLAN for two queries like the above shows > that they are not actually optimized to the same strategy; the former still > does a GROUP BY. > > I agree, it does not make sense to use GROUP BY here instead of > DISTINCT/UNIQUE. The two queries will return the same results, but using > DISTINCT/UNIQUE will be faster. > > > A similar mistake which I have seen made is to put conditionals in the > HAVING clause that should be in the WHERE clause instead. For example: > > SELECT status, MAX(id) > FROM my_table > GROUP BY status > HAVING status IN (1, 2) > > should be > > SELECT status, MAX(id) > FROM my_table > WHERE status IN (1, 2) > GROUP BY status > > > Ronald -- Jeff Seger <[EMAIL PROTECTED]> Fairchild Semiconductor
