extdemo3.sql Demonstration Script

The extdemo3.sql script demonstrates extensible indexing, implemented as C 
routines. It illustrates how to implement the interface routines in Java, but 
does not go into the complex domain details of actually implementing an 
indextype for a specific domain. 

Design of the indextype

The indextype implemented here, called extdemo3, operates similar to btree 
indexes. It supports the evaluation of three user-defined operators 
     gt(Greater Than) 

     lt(Less Than) 

     eq(EQuals) 

These operators can operate on the operands of VARCHAR2 datatype.

To simplify the implementation of the indextype, we will store
the index data in a regular table.
Thus, our code merely translates operations on the SB-tree into
operations on the table storing the index data.
When a user creates a SB-tree index, we will create a table
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 SB-tree is queried based on a user-defined operator (one
of gt, lt and eq), we will fire off an appropriate query against
the index table to retrieve all the satisfying rows and return them.


Implementing Operators

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. 

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 (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;

Functional Implementation of GT (GREATER THAN)

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;

Create Operators

To create the operator, you need to specify the signature of the operator along 
with its return type and also 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. Note that the mapping between the function and the java class is done 
at this point. 

create or replace type extdemo3 as object
(
   scanctx integer,
   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,


Register the implementation of the ODCIIndexInsert routine. 


  STATIC FUNCTION odciindexinsert(ia sys.odciindexinfo, rid VARCHAR2, 
                                                    newval VARCHAR2)
                                  RETURN NUMBER AS LANGUAGE JAVA NAME 
	'extdemo3.ODCIInsert(oracle.ODCI.ODCIIndexInfo, java.lang.String, 
		java.lang.String) return java.math.BigDecimal',
  

Register the implementation of the ODCIIndexDelete routine 


  STATIC FUNCTION odciindexdelete(ia sys.odciindexinfo, rid VARCHAR2, 
                                                    oldval VARCHAR2)
                                  RETURN NUMBER AS LANGUAGE JAVA NAME
	'extdemo3.ODCIDelete(oracle.ODCI.ODCIIndexInfo, java.lang.String, 
		java.lang.String) return java.math.BigDecimal',
  

Register the implementation of the ODCIIndexUpdate routine 


  STATIC FUNCTION odciindexupdate(ia sys.odciindexinfo, rid VARCHAR2, 
                                  oldval VARCHAR2, newval VARCHAR2)
                                  RETURN NUMBER AS LANGUAGE JAVA NAME
	'extdemo3.ODCIUpdate(oracle.ODCI.ODCIIndexInfo, java.lang.String, 
		java.lang.String, java.lang.String) return 
		java.math.BigDecimal',
  

Register the implementation of the ODCIIndexStart routine 


  static function ODCIIndexStart(sctx in out extdemo3, ia sys.odciindexinfo,
                         op sys.odciPredInfo, 
                         qi sys.ODCIQueryInfo, 
                         strt number, 
                         stop number,
                         cmpval varchar2) 
     RETURN NUMBER AS LANGUAGE JAVA NAME
	'extdemo3.ODCIStart(extdemo3[], oracle.ODCI.ODCIIndexInfo, 
		oracle.ODCI.ODCIPredInfo, 
		oracle.ODCI.ODCIQueryInfo, java.math.BigDecimal, 
		java.math.BigDecimal, 
                java.lang.String) return java.math.BigDecimal',



Register the implementation of the ODCIIndexFetch routine 


  member function ODCIIndexFetch(nrows number, rids OUT sys.odciridlist)
   return number as LANGUAGE JAVA NAME
	'extdemo3.ODCIFetch(java.math.BigDecimal, 
	oracle.ODCI.ODCIRidList[]) return java.math.BigDecimal',


Register the implementation of the ODCIIndexClose routine. 


  member function ODCIIndexClose return number as LANGUAGE JAVA NAME
	'extdemo3.ODCIClose() return java.math.BigDecimal'
  
);

2.Define the implementation type body 
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 Java. 

load and resolve the java classes that provide the implementation of 
certain functions defined in the type. See below for the implementation
details of extdemo3a.java and extdemo3.java. 
This requires some setup to be done before this statement. 
Specifically, you need to create a directory object called vmtestdir
that points to the location where your .class files reside. 


CREATE OR REPLACE JAVA CLASS USING BFILE (vmtestdir, 'extdemo3a.class')
/
CREATE OR REPLACE JAVA CLASS USING BFILE (vmtestdir, 'extdemo3.class')
/

ALTER JAVA CLASS "extdemo3a" RESOLVE;
ALTER JAVA CLASS "extdemo3"  RESOLVE;




create or replace type body extdemo3 
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;
  
end;







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 sbtree
for
eq(varchar2, varchar2),
lt(varchar2, varchar2),
gt(varchar2, varchar2)
using extdemo3;



Usage examples

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 parameters('test');



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;



Explain Plan Output 

             OPERATIONS                     OPTIONS                        
OBJECT_NAME            
       
             ------------------------------ ------------------------------ 
-------------
             SELECT STATEMENT                                                                            
             TABLE ACCESS                   BY ROWID                       T1                     
             DOMAIN INDEX                                                  IT1