Release 8.1.5
A68002-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Data Cartridge Developer's Guide Release 8.1.5 A68002-01 |
|
This chapter presents an extensible indexing example in which some of the ODCIIndex interface routines are implemented in C:
This example is meant as an illustration of how to implement the interface routines in C, but does not go into the complex domain details of actually implementing an indextype for a specific domain. The code for the example described here is in the demo directory (See file extdemo2.sql).
The indextype implemented here, called sbtree operates similar to btree indexes. It supports the evaluation of three user-defined operators
These operators can operate on the 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 will be stored in a regular table. Thus, the index manipulation routines merely translate operations on the SBtree into operations on the table storing the index data. When a user creates a SBtree index, a table is created consisting of the indexed column and a rowid column. Inserts into the base table will cause appropriate insertions into the index table. Deletes and updates are handled similarly. When the SBtree is queried based on a user-defined operator (one of gt, lt and eq), an appropriate query will be issued against the index table to retrieve all the satisfying rows.
The SBtree indextype supports three operators. Each operator has a corresponding functional implementation. The functional implementations of the eq, gt and lt operators are presented next.
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;
The functional implementation for lt is provided by a function (lt_eq) 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;
The functional implementation for gt is provided by a function (gt_eq) 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;
To create the operator, you need to specify the signature of the operator along with its return type and also its functional implementation.
CREATE OPERATOR eq BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER USING bt_eq;
CREATE OPERATOR lt BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER USING bt_lt;
CREATE OPERATOR gt BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER USING bt_gt;
CREATE TYPE sbtree_im AS OBJECT ( scanctx RAW(4), STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList) RETURN NUMBER, STATIC FUNCTION ODCIIndexCreate (ia sys.odciindexinfo, parms VARCHAR2) RETURN NUMBER, STATIC FUNCTION ODCIIndexDrop(ia sys.odciindexinfo) RETURN NUMBER, STATIC FUNCTION ODCIIndexInsert(ia sys.odciindexinfo, rid VARCHAR2, newval VARCHAR2) RETURN NUMBER, STATIC FUNCTION ODCIIndexDelete(ia sys.odciindexinfo, rid VARCHAR2, oldval VARCHAR2) RETURN NUMBER, STATIC FUNCTION ODCIIndexUpdate(ia sys.odciindexinfo, rid VARCHAR2, oldval VARCHAR2, newval VARCHAR2) RETURN NUMBER, STATIC FUNCTION ODCIIndexStart(sctx IN OUT sbtree_im, ia sys.odciindexinfo, op sys.odciPredInfo, qi sys.ODCIQueryInfo, strt number, stop number, cmpval VARCHAR2) RETURN NUMBER, MEMBER FUNCTION ODCIIndexFetch(nrows number, rids OUT sys.odciridlist) RETURN NUMBER, MEMBER FUNCTION ODCIIndexClose RETURN NUMBER );
You have a choice of implementing the index routines in any of the languages supported by Oracle. For this example, we will implement the get interfaces routine and the index definition routines in PL/SQL. The index manipulation and query routines are implemented in C.
CREATE OR REPLACE TYPE BODY sbtree_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','ODCIINDEX1')); 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. DBMS_SQL is used to execute the dynamically constructed SQL statement.
STATIC FUNCTION ODCIIndexCreate (ia sys.odciindexinfo, parms VARCHAR2) RETURN NUMBER is i INTEGER; stmt VARCHAR2(1000); cnum INTEGER; junk INTEGER; BEGIN -- construct the sql statement stmt := 'create table ' || ia.IndexSchema || '.' || ia.IndexName || '_sbtree' || '( f1 , f2 ) as select ' || ia.IndexCols(1).ColName || ', ROWID from ' || ia.IndexCols(1).TableSchema || '.' || ia.IndexCols(1).TableName; DBMS_OUTPUT.PUT_LINE('CREATE'); 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); RETURN ODCIConst.Success; END;
The ODCIIndexDrop routine drops the index storage table.
STATIC FUNCTION ODCIIndexDrop(ia sys.odciindexinfo) RETURN NUMBER is stmt VARCHAR2(1000); cnum INTEGER; junk INTEGER; BEGIN -- construct the sql statement stmt := 'drop table ' || ia.IndexSchema || '.' || ia.IndexName || '_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); RETURN ODCIConst.Success; END;
The index manipulation and query routines are implemented in C. This requires some setup to be done before this statement. Specifically, you need to create a library object called extdemo2l for your compiled C code.
After the setup, the foll. 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) RETURN NUMBER AS external name "qxiqtbi" library extdemo2l WITH context parameters ( context, ia, ia indicator struct, rid, rid indicator, newval, newval indicator, RETURN ocinumber );
Register the implementation of the ODCIIndexDelete routine.
STATIC FUNCTION ODCIIndexDelete(ia sys.odciindexinfo, rid VARCHAR2, oldval VARCHAR2) RETURN NUMBER AS external name "qxiqtbd" library extdemo2l WITH context parameters ( context, ia, ia indicator struct, rid, rid indicator, oldval, oldval indicator, RETURN ocinumber );
Register the implementation of the ODCIIndexUpdate routine.
STATIC FUNCTION ODCIIndexUpdate(ia sys.odciindexinfo, rid VARCHAR2, oldval VARCHAR2, newval VARCHAR2) RETURN NUMBER AS external name "qxiqtbu" library extdemo2l WITH context parameters ( context, ia, ia indicator struct, rid, rid indicator, oldval, oldval indicator, newval, newval indicator, RETURN ocinumber );
Register the implementation of the ODCIIndexStart routine.
STATIC FUNCTION ODCIIndexStart(sctx in out sbtree_im, ia sys.odciindexinfo, op sys.odciPredInfo, qi sys.ODCIQueryInfo, strt number, stop number, cmpval VARCHAR2) RETURN NUMBER as external name "qxiqtbs" library extdemo2l 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, RETURN OCINumber );
Register the implementation of the ODCIIndexFetch routine.
member function ODCIIndexFetch(nrows number, rids OUT sys.odciridlist) RETURN NUMBER as external name "qxiqtbf" library extdemo2l with context parameters ( context, self, self INDICATOR STRUCT, nrows, nrows INDICATOR, rids, rids INDICATOR, RETURN OCINumber );
Register the implementation of the ODCIIndexClose routine.
member function ODCIIndexClose RETURN NUMBER as external name "qxiqtbc" library extdemo2l with context parameters ( context, self, self INDICATOR STRUCT, RETURN OCINumber );
The C structs for mapping the ODCI types are all defined in the file "odci.h". For example, the C struct ODCIIndexInfo is the mapping for the corresponding ODCI object type. Further, the C struct ODCIIndexInfo_ind is the mapping for null object.
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); }
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) OCIExtProcContext *ctx; ODCIIndexInfo *ix; ODCIIndexInfo_ind *ix_ind; char *rid; short rid_ind; char *newval; short newval_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 * ******************************/ sprintf(insstmt, "INSERT into %s.%s_sbtree values (:newval, :mrid)", OCIStringPtr(envhp, ix->IndexSchema), OCIStringPtr(envhp, ix->IndexName)); /**************************************** * Parse and Execute Create 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); }
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) OCIExtProcContext *ctx; ODCIIndexInfo *ix; ODCIIndexInfo_ind *ix_ind; char *rid; short rid_ind; char *oldval; short oldval_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 * ******************************/ sprintf(delstmt, "DELETE FROM %s.%s_sbtree WHERE f2 = :rr", OCIStringPtr(envhp, ix->IndexSchema), OCIStringPtr(envhp, ix->IndexName)); /**************************************** * 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); }
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) OCIExtProcContext *ctx; ODCIIndexInfo *ix; ODCIIndexInfo_ind *ix_ind; char *rid; short rid_ind; char *oldval; short oldval_ind; char *newval; short newval_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 * ******************************/ sprintf(updstmt, "UPDATE %s.%s_sbtree SET f1 = :newval, f2 = :rr WHERE f1 = :oldval", OCIStringPtr(envhp, ix->IndexSchema), OCIStringPtr(envhp, ix->IndexName)); /**************************************** * Parse and Execute Create 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); }
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 amongst the index scan routines is an instance of the type sbtree_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 will be 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) OCIExtProcContext *ctx; qxiqtim *sctx; qxiqtin *sctx_ind; ODCIIndexInfo *ix; dvoid *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; { 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, "<="); sprintf(selstmt, "select f2 from %s.%s_sbtree where f1 %s :val", OCIStringPtr(envhp, ix->IndexSchema), OCIStringPtr(envhp, ix->IndexName), 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); }
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) OCIExtProcContext *ctx; qxiqtim *self; qxiqtin *self_ind; OCINumber *nrows; short nrows_ind; OCIArray **rids; short *rids_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); }
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) OCIExtProcContext *ctx; qxiqtim *self; qxiqtin *self_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); }
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 sbtree FOR eq(VARCHAR2, VARCHAR2), lt(VARCHAR2, VARCHAR2), gt(VARCHAR2, VARCHAR2) USING sbtree_im;
One typical usage scenario is described below. Create a table and populate it.
CREATE TABLE t1 (f1 number, f2 VARCHAR2(200)); INSERT INTO t1 VALUES (1, 'adam'); insert into t1 VALUES (3, 'joe');
Create a sbtree index on column f2. The create index statement specifies the indextype to be used.
create index it1 on t1(f2) indextype is sbtree;
Execute a query that uses one of the sbtree operators. The explain plan output for the same shows that the domain index is being used to efficiently evaluate the query.
SELECT * FROM t1 WHERE eq(f2, 'joe') = 1;
OPERATIONS OPTIONS OBJECT_NAME ------------------------------ ------------------------------ ------------- SELECT STATEMENT TABLE ACCESS BY ROWID T1 DOMAIN INDEX IT1
