Re: SQL order by question

[pytel@xxxxxxxxxx]

Optimizer limitation on access paths evaluation exists only for interactive
jobs.
The reason is (was) that for interactive ad-hoc queries time to first
screen of response should be minimized at expense of better optimization
even at the risk of actually increasing this response time. It's a matter
of statistics, of course. What is considered a rare and heavy query for one
user is an everyday routine for somebody else.

During CRTSQLxxx and in batch environment there's no such limitation, so
all access paths will be considered regardless of their number.
Since ODBC server is nominally a batch job, I think ODBC queries should not
suffer from this limitation.

Best regards
    Alexey Pytel



DAsmussen@aol.com on 01/21/99 12:58:54 PM

Please respond to MIDRANGE-L@midrange.com

To:   MIDRANGE-L@midrange.com
cc:    (bcc: Alexei Pytel/Rochester/IBM)
Subject:  Re: SQL order by question





Gary,

In a message dated 1/21/99 9:32:00 AM Eastern Standard Time,
Gary_Lehman@mail.mchcp.org writes:

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

Yes, the manager knows in what order the logicals were built and searches
the
first n (someone posted the new figure a while back, I don't recall) of
them
in date order before deciding to build its own.  This brings up an
interesting
question that perhaps someone listening in at IBM could answer.  Why?  Oh,
I
understand the original logic -- a well-designed database shouldn't have
that
darned many logicals to search in the first place.  Those of us trying to
run
third-party packages (e.g., BPCS ships with over 30 logicals against the
inventory transaction history file, most of which are _USELESS_ for
anything
other than MRP) have rarely seen a well-designed database.

With access path evaluation taking so little time in comparison to building
a
new access path over a large file, why not remove the access path
consideration restriction or at least provide a system value?  I too am a
proponent of specifying the physical file in an SQL statement, but this can
cause problems in tuning should you recompile a logical for performance and
then have the aforementioned n logicals recompiled for a different reason.
Programs that once ran well suddenly start dying, and nobody knows why
because
the guy/girl that recompiled the original logical for performance didn't
document it adequately and has since taken that $30K signing bonus to work
somewhere else.  With the advent of data warehousing, client/server, and
terabyte storage capabilities, why do we still have this antiquated access
path evaluation restriction?

Curious,

Dean Asmussen
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC  USA
E-Mail:  DAsmussen@aol.com

"Success is not the result of spontaneous combustion.  You must first set
yourself on fire." -- Fred Shero
+---
| 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
+---