Re: how to tune up this query?

She does need to do this sort of thing if she wants to use ROWNUM since otherwise she can't pull out the first 1000.

Looking at it , it's retrieving 5 fields ; unless all 5 are indexed (and even then I'd expect it to go for FTS)

then we're probably looking at

FTS
ORDER BY
COUNT STOPKEY using trad methods .

However even for 3E6 row FTS and assuming not too wide this should peg along fairly quickly although I wouldn't wait for it on a web page.

Alternative approach use analytic functions (DENSE_RANK springs to mind) but IMHO the same amount of data needs crunching tho'.

Tkprof and let's see the plan and waits....

"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
>
Received on Thu Jan 30 2003 - 05:40:52 CST