Re: Optimizer NOT using available indexes -- MIDRANGE-L

Fernando

You've taken care of the first suggestion - to have an index whose leftmost columns match your selection criteria. The optimizer will use table scan when it thinks it will return more than 20% of the records. The index is used to determine the number of records that will be returned. You can see how many this is by running

select count(*) from myFileLxx where Field1 = xxx and Field2 = yyy

Do you have SMP (Symmetric Multiprocessing) on your system? This will tend toward parallel table scan when more than 10% of records will be returned. I could not see a message that specifically said this method would be used instead of a simple table scan.

The optimizer messages are all (IIRC) in the SQL40xx range in QSQLMSG.

You could try creating EVI indexes over each column, but I don't think this will help. They are used for selection and are most beneficial, it seems, when an OR is in the mix.

Be sure there is no need for data conversion between the fields and the constants you specify.

You could try adding an OPTIMIZE FOR n ROWS clause - a low value for "n" should incline the optimizer toward using an existing index, at least according to the Database Performance and Query Optimization manual, which you should look at. Chapter 7 contains a lot of suggestions.

BTW, specifying a logical file does not mean it will be used - it might even slow down startup a little, I think, as it needs to find the related physical, then retrieve all of the indexes for that physical. Optimizer considers all the indexes it has time to do.

Also, there is a file that controls queries, called QAQQINI. Look for one in your library list and see if it has an entry for OPTIMIZATION_GOAL - a value of *ALLIO will be inclined not to use indexes, *FIRSTIO will. The OPTIMIZE FOR clause can be used to override this, but you do not have that in Query/400.

HTH
Vern

At 07:38 AM 11/25/2004, you wrote:

Have a performance problem on a iSeries I don't know how to address...
Running V5R2, lastest Cum and Group PTF's (DB, ...)
For some reason neither SQL nor Query/400 seem to honour available indexes (LFs) As a sample case, running a "Select * from myFileLxx where Field1 = xxx and Field2 = yyy" or same kind of search with a Query/400

will always use the Physical file (sequential file) even though there are some different (several...) logical files (LFs) that have fields Field1 and Field2 as the first keys! (plus other different fields in each case).

Trying same Select from either the Physical or from any of the mentioned Logicals produce same "sequential proccess" from the Physical! (to be exact, if Logicals used, then it points to "arrival sequence" for that Logical, which obviously means same for the Physical, isn't it?)

Running in Debug mode, in all cases I find the CPI432C message froom the Optimizer, saying "All available access paths have been examined" followed by the resulting code for each one of them. The interesting Logicals I would have expected to be used give a 4 result value, which means "The cost for using this access path as determined by the Optimizer would be greater than the selected method..." which in the end gives processing the Logical/Physical sequentially!
The Physical file has about 5,000,000 records.
The logicals I would have expected to be used have
- Field1 and Field2 as the first key fields
- NO select/omit at all, neither specific nor implied, nor Dynamic
Trying Select with "order by Field1, Field2" does not produce any difference.
Query Options QAQQINI file exists in QSYS and QUSRSYS, in both cases with al entries set to *DEFAULT values. Used file is QUSRSYS's.
System values:
- QQRYDEGREE    : *OPTIMIZE
- QQRYTIMLMT        : *NOMAX
Tried same selects thru Op'sNavigator running SQL scripts to look thru the Performance Supervisors, with same results.

Is there any obvious (or not so obvious...) reason for the Optimizer NOT using available indexes? If a file is being journalled with Commit/Rollback, does this prevent using an index?

The logicals I'm referring to are in good shape (not damaged or something like that...) since they are being used by normal programs running without problems!
I just cannot imagine any reason for this behaviour...
Any ideas will be appreciated! TIA
--
Antonio Fernandez-Vicenti
afvaiv@xxxxxxxxxx
--
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.