Introducing the PSBTREE Example

The example in this chapter illustrates how to implement the extensible indexing interface routines in C. The example's focus is on topics that are common to all implementations; it does not expose domain-specific details.

The code for the example is in the demo directory, in the file extdemo6.sql. It extends an earlier example (extdemo2.sql, also in demo directory) by adding to the indextype support for local domain indexes on range partitioned tables.

The index data consists of records of the form <key, rid> where key is the value of the indexed column and rid is the row identifier of the corresponding row. To simplify the implementation of the indextype, the index data is stored in an system-partitioned table.

When an index is a system-managed local domain index, one partition in a system-partitioned table is created for each partition to store the index data for that partition. Thus, the index manipulation routines merely translate operations on the PSBtree into operations on the table partition that stores the index data.

When a user creates a PSBtree index (a local index), n table partitions are created consisting of the indexed column and a rowid column, where n is the number of partitions in the base table. Inserts into the base table cause appropriate insertions into the affected index table partition. Deletes and updates are handled similarly. When the PSBtree is queried based on a user-defined operator (one of gt, lt and eq), an appropriate query is issued against the index table partitions to retrieve all the satisfying rows. Appropriate partition pruning occurs, and only the index table partitions that correspond to the relevant, or "interesting", partitions are accessed.

Implementing Operators

The PSBtree indextype supports three operators. Each operator has a corresponding functional implementation. The functional implementations of the eq, gt and lt operators are presented in the following section.

Create Operators

To create the operator, you must specify the signature of the operator along with its return type and its functional implementation. Example 16-4 shows how to create eq (equals), Example 16-5 shows how to create lt (less than), and Example 16-6 shows how to create gt (greater than) operators.

Implementing the ODCIIndex Interfaces

To implement the PSBTREE, you must implement the ODCIIndexXXX() routines, as outlined in the following sections. You can implement the index routines in any language supported by Oracle. This section implements the ODCIGetInterfaces() routine in the C programming language. Note that these require advance setup, such as creating a library object, extdemo6l, for your compiled C code.

Defining an Implementation Type for PSBTREE

Define an implementation type that implements the ODCIIndex interface routines, as demonstrated in Example 16-7.

The ODCIIndexCreate() routine creates a system-partitioned index storage table with two columns. The first column stores the VARCHAR2 indexed column value. The routine makes use of the information passed in to determine the context in which it is invoked. Dynamic SQL is used to execute the dynamically constructed SQL statement.

To handle partition maintenance operations, the kernel performs the maintenance tasks on behalf of the user. The indextype, to maintain its metadata, should have the ODCIIndexUpdPartMetadata() routine.

The C structs for mapping the ODCI types are defined in the file odci.h. For example, the C structODCIIndexInfo is the mapping for the corresponding ODCI object type. The C structODCIIndexInfo_ind is the mapping for the null object.

Example 16-21 Defining Mappings for the Object Type and Its Null Value

We have defined a C struct, qxiqtim, as a mapping for the object type. There is an additional C struct, qxiqtin, for the corresponding null object. The C structs for the object type and its null object can be generated from the Object Type Translator (OTT).

There are a set of OCI handles that must be cached away and retrieved during fetch calls. A C struct, qxiqtcx, is defined to hold all the necessary scan state. This structure is allocated out of OCI_DURATION_STATEMENT memory to ensure that it persists till the end of fetch. After populating the structure with the required info, a pointer to the structure is saved in OCI context. The context is identified by a 4-byte key that is generated by calling an OCI routine. The 4-byte key is stashed away in the scan context - exiting. This object is returned back to the Oracle server and is passed in as a parameter to the next fetch call.

The insert routine, ODCIIndexInsert(), parses and executes a statement that inserts a new row into the index table. The new row consists of the new value of the indexed column and the rowid that have been passed in as parameters.

The delete routine constructs a SQL statement to delete a row from the index table corresponding to the row being deleted from the base table. The row in the index table is identified by the value of rowid that is passed in as a parameter to this routine.

The update routine constructs a SQL statement to update a row in the index table corresponding to the row being updated in the base table. The row in the index table is identified by the value of rowid that is passed in as a parameter to this routine. The old column value (oldval) is replaced by the new value (newval).

The start routine performs the setup for an psbtree index scan. The query information in terms of the operator predicate, its arguments, and the bounds on return values are passed in as parameters to this function. The scan context that is shared among the index scan routines is an instance of the type psbtree_im.

This function sets up a cursor that scans the index table. The scan retrieves the stored rowids for the rows in the index table that satisfy the specified predicate. The predicate for the index table is generated based on the operator predicate information that is passed in as parameters. For example, if the operator predicate is of the form eq(col, 'joe') = 1, then the predicate on the index table is set up to be f1 = 'joe'.

The scan context set up by the start routine is passed in as a parameter to the fetch routine. This function first retrieves the 4-byte key from the scan context. The C mapping for the scan context is qxiqtim (see Example 16-21). Next, key is used to look up the OCI context. This gives the memory address of the qxiqtcx structure (see Example 16-22) that holds the OCI handles.

This function returns the next batch of rowids that satisfy the operator predicate. It uses the value of the nrows parameter as the size of the batch. It repeatedly fetches rowids from the open cursor and populates the rowid list. When the batch is full or when there are no more rowids left, the function returns them back to the Oracle server.

The scan context set up by the start routine is passed in as a parameter to the close routine. This function first retrieves the 4-byte key from the scan context. The C mapping for the scan context is qxiqtim (see Example 16-21). Next, the OCI context is looked up based on the key. This gives the memory address of the structure that holds the OCI handles, the qxiqtcx structure (see Example 16-22).

This function closes and frees all the OCI handles. It also frees the memory that was allocated in the start routine.

Implementing the Indextype

You should next create the indextype object and specify the list of operators that it supports. In addition, specify the name of the implementation type that implements the ODCIIndexXXX() interface routines. This step is demonstrated in Example 16-30.