RE: Query optimizer tells me to build an access path that already exists

Why don't you just try it and let us know if the recommended index really
did improve performance.  It's looking (to me, anyway) like IBM prefers SQL
based objects over DDS based objects for processing queries, which seems to
fit what they've been telling us for years. All new database enhancements
are made exclusively to SQL, not the legacy database engine. New query
optimizers are exclusively SQL......  

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: rick.baird@xxxxxxxxxxxxxxx [mailto:rick.baird@xxxxxxxxxxxxxxx]
Sent: Thursday, March 25, 2004 7:05 AM
To: Midrange Systems Technical Discussion
Subject: Re: Query optimizer tells me to build an access path that
already exists



Clare,

No, I haven't created an index with the same keys as the physical.  Are you
saying that I should ALWAYS build an index (logical or sql) over the
primary key of a big file? That seems contrary to everything I've ever
known in 20 years of midrange development.

We don't have the sql product right yet (new box, June install).  But to
create a logical over the physical's primary key seems a little redundant,
don't you think?

I've seen lines of thought bandied about here and other forums where all
physicals should be un-keyed, with logicals built for any indices needed,
but the reasons for doing so weren't for performance.

Anyway, I'm trying to get this job to run faster.  I can't imagine that
actually following the QO's recommendation and creating a logical over the
same keys as the physical would do any good.  Do you think creating a join
LF would help?

Rick

-----original message-------
That's one of the indexes you ALWAYS want to build. The Optimizer may well
use it for statistics (data skew). Or it may want to use an index from
index
access method or whatever.
Or do you mean you have already created the index? That can happen too.
When
the optimizer looks for an index, it may use different criteria or time out
before it gets to the right one. These rules are different depending which
version of the OS/400 you have. Sometimes it searches fifo, sometimes the
other way, etc.
If the file is really huge you could also try creating an EVA index.
If you are on V5R2 there are now two different SQL Optimizers with
different
rules, and a new statistics gatherer.
If you're in the US, I believe IBM do workshops on this subject....

cheers,

Clare


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.