Revising the Estimated Row Count for a Table

ALTER TABLE Statement

To change the estimated row count on the table definition, use the SQL DDL ALTER TABLE statement with the ESTIMATED NUMBER OF ROWS option.

Considerations

Changing the estimated number of rows for a table will not affect default index sizing unless you drop and re-add the index or referential constraint. The estimated number of rows is used for index calculations only if it is greater than the NUMROWS column in SYSCA.TABLE. NUMROWS is updated whenever an UPDATE STATISTICS utility statement is issued for the table or the table's area.
Note: For more information about index calculations, see the CA IDMS SQL Reference Guide.
Changing the estimated row count may affect the access paths chosen by the access module compiler for SQL DML statements that reference the table. Unlike other table modifications, though, changing the estimated row count will not cause existing access modules that reference the table to be automatically recompiled. If recompilation of selected access modules is desired, you must use the ALTER ACCESS MODULE statement to force reoptimization.
Note: Estimated number of rows is used for optimization purposes only if the NUMROWS column of SYSCA.TABLE is 0.

Example

In the following example, the estimated row count for the EMPLOYEE table is revised.

alter table emp_employee
   estimated row count 750000;