Re: Performance Question:Forced sortign Vs. MIN/MAX?

There are quite a few "it depends", such as whether 'field' in indexed.

At a simple level, I would bet that (B) requires a scan+sort while (A) can be done with a just a scan.

Azaria Elburg wrote:
>
> Which method to use to retrieve the minimal entry from a large table?
>
> A. Use Oracle's MINIMUM function:
>
> select min(field)
> from table
> where conditions;
> or
> B. A forced sorting select with a where condition of ROWID<2?
>
> select field
> from (select field
> from table
> where
> conditions
> order by field )
> where ROWID < 2;
>
> The table is very large with many millions of rows. The conditions are
> complex (including a sub query of "not exists select 'X' from same
> table", to get only the most recent data for any given date. And of
> course the select is not from just one table, but a JOIN of three
> tables)
Received on Thu Aug 29 2002 - 03:20:04 CEST