14
PSBTREE: An Example of Extensible Indexing

This chapter presents an extensible indexing example in which some of the ODCIIndex interface routines are implemented in C:

• Introduction
• Design of the indextype
• Implementing Operators
• Implementing the Index Routines
• The C Code
• Implementing the Indextype
• Usage examples

Introduction

The example in this chapter gives a general illustration of how to implement the extensible indexing interface routines in C. The example tries to concentrate on topics that are common to all implementations and glosses over domain-specific details.

The code for the example is in the demo directory (see file extdemo5.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.

Design of the indextype

The indextype implemented here, called PSBtree, operates like a btree index. It supports three user-defined operators:

• gt(Greater Than)
• lt(Less Than)
• eq(EQuals)

These operators operate on operands of VARCHAR2 datatype.

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 index-organized table.

When an index is a local domain index, one index-organized 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 storing the index data.

When a user creates a PSBtree index (a local index), n tables 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. 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 to retrieve all the satisfying rows. Appropriate partition pruning occurs, and only the index tables 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 Functional Implementations

Functional Implementation of EQ (EQUALS)

The functional implementation for eq is provided by a function (bt_eq) that takes in two VARCHAR2 parameters and returns 1 if they are equal and 0 otherwise.

CREATE FUNCTION bt_eq(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
BEGIN 
  IF a = b then
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END;

Functional Implementation of LT (LESS THAN)

The functional implementation for lt is provided by a function (bt_lt) that takes in two VARCHAR2 parameters and returns 1 if the first parameter is less than the second, 0 otherwise.

CREATE FUNCTION bt_lt(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
BEGIN 
  IF a < b then
    RETURN 1;
  ELSE
   RETURN 0;
  END IF;
END;

Functional Implementation of GT (GREATER THAN)

The functional implementation for gt is provided by a function (bt_gt) that takes in two VARCHAR2 parameters and returns 1 if the first parameter is greater than the second, 0 otherwise.

CREATE FUNCTION bt_gt(a VARCHAR2, b VARCHAR2) RETURN NUMBER AS
BEGIN 
  IF a > b then
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END;

Create Operators

To create the operator, you need to specify the signature of the operator along with its return type and its functional implementation.

Operator EQ

CREATE OPERATOR eq 
BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER 
USING bt_eq;

Operator LT

CREATE OPERATOR lt 
BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER 
USING bt_lt;

Operator GT

CREATE OPERATOR gt 
BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER 
USING bt_gt;

Implementing the Index Routines

1. Define an implementation type that implements the ODCIIndex interface routines.

   CREATE TYPE psbtree_im AS OBJECT
   (
     scanctx RAW(4),
     STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList)
       RETURN NUMBER,
     STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2,
       env SYS.ODCIEnv) RETURN NUMBER,
       STATIC FUNCTION ODCIIndexDrop(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv) 
       RETURN NUMBER,
     STATIC FUNCTION ODCIIndexExchangePartition(ia SYS.ODCIIndexInfo,
       ia1 SYS.ODCIIndexInfo, env SYS.ODCIEnv) RETURN NUMBER,
     STATIC FUNCTION ODCIIndexMergePartition(ia SYS.ODCIIndexInfo,
       part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo, parms VARCHAR2,
       env SYS.ODCIEnv) RETURN NUMBER,
     STATIC FUNCTION ODCIIndexSplitPartition(ia SYS.ODCIIndexInfo,
       part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo, parms VARCHAR2,
       env SYS.ODCIEnv) RETURN NUMBER,
     STATIC FUNCTION ODCIIndexTruncate(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv)
       RETURN NUMBER,
     STATIC FUNCTION ODCIIndexInsert(ia SYS.ODCIIndexInfo, rid VARCHAR2,
       newval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
     STATIC FUNCTION ODCIIndexDelete(ia SYS.ODCIIndexInfo, rid VARCHAR2,
       oldval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
     STATIC FUNCTION ODCIIndexUpdate(ia SYS.ODCIIndexInfo, rid VARCHAR2,
       oldval VARCHAR2, newval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
     STATIC FUNCTION ODCIIndexStart(sctx IN OUT psbtree_im, ia SYS.ODCIIndexInfo,
       op SYS.ODCIPredInfo, qi sys.ODCIQueryInfo, strt number, stop number,
       cmpval VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
     MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT SYS.ODCIridlist,
       env SYS.ODCIEnv) RETURN NUMBER,
     MEMBER FUNCTION ODCIIndexClose(env SYS.ODCIEnv) RETURN NUMBER
   );
   /
   SHOW ERRORS
   
   

2. Define the implementation type body

You can implement the index routines in any language supported by Oracle. For this example, we will implement the get interfaces routine and the index definition routines in PL/SQL. We will implement the index manipulation and query routines in C.

CREATE OR REPLACE TYPE BODY psbtree_im 
IS

The get interfaces routine returns the expected interface name through its OUT parameter.

   STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList) 
       RETURN NUMBER IS
   BEGIN
       ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
       RETURN ODCIConst.Success;
   END ODCIGetInterfaces;

The ODCIIndexCreate routine creates an index storage table with two columns. The first column stores the VARCHAR2 indexed column value. The second column in the index table stores the rowid of the corresponding row in the base table. When the create routine is invoked during creation of a local domain index, it is invoked n+2 times, where n is the number of partitions in the base table. The routine creates one index storage table for each partition. The create routine is also invoked during execution of ALTER TABLE ADD PARTITION if there are local domain indexes defined on the table. In this case, the routine simply creates a new index storage table to correspond to the newly created partition. The routine makes use of the information passed in to determine the context in which it is invoked. DBMS_SQL is used to execute the dynamically constructed SQL statement.

STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2,
    env SYS.ODCIEnv)
    RETURN NUMBER
  IS
   i INTEGER;
   stmt VARCHAR2(1000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
   -- construct the sql statement
   stmt := '';

   IF ((env.CallProperty IS NULL) and (ia.IndexPartition IS NULL ))  THEN
     stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
             '_sbtree'  ||
             '( f1 , f2, PRIMARY KEY (f1) ) ORGANIZATION INDEX AS SELECT ' ||
             ia.IndexCols(1).ColName || ', ROWID FROM ' || 
             ia.IndexCols(1).TableSchema || '.' || ia.IndexCols(1).TableName;
   END IF;

   IF ((env.CallProperty IS NOT NULL) AND (ia.IndexPartition IS NOT NULL)) THEN
     stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
             '_' || ia.indexpartition || '_sbtree'  ||
             '( f1 , f2, PRIMARY KEY (f1) ) ORGANIZATION INDEX AS SELECT ' ||
             ia.IndexCols(1).ColName || ', ROWID FROM ' || 
             ia.IndexCols(1).TableSchema || '.' || 
             ia.IndexCols(1).TableName || ' PARTITION (' ||
             ia.IndexCols(1).TablePartition || ')';
   END IF;

   IF ((env.CallProperty IS NULL) AND (ia.IndexPartition IS NOT NULL)) THEN
     stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
             '_' || ia.IndexPartition || '_sbtree'  ||
             '( f1 ' || ia.IndexCols(1).ColTypeName ||'(200) , f2 ROWID, ' ||
             ' PRIMARY KEY (f1)) ORGANIZATION INDEX';
   END IF;
  
   DBMS_OUTPUT.PUT_LINE('Create');
   DBMS_OUTPUT.PUT_LINE(stmt);

   -- execute the statement
   IF ( (env.CallProperty IS NULL) OR
        (env.CallProperty = SYS.ODCIConst.IntermediateCall) ) THEN
     cnum := DBMS_SQL.OPEN_CURSOR;
     DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
     junk := DBMS_SQL.EXECUTE(cnum);
     DBMS_SQL.CLOSE_CURSOR(cnum);
   END IF;

   RETURN ODCIConst.Success;
  END;

The ODCIIndexDrop routine drops the index storage table(s). For a local domain index, the routine is invoked n+2 times, where n is the number of partitions in the base table.

STATIC FUNCTION ODCIIndexDrop(ia SYS.ODCIIndexInfo, env SYS.ODCIEnv) 
   RETURN NUMBER IS
   stmt VARCHAR2(1000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
    -- construct the sql statement
   stmt := '';
   IF ((env.CallProperty IS NULL) and (ia.IndexPartition IS NULL) ) THEN
     stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
             '_sbtree';
   ELSE
     IF (ia.IndexPartition IS NOT NULL) THEN
       stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
               '_' || ia.IndexPartition || '_sbtree';
     END IF;
   END IF;

   DBMS_OUTPUT.PUT_LINE('Drop');
   DBMS_OUTPUT.PUT_LINE(stmt);

   -- execute the statement
   IF ( (env.CallProperty IS NULL) OR
        (env.CallProperty = SYS.ODCIConst.IntermediateCall) ) THEN
     cnum := DBMS_SQL.OPEN_CURSOR;
     DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
     junk := DBMS_SQL.EXECUTE(cnum);
     DBMS_SQL.CLOSE_CURSOR(cnum);
   END IF;

   RETURN ODCIConst.Success;
  END;

To handle partition maintenance operations, the indextype also has addiitonal methods that take appropriate actions on the index storage tables when the base table partitions are merged, split, or exchanged.

The ODCIIndexMergePartition routine drops the index storage tables for the two index partitions being merged and creates a new table corresponding to the resulting merged partition. If there is data in the resulting merged partition, the index is marked UNUSABLE so that the table is not populated with rows. That is left to be done during a subsequent ALTER INDEX REBUILD PARTITION.

  STATIC FUNCTION ODCIIndexMergePartition(ia SYS.ODCIIndexInfo,
    part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo,
    parms VARCHAR2, env SYS.ODCIEnv)
    return number
  IS
   stmt VARCHAR2(2000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
   DBMS_OUTPUT.PUT_LINE('Merge Partitions');
   stmt := '';

   IF (ia.IndexPartition IS NOT NULL) THEN
      stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
               '_' || ia.IndexPartition || '_sbtree';

      DBMS_OUTPUT.PUT_LINE('drop');
      DBMS_OUTPUT.PUT_LINE(stmt);

      -- execute the statement
      cnum := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
      junk := DBMS_SQL.EXECUTE(cnum);
      DBMS_SQL.CLOSE_CURSOR(cnum);
   END IF;

   IF ( part_name1 IS NOT NULL) THEN
      stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
               '_' || part_name1.IndexPartition || '_sbtree';

      DBMS_OUTPUT.PUT_LINE('drop');
      DBMS_OUTPUT.PUT_LINE(stmt);

      -- execute the statement
      cnum := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
      junk := DBMS_SQL.EXECUTE(cnum);
      DBMS_SQL.CLOSE_CURSOR(cnum);
   END IF;

   IF ( part_name2 IS NOT NULL) THEN
      stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
           '_' || part_name2.IndexPartition ||  '_sbtree'  ||
           '( f1 ' || ia.IndexCols(1).ColTypeName ||
           '(200) , f2 ROWID, PRIMARY KEY (f1) ) ORGANIZATION INDEX';

      DBMS_OUTPUT.PUT_LINE('create');
      DBMS_OUTPUT.PUT_LINE('Parameter string : ' || parms);

      DBMS_OUTPUT.PUT_LINE(stmt);

      -- execute the statement
      cnum := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
      junk := DBMS_SQL.EXECUTE(cnum);
      DBMS_SQL.CLOSE_CURSOR(cnum);
    END IF;

    RETURN ODCIConst.Success;   
  END;

The ODCIIndexSplitPartition routine drops the index storage table corresponding to the partition being split and creates two new index tables that correspond to the two new index partitions created.

STATIC FUNCTION ODCIIndexSplitPartition(ia SYS.ODCIIndexInfo,
    part_name1 SYS.ODCIPartInfo, part_name2 SYS.ODCIPartInfo,
    parms VARCHAR2, env sys.odcienv)
    return number
  IS
   stmt VARCHAR2(2000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
   DBMS_OUTPUT.PUT_LINE('Split Partition');
   stmt := '';

   IF (ia.IndexPartition IS NOT NULL) THEN
      stmt := 'DROP TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
               '_' || ia.IndexPartition || '_sbtree';

      DBMS_OUTPUT.PUT_LINE('drop');
      DBMS_OUTPUT.PUT_LINE(stmt);

      -- execute the statement
      cnum := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
      junk := DBMS_SQL.EXECUTE(cnum);
      DBMS_SQL.CLOSE_CURSOR(cnum);
    END IF;

   IF ( part_name1 IS NOT NULL) THEN
      stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
           '_' || part_name1.IndexPartition ||  '_sbtree'  ||
           '( f1 ' || ia.IndexCols(1).ColTypeName ||
           '(200) , f2 ROWID, PRIMARY KEY (f1)) ORGANIZATION INDEX';

      DBMS_OUTPUT.PUT_LINE('create');
      DBMS_OUTPUT.PUT_LINE('Parameter string : ' || parms);
      DBMS_OUTPUT.PUT_LINE(stmt);

      -- execute the statement
      cnum := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
      junk := DBMS_SQL.EXECUTE(cnum);
      DBMS_SQL.CLOSE_CURSOR(cnum);
    END IF;

   IF ( part_name2 IS NOT NULL) THEN
      stmt := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName || 
           '_' || part_name2.IndexPartition ||  '_sbtree'  ||
           '( f1 ' || ia.IndexCols(1).ColTypeName ||
           '(200) , f2 ROWID, PRIMARY KEY (f1)) ORGANIZATION INDEX';

      DBMS_OUTPUT.PUT_LINE('create');
      DBMS_OUTPUT.PUT_LINE('Parameter string : ' || parms);
      DBMS_OUTPUT.PUT_LINE(stmt);

      -- execute the statement
      cnum := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cnum, stmt, dbms_sql.native);
      junk := DBMS_SQL.EXECUTE(cnum);
      DBMS_SQL.CLOSE_CURSOR(cnum);
    END IF;

    RETURN ODCIConst.Success;   
  END;

The ODCIIndexExchangePartition exchanges the index storage tables for the index partition being exchanged, with the index storage table for the global domain index.

STATIC FUNCTION ODCIIndexExchangePartition(ia SYS.ODCIIndexInfo,
    ia1 SYS.ODCIIndexInfo, env SYS.ODCIEnv)
   RETURN NUMBER
  IS
   stmt VARCHAR2(2000);
   cnum INTEGER;
   junk INTEGER;
  BEGIN
   stmt := '';
   DBMS_OUTPUT.PUT_LINE('Exchange Partitions');

    -- construct the sql statement
   stmt := 'ALTER TABLE temp EXCHANGE PARTITION p1 WITH TABLE ' || 
          ia1.IndexSchema || '.' || ia1.IndexName || '_sbtree';
   cnum := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
   junk := DBMS_SQL.EXECUTE(cnum);
   DBMS_SQL.CLOSE_CURSOR(cnum);
   
   stmt := 'ALTER TABLE temp EXCHANGE PARTITION p1 WITH TABLE ' || 
        ia.IndexSchema || '.' || ia.IndexName || 
        '_' || ia.IndexPartition || '_sbtree';

   cnum := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
   junk := DBMS_SQL.EXECUTE(cnum);
   DBMS_SQL.CLOSE_CURSOR(cnum);

   stmt := 'ALTER TABLE temp EXCHANGE PARTITION p1 WITH TABLE ' || 
        ia1.IndexSchema || '.' || ia1.IndexName || '_sbtree';
   cnum := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cnum, stmt, DBMS_SQL.NATIVE);
   junk := DBMS_SQL.EXECUTE(cnum);
   DBMS_SQL.CLOSE_CURSOR(cnum);

   RETURN ODCIConst.Success;   
  END;

The index manipulation and query routines are implemented in C. These require some setup to be done in advance. Specifically, you need to create a library object called extdemo5l for your compiled C code.

After the setup, the following statements register the implementation of the index manipulation and query routines in terms of their corresponding C functions.

Register the implementation of the ODCIIndexInsert routine.

STATIC FUNCTION ODCIIndexInsert(ia SYS.ODCIIndexInfo, rid VARCHAR2,
                              newval VARCHAR2, env SYS.ODCIEnv)
                              RETURN NUMBER AS EXTERNAL
name "qxiqtbi"
library extdemo5l
with context
parameters (
context,
ia,
ia indicator struct,
rid,
rid indicator,
newval,
newval indicator,
env,
env indicator struct,
return OCINumber
           );

Register the implementation of the ODCIIndexDelete routine.

STATIC FUNCTION ODCIIndexDelete(ia SYS.ODCIIndexInfo, rid VARCHAR2,
                                oldval VARCHAR2, env SYS.ODCIEnv)
                                RETURN NUMBER AS EXTERNAL
name "qxiqtbd"
library extdemo5l
with context
parameters (
context,
ia,
ia indicator struct,
rid,
rid indicator,
oldval,
oldval indicator,
env,
env indicator struct,
return OCINumber
           );

Register the implementation of the ODCIIndexUpdate routine.

STATIC FUNCTION ODCIIndexUpdate(ia SYS.ODCIIndexInfo, rid VARCHAR2,
                          oldval VARCHAR2, newval VARCHAR2, env SYS.ODCIEnv)
                                RETURN NUMBER AS EXTERNAL
name "qxiqtbu"
library extdemo5l
with context
parameters (
context,
ia,
ia indicator struct,
rid,
rid indicator,
oldval,
oldval indicator,
newval,
newval indicator,
env,
env indicator struct,
return OCINumber
           );

Register the implementation of the ODCIIndexStart routine.

STATIC FUNCTION ODCIIndexStart(sctx IN OUT psbtree_im, ia SYS.ODCIIndexInfo,
                         op SYS.ODCIPredInfo,
                         qi SYS.ODCIQueryInfo,
                         strt NUMBER,
                         stop NUMBER,
                         cmpval VARCHAR2,
                         env SYS.ODCIEnv)
   RETURN NUMBER AS EXTERNAL
   name "qxiqtbs"
   library extdemo5l
   with context
   parameters (
     context,
     sctx,
     sctx indicator struct,
     ia,
     ia indicator struct,
     op,
     op indicator struct,
     qi,
     qi indicator struct,
     strt,
     strt indicator,
     stop,
     stop indicator,
     cmpval,
     cmpval indicator,
     env,
     env indicator struct,
     return OCINumber
  );

Register the implementation of the ODCIIndexFetch routine.

MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT SYS.ODCIRidList,
     env SYS.ODCIEnv)
 RETURN NUMBER AS EXTERNAL
 name "qxiqtbf"
 library extdemo5l
 with context
 parameters (
   context,
   self,
   self indicator struct,
   nrows,
   nrows indicator,
   rids,
   rids indicator,
   env,
   env indicator struct,
   return OCINumber
 );

Register the implementation of the ODCIIndexClose routine.

MEMBER FUNCTION ODCIIndexClose (env SYS.ODCIEnv) RETURN NUMBER AS EXTERNAL
name "qxiqtbc"
library extdemo5l
with context
parameters (
  context,
  self,
  self indicator struct,
  env,
  env indicator struct,
  return OCINumber
);

The C Code

General Notes

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

Common Error Processing Routine

This function is used to check and process the return code from all OCI routines. It checks the status code and raises an exception in case of errors.

static int qxiqtce(ctx, errhp, status)
OCIExtProcContext *ctx;
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;
  int errnum = 29400;  /* choose some oracle error number */
  int rc = 0;
  
  switch (status)
  {
  case OCI_SUCCESS: 
    rc = 0;
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode,
                       errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
    /* Raise exception */
    OCIExtProcRaiseExcpWithMsg(ctx, errnum, errbuf, strlen((char *)errbuf));
    rc = 1;
    break;
  default:
    (void) sprintf((char *)errbuf, "Warning - some error\n");
    /* Raise exception */
    OCIExtProcRaiseExcpWithMsg(ctx, errnum, errbuf, strlen((char *)errbuf));
    rc = 1;
    break;
  }
  return (rc);
}

Implementation Of The ODCIIndexInsert Routine

The insert routine 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.

OCINumber *qxiqtbi(ctx, ix, ix_ind, rid, rid_ind,
                   newval, newval_ind, env, env_ind)
OCIExtProcContext *ctx;
ODCIIndexInfo     *ix;
ODCIIndexInfo_ind *ix_ind;
char              *rid;
short             rid_ind;
char              *newval;
short             newval_ind;
ODCIEnv           *env;
ODCIEnv_ind       *env_ind;
{
  OCIEnv *envhp = (OCIEnv *) 0;                /* env. handle */
  OCISvcCtx *svchp = (OCISvcCtx *) 0;          /* service handle */
  OCIError *errhp = (OCIError *) 0;            /* error handle */
  OCIStmt *stmthp = (OCIStmt *) 0;             /* statement handle */
  OCIBind *bndp = (OCIBind *) 0;               /* bind handle */
  OCIBind *bndp1 = (OCIBind *) 0;              /* bind handle */

  int retval = (int)ODCI_SUCCESS;              /* return from this function */
  OCINumber *rval = (OCINumber *)0;
  ub4 key;                                     /* key value set in "self" */

  char insstmt[2000];                          /* sql insert statement */

  /* allocate memory for OCINumber first */
  rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));

  /* Get oci handles */
  if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
    return(rval);

  /* set up return code */
  if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                           sizeof(retval),
                                           OCI_NUMBER_SIGNED, rval)))
    return(rval);

  /******************************
   * Construct insert Statement *
   ******************************/
   if (ix_ind->IndexPartition == OCI_IND_NULL)
   {
     sprintf(insstmt,
                  "INSERT into %s.%s_sbtree values (:newval, :mrid)",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName));
   }
   else
   { 
     sprintf(insstmt,
                  "INSERT into %s.%s_%s_sbtree values (:newval, :mrid)",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName),
                  OCIStringPtr(envhp, ix->IndexPartition));

      }

  /****************************************
   * Parse and Execute Insert Statement   *
   ****************************************/

  /* allocate stmt handle */
  if (qxiqtce(ctx, errhp, OCIHandleAlloc((dvoid *)envhp,
                                         (dvoid **)&stmthp,
                                         (ub4)OCI_HTYPE_STMT, (size_t)0,
                                         (dvoid **)0)))
    return(rval);

  /* prepare the statement */
  if (qxiqtce(ctx, errhp, OCIStmtPrepare(stmthp, errhp, (text *)insstmt,
                                         (ub4)strlen(insstmt), OCI_NTV_SYNTAX,
                                         OCI_DEFAULT)))
    return(rval);



  /* Set up bind for newval */
  if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)1,
                                       (dvoid *)newval,
                                       (sb4)(strlen(newval)+1),
                                       (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                       (ub2 *)0, (ub4)0, (ub4 *)0,
                                       (ub4)OCI_DEFAULT)))
    return(rval);

  /* Set up bind for rid */
  if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)2,
                                       (dvoid *)rid,
                                       (sb4)(strlen(rid)+1),
                                       (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                       (ub2 *)0, (ub4)0, (ub4 *)0,
                                       (ub4)OCI_DEFAULT)))
    return(rval);


  /* Execute statement */
  if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1,
                                         (ub4)0, (OCISnapshot *)NULL,
                                         (OCISnapshot *)NULL,
                                         (ub4)OCI_DEFAULT)))
    return(rval);

  return(rval);
}

