RE: SQL order by question

[Gary Lehman <Gary_Lehman@xxxxxxxxxxxxxx>]

Forgive me for sounding sort of ignorant, but when you say build the access
paths do you just mean rebuilding the logicals?  If so, if you build them in
order of least used to used the manager knows this?

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




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!

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