Re: RPG/SQL indexing... -- RPG400-L

There are (at least) two ways to implement your query:
- read all records in the order of existing index and evaluate the
selection predicate
- read all records in arrival order, evaluate the selection predicate and
create a (smaller) index, which will contain only entries which passed the
selection criteria (and which will be used to return records in requested
ORDER BY).
Optimizer chooses faster method, depending on file size, value
distribution, selectivity of a selection predicate, and other criteria.
This could explain different implementation of the same query in different
circumstances.

    Alexei Pytel
always speaking only on myself




                      dmosley@dancik.co
                      m                        To:       rpg400-l@midrange.com
                      Sent by:                 cc:
                      rpg400-l-admin@mi        Subject:  Re: RPG/SQL indexing...
                      drange.com


                      10/18/2002 12:34
                      PM
                      Please respond to
                      rpg400-l






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

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

So, now I'm torn between the ALWCPYDTA parameter value.

Thanks for the reply, Bruce

David L. Mosley, Jr.
Systems Analyst
2000 CentreGreen Way
Suite 250
Cary, NC 27513



                    "R. Bruce
                    Hoffman, Jr."            To:
<rpg400-l@midrange.com>
                    <rbruceh@attgloba        cc:
                    l.net>                   Subject:     Re: RPG/SQL
indexing...
                    Sent by:
                    rpg400-l-admin@mi
                    drange.com


                    10/18/02 01:23 PM
                    Please respond to
                    rpg400-l






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


> I've created a Dynamic SQL, and when executed, I can see that it creating
> an index, that already exists..
> Would anyone know why this is happening, and how I can get it to
> acknowledge the logical file with the index that it creates.


Why? Of course. The optimizer said don't use it.

For the details, well, we need a lot more information, what is the AP you
think it should use _really_ look like? What's the statement? Not a part of
it, the whole thing. What are the selection fields? Is there an order by? A
join? What makes you think it should use your index? What other indexes are
there? What's the data in the file look like? Whats the cardinality of the
fields in the select? etc, etc.



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

_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.