Implementation of the ODCIIndexDelete Routine

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.

OCINumber *qxiqtbd(ctx, ix, ix_ind, rid, rid_ind,
                   oldval, oldval_ind, env, env_ind)
OCIExtProcContext *ctx;
ODCIIndexInfo     *ix;
ODCIIndexInfo_ind *ix_ind;
char              *rid;
short             rid_ind;
char              *oldval;
short             oldval_ind;
ODCIEnv           *env;
ODCIEnv_ind       *env_ind;
{
  OCIEnv *envhp = (OCIEnv *) 0;                /* env. handle */
  OCISvcCtx *svchp = (OCISvcCtx *) 0;          /* service handle */
  OCIError *errhp = (OCIError *) 0;            /* error handle */
  OCIStmt *stmthp = (OCIStmt *) 0;             /* statement handle */
  OCIBind *bndp = (OCIBind *) 0;               /* bind handle */
  OCIBind *bndp1 = (OCIBind *) 0;              /* bind handle */

  int retval = (int)ODCI_SUCCESS;              /* return from this function */
  OCINumber *rval = (OCINumber *)0;
  ub4 key;                                     /* key value set in "self" */

  char delstmt[2000];                          /* sql insert statement */

  /* Get oci handles */
  if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
    return(rval);

  /* set up return code */
  rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
  if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                           sizeof(retval),
                                           OCI_NUMBER_SIGNED, rval)))
    return(rval);

  /******************************
   * Construct delete Statement *
   ******************************/
   if (ix_ind->IndexPartition == OCI_IND_NULL)
   {
     sprintf(delstmt,
                  "DELETE FROM %s.%s_sbtree WHERE f2 = :rr",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName));
   }
   else
   {
     sprintf(delstmt,
                  "DELETE FROM %s.%s_%s_sbtree WHERE f2 = :rr",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName),
                  OCIStringPtr(envhp, ix->IndexPartition));

   }

  /****************************************
   * Parse and Execute delete Statement   *
   ****************************************/

  /* allocate stmt handle */
  if (qxiqtce(ctx, errhp, OCIHandleAlloc((dvoid *)envhp,
                                         (dvoid **)&stmthp,
                                         (ub4)OCI_HTYPE_STMT, (size_t)0,
                                         (dvoid **)0)))
    return(rval);

  /* prepare the statement */
  if (qxiqtce(ctx, errhp, OCIStmtPrepare(stmthp, errhp, (text *)delstmt,
                                         (ub4)strlen(delstmt), OCI_NTV_SYNTAX,
                                         OCI_DEFAULT)))
    return(rval);



  /* Set up bind for rid */
  if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)1,
                                       (dvoid *)rid,
                                       (sb4)(strlen(rid)+1),
                                       (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                       (ub2 *)0, (ub4)0, (ub4 *)0,
                                       (ub4)OCI_DEFAULT)))
    return(rval);


  /* Execute statement */
  if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1,
                                         (ub4)0, (OCISnapshot *)NULL,
                                         (OCISnapshot *)NULL,
                                         (ub4)OCI_DEFAULT)))
    return(rval);

  return(rval);
}

