9 Defining Operators

Operator Bindings

An operator binding associates the operator with the signature of a function that implements the operator. A signature consists of a list of the datatypes of the arguments of the function, in order of occurrence, and the function's return type. Operator bindings tell Oracle which function to execute when the operator is invoked. An operator can be bound to more than one function if each function has a different signature. To be considered different, functions must have different argument lists. Functions whose argument lists match, but whose return datatypes do not match, are not considered different and cannot be bound to the same operator.

Operators can be bound to:

• Standalone functions

• Package functions

• User-defined type member methods

Operators can be bound to functions and methods in any accessible schema. Each operator must have at least one binding when you create it. If you attempt to specify non-unique operator bindings, the Oracle server raises an error.

Operator Privileges

To create an operator and its bindings, you must have:

• CREATE OPERATOR or CREATE ANY OPERATOR privilege

• EXECUTE privilege on the function, operator, package, or type referenced

To drop a user-defined operator, you must own the operator or have the DROP ANY OPERATOR privilege.

To invoke a user-defined operator in an expression, you must own the operator or have EXECUTE privilege on it.

Creating Operators

To create an operator, specify its name and its bindings with the CREATE OPERATOR statement. For example, the following statement creates the operator Contains in the Ordsys schema, binding it to functions that provide implementations in the Text and Spatial domains.

CREATE OPERATOR Ordsys.Contains
BINDING
(VARCHAR2, VARCHAR2) RETURN NUMBER USING text.contains,
(Spatial.Geo, Spatial.Geo) RETURN NUMBER USING Spatial.contains;

Dropping Operators

To drop an operator and all its bindings, specify its name with the DROP OPERATOR statement. For example, the following statement drops the operator Contains:

DROP OPERATOR Contains;

The default DROP behavior is DROP RESTRICT: if there are dependent indextypes or ancillary operators for any of the operator bindings, then the DROP operation is disallowed.

To override the default behavior, use the FORCE option. For example, the following statement drops the operator and all its bindings and marks any dependent indextype objects and dependent ancillary operators invalid:

DROP OPERATOR Contains FORCE;

Altering Operators

You can add bindings to or drop bindings from an existing operator with the ALTER OPERATOR statement. For example, the following statement adds a binding to the operator CONTAINS:

ALTER OPERATOR Ordsys.Contains
  ADD BINDING (music.artist, music.artist) RETURN NUMBER
  USING music.contains;

You need certain privileges to perform alteration operations:

• To alter an operator, the operator must be in your own schema, or you must have the ALTER ANY OPERATOR privilege.

• You must have EXECUTE privileges on the operators and functions referenced.

The following restrictions apply to the ALTER OPERATOR statement:

• You can only issue ALTER OPERATOR statements that relate to existing operators.

• You can only add or drop one binding in each ALTER OPERATOR statement.

• You cannot drop an operator's only binding with ALTER OPERATOR; use the DROP OPERATOR statement to drop the operator. An operator cannot exist without any bindings.

• If you add a binding to an operator associated with an indextype, the binding is not associated to the indextype unless you also issue the ALTER INDEXTYPE ADD OPERATOR statement

Commenting Operators

To add comment text to an operator, specify the name and text with the COMMENT statement. For example, the following statement supplies information about the Contains operator:

COMMENT ON OPERATOR
Ordsys.Contains IS 'a number indicating whether the text contains the key';

Comments on operators are available in the data dictionary through these views:

• USER_OPERATOR_COMMENTS

• ALL_OPERATOR_COMMENTS

• DBA_OPERATOR_COMMENTS

You can only comment operators in your own schema unless you have the COMMENT ANY OPERATOR privilege.

Invoking Operators

Like built-in operators, user-defined operators can be invoked wherever expressions can occur. For example, user-defined operators can be used in:

• the select list of a SELECT command

• the condition of a WHERE clause

• the ORDER BY and GROUP BY clauses

When an operator is invoked, Oracle evaluates the operator by executing a function bound to it. When more than one function is bound to the operator, Oracle executes the function whose argument datatypes match those of the invocation (after any implicit type conversions). Invoking an operator with an argument list that does not match the signature of any function bound to that operator causes an error to be raised. Because user-defined operators can have multiple bindings, they can be used as overloaded functions.

