Registering Strategy and Support Functions

Home | Previous Page | Next Page Developing an Access Method > Specifying an Operator Class >

Registering Strategy and Support Functions

Issue a separate CREATE FUNCTION statement for each operator-class function. Do not issue the CREATE FUNCTION statement for any built-in function or user-defined function that is already registered in the sysprocedures system catalog table.

Warning:

Include the NOT VARIANT routine modifier for each operator-class function, or the optimizer might ignore the virtual index and scan the underlying table sequentially instead.

Making a Function Nonvariant

A nonvariant UDR exhibits the following characteristics:

The function always returns the same result when invoked with the same arguments.
In the sysprocedures system catalog table entry for the UDR, the variant column contains the value f (for false).
The CREATE FUNCTION statement inserts a description of the strategy function in the sysprocedures system catalog table. By default, the variant column of the sysprocedures system catalog table contains a t (for true), even if that function invariably returns equivalent results. When you create a function with the NOT VARIANT routine modifier, the database server sets the sysprocedures variant indicator for that function too.

If you do write strategy or support functions, specify the NOT VARIANT routine modifier in the CREATE FUNCTION statement and ensure that the database server recognizes them as nonvariant.

Tip:

Create the UDR as NOT VARIANT only if it really is not variant.

By contrast, a variant UDR exhibits the following characteristics:

In the sysprocedures system catalog table entry for the UDR, the variant column contains the value t (for true).
Because the CREATE FUNCTION statement for the function did not specify the NOT VARIANT routine modifier, the variant column contains the default value.
Each execution of a variant function with the same arguments can return a different result.

Warning:

Always specify the NOT VARIANT routine modifier in the CREATE function statement for an operator-class strategy function. If the variant column for a strategy function contains a t, the optimizer does not invoke the access method to scan the index keys. Instead, the database server performs a full table scan.

In the following example, the FileToCLOB() function returns variable results. Therefore, the optimizer examines every smart large object that the reports file references:

SELECT * FROM reports WHERE
   contains(abstract, ROW("IFX_CLOB",
   FileToCLOB("/data/clues/clue1.txt","server")
      ::lld_lob,NULL::LVARCHAR),

Granting Privileges

By default, the database server grants Execution privilege to the generic user public when you register a UDR. However, if the NODEFAC environment variable overrides default privileges in a database, you must explicitly grant Execution privilege to SQL users of that database. The following statement grants Execution privilege to all potential end users:

GRANT EXECUTE ON FUNCTION strategy_function TO PUBLIC

For more information, about Execution privileges, refer to the CREATE FUnCTION and GRANT statements in the IBM Informix: Guide to SQL Syntax. For more information about environment variables, refer to the IBM Informix: Guide to SQL Reference.