Implementation of the ODCIIndexUpdate 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).

OCINumber *qxiqtbu(ctx, ix, ix_ind, rid, rid_ind,
                   oldval, oldval_ind, newval, newval_ind, env, env_ind)
OCIExtProcContext *ctx;
ODCIIndexInfo     *ix;
ODCIIndexInfo_ind *ix_ind;
char              *rid;
short             rid_ind;
char              *oldval;
short             oldval_ind;
char              *newval;
short             newval_ind;
ODCIEnv           *env;
ODCIEnv_ind       *env_ind;
{
  OCIEnv *envhp = (OCIEnv *) 0;                /* env. handle */
  OCISvcCtx *svchp = (OCISvcCtx *) 0;          /* service handle */
  OCIError *errhp = (OCIError *) 0;            /* error handle */
  OCIStmt *stmthp = (OCIStmt *) 0;             /* statement handle */
  OCIBind *bndp = (OCIBind *) 0;               /* bind handle */
  OCIBind *bndp1 = (OCIBind *) 0;              /* bind handle */

  int retval = (int)ODCI_SUCCESS;              /* return from this function */
  OCINumber *rval = (OCINumber *)0;
  ub4 key;                                     /* key value set in "self" */

  char updstmt[2000];                          /* sql insert statement */

  /* Get oci handles */
  if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
    return(rval);

  /* set up return code */
  rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
  if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                           sizeof(retval),
                                           OCI_NUMBER_SIGNED, rval)))
    return(rval);

  /******************************
   * Construct update Statement *
   ******************************/
   if (ix_ind->IndexPartition == OCI_IND_NULL)
   {
     sprintf(updstmt,
          "UPDATE %s.%s_sbtree SET f1 = :newval, f2 = :rr WHERE f1 = :oldval",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName));
   }
   else
   {
     sprintf(updstmt,
       "UPDATE %s.%s_%s_sbtree SET f1 = :newval, f2 = :rr WHERE f1 = :oldval",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName),
                  OCIStringPtr(envhp, ix->IndexPartition));

   }

  /****************************************
   * Parse and Execute Update Statement   *
   ****************************************/

  /* allocate stmt handle */
  if (qxiqtce(ctx, errhp, OCIHandleAlloc((dvoid *)envhp,
                                         (dvoid **)&stmthp,
                                         (ub4)OCI_HTYPE_STMT, (size_t)0,
                                         (dvoid **)0)))
    return(rval);

  /* prepare the statement */
  if (qxiqtce(ctx, errhp, OCIStmtPrepare(stmthp, errhp, (text *)updstmt,
                                         (ub4)strlen(updstmt), OCI_NTV_SYNTAX,
                                         OCI_DEFAULT)))
    return(rval);


  /* Set up bind for newval */
  if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)1,
                                       (dvoid *)newval,
                                       (sb4)(strlen(newval)+1),
                                       (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                       (ub2 *)0, (ub4)0, (ub4 *)0,
                                       (ub4)OCI_DEFAULT)))
    return(rval);

  /* Set up bind for rid */
  if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)2,
                                       (dvoid *)rid,
                                       (sb4)(strlen(rid)+1),
                                       (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                       (ub2 *)0, (ub4)0, (ub4 *)0,
                                       (ub4)OCI_DEFAULT)))
    return(rval);

  /* Set up bind for oldval */
  if (qxiqtce(ctx, errhp, OCIBindByPos(stmthp, &bndp, errhp, (ub4)3,
                                       (dvoid *)oldval,
                                       (sb4)(strlen(oldval)+1),
                                       (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                       (ub2 *)0, (ub4)0, (ub4 *)0,
                                       (ub4)OCI_DEFAULT)))
    return(rval);

  /* Execute statement */
  if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1,
                                         (ub4)0, (OCISnapshot *)NULL,
                                         (OCISnapshot *)NULL,
                                         (ub4)OCI_DEFAULT)))
    return(rval);

  return(rval);
}