Examples

Assume that the operator Contains was created with the following statement:

CREATE OPERATOR Ordsys.Contains
BINDING 
(VARCHAR2, VARCHAR2) RETURN NUMBER 
USING text.contains, 
(spatial.geo, spatial.geo) RETURN NUMBER 
USING spatial.contains;

Consider the use of Contains in the following SQL statement:

SELECT * FROM Employee
WHERE Contains(resume, 'Oracle')=1 AND Contains(location, :bay_area)=1;

The operator invocation Contains(resume, 'Oracle') causes Oracle to execute the function text.contains(resume, 'Oracle'), because the signature of the function matches the datatypes of the operator arguments. Similarly, the operator invocation Contains(location, :bay_area) causes execution of the function spatial.contains(location, :bay_area).

Executing the following statement raises an error because none of the operator bindings satisfy the argument datatypes:

Select * FROM Employee 
WHERE Contains(address, employee_addr_type('123 Main Street', 'Anytown', 'CA', '90001'))=1; 

Operators in the WHERE Clause

Operators appearing in the WHERE clause can be evaluated efficiently by an index scan using the scan methods provided by the indextype. This involves:

• creating an indextype that supports the evaluation of the operator

• recognizing operator predicates of a certain form

• selecting a domain index

• setting up an appropriate index scan

• executing the index scan methods

The following sections describe each of these steps in detail.

Operator Predicates

An indextype supports efficient evaluation of operator predicates that can be represented by a range of lower and upper bounds on the operator return values. Specifically, predicates of the form

op(...) relop <value expression>, where relop in {<, <=, =, >=,>}

op(...) LIKE <value_expression>

are candidates for index scan-based evaluation.

Operator predicates that Oracle can convert internally into one of the preceding forms can also make use of the index scan-based evaluation.

Using the operators in expressions, such as

op(...) + 2 = 3

precludes index scan-based evaluation.

Predicates of the form

op() is NULL
 

are evaluated using the functional implementation.

Operator Resolution

An index scan-based evaluation of an operator is only possible if the operator operates on a column or object attribute indexed by an indextype. The optimizer makes the final decision between the indexed implementation and the functional implementation, taking into account the selectivity and cost while generating the query execution plan.

For example, consider the query

SELECT * FROM Employees WHERE Contains(resume, 'Oracle') = 1;

The optimizer can choose to use a domain index in evaluating the Contains operator if

• The resume column has an index defined on it

• The index is of type TextIndexType

• TextIndexType supports the appropriate Contains() operator

If any of these conditions do not hold, Oracle performs a complete scan of the Employees table and applies the functional implementation of Contains as a post-filter. However, if all these conditions are met, the optimizer uses selectivity and cost functions to compare the cost of index-based evaluation with the full table scan and generates the appropriate execution plan.

Consider a slightly different query:

SELECT * FROM Employees WHERE Contains(resume, 'Oracle') =1 AND id =100;

In this query, the Employees table can be accessed through an index on the id column, one on the resume column, or a bitmap merge of the two. The optimizer estimates the costs of the three plans and picks the cheapest one, which could be to use the index on id and apply the Contains operator on the resulting rows. In that case, Oracle would use the functional implementation of Contains() rather than the domain index.

Index Scan Setup

If a domain index is selected for the evaluation of an operator predicate, an index scan is set up. The index scan is performed by the scan methods (ODCIIndexStart(), ODCIIndexFetch(), ODCIIndexClose()) specified as part of the corresponding indextype implementation. The ODCIIndexStart() method is invoked with the operator-related information, including name and arguments and the lower and upper bounds describing the predicate. After the ODCIIndexStart() call, a series of fetches are performed to obtain row identifiers of rows satisfying the predicate, and finally the ODCIIndexClose() is called when the SQL cursor is destroyed.

Execution Model for Index Scan Methods

To implement the index scan routines, you must understand how they are invoked and how multiple sets of invocations can be interleaved.

As an example, consider the following query:

