Performance considerations of UPDATE STATISTICS statements

The more specific you make the list of objects that the UPDATE STATISTICS statement examines, the faster it completes execution. Limiting the number of column distributions speeds the update. Similarly, precision affects the speed of the update. If all other keywords are the same, LOW works fastest, but HIGH examines the most data.

For version 11.70 and later of the HCL OneDB™ database server, the AUTO_STAT_MODE setting can improve the efficiency of UPDATE STATISTICS operations that refresh data distribution statistics. This enables the database server to selectively recalculate only the table or fragment distributions that have become stale as a result of DML operations since the statistics were last calculated, as determined by a change threshold that an explicit or default STATCHANGE table attribute defines. For information about how to set STATCHANGE and how to enable the automatic mode of UPDATE STATISTICS for refreshing only stale distribution statistics, see these topics:

The USTLOW_SAMPLE environment option enables sampling during the gathering of index statistics for UPDATE STATISTICS operations in LOW mode. For an index with more than 100 K leaf pages, the gathering of statistics using sampling can increase the speed of the UPDATE STATISTICS operation.

Examples of UPDATE STATISTICS statements

UPDATE STATISTICS MEDIUM;
UPDATE STATISTICS MEDIUM RESOLUTION 10;
UPDATE STATISTICS MEDIUM RESOLUTION 10 .95;
{ RESOLUTION 10, CONFIDENCE .95}
UPDATE STATISTICS MEDIUM RESOLUTION 10 DISTRIBUTIONS ONLY;
UPDATE STATISTICS MEDIUM RESOLUTION 10 .95 DISTRIBUTIONS ONLY;

UPDATE STATISTICS HIGH;
UPDATE STATISTICS HIGH RESOLUTION 10;
UPDATE STATISTICS HIGH RESOLUTION 10 DISTRIBUTIONS ONLY;

Resolution must be greater than 0.005 and less than or equal to 10.0. Confidence must be in the range [0.80, 0.99] (inclusive).

Examples that follow are based on the company_proc procedure and square_w_default function, as defined below:

CREATE PROCEDURE company_proc ( no_of_items INT,
        itm_quantity SMALLINT, sale_amount MONEY,
        customer VARCHAR(50), sales_person VARCHAR(30) )
SPECIFIC spec_cmpy

DEFINE salesperson_proc VARCHAR(60);

-- Update the company table
INSERT INTO company_tbl VALUES (no_of_items, itm_quantity,
        sale_amount, customer, sales_person);

-- Generate the procedure name for the variable salesperson_proc
LET salesperson_proc = sales_person || "." || "tbl" ||
        month(current) || "_" || year(current) || "_proc" ;

-- Execute the SPL procedure that the salesperson_proc
-- variable specifies
EXECUTE PROCEDURE salesperson_proc (no_of_items,
        itm_quantity, sale_amount, customer);
END PROCEDURE;


CREATE FUNCTION square_w_default
        (i INT DEFAULT 0) {Specifies default value of i}
RETURNING INT {Specifies return of INT value}
SPECIFIC spec_square
        DEFINE j INT; {Defines routine variable j}
        LET j = i * i; {Finds square of i and assigns it to j}
        RETURN j; {Returns value of j to calling module}
END FUNCTION;

The UPDATE STATISTICS examples that follow reference the company_proc procedure and square_w_default function:

UPDATE STATISTICS  FOR PROCEDURE;
UPDATE STATISTICS  FOR PROCEDURE company_proc1;
UPDATE STATISTICS  FOR PROCEDURE
   company_proc1(INT,SMALLINT,MONEY,VARCHAR(50), VARCHAR(30));
UPDATE STATISTICS FOR SPECIFIC PROCEDURE spec_cmpy;

UPDATE STATISTICS FOR FUNCTION;
UPDATE STATISTICS FOR FUNCTION square_w_default;
UPDATE STATISTICS FOR FUNCTION square_w_default(INT);
UPDATE STATISTICS FOR SPECIFIC FUNCTION spec_square;

For a discussion of the performance implications of UPDATE STATISTICS, see your HCL OneDB Performance Guide.

For a discussion of how to use the dbschema utility to view distributions created with UPDATE STATISTICS, see the HCL OneDB Migration Guide.