× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 1/20/11 2:22 AM, Rui wrote:

Sometimes, when I run some SQL operations (like a SELECT), the SQL
Optimizer does not use the correct index.

There is not any one\specific "correct index". The optimizer may or may not choose to implement a query using one or more access paths. SQL does not support querying an INDEX, for which one might infer forcing the use of a specific INDEX might be possible.

Before I load the table, I run a CLRPFM over it.

Not sure what "load the table" means here, since there is no LOAD TABLE statement in the DB2 for i SQL. Presumably however, an SQL SELECT is implied.?

Can the CLRPFM be responsible for the non-use of the indexes?

The optimizer would normally not choose to use an index to implement a query of an empty table, because an INDEX as a path to the data is one level of indirection from the data; i.e. why look for data in an INDEX when the last known condition of the TABLE is zero rows.? The optimizer will automatically [re]build a new plan when the number of rows changes "significantly"; i.e. changes enough from what was known to the original plan, such that perhaps accessing the data via the INDEX versus sequentially, might now have some value with respect to the cost of accessing the separate INDEX object(s).

The RGZPFM fix it?

The RGZPFM is not allowed on an empty member; i.e. CPF2995 for "no data".

Regards, Chuck

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.