Skip Headers
Oracle® Database Data Cartridge Developer's Guide
12c Release 1 (12.1)

Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
PDF · Mobi · ePub

9 Defining Operators

This chapter introduces user-defined operators and then demonstrates how to use them, both with and without indextypes.

This chapter contains these topics:

User-Defined Operators


User-defined operators are:


  • Bound to functions, which define operator behavior in specified contexts



See Also:

Oracle Database SQL Language Reference for detailed information on syntax and privileges

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 data types 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 several functions 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 data types 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


Operator Privileges

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





Creating Operators

To create an operator, specify its name and its bindings with the CREATE OPERATOR statement. Example 9-1 creates the operator Contains(), binding it to functions that provide implementations in the Text and Spatial domains.

Example 9-1 Creating an Operator

(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. Example 9-2 drops the operator Contains().

Example 9-2 Dropping an Operator; RESTRICT Option



To override the default behavior, use the FORCE option. Example 9-3 drops the operator and all its bindings and marks any dependent indextype objects and dependent ancillary operators invalid.

Example 9-3 Dropping an Operator; FORCE Option


Altering Operators

You can add bindings to or drop bindings from an existing operator with the ALTER OPERATOR statement. Example 9-4 adds a binding to the operator Contains().

Example 9-4 Adding a Binding to an Operator

  ADD BINDING (music.artist, music.artist) RETURN NUMBER
  USING music.contains;

You need certain privileges to perform alteration operations:



The following restrictions apply to the ALTER OPERATOR statement:





Commenting Operators

To add comment text to an operator, specify the name and text with the COMMENT statement. Example 9-5 supplies information about the Contains() operator:

Example 9-5 Adding COMMENTs to an Operator

Contains IS 'a number that indicates if the text contains the key';






Invoking Operators


  • 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 several functions are bound to the operator, Oracle executes the function whose argument data types 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.

Assume that Example 9-6 creates the operator Contains().

Example 9-6 Creating the Contains() Operator

USING text.contains, 
(spatial.geo, spatial.geo) RETURN NUMBER 
USING spatial.contains;

If Contains() is used in Example 9-7, the operator invocation Contains(resume, 'Oracle') causes Oracle to execute the function text.contains(resume, 'Oracle') because the signature of the function matches the data types of the operator arguments. Similarly, the operator invocation Contains(location, :bay_area) executes the function spatial.contains(location, :bay_area).

Example 9-7 Using the Operator Contains() in a Query

SELECT * FROM MyEmployees

Executing the statement in Example 9-8 raises an error because none of the operator bindings satisfy the argument data types.

Example 9-8 An Incorrect Use of the Operator Contains()

SELECT * FROM MyEmployees

Operators and Indextypes



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 process involves:

  1. Creating an indextype that supports the evaluation of the operator

  2. Recognizing operator predicates of a certain form

  3. Selecting a domain index

  4. Setting up an appropriate index scan

  5. 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 forms listed in Example 9-9 are candidates for index scan-based evaluation.

Example 9-9 Operator Predicates

op(...) LIKE value_expression
op(...) relop value_expression

   where value_expression must evaluated to a constant (not a column) that can be used as a domain index key, and relop is one of <, <=, =, >=, or >

Operator predicates that Oracle can convert internally into one of the forms in Example 9-9 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


Consider the query in Example 9-10.

Example 9-10 Using the Contains() Operator in a Simple Query

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

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

  • The resume column has a defined index.

  • 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 MyEmployees 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 in Example 9-11.

Example 9-11 Using the Contains() Operator in a Complex Query


Here, you can access the MyEmployees table 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 least expensive variant 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(), and 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


As an example, consider the query in Example 9-12.

Example 9-12 Using the Contains() Operator in a Multiple Table Query

SELECT * FROM MyEmployees1, MyEmployees2 
  Contains(MyEmployees1.resume, 'Oracle') =1 AND 
  Contains(MyEmployees2.resume, 'UNIX') =1 AND 
  MyEmployees1.employee_id = MyEmployees2.employee_id;

If the optimizer chooses to use the domain indexes on the resume columns of both tables, the indextype routines might be invoked in the sequence demonstrated in Example 9-13.

Example 9-13 Invoking Indextype Routines for the Contains() Operator Query

start(ctx1, ...); /* corr. to Contains(MyEmployees1.resume, 'Oracle') */
start(ctx2, ...); /* corr. to Contains(MyEmployees2.resume, 'UNIX');
fetch(ctx1, ...);
fetch(ctx2, ...);
fetch(ctx1, ...);

In this example, a single indextype routine is invoked several times for different instances of the Contains() 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 maintain any state across calls, so evaluating multiple operators concurrently is not a problem. Other routines that must maintain state across calls, like the FETCH routine, must know which row to return next. These routines should maintain state information in the SELF parameter that is passed in to each call. The SELF parameter, an instance of the implementation type, can be used to store either the entire state or a handle to the cursor-duration memory that stores the state (if the state information is large).

Using Operators Outside the WHERE Clause

Operators that are used outside the WHERE clause are evaluated using the functional implementation. To execute the statement in Example 9-14, Oracle scans the MyEmployees table and invokes the functional implementation for Contains() on each instance of resume, passing it the actual value of the resume, the text data, in the current row. Note that this function would not make use of any domain indexes built on the resume column.

Example 9-14 Using Operators Outside the WHERE Clause

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

Because 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:


    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.






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

Example 9-15 Implementing the Contains() Operator in Index-Based Functions

END TextContains;

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

Example 9-16 Binding the Contains() Operator to the Functional Implementation

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 data type of the scan context argument, which must be identical to 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, 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.

Operator Execution


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






  • 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.



Operators that Return Ancillary Data

In addition to filtering rows, operators in WHERE clauses sometimes must 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

In the query in Example 9-17, 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.

Example 9-17 Accessing Ancillary Data with the Contains() Operator

SELECT Score(1) FROM MyEmployees 


  • 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


Operator Bindings that Compute Ancillary Data

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

Example 9-18 Comparing Ancillary Data with the Contains() Operator

USING TextContains;

This definition registers two bindings for Contains():



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

Example 9-19 Implementing Bindings for Computations


Operator Bindings That Model Ancillary Data



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

Example 9-20 Evaluating an Ancillary Operator

END TextScore;

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

Example 9-21 Creating an Ancillary Operator Binding

USING TextScore;



Operator Resolution

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.


  • 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

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.