Re: which is faster?

Hi Mary,

Actually you have to answer these questions by looking at the explain plan. I wrote a utility to do this. It's called Explain and it can be found at

http://members.aol.com/gjlinker

Applying Explain to your question and taking the ename field of the EMP table as an example I get the following:

SELECT MIN(ENAME) FROM EMP: SORT AGGREGATE
     TABLE ACCESS FULL EMP SELECT ENAME FROM EMP ORDER BY ENAME ASC: SORT ORDER BY
     TABLE ACCESS FULL EMP In both cases the full table access results are processed by the first operation. That leaves the question what is faster a Sort Aggregate or a Sort Order by

My guess would be that the Sort Aggregate is slower since it has to group the duplicate records together to see which unique value is the lowest. The Sort Order by will not group the results and in fact will not even strip out the duplicates. Hence I think the SELECT ENAME FROM EMP ORDER BY ENAME ASC is faster.

Regards, Gerrit-Jan Linker
Author of DataEntry, Explain, Oraxcel, OraCodes, OraWeb and OraSQL http://members.aol.com/gjlinker

In article <36A76490.3E6DADD1_at_globeset.com>, Mary Orcutt <mary_at_globeset.com> writes:

>I was wondering which of the following would be more efficient?
>
> select min(col1) from table1
>or
> select col1 from table1 order by col1 (and just get the first
>record)
>
>The first is what we started with and seems really slow with a
>table of a thousand records. col1 is a varchar(40) field.
>
>Thanks for any help.
>Mary
>
Received on Thu Jan 21 1999 - 12:26:28 CST