Re: RPG/SQL indexing...

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

----- Original Message -----
From: <dmosley@dancik.com>
To: <rpg400-l@midrange.com>
Sent: Friday, October 18, 2002 1:34 PM
Subject: Re: RPG/SQL indexing...


>
> Wow, OK sorry for being viege..
>
> Well, I'm doing an SELECT * FROM ITEMFILE WHERE field1 LIKE '%VALUE%'  or
> field2 LIKE '%VALUE%'  ORDER BY field2.

Ok. So prepare yourself.... using a wildcard like that, the optimizer has
almost _no_ clue as to how many rows might be returned. In other words,
there's no stats on the system for those values, even with keys on the
field1 and field2. Now 'VALUE%' is a different story in a key. But since the
first character is wild.....

The order by will create the key on the result table, if it creates a
copy.... (coming up, see below)

> And I do have a logical with FIELD2 as the single key, over "ITEMFILE".
> After viewing the Optimizer's Debug Messages, I could see that a Temp
> Access Path was built over "FIELD2".
> When viewing the "Consideration" details, the reason code for not using
the
> logical, was "4", which states.
>        4 - The cost to use this access path, as determined by the
> optimizer, was higher than the cost associated with the chosen access
> method.
>
> Which doesn't make sense.

Sure it does, it would have to read the index for order, read the record,
test the field and then return the result. Indexed access is fast for one
record, not lots.

> But, to add to my mystery, is that I have this SQL running on two
identical
> systems.   One is picks up the logical, the other doesn't.
>

And that can be due to volumes of data. Small on one, large on the other...
statistics available... different keys on other system, evi's etc.

> Now, what I did try was changing the ALWCPYDTA parameter from *OPTIMIZE to
> *YES,  and it then picked up the logical correctly.
>

Yup, when you say yes, it assumes immediately that it can select the data,
output it and then do the index or sort on the output only. So the opimizer
"cuts and runs". NOW, as for the difference between the machines, add the
fact that one may be building an index and the other may be sorting.

> So, now I'm torn between the ALWCPYDTA parameter value.
>
> Thanks for the reply, Bruce
>

Your answer is in "which runs faster?"

This stuff is never exact, and as you can see, unless we have all the
information that the optimizer has (and it has a LOT of info on the 400) we
can usually only guess, guide and point, but never completely solve, unless
we sit in your chair....  <VBG>

good luck and keep experimenting!



===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"If you pick up a starving dog and make him prosperous,
  he will not bite you. This is the principal difference
  between a dog and a man."

    - Mark Twain