Re[2]: SQL order by question

     Were there a lot of logicals over this PF?  The query optimizer has 
     a limit on how long it will search for useful access paths. If too 
     many access paths exist, the optimizer will time out and continue 
     with the processing. By naming the logical file, you've given the 
     query optimizer a better starting point, and allowed the optimizer 
     to build a solution much faster.
     
     eric.delong@pmsi-services.com


______________________________ Reply Separator _________________________________
Subject: RE: SQL order by question 
Author:  <MIDRANGE-L@midrange.com > at INET_WACO
Date:    1/20/99 7:58 AM


>First, don't declare the cursor over the logical.  Declare it over the
physical.
Don't try to paint the picture explicitly for the database manager.  If you
declare the cursor over the physical file and ask for the order of SSN, DATE
and
PRODUCT, and a logical exists in that sequence, and no other reason exists
to
produce another index, the database manager will use the *existing* index
and
*not* build a new one.<

Ok there's something I don't understand.  I had been doing a DECLARE CURSOR
over a physical and then changed it to a logical that was sorted in the same
way I was doing my ORDER BY and the processing time of my program was cut
dramatically.  What would be the reason for this?

-----Original Message-----
From: R. Bruce Hoffman, Jr. [mailto:rbruceh@ibm.net]
Sent: Tuesday, January 19, 1999 12:37 PM
To: MIDRANGE-L@midrange.com
Subject: Re: SQL order by question




Gary Lehman wrote:

> you do a DECLARE CURSOR on a logical that is in say SSN, DATE, PRODUCT
order
> and then do a order by SSN, DATE, PRODUCT order does that cause it to do
> more work to create an extra index??  If the logical is already in that
> order is it detrimental to do an order by with the same fields?


> Also do you specify a FETCH ONLY on a DECLARE CURSOR if you're not doing
any
> updating and you want the time it takes to build the table to be less??

IMHO, I think it best to *always* declare you intentions in the program.  As
for
the question of less time, yes, it may take less time if, and only if, it
can
find an *existing* access path to use.  If the database manager has to
create an
access path, it will take the time it takes to build it.  In an update
situation,
there may be reasons for the existing access path to *not* be used.

--
===========================================================
R. Bruce Hoffman, Jr. -- IBM Certified AS/400 Administrator

-- The sum of all human knowledge is a fixed constant.
    It's the population that keeps growing!

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---



+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---