On 1/24/11 3:53 AM, Rui Feliz wrote:
On Thu, Jan 20, 2011 at 10:12 PM, CRPence wrote:
On 1/20/11 2:22 AM, Rui wrote:
<<SNIP>>
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.
So there is an existing plan which was created [for a SELECT on that
TABLE] before the CLRPFM was issued, and the concern is that the INDEX
previously utilized to implement the query [before the "clear"] is no
longer being utilized after the "clear"? Review the monitor for the
reason the plan changed\rebuilt, and for the reason the index was not
chosen if the plan was rebuilt.
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"?
An attempt to utilize the plan is apparently made after the clear,
but is that before or after the load of the data? If before loading
data, then there is no reason to use an index to implement the query,
and so a plan rebuild is likely considered worthwhile to avoid
redirection to the index. If after loading the data, then any value in
continuing to access the data via the index is mostly dependent on the
cardinality of the data for selection and utilization as join keys.
Since one of the features of the optimizer is to recognize that a
dataspace is "in memory", a freshly populated file may be more
efficiently accessed sequentially than via the random access of an
index, just because the data is already paged; similarly a "small" set
of data can be easily paged and processed sequentially more efficiently
than accessing random pages according to the ordered entries processed
via the index.
The access paths [indexes] were updated by the clear and then by the
INSERT activity. Data statistics are discarded by the clear, for both
the dataspace and the access path, and then maintained anew for the
index since and while both new and changed data appear in the key;
although I am not sure about if or what of the historical statistics
would also be discarded, and IIRC the dataspace statistics are only
updated in the background [according to some aggressiveness setting;
QDBFSTCCOL system value?]. I seem to recall the iNav database feature
presented some statistics, and if so, those could be compared before and
after the CLRPFM.
When an existing and [what was once a] valid plan is referenced for
use, a minimal amount of plan "validation" will occur to determine if
the plan can be utilized as defined, perhaps modified, or entirely
discarded such that a new plan must be rebuilt. There are many rules in
the validation, with regard to both function and optimization, whether
the plan will be discarded; e.g. a stored pointer to an access path that
defined the plan for data access requires a rebuild, or perhaps a
fall-back to alternate access as defined within that plan for just that
situation [or per a different change in environment, such as for reduced
memory available compared to when the original plan was created, for
which that fall-back plan might exist].
Unless [and in some specific scenarios even if] there is some
significant degradation in the query having chosen to implement without
the index as compared to the "same" [thus the gray area, since
environment matters beyond just the SQL statement] query implemented
with the index, then there is nothing specifically wrong with the optimizer.
<<SNIP>>
An appropriate reaction to the described situation is typically to
allow the optimizer to make its decisions on what plan to follow and for
which access path(s) to utilize, and not to be concerned about the
choices made by the optimizer unless\until there is some problematic
degradation effected by some poor decisions of the optimizer. That is,
while one may infer their INDEX is best for implementing the query, that
is not their decision; with only a limited ability to influence.
Note: One means to "influence" the optimizer in its decision making
is to add the OPTIMIZE FOR number-of ROWS clause. Choosing a small
number "encourages" the optimizer to look more thoroughly for an
appropriate index to locate the specific small number of records
fastest, without regard to how long continued fetching of a
significantly larger percentage of the rows might take while using that
same [possibly poor] access path.
Regards, Chuck
midrange.com
