Re: SQL*ReportWriter order by clause

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! Received on Sat Sep 26 1992 - 09:54:59 CEST