Re: SQL*ReportWriter order by clause (PLEASE HELP)

In article <1992Sep25.181346.2835_at_deepthought.uucp> Neil Greene <deepthought!neil_at_ms.uky.edu> writes:
>In article <1992Sep25.170736.2535_at_deepthought.uucp> Neil Greene
><deepthought!neil_at_ms.uky.edu> writes:
>> I have an oracle database with approximately 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 optimize 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 have examined a little further my current indexes for the database. The
>table master is index on each column (fname, mname, lname, ss, birthdate).
>When performing the following sql select things run fine:

First off, your paragraph above is not consistent with the index creation you included earlier. I'm not quite sure how your database is set up, therefore.

Do you have 5 separate indexes or one concatenated index? It makes a big difference.

>> select lname, fname from master order by lname
>[ output is quickly sorted ]

Since you want them sorted in lname fname order I suggest one index. A bit of experimentation will tell you whether you want lname,fname or just lname. One idea is to find out how many duplicate lnames you have. If it isn't too many then don't use the concatenated index.

Also, try setting maxextents on the index to 1 and makeing the initial extent large enough for the entire thing.

>When performing the following sql select, things run into trouble:
 

>> select lname, fname from master order by fname
>[ output takes forever - literally never ]

This is consistent with your not having any index where fname is the first column. But do you want this query in your production system? It isn't the one you asked for initially. If you do want it then you need an index on fname.

>I have looked at the following tables for additional information
>ALL_INDEXES and ALL_IND_COLUMNS and everything looks correct for the index
>by fname.
 

>Should I recreate the index?

This is unlikely to help.

It really sounds like you need to sit down and list out all your indexes and see if you can figure out how the optimizer is doing things. If you have a DBA available you might want to ask about EXPLAIN. Mike
PS. I'm not in support and this is friendly advice, not Oracle Corp. helping you out.

-- 
-------------------------------------------------------------------------------
I am not an official Oracle spokesman.  I speak for myself and no one else.