From: andy@homebase.vistachrome.com (Andy Finkenstadt)
Subject: Re: SQL*ReportWriter order by clause (PLEASE HELP)
Reply-To: andy@homebase.vistachrome.com
Organization: Vista-Chrome Incorporated
Date: Sat, 26 Sep 1992 03:09:25 GMT

Neil Greene <deepthought!neil@ms.uky.edu> writes:
>In article <1992Sep25.170736.2535@deepthought.uucp> Neil Greene  
><deepthought!neil@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@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@vistachrome.com

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