>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 thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].