15
Reference -- Extensible Indexing Interface

The following ODCI (Oracle Data Cartridge Interface) interfaces are described:

• ODCIGetInterfaces

• ODCIIndexAlter

• OCDIIndexCreate

• ODCIIndexClose

• ODCIIndexDelete

• ODCIIndexDrop

• ODCIIndexFetch

• ODCIIndexGetMetadata

• ODCIIndexInsert

• ODCIIndexStart

• ODCIIndexTruncate

• OCIIndexUpdate

---

Extensible Indexing -- System Defined Interface Routines

Caution: These routines are invoked by Oracle at the appropriate times based on SQL statements executed by the end user. The user should not try to invole these routines directly as this may result in corruption of index data.

---

ODCIGetInterfaces

Syntax

ODCIGetInterfaces(ifclist OUT ODCIObjectList) RETURN NUMBER

Purpose

The ODCIGetInterfaces function is invoked when an INDEXTYPE is created by a CREATE INDEXTYPE... statement.

Table 15-1 OCDIGetInterfaces Arguments

Argument	Meaning
ifclist	Contains information about the list of interfaces it supports

Returns

• ODCIConst.Success on success

• ODCIConst.Error on error.

Usage Notes

This function should be implemented as a static type method.

This function must return 'SYS.ODCIINDEX1' in the ODCIObjectList. This indicates that the indextype uses the first version of the ODCIIndex interface.

---

ODCIIndexAlter

Syntax

ODCIIndexAlter(ia ODCIIndexInfo, parms IN OUT VARCHAR2, alter_option NUMBER) 
RETURN NUMBER

Purpose

The ODCIIndexAlter procedure is invoked when a domain index is altered using an ALTER INDEX statement.

Table 15-2 ODCIIndexAlter Arguments

Argument	Meaning
ia	Contains information about the indexed column(s)
parms (IN)	Parameter string if ALTER INDEX PARAMETERS or ALTER INDEX REBUILD contains the user specified parameter string if ALTER INDEX RENAME contain the new name of the domain index
parms (OUT)	Parameter string is only valid if ALTER INDEX PARAMETERS or ALTER INDEX REBUILD. Contains the resultant string to be stored in system catalogs
alter_option	Specifies one of the following options: AlterIndexNone if ALTER INDEX PARAMETERS AlterIndexRename if ALTER INDEX RENAME AlterIndexRebuild if ALTER INDEX REBUILD

Returns

• ODCIConst.Success on success

• ODCIConst.Warning on warning,

• ODCIConst.Error on error.

Usage Notes

• This function should be implemented as a static type method.

• An ALTER INDEX statement can be invoked for domain indexes in multiple ways.

  ALTER INDEX index_name
  PARAMETERS params;
  
  or
  
  ALTER INDEX index_name
  REBUILD PARAMETERS params;
      
  
           

  The precise behavior in these two cases is defined by the implementor. One possibility is that the first statement would merely reorganize the index based on the parameters while the second would rebuild it from scratch.

• The maximum length of the input parameters string is 1000 characters. The OUT value of the parms argument can be set to resultant parameters string to be stored in the system catalogs.

• The ALTER INDEX statement can also be used to rename a domain index in the following way:

  ALTER INDEX index_name
  RENAME TO new_index_name
  
  

  In this case, the new name of the domain index is passed to the parms argument.

• If the ODCIIndexAlter routine returns with the ODCIConst.Success, the index is valid and usable. If the ODCIIndexAlter routine returns with ODCIConst.Warning, the index is valid and usable but a warning message is returned to the user. If ODCIIndexAlter returns with an error (or exception), the domain index will be marked FAILED.

• When the ODCIIndexAlter routine is being executed, the domain index is marked LOADING.

---

OCDIIndexCreate

Syntax

OCDIIndexCreate(ia ODCIIndexInfo, parms VARCHAR2) RETURN NUMBER

Purpose

The OCDIIndexCreate procedure is invoked when a domain index is created by a CREATE INDEX ... INDEXTYPE IS .... PARAMETERS ... statement issued by the user.

Table 15-3 OCDIIndexCreate Arguments

Argument	Meaning
ia	Contains information about the indexed column
parms	Is the PARAMETERS string passed in uninterpreted by Oracle. The maximum size of the parameter string is 1000 characters.

Returns

• ODCIConst.Success on success

• ODCIConst.Warning on warning,

