Enterprise Edition Home | Express Edition Home | Previous Page | Next Page   Improving Individual Query Performance > Updating Statistics >

Updating Statistics for Columns with User-Defined Data Types

Because information about the nature and use of a user-defined data type (UDT) is not available to the database server, it cannot collect the colmin and colmax column of the syscolumns system catalog table for user-defined data types. To gather statistics for columns with user-defined data types, programmers must write functions that extend the UPDATE STATISTICS statement. For more information, see the performance chapter in IBM Informix: User-Defined Routines and Data Types Developer's Guide.

Because the data distributions for user-defined data types can be large, you can optionally store them in an sbspace instead of the sysdistrib system catalog table.

To store data distributions for user-defined data types in an sbspace
  1. Use the onspaces -c -S command to create an sbspace.

    To ensure recoverability of the data distributions, specify LOGGING=ON in the -Df option, as the following sample shows:

    % onspaces -c -S distrib_sbsp -p /dev/raw_dev1 -o 500 -s
          20000
       -m /dev/raw_dev2 500 -Ms 150 -Mo 200 -Df
          "AVG_LO_SIZE=32,LOGGING=ON"

    For information on sizing an sbspace, see Estimating Pages That Smart Large Objects Occupy.

    For more information about specifying storage characteristics for sbspaces, see Configuration Parameters That Affect Sbspace I/O.

  2. Specify the sbspace that you created in step 1 in the configuration parameter SYSSBSPACENAME.
  3. Specify the column with the user-defined data type when you run the UPDATE STATISTICS statement with the MEDIUM or HIGH keywords to generate data distributions.

To print the data distributions for a column with a user-defined data type, use the dbschema -hd option.

Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]