Re: how to tune up this query?

"Thomas Niering" <thomas.niering_at_arcor.de> wrote in message news:8eq51sKYXzB.thomas.niering_at_arcor.de...
>
> Hallo Susan,
>
> > I have a 3 million records table. I want to retrieve the first
> > 1000 records of the lowest number of priority.
>
> You need no subquery:
>
> > SELECT id, name, priority
> > FROM table_name
> > WHERE stage = 'start' and state = 1
> > and rownum <= 1000;
>
> Ciao Thomas
>

Hi Thomas,

True, but unfortunately your example is potentially significantly less efficient and somewhat a backward step.

In your example, Oracle has to retrieve all the data matching the selection criteria, *sort all the data* and return the first 999 rows of interest. If 10,000,000 rows match the predicates, the sort requires a substantial amount of work, possibly an inefficient disk sort.

In Susan's example, Oracle has to retrieve all the data matching the selection criteria but as it does so keeps track of only those rows that correspond to the bottom 999 rows of interest, ie it looks at the outer predicate, notes only 999 rows are of interest and as it builds the inline view only keeps those rows that currently are ranked in the bottom (or top if DESC) 999. After retrieving the required data, it then only has to sort the 999 rows which is potentially far fewer rows than 10,000,000 and is able to do so with an efficient memory sort.

As mentioned by other wise souls, the order by looks suz in it's own right but who's to say what bottom-n analysis is required.

Cheers

Richard Received on Thu Jan 30 2003 - 06:53:53 CST