Re: SQL*ReportWriter order by clause

In article <1992Sep25.235459.1_at_us.oracle.com> sstephen_at_us.oracle.com writes:
> In article <1992Sep25.170736.2535_at_deepthought.uucp>, Neil Greene
 <deepthought!neil_at_ms.uky.edu> writes:
> > I have an oracle database with approximatley 500,000+ records
 consisting
> > of lname, fname, mname, ss, birthdate.
> >
> > I would like to sort these records in lname, fname order. However,
> > sorting this many records in this fashion seems to take forever.
 Closer
> > magnitude of non-existant.
> >
> > Is there some way I may better optimise the use of indexes?
> >
> > I have the following index, but things are still S_L_O_W:
> >
> >> create index I_MASTER$FLNAME on
> >> MASTER(LNAME, FNAME)
> >> TABLESPACE NASRIS_INDEX
> >> PCTFREE 10
> >> storage (initial 10M next 1M
> >> maxextents 99
> >> pctincrease 0);
>
> I don't think storage is going to effect performance until after you
 start
> updating the LNAME and FNAME columns of your table.
>
> The trick is not to sort the records at all! If you have an index AND a
> sort clause it has to sort the records all over again when the index had
 been
> keeping everything in sorted order all this time for you anyway. So,
>
> Select LNAME,FNAME,MNAME,SS,BIRTHDAYE
> from MASTER
> order by LNAME,FNAME;
>
> -- is going to be slow.
>
> Select LNAME,FNAME,MNAME,SS,BIRTHDAY
> from MASTER
> WHERE LNAME >= CHR(1);
>
> -- should be about as fast as you can go, (as long as it does a long
 index
> scan). As an example, here is a quick check against a sample database
 chosen
> at random :
>
> Select count(*) from SYS.COL$ order by OBJ#;
>
> real: 0.6100
>
> Select count(*) from SYS.COL$ where OBJ# >= 0;
>
> real: 0.2300
>
> Almost 3 times as fast. Of course, this is taking advantage of some
 internal
> knowledge, but it should be good enough for faster reports. And a
 disclaimer
> ... individual results may vary!

Adding a WHERE clause improved performace considerably. Now, records are found instantly, sorted too. Received on Sun Sep 27 1992 - 01:50:36 CEST