Field procedure examples

The following examples show various field procedure-related optimizations done by the SQE optimizer.

The examples show the FieldProc name along with the encoding (field procedure function code 0) or decoding (field procedure function code 4) in the pseudo-SQL. These codes indicate how the optimizer is optimizing the field procedure calls.

Given the following table and index:

CREATE TABLE T1 (col1 CHAR(10), col2 CHAR(10) FIELDPROC ‘FP1')
CREATE INDEX IX1 on T1(col2)

Example 1

A user query written as:

SELECT col1, col2 FROM T1 WHERE col2 = ‘abc'

Is represented by the optimizer as:

SELECT col1, FP1(4, col2)  FROM T1 WHERE FP1(4,col2) = ‘abc'

Note the FP1 with the decode operation around the COL2 references in the SELECT list and the WHERE clause.

Assuming the QAQQINI FIELDPROC_ENCODED COMPARISON is set to *ALLOW_EQUAL, *ALLOW_RANGE or *ALL:

The query optimizer rewrites the query as:

SELECT col1, ‘abc' FROM T1 WHERE col2 = FP1(0, ‘abc')

This rewrite allows the query optimizer to use the encoded index IX1 to implement the WHERE clause and only cause one invocation of the field procedure for the query.

Example 2

SELECT col2 FROM T1 ORDER BY col2

Is represented by the query optimizer as:

SELECT FP1(4, col2) FROM T1 ORDER BY FP1(4, col2)

The optimized version removes the FieldProc from the ORDER BY clause assuming that the field procedure QAQQINI option is set to *ALLOW_RANGE or *ALL:

SELECT FP1(4, col2) FROM T1 ORDER BY col2

Example 3

Select col2, COUNT(*) FROM T1 GROUP BY col2

Is represented by the query optimizer as:

Select FP1(4, col2), COUNT(*) FROM T1 GROUP BY FP1(4, col2)

The optimized version removes the field procedure invocation from the GROUP BY clause column col2, allowing it to group the encoded data and only run the field procedure once per group. The decoded grouped data is returned to the user. This optimization is done if the field procedure QAQQINI option is set to *ALLOW_RANGE or *ALL:

SELECT  FP1(4, col2), COUNT(*) FROM T1 GROUP BY col2

IS NULL/IS NOT NULL predicate does not require calling the field procedure field-decode option 4. The field procedure cannot change the nullability of the field.