Re: SQL order by question

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxx>]

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?

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.

> 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!

begin:          vcard
fn:             R. Bruce Hoffman, Jr.
n:              ;R. Bruce Hoffman, Jr.
adr:            991 Lucon Road;;;Schwenksville;PA;19473;USA
email;internet: rbruceh@ibm.net
title:          Independant AS/400 Consultant
tel;work:       610-222-9707
tel;fax:        610-222-9717
x-mozilla-cpt:  ;0
x-mozilla-html: FALSE
version:        2.1
end:            vcard