Implementation of the ODCIIndexStart Routine

The start routine performs the setup for an sbtree 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. We have defined a C struct (qxiqtim) as a mapping for the object type. In addition, there is a C struct (qxiqtin) for the corresponding null object. Note that the C structs for the object type and its null object can be generated by using the Object Type Translator (OTT).

/* The index implementation type is an object type with a single RAW attribute
 * which will be used to store the context key value.
 * C mapping of the implementation type :
 */
struct qxiqtim
{
   OCIRaw *sctx_qxiqtim;
};
typedef struct qxiqtim qxiqtim;

struct qxiqtin
{
  short atomic_qxiqtin;
  short scind_qxiqtin;
};
typedef struct qxiqtin qxiqtin;

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

the predicate on the index table is set up to be

f1 = 'joe'

There are a set of OCI handles that need to be cached away and retrieved on the next fetch call. 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 index scan context - should be stored in "statement" duration memory
 * and used by start, fetch and close routines.
 */
struct qxiqtcx
{
  OCIStmt *stmthp;
  OCIDefine *defnp;
  OCIBind *bndp;
  char ridp[19];
};

typedef struct qxiqtcx qxiqtcx;

OCINumber *qxiqtbs(ctx, sctx, sctx_ind, ix, ix_ind, pr, pr_ind, qy, qy_ind,
                   strt, strt_ind, stop, stop_ind, cmpval, cmpval_ind,
                   env, env_ind)
