Re: Behavior of indexes after run a CLRPFM

*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.?*
**
I'm working on a migration process from one table to another one. Every time I
run the process for testing it, I clean the new table using a CLRPFM
instruction and then I insert the values using SQL Inserts.



*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).
*
So after run a CLRPFM over the table, it is certain that the plan is rebuild
and the information to the indexes are "updated"?

Rui

On Thu, Jan 20, 2011 at 10:12 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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