• ODCIConst.Error on error.

Usage Notes

• This function should be implemented as a static type method.

• The OCDIIndexCreate procedure should handle creation of indexes on both empty and non-empty tables. If the base table is not empty, the OCDIIndexCreate procedure can scan the entire table and generate index data.

• Every SQL statement executed by OCDIIndexCreate is treated as an independent operation. The changes made by OCDIIndexCreate are not guaranteed to be atomic.

• When the OCDIIndexCreate routine is being executed, the domain index is marked LOADING.

• If the ODCIIndexCreate routine returns with the ODCIConst.Success, the index is valid and usable. If the ODCIIndexCreate routine returns with ODCIConst.Warning, the index is valid and usable but a warning message is returned to the user. If the OCDIIndexCreate routine returns with an ODCIConst.Error (or exception), the domain index will be marked FAILED.

• The only operations permitted on FAILED domain indexes is DROP INDEX, TRUNCATE INDEX or ALTER INDEX REBUILD.

• If a domain index is crated on an column of object type which contains a REF attribute, you should not be dereferencing the REFs while building your index. Dereferencing a REF fetches data from a different table instance. If the data in the other table is modifieD, you Will not be notified and your domain index will become incorrect.

---

ODCIIndexClose

Syntax

ODCIIndexClose(self IN <impltype>) RETURN NUMBER

Purpose

This procedure is invoked to end the processing of an operator.

Table 15-4 ODCIIndexClose Arguments

Argument	Meaning
self(IN)	Is the value of the context returned by the previous invocation of ODCIIndexFetch

Returns

• ODCIConst.Success on success

• ODCIConst.Error on error.

Usage Notes

• The index implementor can perform any appropriate actions to finish up the processing of an domain index scan, such as freeing memory and other resources.

---

ODCIIndexDelete

Syntax

ODCIIndexDelete(ia ODCIIndexInfo, rid VARCHAR2, oldval <icoltype>) RETURN NUMBER

Purpose

This procedure is invoked when a row is deleted from a table that has a domain index defined on one of its columns.

Table 15-5 ODCIIndexDelete Arguments

Argument	Meaning
ia	Contains information about the indexed column
rid	Is the row identifier of the deleted row
oldval	Is the value of the indexed column in the deleted row. The datatype is the same as that of the indexed column.

Returns

• ODCIConst.Success on success

• ODCIConst.Error on error.

Usage Notes

• This function should be implemented as a static type method.

• This procedure should delete index data corresponding to the deleted row from the appropriate tables/files storing index data.

---

ODCIIndexDrop

Syntax

ODCIIndexDrop(ia ODCIIndexInfo) RETURN NUMBER

Purpose

The ODCIIndexDrop procedure is invoked when a domain index is dropped explicitly using a DROP INDEX statement, or implicitly through a DROP TABLE, or DROP USER statement.

Table 15-6 ODCIIndexDrop Arguments

Argument	Meaning
ia	Contains information about the indexed column

Returns

• ODCIConst.Success on success

• ODCIConst.Warning on warning,

• ODCIConst.Error on error.

Usage Notes

• This function should be implemented as a static type method.

• This procedure should drop the domain index data.

• Since it is possible that the domain index is marked FAILED (due to abnormal termination of some DDL routine), the ODCIIndexDrop routine should be capable of cleaning up partially created domain indexes. When the ODCIIndexDrop routine is being executed, the domain index is marked LOADING.

• It should be noted that if the ODCIIndexDrop routine returns with an ODCIConst.Error or exception, the DROP INDEX statement would fail and the index would be marked FAILED. In that case, there is no mechanism to get rid of the domain index except by using the FORCE option. If the ODCIIndexDrop routine returns with ODCIConst.Warning in the case of an explicit DROP INDEX statement, the operation succeeds but a warning message is returned to the user.

---

ODCIIndexFetch

Syntax

ODCIIndexFetch(self IN [OUT] <impltype>, nrows IN NUMBER, rids OUT ODCIRidList) 
RETURN NUMBER

Purpose

This procedure is invoked repeatedly to retrieve the rows satisfying the operator predicate.

Table 15-7 ODCIIndexFetch Arguments