OCIExtProcContext *ctx;
qxiqtim           *sctx;
qxiqtin           *sctx_ind;
ODCIIndexInfo     *ix;
ODCIIndexInfo_ind *ix_ind;
ODCIPredInfo      *pr;
dvoid             *pr_ind;
ODCIQueryInfo     *qy;
dvoid             *qy_ind;
OCINumber         *strt;
short             strt_ind;
OCINumber         *stop;
short             stop_ind;
char              *cmpval;
short             cmpval_ind;
ODCIEnv           *env;
dvoid             *env_ind;
{
  sword status;
  OCIEnv *envhp;                                              /* env. handle */
  OCISvcCtx *svchp;                                        /* service handle */
  OCIError *errhp;                                           /* error handle */
  OCISession *usrhp;                                          /* user handle */
  qxiqtcx *icx;                         /* state to be saved for later calls */

  int strtval;                                                /* start bound */
  int stopval;                                                 /* stop bound */

  int errnum = 29400;                     /* choose some oracle error number */
  char errmsg[512];                                  /* error message buffer */
  size_t errmsglen;                               /* Length of error message */

  char relop[3];                     /* relational operator used in sql stmt */
  char selstmt[2000];                                /* sql select statement */

  int retval = (int)ODCI_SUCCESS;               /* return from this function */
  OCINumber *rval = (OCINumber *)0;
  ub4 key;                                        /* key value set in "sctx" */

  /* Get oci handles */
  if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
    return(rval);

  /* set up return code */
  rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
  if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                           sizeof(retval),
                                           OCI_NUMBER_SIGNED, rval)))
    return(rval);

  /* get the user handle */
  if (qxiqtce(ctx, errhp, OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
                                     (dvoid *)&usrhp, (ub4 *)0,
                                     (ub4)OCI_ATTR_SESSION,
                                     errhp)))
    return(rval);


  /**********************************************/
  /* Allocate memory to hold index scan context */
  /**********************************************/
  if (qxiqtce(ctx, errhp, OCIMemoryAlloc((dvoid *)usrhp, errhp,
                                         (dvoid **)&icx,
                                         OCI_DURATION_STATEMENT,
                                         (ub4)(sizeof(qxiqtcx)),
                                         OCI_MEMORY_CLEARED)))
    return(rval);

  icx->stmthp = (OCIStmt *)0;
  icx->defnp = (OCIDefine *)0;
  icx->bndp = (OCIBind *)0;

  /***********************************/
  /* Check that the bounds are valid */
  /***********************************/
  /* convert from oci numbers to native numbers */
  if (qxiqtce(ctx, errhp, OCINumberToInt(errhp, strt,
                                         sizeof(strtval), OCI_NUMBER_SIGNED,
                                         (dvoid *)&strtval)))
    return(rval);
  if (qxiqtce(ctx, errhp, OCINumberToInt(errhp, stop,
                                        sizeof(stopval),
                                        OCI_NUMBER_SIGNED, (dvoid *)&stopval)))
    return(rval);

  /* verify that strtval/stopval are both either 0 or 1 */
  if (!(((strtval == 0) && (stopval == 0)) ||
        ((strtval == 1) && (stopval == 1))))
    {
      strcpy(errmsg, "Incorrect predicate for sbtree operator");
      errmsglen = (size_t)strlen(errmsg);
      if (OCIExtProcRaiseExcpWithMsg(ctx, errnum, (text *)errmsg, errmsglen)
          != OCIEXTPROC_SUCCESS)
        /* Use cartridge error services here */;
      return(rval);
    }

  /*********************************************/
  /* Generate the SQL statement to be executed */
  /*********************************************/
  if (memcmp((dvoid *)OCIStringPtr(envhp, pr->ObjectName), "EQ", 2)
      == 0)
    if (strtval == 1)
      strcpy(relop, "=");
    else
      strcpy(relop, "!=");
  else if (memcmp((dvoid *)OCIStringPtr(envhp, pr->ObjectName), "LT",
                  2) == 0)
    if (strtval == 1)
      strcpy(relop, "<");
    else
      strcpy(relop, ">=");
  else
    if (strtval == 1)
      strcpy(relop, ">");
    else
      strcpy(relop, "<=");

  if (ix_ind->IndexPartition == OCI_IND_NULL)
  {
    sprintf(selstmt, "select f2 from %s.%s_sbtree where f1 %s :val",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName), relop);
  }
  else
  {
    sprintf(selstmt, "select f2 from %s.%s_%s_sbtree where f1 %s :val",
                  OCIStringPtr(envhp, ix->IndexSchema),
                  OCIStringPtr(envhp, ix->IndexName), 
                  OCIStringPtr(envhp, ix->IndexPartition), relop);
  }

  /***********************************/
  /* Parse, bind, define and execute */
  /***********************************/
  /* allocate stmt handle */
  if (qxiqtce(ctx, errhp,
              OCIHandleAlloc((dvoid *)envhp, (dvoid **)&(icx->stmthp),
                             (ub4)OCI_HTYPE_STMT, (size_t)0,
                             (dvoid **)0)))
    return(rval);
  /* prepare the statement */
  if (qxiqtce(ctx, errhp, OCIStmtPrepare(icx->stmthp, errhp, (text *)selstmt,
                                         (ub4)strlen(selstmt), OCI_NTV_SYNTAX,
                                         OCI_DEFAULT)))
    return(rval);

  /* Set up bind */
  if (qxiqtce(ctx, errhp,
              OCIBindByPos(icx->stmthp, &(icx->bndp), errhp, (ub4)1,
                           (dvoid *)cmpval,
                           (sb4)(strlen(cmpval)+1),
                           (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                           (ub2 *)0, (ub4)0, (ub4 *)0,
                           (ub4)OCI_DEFAULT)))
    return(rval);

  /* Set up define */
  if (qxiqtce(ctx, errhp, OCIDefineByPos(icx->stmthp, &(icx->defnp), errhp,
                                         (ub4)1, (dvoid *)(icx->ridp),
                                         (sb4) sizeof(icx->ridp),
                                         (ub2)SQLT_STR, (dvoid *)0, (ub2 *)0,
                                         (ub2 *)0, (ub4)OCI_DEFAULT)))
    return(rval);

  /* execute */
  if (qxiqtce(ctx, errhp, OCIStmtExecute(svchp, icx->stmthp, errhp, (ub4)0,
                                         (ub4)0, (OCISnapshot *)NULL,
                                         (OCISnapshot *)NULL,
                                         (ub4)OCI_DEFAULT)))
    return(rval);

  /************************************/
  /* Set index context to be returned */
  /************************************/
  /* generate a key */
  if (qxiqtce(ctx, errhp, OCIContextGenerateKey((dvoid *)usrhp, errhp, &key)))
    return(rval);
  /* set the memory address of the struct to be saved in the context */
  if (qxiqtce(ctx, errhp, OCIContextSetValue((dvoid *)usrhp, errhp,
                                             OCI_DURATION_STATEMENT,
                                             (ub1 *)&key, (ub1)sizeof(key),
                                             (dvoid *)icx)))
    return(rval);
  /* set the key as the member of "sctx" */
  if (qxiqtce(ctx, errhp, OCIRawAssignBytes(envhp, errhp, (ub1 *)&key,
                                            (ub4)sizeof(key),
                                            &(sctx->sctx_qxiqtim))))
    return(rval);

  sctx_ind->atomic_qxiqtin = OCI_IND_NOTNULL;
  sctx_ind->scind_qxiqtin = OCI_IND_NOTNULL;

  return(rval);
}

