For many domain-specific operators, such as Contains(), the functional implementation has two options:

• If the operator is operating on a column or OBJECT attribute that has a domain index, the function can evaluate the operator by looking at the index data rather than the actual argument value.

  For example, when Contains(resume, 'Oracle') is invoked on a particular row of the MyEmployees table, it is easier for the function to look up the text domain index defined on the resume column and evaluate the operator based on the row identifier for the row containing the resume than to work on the resume text data argument.

• If the operator is operating on a column that does not have an appropriate domain index defined on it or if the operator is invoked with literal values (non-columns), the functional implementation evaluates the operator based on the argument values. This is the default behavior for all operator bindings.

To make your operator handle both options, provide a functional implementation that has three arguments in addition to the original arguments to the operator:

• Index context: domain index information and the row identifier of the row on which the operator is being evaluated

• Scan context: a context value to share state with subsequent invocations of the same operator operating on other rows of the table

• Scan flag: indicates whether the current call is the last invocation during which all cleanup operations should be performed

The function TextContains() in Example 9-15 provides the index-based functional implementation for the Contains() operator.

The Contains() operator is bound to the functional implementation, as demonstrated in Example 9-16.

The WITH INDEX CONTEXT clause specifies that the functional implementation can make use of any applicable domain indexes. The SCAN CONTEXT specifies the data type of the scan context argument, which must be identical to the implementation type of the indextype that supports this operator.

Oracle invokes the functional implementation for the operator if the operator appears outside the WHERE clause. If the functional implementation is index-based, or defined to use an indextype, the additional index information is passed in as arguments, but only if the operator's first argument is a column or object attribute with a domain index of the appropriate indextype.

For example, in the query SELECT Contains(resume, 'Oracle & Unix') FROM MyEmployees, Oracle evaluates the operator Contains() using the index-based functional implementation, passing it the index information about the domain index on the resume column instead of the resume data.

To execute the index-based functional implementation, Oracle sets up the arguments in the following manner:

• The initial set of arguments is identical to those specified by the user for the operator.

• If the first argument is not a column, the ODCIIndexCtx attributes are set to NULL.

• If the first argument is a column, the ODCIIndexCtx attributes are set up as follows.

  • If there is an applicable domain index, the ODCIIndexInfo attribute contains information about it; otherwise the attribute is set to NULL.

  • The rowid attribute holds the row identifier of the row being operated on.

• The scan context is set to NULL on the first invocation of the operator. Because it is an IN/OUT parameter, the return value from the first invocation is passed in to the second invocation and so on.

• The scan flag is set to RegularCall for all normal invocations of the operator. After the last invocation, the functional implementation is invoked one more time, at which time any cleanup actions can be performed. During this call, the scan flag is set to CleanupCall and all other arguments except the scan context are set to NULL.

When index information is passed in, the implementation can compute the operator value with a domain index lookup using the row identifier as key. The index metadata is used to identify the index structures associated with the domain index. The scan context is typically used to share state with the subsequent invocations of the same operator.

If there is no indextype that supports the operator, or if there is no domain index on the column passed to the operator as its first argument, then the index context argument is null. However, the scan context argument is still available, Thus, the operator can maintain state between invocations even if no index is used by the query.

An operator binding that computes ancillary data is called a primary binding. Example 9-18 defines a primary binding for the operator Contains().

This definition registers two bindings for Contains():

• CONTAINS(VARCHAR2, VARCHAR2), used when ancillary data is not required

• CONTAINS(VARCHAR2, VARCHAR2, NUMBER), used when ancillary data is required (the NUMBER argument associates this binding with the ancillary operator binding)

The two bindings have a single functional implementation, as shown in Example 9-19:

CREATE OPERATOR Contains
BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER
WITH INDEX CONTEXT, SCAN CONTEXT TextIndexMethods COMPUTE ANCILLARY DATA
USING TextContains;

TextContains(VARCHAR2, VARCHAR2, ODCIIndexCtx, TextIndexMethods, NUMBER).

An operator binding that models ancillary data is called an ancillary binding. Functional implementations for ancillary data operators are similar to index-based functional implementations. When you have defined the function, you bind it to the operator with an additional ANCILLARY TO attribute, indicating that the functional implementation must share its state with the primary operator binding.

Note that the functional implementation for the ancillary operator binding must have the same signature as the functional implementation for the primary operator binding.

Example 9-20 demonstrates how to evaluate the ancillary operator inside a TextScore() function.

Using the TextScore() definition, you could create an ancillary binding, as in Example 9-21.

The ANCILLARY TO clause specifies that Score shares state with the primary operator binding CONTAINS(VARCHAR2, VARCHAR2).

The ancillary operator binding is invoked with a single literal number argument, such as Score(1), Score(2), and so on.

CREATE FUNCTION TextScore (Text IN VARCHAR2, Key IN VARCHAR2,
  indexctx IN ODCIIndexCtx, scanctx IN OUT TextIndexMethods, scanflg IN NUMBER)
RETURN NUMBER AS
BEGIN
.......
END TextScore;

CREATE OPERATOR Score
BINDING (NUMBER) RETURN NUMBER
ANCILLARY TO Contains(VARCHAR2, VARCHAR2) 
USING TextScore;

The operators corresponding to ancillary data are invoked by the user with a single number argument. This number argument must be a literal in both the ancillary operation, and in the primary operator invocation, so that the operator association can be done at query compilation time.

To determine the corresponding primary operator, Oracle matches the number passed to the ancillary operator with the number passed as the last argument to the primary operator. It is an error to find zero or more than one matching primary operator invocation. After the matching primary operator invocation is found,

• The arguments to the primary operator become operands of the ancillary operator.

• The ancillary and primary operator executions are passed the same scan context.

For example, in the Example 9-17 query, the invocation of Score is determined to be ancillary to Contains() based on the number argument 1, and the functional implementation for Score gets the operands (resume, 'Oracle&Unix', indexctx, scanctx, scanflg), where scanctx is shared with the invocation of Contains().

Operator execution uses an index scan to process the Contains() operator. For each of the rows returned by the fetch() call of the index scan, the functional implementation of Score is invoked by passing to it the ODCIIndexCtx argument, which contains the index information, row identifier, and a handle to the index scan state. The functional implementation can use the handle to the index scan state to compute the score.