Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Performance Question:Forced sortign Vs. MIN/MAX?

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

From: Stephen T. Parfitt <steve.parfitt_at_sympatico.ca>
Date: Wed, 28 Aug 2002 21:20:04 -0400
Message-ID: <3D6D76C4.CEDD57B5@sympatico.ca>


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 Wed Aug 28 2002 - 20:20:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US