Implementation of the ODCIIndexFetch Routine

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

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 with them. When the batch is full or when there are no more rowids left, the function returns them back to the Oracle server.

OCINumber *qxiqtbf(ctx, self, self_ind, nrows, nrows_ind, rids, rids_ind,
                   env, env_ind)
OCIExtProcContext *ctx;
qxiqtim           *self;
qxiqtin           *self_ind;
OCINumber         *nrows;
short             nrows_ind;
OCIArray          **rids;
short             *rids_ind;
ODCIEnv           *env;
dvoid             *env_ind;
{
  sword status;
  OCIEnv *envhp;
  OCISvcCtx *svchp;
  OCIError *errhp;
  OCISession *usrhp;                                          /* user handle */
  qxiqtcx *icx;

  int idx = 1;
  int nrowsval;

  OCIArray *ridarrp = *rids;                             /* rowid collection */
  OCIString *ridstr = (OCIString *)0;

  int done = 0;
  int retval = (int)ODCI_SUCCESS;
  OCINumber *rval = (OCINumber *)0;

  ub1 *key;                                       /* key to retrieve context */
  ub4 keylen;                                               /* length of key */

  /*******************/
  /* Get OCI handles */
  /*******************/
  if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
      return(rval);

  /* set up return code */
  rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
  if (qxiqtce(ctx, errhp,
              OCINumberFromInt(errhp, (dvoid *)&retval, sizeof(retval),
                                           OCI_NUMBER_SIGNED, rval)))
    return(rval);

  /* get the user handle */
  if (qxiqtce(ctx, errhp, OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
                                     (dvoid *)&usrhp, (ub4 *)0,
                                     (ub4)OCI_ATTR_SESSION, errhp)))
    return(rval);

  /********************************/
  /* Retrieve context from key    */
  /********************************/
  key = OCIRawPtr(envhp, self->sctx_qxiqtim);
  keylen = OCIRawSize(envhp, self->sctx_qxiqtim);

  if (qxiqtce(ctx, errhp, OCIContextGetValue((dvoid *)usrhp, errhp,
                                             key, (ub1)keylen,
                                             (dvoid **)&(icx))))
    return(rval);

  /* get value of nrows */
  if (qxiqtce(ctx, errhp, OCINumberToInt(errhp, nrows, sizeof(nrowsval),
                                       OCI_NUMBER_SIGNED, (dvoid *)&nrowsval)))
    return(rval);

  /****************/
  /* Fetch rowids */
  /****************/
  while (!done)
  {
    if (idx > nrowsval)
      done = 1;
    else
    {
      status = OCIStmtFetch(icx->stmthp, errhp, (ub4)1, (ub2) 0,
                            (ub4)OCI_DEFAULT);
      if (status == OCI_NO_DATA)
      {
        short col_ind = OCI_IND_NULL;
        /* have to create dummy oci string */
        OCIStringAssignText(envhp, errhp, (text *)"dummy",
                            (ub2)5, &ridstr);
        /* append null element to collection */
        if (qxiqtce(ctx, errhp, OCICollAppend(envhp, errhp,(dvoid *)ridstr,
                                              (dvoid *)&col_ind,
                                              (OCIColl *)ridarrp)))
          return(rval);
        done = 1;
      }
      else if (status == OCI_SUCCESS)
      {
        OCIStringAssignText(envhp, errhp, (text *)icx->ridp,
                            (ub2)18, (OCIString **)&ridstr);
        /* append rowid to collection */
        if (qxiqtce(ctx, errhp, OCICollAppend(envhp, errhp, (dvoid *)ridstr,
                                              (dvoid *)0, (OCIColl *)ridarrp)))
          return(rval);
        idx++;
      }
      else if (qxiqtce(ctx, errhp, status))
        return(rval);
    }
  }

  /* free ridstr finally */
  if (ridstr &&
      (qxiqtce(ctx, errhp, OCIStringResize(envhp, errhp, (ub4)0,
                                           &ridstr))))
    return(rval);

  *rids_ind = OCI_IND_NOTNULL;

  return(rval);
}