Argument	Meaning
self(IN)	Is the value of the context returned by the previous call (to ODCIIndexFetch or to ODCIIndexStart if this is the first time fetch is being called for this operator instance
self(OUT)	The context that is passed to the next query-time call.Note that this parameter does not have to be defined as OUT if the value is not modified in this routine.
nrows	Is the maximum number of result rows that can be returned to Oracle in this call
rids	Is the array of row identifiers for the result rows being returned by this call

Returns

• ODCIConst.Success on success

• ODCIConst.Error on error.

Usage Notes

• ODCIIndexFetch returns rows satisfying the operator predicate. i.e. it returns the row identifiers of all the rows for which the operator return value falls within the specified bounds.

• Each call to ODCIIndexFetch can return a maximum of nrows number of rows. The value of nrows passed in is decided by Oracle based on some internal factors. However, the ODCIIndexFetch routine can return lesser than nrows number of rows. The row identifiers are returned through the output rids array. A NULL ROWID (as an element of the rids array) indicates that all satisfying rows have been returned.

  Assume that there are 3000 rows which satisfy the operator predicate, and that the value of nrows = 2000. The first invocation of ODCIIndexFetch can return the first 2000 rows. The second invocation can return a ridlist consisting of the remaining 1000 rows followed by a NULL element. The NULL value in ridlist indicates that all satisfying rows have now been returned.

• If the context value is changed within this call, the new value will be passed in to the subsequent query-time calls.

---

ODCIIndexGetMetadata

Syntax

FUNCTION ODCIIndexGetMetadata(
     
ia          IN    ODCIIndexInfo,
version     IN    VARCHAR2,
new_block   OUT   PLS_INTEGER)
     
RETURN VARCHAR2;

              

Purpose

This routine is called repeatedly to return a series of strings of PL/SQL code that comprise the non-dictionary metadata associated with the index in ia. The routine can pass whatever information is required at import time; that is, policy, version, preferences, and so on. This method on the ODCIIndex interface is required in version 8.1.3 and must be implemented by all domain index implementation types. If ODCIIndexGetMetadata is not found, export will abort the creation of the index. However, for the final release of 8.1, this method will be optional if no implementation-specific metadata is required.

Table 15-8 ODCIIndexGetMetadata Arguments

Argument	Description
ia	Specifies the index on which export is currently working.
version	Version of export making the call in the form 08.01.03.00.00.
new_block	Non-zero (TRUE): Returned string starts a new PL/SQL block. Export will terminate the current block (if any) with END; and open a new block with BEGIN before writing strings to the dump file. The routine is called again. 0 (FALSE): Returned string continues current block. Export writes only the returned string to the dump file then calls the routine again.

Developers of domain index implementation types in 8.1.3 must implement ODCIIndexGetMetadata even if only to indicate that no PL/SQL metadata exists or that the index is not participating in fast rebuild.

For informational on the PL/SQL interfaces in SYS.DBMS that export will call, see Oracle8i Supplied Packages Reference.

Returns

• A null-terminated string containing a piece of an opaque block of PL/SQL code.

• A zero-length string indicates no more data; export stops calling the routine.

Usage Notes

This function should be implemented as a static type method.

The routine will be called repeatedly until the return string length is 0. If an index has no metadata to be exported using PL/SQL, it should return an empty string upon first call.

This routine can be used to build one or more blocks of anonymous PL/SQL code for execution by import.Each block returned will be invoked independently by import. That is, if a block fails for any reason at import time, subsequent blocks will still be invoked. Therefore any dependent code should be incorporated within a single block. The size of an individual block of PL/SQL code is limited only by the size of import's read buffer controlled by its BUFFER parameter.

The execution of these PL/SQL blocks at import time will be considered part of the associated domain index's creation. Therefore, their execution will be dependent upon the successful import of the index's underlying base table and user's setting of import's INDEXES=Y/N parameter, as is the creation of the index.

The routine should not pass back the BEGIN/END strings that open and close the individual blocks of PL/SQL code; export will add these to mark the individual units of execution.

The parameter version is the version number of the currently executing export client. Since export and import can be used to downgrade a database to the previous functional point release, it also represents the minimum server version you can expect to find at import time; it may be higher, but never lower.

The cartridge developer can use this information to determine what version of information should be written to the dump file. For example, assume the current server version is 08.02.00.00.00, but the export version handed in is 08.01.04.00.00. If a cartridge's metadata changed formats between 8.1 and 8.2, it would know to write the data to the dump file in 8.1 format anticipating an import into an 8.1.4 system. Server versions starting at 8.2 and higher will have to know how to convert 8.1 format metadata.

Some points of caution:

1. The data contained within the strings handed back to export must be completely platform-independent. That is, they should contain no binary information that may reflect the endian nature of the export platform which may be different from the import platform. Binary information may be passed as hex strings and converted via RAWTOHEX and HEXTORAW.

2. The strings are translated from the export server to export client character set and are written to the dump file as such. At import time, they are translated from export client character set to import client character set, then from import client char set to import server character set when handed over the UPI interface.

3. Specifying a specific target schema in the execution of any of the PL/SQL blocks should be avoided as it will most likely cause an error if you exercise import's FROMUSER -> TOUSER schema replication feature. For example, a procedure prototype such as:

   PROCEDURE AQ_CREATE ( schema IN VARCHAR2, que_name IN VARCHAR2) ...
   

   Should be avoided since this will fail if you have remapped schema A to schema B on import. You can assume at import time that you are already connected to the target schema.

4. Export dump files from a particular version must be importable into all future versions. This means that all PL/SQL routines invoked within the anonymous PL/SQL blocks written to the dump file must be supported for all time. You may wish to encode some version information to assist with detecting when conversion may be required.

5. Export will be operating in a read-only transaction if its parameter CONSISTENT=Y. In this case, no writes are allowed from the export session. Therefore, this method must not write any database state.

6. You can attempt to import the same dump file multiple times, especially when using import's IGNORE=Y parameter. Therefore, this method must produce PL/SQL code that is idempotent, or at least deterministic when executed multiple times.

7. Case on database object names must be preserved; that is, objects named 'Foo' and 'FOO' are distinct objects. Database object names should be enclosed within double quotes ("") to preserve case.

Error Handling

Any unrecoverable error should raise an exception allowing it to propagate back to get_domain_index_metadata and thence back to export. This will cause export to abort the creation of the current index's DDL in the dump file and to move on to the next index.

At import time, failure of the execution of any metadata PL/SQL block will cause the associated index to not be created under the assumption that the metadata creation is an integral part of the index creation.

---

ODCIIndexInsert

Syntax

ODCIIndexInsert(ia ODCIIndexInfo, rid VARCHAR2, newval <icoltype>) RETURN NUMBER

Purpose

This procedure is invoked when a new row is inserted into a table that has a domain index defined on one of its columns.

Table 15-9 ODCIIndexInsert Arguments

Argument	Meaning
ia	Contains information about the indexed column
rid	Is the row identifier of the new row in the table
newval	Is the value of the indexed column in the inserted row.

Returns

• ODCIConst.Success on success

• ODCIConst.Error on error.

Usage Notes

This function should be implemented as a static type method.

This procedure should insert index data corresponding to the new row into the appropriate tables/files storing index data.

---

ODCIIndexStart

Syntax

ODCIIndexStart(scanctx IN OUT <impltype>, ia ODCIIndexInfo, pi ODCIPredInfo, qi 
ODCIQueryInfo, strt <opbndtype>, stop <opbndtype>, <valargs>) RETURN NUMBER

Purpose

This procedure is invoked to start the evaluation of an operator on an indexed column.

Table 15-10 ODCIIndexStart Arguments

Argument	Meaning
scanctx(IN)	Is the value of the context returned by some previous related query-time call (e.g., the corresponding ancillary operator, if invoked before the primary operator), NULL otherwise
scanctx(OUT)	The context that is passed to the next query-time call; the next query-time call will be to ODCIIndexFetch
ia	Contains information about the indexed column
pi	Contains information about the operator predicate
qi	Contains query information (hints plus list of ancillary operators referenced)
strt	Is the start value of the bounds on the operator return value. The datatype is the same as that of the operator's return value
stop	Is the stop value of the bounds on the operator return value. The datatype is the same as that of the operator's return value.
valargs	Are the value arguments of the operator invocation. The number and datatypes of these arguments are the same as those of the value arguments to the operator.

Returns

• ODCIConst.Success on success

• ODCIConst.Error on error.

Usage Notes

• ODCIIndexStart is invoked to begin the evaluation of an operator on a indexed column. In particular, the following conditions hold:
  • The first argument to the operator is a column which has a domain index defined on it.

  • The indextype of the domain index (specified in ODCIIndexInfo parameter) supports the current operator.

  • All other arguments to the operator are value arguments (literals) which are passed in through the <valargs> parameters.

• The ODCIIndexStart procedure should initialize the index scan as needed (using the operator-related information in pi argument) and prepare for the subsequent invocations of ODCIIndexFetch.

• The strt, stop parameters together with the bndflg value in ODCIPredInfo parameter specify the range of values within which the operator return value should lie.

• Bounds for operator return values are specified as follows:
  • If the predicate to be evaluated is of the form op LIKE val, the ODCIIndexPrefixMatch flag is set. In this case, the start key contains the value <val> and the stop key value is irrelevant.

  • If the predicate to be evaluated is of the form op = val, the ODCIIndexExactMatch flag is set. In this case, the start key contains the value <val> and the stop key value is irrelevant.

  • If the predicate to be evaluated is of the form op > val, startkey contains the value <val> and stop key value is set to NULL. If the predicate is of the form op >= <val>, the flag ODCIIndexIncludeStart is also set.

  • If the predicate to be evaluated is of the form op < val, stop key contains the value <val> and the start key value is set to NULL. If the predicate is of the form op <= val, the flag ODCIIndexIncludeStop is also set.

• A context value can be returned back to Oracle (through the SELF argument) which will then be passed back to the next query-time call. The next call will be to ODCIIndexFetch, if the evaluation continues or to ODCIIndexStart if the evaluation is restarted. The context value can be used to store the entire evaluation state or just a handle to the memory containing the state.

• Note that if the same indextype supports multiple operators with different signatures, multiple ODCIIndexStart methods need to be implemented, one for each distinct combination of value argument datatypes. For example, if an indextype supports three operators:
  1. op1(number, number)

  2. op1(varchar2, varchar2)

  3. op2(number, number)

  two ODCIIndexStart routines would need to be implemented:

  • ODCIIndexStart(...., NUMBER)-- handles cases (1) and (3) which has a NUMBER value argument

  • ODCIIndexStart(...., VARCHAR2)-- handles case (2) which has a VARCHAR2 value argument

• The query information in qi parameter can be used to optimize the domain index scan, if possible. The query information includes hints that have been specified for the query and the list of relevant anciliary operators referenced in the query block.

---

ODCIIndexTruncate

Syntax

ODCIIndexTruncate(ia ODCIIndexInfo) RETURN NUMBER

Purpose

The ODCIIndexTruncate procedure is invoked when a TRUNCATE statement is issued against a table that has a domain index defined on one of its columns.

Table 15-11 ODCIIndexTruncate Arguments

Argument	Meaning
ia	Contains information about the indexed column

Returns

• ODCIConst.Success on success

• ODCIConst.Warning on warning,

• ODCIConst.Error on error.

Usage Notes

• This function should be implemented as a static type method.

• After this function executes, the domain index should be empty (corresponding to the empty base table).

• While the ODCIIndexTruncate routine is being executed, the domain index is marked LOADING. If the ODCIIndexTruncate routine returns with an ODCIConst.Error (or exception), the domain index will be marked FAILED. The only operation permitted on FAILED domain indexes is DROP INDEX, TRUNCATE INDEX or ALTER INDEX REBUILD. If ODCIIndexTruncate returns with ODCIConst.Warning, the operation succeeds but a warning message is returned to the user.

---

OCIIndexUpdate

Syntax

OCIIndexUpdate(ia ODCIIndexInfo, rid VARCHAR2, oldval <icoltype>, newval 
<icoltype>) RETURN NUMBER

Purpose

This procedure is invoked when a column is updated that has a domain index defined on it.

Table 15-12 OCIIndexUpdate Arguments

Argument	Meaning
ia	Contains information about the indexed column
rid	Is the row identifier of the updated row
oldval	Is the value of the indexed column before the update. The datatype is the same as that of the indexed column.
newval	Is the value of the indexed column after the update. The datatype is the same as that of the indexed column.

Returns

• ODCIConst.Success on success

• ODCIConst.Error on error.

Usage Notes

• This procedure should update the index data (corresponding to the updated row) in the appropriate tables/files storing index data.

• In addition to a SQL UPDATE statement, a LOB value can be updated through a vairety of "WRITE" interfaces (see Oracle8i Application Developer's Guide - Large Objects (LOBs)). If a domain index is defined on a LOB column or an object type containing a LOB attribute, the ODCIIndexUpdate routine is called when a LOB locator is implicitly or explicitly closed after one or more write operations.

---