Optimizing Queries for User-Defined Data Types

Queries that access user-defined data types (UDTs) can take advantage of the same performance features that built-in data types use:

• Indexes

  If a query accesses a small number of rows, an index speeds retrieval because the database server does not need to read every page in a table to find the rows. For more information, see Indexes on User-Defined Data Types.

• Parallel database query (PDQ)

  Queries that access user-defined data can take advantage of parallel scans and parallel execution. For information about parallel execution of user-defined routines, see the performance chapter in IBM Informix: User-Defined Routines and Data Types Developer's Guide.

  To turn on parallel execution for a query, set the PDQPRIORITY environment variable or use the SQL statement SET PDQPRIORITY. For more information about how to set PDQ priority and configuration parameters that affect PDQ, see Allocating Resources for Parallel Database Queries.

• Optimizer directives

In addition, programmers can write the following functions or UDRs to help the optimizer create an efficient query plan for your queries:

• Parallel UDRs that can take advantage of parallel database queries
• User-defined selectivity functions that calculate the expected fraction of rows that qualify for the function
• User-defined cost functions that calculate the expected relative cost to execute a user-defined routine
• User-defined statistical functions that the UPDATE STATISTICS statement can use to generate statistics and data distributions
• User-defined negator functions to allow more choices for the optimizer

The following sections summarize these techniques. For a more complete description of how to write and register user-defined selectivity functions and user-defined cost functions, see IBM Informix: User-Defined Routines and Data Types Developer's Guide.