Implementation of the ODCIIndexClose Routine

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

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

OCINumber *qxiqtbc(ctx, self, self_ind, env, env_ind)
OCIExtProcContext *ctx;
qxiqtim           *self;
qxiqtin           *self_ind;
ODCIEnv           *env;
dvoid             *env_ind;
{
  sword status;
  OCIEnv *envhp;
  OCISvcCtx *svchp;
  OCIError *errhp;
  OCISession *usrhp;                                          /* user handle */

  qxiqtcx *icx;

  int retval = (int) ODCI_SUCCESS;
  OCINumber *rval = (OCINumber *)0;

  ub1 *key;                                       /* key to retrieve context */
  ub4 keylen;                                               /* length of key */

  if (qxiqtce(ctx, errhp, OCIExtProcGetEnv(ctx, &envhp, &svchp, &errhp)))
      return(rval);

  /* set up return code */
  rval = (OCINumber *)OCIExtProcAllocCallMemory(ctx, sizeof(OCINumber));
  if (qxiqtce(ctx, errhp, OCINumberFromInt(errhp, (dvoid *)&retval,
                                           sizeof(retval),
                                           OCI_NUMBER_SIGNED, rval)))
    return(rval);

  /* get the user handle */
  if (qxiqtce(ctx, errhp, OCIAttrGet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
                                     (dvoid *)&usrhp, (ub4 *)0,
                                     (ub4)OCI_ATTR_SESSION, errhp)))
    return(rval);

  /********************************/
  /* Retrieve context using key   */
  /********************************/
  key = OCIRawPtr(envhp, self->sctx_qxiqtim);
  keylen = OCIRawSize(envhp, self->sctx_qxiqtim);

  if (qxiqtce(ctx, errhp, OCIContextGetValue((dvoid *)usrhp, errhp,
                                             key, (ub1)keylen,
                                             (dvoid **)&(icx))))
    return(rval);

  /* Free handles and memory */
  if (qxiqtce(ctx, errhp, OCIHandleFree((dvoid *)icx->stmthp,
                                        (ub4)OCI_HTYPE_STMT)))
    return(rval);

  if (qxiqtce(ctx, errhp, OCIMemoryFree((dvoid *)usrhp, errhp, (dvoid *)icx)))
    return(rval);

  return(rval);
}

