Re: how to tune up this query?

"Richard Foote" <richard.foote_at_bigpond.com> wrote:
> "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,

Nope, not true. At least not for me on 9iR2:

SQL> create table xxxx as select dbms_random.random() as id, mod (dbms_random.random(),20) as name, dbms_random.random()

  2     as priority from (select 1 from dba_objects where rownum<=1000) a,
  3                      (select 1 from dba_objects where rownum<=3000) b;

Table created.

SQL> select id, name, priority from xxxx where name=19 and rownum<=10 order by priority ASC;

        ID NAME PRIORITY

---------- ---------- ----------
1519272062         19 -1.976E+09
 573875120         19 -1.228E+09
-990884459         19 -283503762
1148176511         19 -102043359
-1.399E+09         19  -41345040
2019382837         19  293262092
 625069607         19  296978727
1863923849         19  624093090
-1.678E+09         19 1289321138
 887976565         19 1735933823

10 rows selected.

SQL> select id, name, priority from
  2 (select id, name, priority from xxxx where name=19 order by   priority ASC) 3 where rownum<=10;

        ID NAME PRIORITY

---------- ---------- ----------
1934494732         19 -2.147E+09
1176016854         19 -2.147E+09
 395828885         19 -2.147E+09
1014609463         19 -2.147E+09
-267833935         19 -2.147E+09
-1.393E+09         19 -2.147E+09
1545957380         19 -2.147E+09
 -73447598         19 -2.147E+09
-493059521         19 -2.147E+09
1515521083         19 -2.147E+09

10 rows selected.

The first query clearly grabbed the first 10 rows and then sorted them. Rownum in the where clause does not appear to be special such that it applied after the "order by" is done.

> 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.

Assuming (subjunctively) that rownum were special, I still see no reason the CBO couldn't notice and apply the same optimization to the above query as it does to the inline view. In fact, I'd think it would be easier to recognize the possibility of a stop-key sort if you don't have to look across select levels to see it. I'm not to say it couldn't happen, but if it did I'd think it a bug.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB