For Application Developers

Informix Application Development - Getting Started

IBM Informix Developer's Handbook

Embedding IBM Informix

Don't miss this

Videos

Are your statistics (distributions) are really up to date ? - PART II

So what we know in Part I is that "Statistics updated." does not always mean that your statistics are up to date.
Sometimes it means: "Statistics are up to date and not changed - STATLEVEL threshold is not reached".

Lets look at the explain plan to verify, that your statistics are up to date or not:

Statistics are updated in sysdistrib after a little bit more than 10% of data has changed (via UPDATE).
In fact USTH changed sysdistrib data after 14.28% of data updated (we have 28 rows and 4 updates).
Maybe USTH changes sysdistrib only if more than % STATCHANGE rows are changed (4 rows).
10% of 28 rows == 2.8 rows this is rounded 3 rows. From the manual STATCHANGE is a threshold.
So it seems that threashold means more than STATCHANGE number of rows changed.

From the manuals:

"...the UPDATE STATISTICS statement compares the STATCHANGE setting with the
percentage of rows that have changed in each table or fragment since the current
data distributions were calculated, and selectively updates only the missing or stale
distribution statistics for each table or fragment within the scope of the UPDATE
STATISTICS statement."

"When the UPDATE STATISTICS statement runs
in MEDIUM or HIGH mode against the table, the database server compares the
stored values in these columns with the current values in the partition. Column
distribution statistics for the table are not updated if the sum of the stored values
differs from the sum of these current sysdistrib DML counter values from the
partition page by less than the threshold specified by the setting of the
STATCHANGE table attribute or of the STATCHANGE configuration parameter."