Implementing the Indextype

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 ODCIIndex interface routines.

CREATE INDEXTYPE psbtree
FOR
eq(VARCHAR2, VARCHAR2),
lt(VARCHAR2, VARCHAR2),
gt(VARCHAR2, VARCHAR2)
USING psbtree_im
WITH LOCAL RANGE PARTITION

Usage examples

One typical usage scenario is described in the following example. Create a range partitioned table and populate it.

CREATE TABLE t1 (f1 NUMBER, f2 VARCHAR2(200))
PARTITION BY RANGE(f1)
(
  PARTITION p1 VALUES LESS THAN (101),
  PARTITION p2 VALUES LESS THAN (201),
  PARTITION p3 VALUES LESS THAN (301),
  PARTITION p4 VALUES LESS THAN (401)
 );
INSERT INTO t1 VALUES (10, 'aaaa');
INSERT INTO t1 VALUES (200, 'bbbb');
INSERT INTO t1 VALUES (100, 'cccc');
INSERT INTO t1 VALUES (300, 'dddd');
INSERT INTO t1 VALUES (400, 'eeee');
COMMIT;

Create a psbtree index on column f2. The create index statement specifies the indextype to be used.

CREATE INDEX it1 ON t1(f2) iINDEXTYPE IS psbtree LOCAL 
(PARTITION pe1 PARAMETERS('test1'), PARTITION pe2,
 PARTITION pe3, PARTITION pe4 PARAMETERS('test4')) 
PARAMETERS('test');

Execute a query that uses one of the sbtree operators. ·

SELECT * FROMM t1 WHERE eq(f2, 'dddd') = 1 AND f1>101 ;

Explain Plan Output

OPERATION OPTIONS PARTITION_START PARTITION_STOP ---------------------------------------------------------------

SELECT STATEMENT

PARTITION RANGE ITERATOR 2 4

TABLE ACCESS BY LOCAL INDEX ROWID 2 4

DOMAIN INDEX