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

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.
>>
[ and don't forget the subject; SQL*REPORTWRITER. ]

>> Is there some way I may better optimize the use of indexes?
>>

[ and here in SQL*Plus ]
>When performing the following sql select things run fine:
>> select lname, fname from master order by lname
>[ output is quickly sorted ]
 

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

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

Well - there are three things here.

1. "select lname, fname from master order by FNAME"

Did you truly mean FIRST name here? This differs from your initial premise of sorting by last name, first name in reportwriter. What happens is - the database can't find an indexed whose LEADING portion is the column FNAME so it has to do a full table scan or other method of reading in the records you want (if you have a WHERE clause), write ALL that info into the sort_area buffer, which if its size exceeds the init.ora parameter sort_area_size (default 64K under Unix and probably most others) then it starts writing temporary records to the TEMPORARY TABLESPACE for the user ID the connection is running under, then do a sort-merge on the new temporary table to put them in FIRST name order, and THEN report it, finally.

If you mean LNAME instead of FNAME in the ORDER BY clause, then it is entirely possible that for some reason it is thinking it has to do a sort-merge to get the list in the right order. I can't think of why this would happen unless the tapespace NASRIS_INDEX were offline.

2: "select lname, fname from master order by lname"

This works fast in SQL*PLUS (and SQL*DBA and SQL*FORMS and usually Pro*C) because you are operating on the table MASTER itself.

3: but in Reportwriter the same "select" is SLO-O-O-W.

ReportWriter sometimes builds temporary tables to do some or all of its page layout and calculations. It reads records from the original tables and then writes them to the temporary tables, then does a select/order by on the temporary table which requires the full table scan sort-merge since no indexes are created on its "temporarily" created table.

Such is from my experience, and a judicious 'strings' of the sqlrep binary.

-Andy

-- 
Andrew Finkenstadt       | Vista-Chrome, Inc.   |           andy_at_GEnie
GEnie Unix Sysop/Manager | The Printing House   |    NIC Handle: AF136
+1 904 222 2639 home     | 1600 Capital Cir SW  |   ...!uunet!rde!andy
+1 904 575 0189 work     | Tallahassee FL 32310 | andy_at_vistachrome.com

     The game of love is never called off on account of darkness.