Re: SQL order by question

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

Gary Lehman wrote:

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

One possible reason could be the number of logical files.  If you have a large
number of logical files, then the optimizer gives up after looking for a good
logical if it thinks that further search time would be better spent building an
index.  The search order is actually in the reverse order of the building of the
access paths.

How the program is processing access paths can be discovered by placing the
program in debug and running it.  Then look in the joblog (make sure you produce
one if you are running the program in batch) for the messages about the access
path searches.  If you find that the search never gets to the access path that
makes sense and the database manager creates one, that is the problem.  The
solution is to delete the access paths and rebuild them from least used to most
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