SELECT * FROM Emp1, Emp2 WHERE 
Contains(Emp1.resume, 'Oracle') =1 AND Contains(Emp2.resume, 'Unix') =1
AND Emp1.id = Emp2.id;

If the optimizer decides to use the domain indexes on the resume columns of both tables, the indextype routines might be invoked in the following sequence:

start(ctx1, ...); /* corr. to Contains(Emp1.resume, 'Oracle') */
start(ctx2, ...); /* corr. to Contains(Emp2.resume, 'Unix');
fetch(ctx1, ...);
fetch(ctx2, ...);
fetch(ctx1, ...);
...
close(ctx1);
close(ctx2);

In this example, a single indextype routine is invoked several times for different instances of the operator. It is possible that many operators are being evaluated concurrently through the same indextype routines. A routine that gets all the information it needs through its parameters (such as the create routine) does not need to maintain any state across calls, so evaluating multiple operators concurrently is not a problem. However, routines that need to maintain state across calls (like the fetch routine, which needs to know which row to return next) should maintain state information in the SELF parameter that is passed in to each call. The SELF parameter (which is an instance of the implementation type) can be used to store either the entire state, if it is not too big, or a handle to the cursor-duration memory that stores the state.

Operators Outside the WHERE Clause

Operators occurring outside the WHERE clause are evaluated using the functional implementation. For example, to execute the statement

SELECT Contains(resume, 'Oracle') FROM Employee;

Oracle scans the Employee table and invokes the functional implementation for Contains on each instance of resume, passing it the actual value of the resume (text data) in the current row. Note that this function would not make use of any domain indexes built on the resume column.

However, functional implementations can make use of domain indexes. The following sections discuss how to write functions that use domain indexes and how they are invoked by the system.

Creating Index-based Functional Implementations

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 Employee 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

For example, the following function provides the index-based functional implementation for the Contains operator:

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

The Contains operator is bound to the functional implementation as follows:

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

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

Operator Resolution

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

For example, in the query

SELECT Contains(resume, 'Oracle & Unix') FROM Employees;

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.

Operator Execution

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

• The initial set of arguments is the same as 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 once more, 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 and can be used as described in this section. Thus, the operator can maintain state between invocations even if no index is used by the query.

Operators that Return Ancillary Data

In addition to filtering rows, operators in WHERE clauses sometimes need to return ancillary data. Ancillary data is modeled as one or more operators, each of which has

• a single literal number argument, which ties it to the corresponding primary operator

• a functional implementation with access to state generated by the index scan-based implementation of the primary operator

For example, in the following query

SELECT Score(1) FROM Employees 
WHERE Contains(resume, 'OCI & UNIX', 1) =1;

The primary operator, Contains, can be evaluated using an index scan that determines which rows satisfy the predicate and computes a score value for each row. The functional implementation for the Score operator accesses the state generated by the index scan to obtain the score for a given row identified by its row identifier. The literal argument 1 associates the ancillary operator Score to the primary operator Contains, which generates the ancillary data.

The functional implementation of an ancillary operator can use either the domain index or the state generated by the primary operator. When invoked, the functional implementation is passed three extra arguments:

• the index context, which contains the domain index information

• the scan context, which provides access to the state generated by the primary operator

• a scan flag to indicate whether the functional implementation is being invoked for the last time

The following sections discuss how operators modeling ancillary data are defined and invoked.

Operator Bindings That Compute Ancillary Data

An operator binding that computes ancillary data is called a primary binding. For example, the following statement defines a primary binding for the operator Contains:

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

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:

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

Operator Bindings That Model Ancillary Data

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 needs to share 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.

For example, you might define a TextScore() function to evaluate the Score ancillary operator with a statement like this:

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;

With TextScore defined, you could create an ancillary binding with the following statement:

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

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.

Operator Resolution

The operators corresponding to ancillary data are invoked by the user with a single number argument.

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 are made operands to the ancillary operator as well

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

For example, consider the query

SELECT Score(1) FROM Employees
WHERE Contains(resume, ' Oracle & Unix', 1) =1;

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 following operands: (resume, 'Oracle&Unix', indexctx, scanctx, scanflg), where scanctx is shared with the invocation of Contains.

Operator Execution

The execution involves using 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 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.