Re: Behavior of indexes after run a CLRPFM

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