Power Demand Cartridge Example , 4 of 8

Creating the Domain Index

This section explains the parts of the power demand cartridge as they relate to extensible indexing. Explanatory text and code segments are mixed.

The entire cartridge definition is available online as extdemo1.sql in the standard Oracle demo directory (location is platform-dependent).

Creating the Schema to Own the Index

Before you create a domain index, create a database user (schema) to own the index. In the power demand example, the user PowerCartUser is created and granted the appropriate privileges. All database structures related to the cartridge are created under this user (that is, while the cartridge developer or DBA is connected to the database as PowerCartUser).

set echo on
connect sys/knl_test7 as sysdba;
drop user PowerCartUser cascade;
create user PowerCartUser identified by PowerCartUser;

-------------------------------------------------------------------
-- INITIAL SET-UP
-------------------------------------------------------------------
-- grant privileges --
grant connect, resource to PowerCartUser;
-- do we need to grant these privileges --
grant create operator to PowerCartUser;
grant create indextype to PowerCartUser;
grant create table to PowerCartUser;

Creating the Object Type (PowerDemand_Typ)

The object type PowerDemand_Typ is used to store the hourly power grid readings. This type is used to define a column in the table in which the readings are stored.

First, two types are defined for later use:

PowerGrid_Typ, to define the cells in PowerDemand_Typ

NumTab_Typ, to be used in the table in which the index entries are stored

CREATE OR REPLACE TYPE PowerGrid_Typ as VARRAY(100) of NUMBER;
CREATE OR REPLACE TYPE NumTab_Typ as TABLE of NUMBER;

The PowerDemand_Typ type includes:

Three attributes (TotGridDemand, MaxCellDemand, MinCellDemand) that are set by three member procedures
Power demand readings (100 cells in a grid)

The date/time of the power demand readings. (Every hour, 100 areas transmit their power demand readings.)

CREATE OR REPLACE TYPE PowerDemand_Typ AS OBJECT (
  -- Total power demand for the grid
  TotGridDemand NUMBER,
  -- Cell with maximum/minimum power demand for the grid
  MaxCellDemand NUMBER,
  MinCellDemand NUMBER,
  -- Power grid: 10X10 array represented as Varray(100)
  -- using previously defined PowerGrid_Typ
  CellDemandValues PowerGrid_Typ,
  -- Date/time for power-demand samplings: Every hour,
  -- 100 areas transmit their power demand readings.
  SampleTime DATE,
  --
  -- Methods (Set...) for this type:
  -- Total demand for the entire power grid for a
  -- SampleTime: sets the value of TotGridDemand.
  Member Procedure SetTotalDemand,
  -- Maximum demand for the entire power grid for a
  -- SampleTime: sets the value of MaxCellDemand.
  Member Procedure SetMaxDemand,
  -- Minimum demand for the entire power grid for a
  -- SampleTime: sets the value of MinCellDemand.
  Member Procedure SetMinDemand
);
/

Defining the Object Type Methods

The PowerDemand_Typ object type has methods that set the first three attributes in the type definition:

TotGridDemand, the total demand for the entire power grid for the hour in question (identified by SampleTime)
MaxCellDemand, the highest power demand value for all cells for the SampleTime
MinCellDemand, the lowest power demand value for all cells for the SampleTime

The logic for each procedure is not complicated. SetTotDemand loops through the cell values and creates a running total. SetMaxDemand compares the first two cell values and saves the higher as the current highest value; it then examines each successive cell, comparing it against the current highest value and saving the higher of the two as the current highest value, until it reaches the end of the cell values. SetMinDemand uses the same approach as SetMaxDemand, but it continually saves the lower value in comparisons to derive the lowest value overall.

CREATE OR REPLACE TYPE BODY PowerDemand_Typ 
IS
  --
  -- Methods (Set...) for this type:
  -- Total demand for the entire power grid for a
  -- SampleTime: sets the value of TotGridDemand.
  Member Procedure SetTotalDemand 
  IS
  I BINARY_INTEGER;
  Total NUMBER;
  BEGIN
    Total :=0;
    I := CellDemandValues.FIRST;   
    WHILE I IS NOT NULL LOOP
   Total := Total + CellDemandValues(I);
        I := CellDemandValues.NEXT(I);
    END LOOP;
    TotGridDemand := Total;
  END;

  -- Maximum demand for the entire power grid for a
  -- SampleTime: sets the value of MaxCellDemand.
  Member Procedure SetMaxDemand 
  IS
  I BINARY_INTEGER;
  Temp NUMBER;
  BEGIN
    I := CellDemandValues.FIRST;   
    Temp := CellDemandValues(I);
    WHILE I IS NOT NULL LOOP
   IF Temp < CellDemandValues(I) THEN
      Temp := CellDemandValues(I);
   END IF;
        I := CellDemandValues.NEXT(I);
    END LOOP;
    MaxCellDemand := Temp;
  END;

  -- Minimum demand for the entire power grid for a
  -- SampleTime: sets the value of MinCellDemand.
  Member Procedure SetMinDemand 
  IS
  I BINARY_INTEGER;
  Temp NUMBER;
  BEGIN
    I := CellDemandValues.FIRST;   
    Temp := CellDemandValues(I);
    WHILE I IS NOT NULL LOOP
   IF Temp > CellDemandValues(I) THEN
      Temp := CellDemandValues(I);
   END IF;
        I := CellDemandValues.NEXT(I);
    END LOOP;
    MinCellDemand := Temp;
  END;
END;
/

Creating the Functions and Operators

The power demand cartridge is designed so that users can query the power grid for relationships of equality, greaterthan, or lessthan. However, because of the way the cell demand data is stored, the standard operators (=, >, <) cannot be used. Instead, new operators must be created, and a function must be created to define the implementation for each new operator (that is, how the operator is to be interpreted by Oracle).

For this cartridge, each of the three relationships can be checked in two ways:

Whether a specific cell in the grid satisfies the relationship. (For example, are there grids where cell (3,7) has demand equal to 25?)

These operators have names in the form Power_XxxxxSpecific (such as Power_EqualsSpecific), and the implementing functions have names in the form Power_XxxxxSpecific_Func.
Whether any cell in the grid satisfies the relationship. (For example, are there grids where any cell has demand equal to 25?)

These operators have names in the form Power_XxxxxAny (such as Power_EqualsAny), and the implementing functions have names in the form Power_XxxxxAny_Func.

For each operator-function pair, the function is defined first and then the operator as using the function. The function is the implementation that would be used if there were no index defined. This implementation must be specified so that the Oracle optimizer can determine costs, decide whether the index should be used, and create an execution plan.

Table 11-3 shows the operators and implementing functions:

Table 11-3 Operators and Implementing Functions

Operator	Implementing Function
Power_EqualsSpecific	Power_EqualsSpecific_Func
Power_EqualsAny	Power_EqualsAny_Func
Power_LessThanSpecific	Power_LessThanSpecific_Func
Power_LessThanAny	Power_LessThanAny_Func
Power_ GreaterThanSpecific	Power_GreaterThanSpecific_Func
Power_GreaterThanAny	Power_GreaterThanAny_Func

Each function and operator returns a numeric value of 1 if the condition is true (for example, if the specified cell is equal to the specified value), 0 if the condition is not true, or null if the specified cell number is invalid.

The following statements create the implementing functions (Power_xxx_Func), first the specific and then the any implementations.

CREATE FUNCTION Power_EqualsSpecific_Func(
  object PowerDemand_Typ, cell NUMBER, value NUMBER)
RETURN NUMBER AS
  BEGIN
  IF cell <= object.CellDemandValues.LAST
  THEN
     IF (object.CellDemandValues(cell) = value) THEN
   RETURN 1;
     ELSE
   RETURN 0;
     END IF;
  ELSE
     RETURN NULL;
  END IF;
  END;
/
CREATE FUNCTION Power_GreaterThanSpecific_Func(
  object PowerDemand_Typ, cell NUMBER, value NUMBER)
RETURN NUMBER AS
  BEGIN
  IF cell <= object.CellDemandValues.LAST
  THEN
     IF (object.CellDemandValues(cell) > value) THEN
   RETURN 1;
     ELSE
   RETURN 0;
     END IF;
  ELSE
     RETURN NULL;
  END IF;
  END;
/
CREATE FUNCTION Power_LessThanSpecific_Func(
  object PowerDemand_Typ, cell NUMBER, value NUMBER)
RETURN NUMBER AS
  BEGIN
  IF cell <= object.CellDemandValues.LAST
  THEN
     IF (object.CellDemandValues(cell) < value) THEN
   RETURN 1;
     ELSE
   RETURN 0;
     END IF;
  ELSE
     RETURN NULL;
  END IF;
  END;
/
CREATE FUNCTION Power_EqualsAny_Func(
  object PowerDemand_Typ, value NUMBER)
RETURN NUMBER AS
   idx NUMBER;
  BEGIN
    FOR idx IN object.CellDemandValues.FIRST..object.CellDemandValues.LAST LOOP
      IF (object.CellDemandValues(idx) = value) THEN
   RETURN 1;
      END IF;
    END LOOP;
   RETURN 0;
  END;
/
CREATE FUNCTION Power_GreaterThanAny_Func(
  object PowerDemand_Typ, value NUMBER)
RETURN NUMBER AS
   idx NUMBER;
  BEGIN
    FOR idx IN object.CellDemandValues.FIRST..object.CellDemandValues.LAST LOOP
      IF (object.CellDemandValues(idx) > value) THEN
   RETURN 1;
      END IF;
    END LOOP;
   RETURN 0;
  END;
/
CREATE FUNCTION Power_LessThanAny_Func(
  object PowerDemand_Typ, value NUMBER)
RETURN NUMBER AS
   idx NUMBER;
  BEGIN
    FOR idx IN object.CellDemandValues.FIRST..object.CellDemandValues.LAST LOOP
      IF (object.CellDemandValues(idx) < value) THEN
   RETURN 1;
      END IF;
    END LOOP;
   RETURN 0;
  END;
/

The following statements create the operators (Power_xxx). Each statement specifies an implementing function.

CREATE OPERATOR Power_Equals BINDING(PowerDemand_Typ, NUMBER, NUMBER)
  RETURN NUMBER USING Power_EqualsSpecific_Func;
CREATE OPERATOR Power_GreaterThan BINDING(PowerDemand_Typ, NUMBER, NUMBER)
  RETURN NUMBER USING Power_GreaterThanSpecific_Func;
CREATE OPERATOR Power_LessThan BINDING(PowerDemand_Typ, NUMBER, NUMBER)
  RETURN NUMBER USING Power_LessThanSpecific_Func;
  
CREATE OPERATOR Power_EqualsAny BINDING(PowerDemand_Typ, NUMBER)
  RETURN NUMBER USING Power_EqualsAny_Func;
CREATE OPERATOR Power_GreaterThanAny BINDING(PowerDemand_Typ, NUMBER)
  RETURN NUMBER USING Power_GreaterThanAny_Func;
CREATE OPERATOR Power_LessThanAny BINDING(PowerDemand_Typ, NUMBER)
  RETURN NUMBER USING Power_LessThanAny_Func;

Creating the Indextype Implementation Methods

The power demand cartridge creates an object type for the indextype that specifies methods for the domain index. These methods are part of the ODCIIndex (Oracle Data Cartridge Interface Index) interface, and they collectively define the behavior of the index in terms of the methods for defining, manipulating, scanning, and exporting the index.

Table 11-4 shows the method functions (all but one starting with ODCIIndex) created for the power demand cartridge.

Table 11-4 Indextype Methods

Method	Description
ODCIGetInterfaces	Returns the list of names of the interfaces implemented by the type.
ODCIIndexCreate	Creates a table to store index data. If the base table containing data to be indexed is not empty, this method builds the index for existing data. This method is called when a `CREATE` `INDEX` statement is issued that refers to the indextype. Upon invocation, any parameters specified in the `PARAMETERS` clause are passed in along with a description of the index.
ODCIIndexDrop	Drops the table that stores the index data. This method is called when a `DROP` `INDEX` statement specifies the index.
ODCIIndexStart	Initializes the scan of the index for the operator predicate. This method is invoked when a query is submitted involving an operator that can be executed using the domain index.
ODCIIndexFetch	Returns the `ROWID` of each row that satisfies the operator predicate.
ODCIIndexClose	Ends the current use of the index. This method can perform any necessary clean-up.
ODCIIndexInsert	Maintains the index structure when a record is inserted in a table that contains columns or object attributes indexed by the indextype.
ODCIIndexDelete	Maintains the index structure when a record is deleted from a table that contains columns or object attributes indexed by the indextype.
ODCIIndexUpdate	Maintains the index structure when a record is updated (modified) in a table that contains columns or object attributes indexed by the indextype.
ODCIIndexGet Metadata	Allows the export and import of implementation-specific metadata associated with the index.

Type Definition

The following statement creates the power_idxtype_im object type. The methods of this type are the ODCI methods to define, manipulate, and scan the domain index. The curnum attribute is the cursor number used as context for the scan routines (ODCIIndexStart, ODCIIndexFetch, and ODCIIndexClose).

CREATE OR REPLACE TYPE power_idxtype_im AS OBJECT
(
  curnum NUMBER,
  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 ODCIIndexStart(self IN OUT power_idxtype_im,
                                 ia sys.ODCIIndexInfo,
                                 op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
                                 strt NUMBER, stop NUMBER,
                                 cmppos NUMBER, cmpval NUMBER) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexStart(self IN OUT power_idxtype_im,
                                 ia sys.ODCIIndexInfo,
                                 op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
                                 strt NUMBER, stop NUMBER,
                                 cmpval NUMBER) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT sys.ODCIRidList)
     RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexClose RETURN NUMBER,
  STATIC FUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  newval PowerDemand_Typ) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  oldval PowerDemand_Typ) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  oldval PowerDemand_Typ, 
                                  newval PowerDemand_Typ) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo, 
                                       expversion VARCHAR2, 
                                       newblock OUT PLS_INTEGER) 
     RETURN VARCHAR2
);
/

The CREATE TYPE statement is followed by a CREATE TYPE BODY statement that specifies the implementation for each member function:

CREATE OR REPLACE TYPE BODY power_idxtype_im 
IS
...

Each type method is described in a separate section, but the method definitions (except for ODCIIndexGetMetadata, which returns a VARCHAR2 string) have the following general form:

  STATIC FUNCTION function-name (...) 
    RETURN NUMBER
  IS
  ...
  END;

ODCIGetInterfaces Method

The ODCIGetInterfaces function returns the list of names of the interfaces implemented by the type. In release 8.1, there is only one set of the extensible indexing interface routines, called SYS.ODCIINDEX1. Thus, in release 8.1, the ODCIGetInterfaces routine must return 'SYS'.'ODCIINDEX1' as one of the implemented interfaces.

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

ODCIIndexCreate Method

The ODCIIndexCreate function creates the table to store index data. If the base table containing data to be indexed is not empty, this method inserts the index data entries for existing data.

The function takes the index information as an object parameter whose type is SYS.ODCIINDEXINFO. The type attributes include the index name, owner name, and so forth. The PARAMETERS string specified in the CREATE INDEX statement is also passed in as a parameter to the function.

  STATIC FUNCTION ODCIIndexCreate (ia sys.ODCIIndexInfo, parms VARCHAR2)
     RETURN NUMBER IS
   i INTEGER;
   r ROWID;
   p NUMBER;
   v NUMBER;
   stmt1 VARCHAR2(1000);
   stmt2 VARCHAR2(1000);
   stmt3 VARCHAR2(1000);
   cnum1 INTEGER;
   cnum2 INTEGER;
   cnum3 INTEGER;
   junk NUMBER;

The SQL statement to create the table for the index data is constructed and executed. The table includes the ROWID of the base table (r), the cell position number (cpos) in the grid from 1 to 100, and the power demand value in that cell (cval).

BEGIN
   -- Construct the SQL statement.
   stmt1 := 'CREATE TABLE ' || ia.IndexSchema || '.' || ia.IndexName ||
           '_pidx' || '( r ROWID, cpos NUMBER, cval NUMBER)';

   -- Dump the SQL statement.
   dbms_output.put_line('ODCIIndexCreate>>>>>');
   sys.ODCIIndexInfoDump(ia);
   dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt1);

   -- Execute the statement.
   cnum1 := dbms_sql.open_cursor;
   dbms_sql.parse(cnum1, stmt1, dbms_sql.native);
   junk := dbms_sql.execute(cnum1);
   dbms_sql.close_cursor(cnum1);

The function populates the index by inserting rows into the table. The function "unnests" the VARRAY attribute and inserts a row for each cell into the table. Thus, each 10 X 10 grid (10 rows, 10 values per row) becomes 100 rows in the table (one row per cell).

   -- Now populate the table.
   stmt2 := ' INSERT INTO '|| ia.IndexSchema || '.' || 
       ia.IndexName || '_pidx' ||
       ' SELECT :rr, ROWNUM, column_value FROM THE' ||
       ' (SELECT CAST (P.'|| ia.IndexCols(1).ColName||'.CellDemandValues 
          AS NumTab_Typ)'||
       ' FROM ' || ia.IndexCols(1).TableSchema || '.' || 
       ia.IndexCols(1).TableName || ' P' ||
       ' WHERE P.ROWID = :rr)';
 
   -- Execute the statement.
   dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt2);
 
   -- Parse the statement.
   cnum2 := dbms_sql.open_cursor;
   dbms_sql.parse(cnum2, stmt2, dbms_sql.native);
 
   stmt3 := 'SELECT ROWID FROM '|| ia.IndexCols(1).TableSchema 
     || '.' || ia.IndexCols(1).TableName;
   dbms_output.put_line('ODCIIndexCreate>>>>>'||stmt3);
   cnum3 := dbms_sql.open_cursor;
   dbms_sql.parse(cnum3, stmt3, dbms_sql.native);
   dbms_sql.define_column_rowid(cnum3, 1, r);   
   junk := dbms_sql.execute(cnum3);
 
   WHILE dbms_sql.fetch_rows(cnum3) > 0 LOOP
      -- Get column values of the row. --
      dbms_sql.column_value_rowid(cnum3, 1, r);
      -- Bind the row into the cursor for the next insert. --
      dbms_sql.bind_variable_rowid(cnum2, ':rr', r);
      junk := dbms_sql.execute(cnum2);
   END LOOP;

The function concludes by closing the cursors and returning a success status.

   dbms_sql.close_cursor(cnum2);
   dbms_sql.close_cursor(cnum3);
   RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexDrop Method

The ODCIIndexDrop function drops the table that stores the index data. This method is called when a DROP INDEX statement is issued.

  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 
     || '_pidx';
  
   dbms_output.put_line('ODCIIndexDrop>>>>>');
   sys.ODCIIndexInfoDump(ia);
   dbms_output.put_line('ODCIIndexDrop>>>>>'||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;

ODCIIndexStart Method (for `Specific` Queries)

The first definition of the ODCIIndexStart function initializes the scan of the index to return all rows that satisfy the operator predicate. For example, if a query asks for all instances where cell (3,7) has a value equal to 25, the function initializes the scan to return all rows in the index-organized table for which that cell has that value. (This definition of ODCIIndexStart differs from the definition in the next section in that it includes the cmppos parameter for the position of the cell.)

The self parameter is the context that is shared with the ODCIIndexFetch and ODCIIndexClose functions. The ia parameter contains the index information (an object instance of type SYS.ODCIINDEXINFO), and the op parameter contains the operator information (an object instance of type SYS.ODCIOPERINFO). The strt and stop parameters are the lower and upper boundary points for the operator return value. The cmppos parameter is the cell position and cmpval is the value in the cell specified by the operator (Power_XxxxxSpecific).

  STATIC FUNCTION ODCIIndexStart(self IN OUT power_idxtype_im, 
        ia sys.ODCIIndexInfo,
        op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
        strt NUMBER, stop NUMBER,
        cmppos NUMBER, cmpval NUMBER ) RETURN NUMBER IS
    cnum INTEGER;
    rid ROWID;
    nrows INTEGER;
    relop VARCHAR2(2);
    stmt VARCHAR2(1000);
  BEGIN
    dbms_output.put_line('ODCIIndexStart>>>>>');
    sys.ODCIIndexInfoDump(ia);
    sys.ODCIPredInfoDump(op);
    dbms_output.put_line('start key : '||strt);
    dbms_output.put_line('stop key : '||stop);
    dbms_output.put_line('compare position : '||cmppos);
    dbms_output.put_line('compare value : '||cmpval);

The function checks for errors in the predicate.

    -- Take care of some error cases.
    -- The only predicates in which btree operators can appear are
    --    op() = 1     OR    op() = 0
    if (strt != 1) and (strt != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;
 
    if (stop != 1) and (stop != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;

The function generates the SQL statement to be executed. It determines the operator name and the lower and upper index value bounds (the start and stop keys). The start and stop keys can both be 1 (= TRUE) or both be 0 (= FALSE).

    -- Generate the SQL statement to be executed.
    -- First, figure out the relational operator needed for the statement.
    -- Take into account the operator name and the start and stop keys.
    -- For now, the start and stop keys can both be 1 (= TRUE) or 
    -- both be 0 (= FALSE).
    if op.ObjectName = 'POWER_EQUALS' then
      if strt = 1 then 
        relop := '=';
      else
        relop := '!=';
      end if;
    elsif op.ObjectName = 'POWER_LESSTHAN' then
      if strt = 1 then 
        relop := '<';
      else
        relop := '>=';
      end if;
    elsif op.ObjectName = 'POWER_GREATERTHAN' then
      if strt = 1 then 
        relop := '>';
      else
        relop := '<=';
      end if;
    else
      raise_application_error(-20101, 'Unsupported operator');
    end if;
 
    stmt := 'select r from '||ia.IndexSchema||'.'||ia.IndexName||'_pidx'||
              ' where cpos '|| '=' ||''''||cmppos||''''||
              ' and cval '||relop||''''||cmpval||'''';
 
    dbms_output.put_line('ODCIIndexStart>>>>>' || stmt);
    cnum := dbms_sql.open_cursor;
    dbms_sql.parse(cnum, stmt, dbms_sql.native);
    dbms_sql.define_column_rowid(cnum, 1, rid);   
    nrows := dbms_sql.execute(cnum);

The function stores the cursor number in the context, which is used by the ODCIIndexFetch function, and sets a success return status.

    -- Set context as the cursor number.
    self := power_idxtype_im(cnum);
 
    -- Return success.
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexStart Method (for Any Queries)

This definition of the ODCIIndexStart function initializes the scan of the index to return all rows that satisfy the operator predicate. For example, if a query asks for all instances where any cell has a value equal to 25, the function initializes the scan to return all rows in the index-organized table for which that cell has that value. (This definition of ODCIIndexStart differs from the definition in the preceding section in that it does not include the cmppos parameter.)

The self parameter is the context that is shared with the ODCIIndexFetch and ODCIIndexClose functions. The ia parameter contains the index information (an object instance of type SYS.ODCIINDEXINFO), and the op parameter contains the operator information (an object instance of type SYS.ODCIOPERINFO). The strt and stop parameters are the lower and upper boundary points for the operator return value. The cmpval parameter is the value in the cell specified by the operator (Power_Xxxxx).

  STATIC FUNCTION ODCIIndexStart(self IN OUT power_idxtype_im, 
        ia sys.ODCIIndexInfo,
        op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
        strt NUMBER, stop NUMBER,
        cmpval NUMBER ) RETURN NUMBER IS
    cnum INTEGER;
    rid ROWID;
    nrows INTEGER;
    relop VARCHAR2(2);
    stmt VARCHAR2(1000);
  BEGIN
    dbms_output.put_line('ODCIIndexStart>>>>>');
    sys.ODCIIndexInfoDump(ia);
    sys.ODCIPredInfoDump(op);
    dbms_output.put_line('start key : '||strt);
    dbms_output.put_line('stop key : '||stop);
    dbms_output.put_line('compare value : '||cmpval);

The function checks for errors in the predicate.

    -- Take care of some error cases.
    -- The only predicates in which btree operators can appear are
    --    op() = 1     OR    op() = 0
    if (strt != 1) and (strt != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;
 
    if (stop != 1) and (stop != 0) then
    raise_application_error(-20101, 'Incorrect predicate for operator');
    END if;

    -- Generate the SQL statement to be executed.
    -- First, figure out the relational operator needed for the statement.
    -- Take into account the operator name and the start and stop keys.
    -- For now, the start and stop keys can both be 1 (= TRUE) or 
    -- both be 0 (= FALSE).
    if op.ObjectName = 'POWER_EQUALSANY' then
      relop := '=';
    elsif op.ObjectName = 'POWER_LESSTHANANY' then
        relop := '<';
    elsif op.ObjectName = 'POWER_GREATERTHANANY' then
        relop := '>';
    else
      raise_application_error(-20101, 'Unsupported operator');
    end if;
 
    -- This statement returns the qualifying rows for the TRUE case.
    stmt := 'select distinct r from '||ia.IndexSchema||'.'||ia.IndexName||
            '_pidx'||' where cval '||relop||''''||cmpval||'''';
    -- In the FALSE case, we need to find the  complement of the rows.
    if (strt = 0) then
      stmt := 'select distinct r from '||ia.IndexSchema||'.'||
              ia.IndexName||'_pidx'||' minus '||stmt;
    end if;
 
    dbms_output.put_line('ODCIIndexStart>>>>>' || stmt);
    cnum := dbms_sql.open_cursor;
    dbms_sql.parse(cnum, stmt, dbms_sql.native);
    dbms_sql.define_column_rowid(cnum, 1, rid);   
    nrows := dbms_sql.execute(cnum);

The function stores the cursor number in the context, which is used by the ODCIIndexFetch function, and sets a success return status.

    -- Set context as the cursor number.
    self := power_idxtype_im(cnum);
 
    -- Return success.
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexFetch Method

The ODCIIndexFetch function returns a batch of ROWIDs for the rows that satisfy the operator predicate. Each time ODCIIndexFetch is invoked, it returns the next batch of rows (rids parameter, a collection of type SYS.ODCIRIDLIST) that satisfy the operator predicate. The maximum number of rows that can be returned on each invocation is specified by the nrows parameter.

Oracle invokes ODCIIndexFetch repeatedly until all rows that satisfy the operator predicate have been returned.

  MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT sys.ODCIRidList)
   RETURN NUMBER IS
    cnum INTEGER;
    idx INTEGER := 1;
    rlist sys.ODCIRidList := sys.ODCIRidList();
    done boolean := FALSE;

The function loops through the collection of rows selected by the ODCIIndexStart function, using the same cursor number (cnum) as in the ODCIIndexStart function, and returns the ROWIDs.

  BEGIN
    dbms_output.put_line('ODCIIndexFetch>>>>>');
    dbms_output.put_line('Nrows : '||round(nrows));
 
    cnum := self.curnum;
 
    WHILE not done LOOP
      if idx > nrows then
         done := TRUE;
      else
         rlist.extEND;
         if dbms_sql.fetch_rows(cnum) > 0 then
            dbms_sql.column_value_rowid(cnum, 1, rlist(idx));
            idx := idx + 1;
         else
            rlist(idx) := null;
            done := TRUE;
         END if;
      END if;   
    END LOOP;
 
    rids := rlist;
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexClose Method

The ODCIIndexClose function closes the cursor used by the ODCIIndexStart and ODCIIndexFetch functions.

  MEMBER FUNCTION ODCIIndexClose RETURN NUMBER IS 
    cnum INTEGER;
  BEGIN
    dbms_output.put_line('ODCIIndexClose>>>>>');
 
    cnum := self.curnum;
    dbms_sql.close_cursor(cnum);
    RETURN ODCICONST.SUCCESS;
  END;

ODCIIndexInsert Method

The ODCIIndexInsert function is called when a record is inserted in a table that contains columns or OBJECT attributes indexed by the indextype. The new values in the indexed columns are passed in as arguments along with the corresponding row identifier.

  STATIC FUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo, rid VARCHAR2, 
   newval PowerDemand_Typ) 
       RETURN NUMBER AS 
       cid INTEGER; 
       i BINARY_INTEGER;
       nrows INTEGER;
       stmt VARCHAR2(1000);
   BEGIN 
     dbms_output.put_line(' ');
     dbms_output.put_line('ODCIIndexInsert>>>>>'|| 
      ' TotGridDemand= '||newval.TotGridDemand ||
      ' MaxCellDemand= '||newval.MaxCellDemand ||
      ' MinCellDemand= '||newval.MinCellDemand) ;
     sys.ODCIIndexInfoDump(ia); 
      
     -- Construct the statement.
     stmt := ' INSERT INTO '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' ||' VALUES (:rr, :pos, :val)';
  
     -- Execute the statement.
     dbms_output.put_line('ODCIIndexInsert>>>>>'||stmt);
     -- Parse the statement.
     cid := dbms_sql.open_cursor;
     dbms_sql.parse(cid, stmt, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid, ':rr', rid);
      
     -- Iterate over the rows of the Varray and insert them.
     i := newval.CellDemandValues.FIRST;   
     WHILE i IS NOT NULL LOOP
         -- Bind the row into the cursor for insert.
         dbms_sql.bind_variable(cid, ':pos', i);   
         dbms_sql.bind_variable(cid, ':val', newval.CellDemandValues(i));
         -- Execute.
         nrows := dbms_sql.execute(cid);
         dbms_output.put_line('ODCIIndexInsert>>>>>('|| 
                               'RID' ||' , '||
                               i   || ' , '||
                               newval.CellDemandValues(i)|| ')');
         i := newval.CellDemandValues.NEXT(i);
      END LOOP;
     dbms_sql.close_cursor(cid);
     RETURN ODCICONST.SUCCESS;
   END ODCIIndexInsert;

ODCIIndexDelete Method

The ODCIIndexDelete function is called when a record is deleted from a table that contains columns or object attributes indexed by the indextype. The old values in the indexed columns are passed in as arguments along with the corresponding row identifier.

  STATIC FUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo, rid VARCHAR2,
oldval PowerDemand_Typ) 
       RETURN NUMBER AS 
       cid INTEGER; 
       stmt VARCHAR2(1000);
       nrows INTEGER; 
   BEGIN 
     dbms_output.put_line(' ');
     dbms_output.put_line('ODCIIndexDelete>>>>>'|| 
      ' TotGridDemand= '||oldval.TotGridDemand ||
      ' MaxCellDemand= '||oldval.MaxCellDemand ||
      ' MinCellDemand= '||oldval.MinCellDemand) ;
     sys.ODCIIndexInfoDump(ia); 
 
     -- Construct the statement.
     stmt := ' DELETE FROM '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' || ' WHERE r=:rr';
     dbms_output.put_line('ODCIIndexDelete>>>>>'||stmt);
 
     -- Parse and execute the statement.
     cid := dbms_sql.open_cursor;
     dbms_sql.parse(cid, stmt, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid, ':rr', rid);
     nrows := dbms_sql.execute(cid);     
     dbms_sql.close_cursor(cid);
 
     RETURN ODCICONST.SUCCESS;
   END ODCIIndexDelete;

ODCIIndexUpdate Method

The ODCIIndexUpdate function is called when a record is updated in a table that contains columns or object attributes indexed by the indextype. The old and new values in the indexed columns are passed in as arguments along with the row identifier.

  STATIC FUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo, rid VARCHAR2, 
   oldval PowerDemand_Typ, newval PowerDemand_Typ) 
       RETURN NUMBER AS 
       cid INTEGER; 
       cid2 INTEGER; 
       stmt VARCHAR2(1000);
       stmt2 VARCHAR2(1000);
       nrows INTEGER; 
       i NUMBER;
   BEGIN 
     dbms_output.put_line(' ');
     dbms_output.put_line('ODCIIndexUpdate>>>>> Old'|| 
      ' TotGridDemand= '||oldval.TotGridDemand ||
      ' MaxCellDemand= '||oldval.MaxCellDemand ||
      ' MinCellDemand= '||oldval.MinCellDemand) ;
     dbms_output.put_line('ODCIIndexUpdate>>>>> New'|| 
      ' TotGridDemand= '||newval.TotGridDemand ||
      ' MaxCellDemand= '||newval.MaxCellDemand ||
      ' MinCellDemand= '||newval.MinCellDemand) ;
     sys.ODCIIndexInfoDump(ia); 
 
     -- Delete old entries.
     stmt := ' DELETE FROM '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' || ' WHERE r=:rr';
     dbms_output.put_line('ODCIIndexUpdate>>>>>'||stmt);
  
     -- Parse and execute the statement.
     cid := dbms_sql.open_cursor;
     dbms_sql.parse(cid, stmt, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid, ':rr', rid);
     nrows := dbms_sql.execute(cid);     
     dbms_sql.close_cursor(cid);
 
     -- Insert new entries.
     stmt2 := ' INSERT INTO '|| ia.IndexSchema || '.' || ia.IndexName
            || '_pidx' || ' VALUES (:rr, :pos, :val)';
     dbms_output.put_line('ODCIIndexUpdate>>>>>'||stmt2);
 
     -- Parse and execute the statement.
     cid2 := dbms_sql.open_cursor;
     dbms_sql.parse(cid2, stmt2, dbms_sql.native);
     dbms_sql.bind_variable_rowid(cid2, ':rr', rid);
     
     -- Iterate over the rows of the Varray and insert them.
     i := newval.CellDemandValues.FIRST;   
     WHILE i IS NOT NULL LOOP
         -- Bind the row into the cursor for insert.
         dbms_sql.bind_variable(cid2, ':pos', i);   
         dbms_sql.bind_variable(cid2, ':val', newval.CellDemandValues(i));
         nrows := dbms_sql.execute(cid2);
         dbms_output.put_line('ODCIIndexUpdate>>>>>('|| 
                               'RID' || ' , '||
                               i   || ' , '||
                               newval.CellDemandValues(i)|| ')');
         i := newval.CellDemandValues.NEXT(i);
      END LOOP;
     dbms_sql.close_cursor(cid2);
 
     RETURN ODCICONST.SUCCESS;
   END ODCIIndexUpdate;

ODCIIndexUpdate is the last method defined in the CREATE TYPE BODY statement, which ends as follows:

END;
/

ODCIIndexGetMetadata Method

The optional ODCIIndexGetMetadata function, if present, is called by the Export utility in order to write implementation-specific metadata (which is not stored in the system catalogs) into the export dump file. This metadata might be policy information, version information, per-user settings, and so on. This metadata is written to the dump file as anonymous PL/SQL blocks that are executed at import time, immediately before the associated index is created.

This method returns strings to the Export utility that comprise the code of the PL/SQL blocks. The Export utility repeatedly calls this method until a zero-length string is returned, thus allowing the creation of any number of PL/SQL blocks of arbitrary complexity. Normally, this method calls functions within a PL/SQL package in order to make use of package-level variables, such as cursors and iteration counters, that maintain state across multiple calls by Export.

For information about the Export and Import utilities, see the Oracle8i Utilities manual.

In the power demand cartridge, the only metadata that is passed is a version string of V1.0, identifying the current format of the index-organized table that underlies the domain index. The power_pkg.getversion function generates a call to the power_pkg.checkversion procedure, to be executed at import time to check that the version string is V1.0.

STATIC FUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo, expversion 
VARCHAR2, newblock OUT PLS_INTEGER) 
  RETURN VARCHAR2 IS 
 
BEGIN 
-- Let getversion do all the work since it has to maintain state across calls. 
 
  RETURN power_pkg.getversion (ia.IndexSchema, ia.IndexName, newblock); 
 
EXCEPTION 
  WHEN OTHERS THEN 
      RAISE; 
 
END ODCIIndexGetMetaData;

The power_pkg package is defined as follows:

CREATE OR REPLACE PACKAGE power_pkg AS  
  FUNCTION getversion(idxschema IN VARCHAR2, idxname IN VARCHAR2,
      newblock OUT PLS_INTEGER) RETURN VARCHAR2;  
  PROCEDURE checkversion (version IN VARCHAR2);  
END power_pkg; 
/ 
SHOW ERRORS; 
 
CREATE OR REPLACE PACKAGE BODY power_pkg AS  
  
-- iterate is a package-level variable used to maintain state across calls 
-- by Export in this session.  
  
iterate NUMBER := 0;  
  
FUNCTION getversion(idxschema IN VARCHAR2, idxname IN VARCHAR2,  
      newblock OUT PLS_INTEGER) RETURN VARCHAR2 IS  
  
BEGIN  
  
-- We are generating only one PL/SQL block consisting of one line of code.
  newblock := 1; 
  
  IF iterate = 0 
  THEN  
-- Increment iterate so we'll know we're done next time we're called. 
    iterate := iterate + 1;  
  
-- Return a string that calls checkversion with a version 'V1.0' 
-- Note that export adds the surrounding BEGIN/END pair to form the anon. 
-- block... we don't have to.  
  
    RETURN 'power_pkg.checkversion(''V1.0'');';  
  ELSE  
-- reset iterate for next index  
    iterate := 0;  
-- Return a 0-length string; we won't be called again for this index. 
    RETURN '';  
  END IF;  
END getversion;  
  
PROCEDURE checkversion (version IN VARCHAR2) IS  
  
  wrong_version            EXCEPTION; 
 
BEGIN  
  IF version != 'V1.0' THEN 
     RAISE wrong_version; 
  END IF; 
END checkversion;  
  
END power_pkg;

Creating the Indextype

The power demand cartridge creates the indextype for the domain index. The specification includes the list of operators supported by the indextype. It also identifies the implementation type containing the OCDI index routines.

CREATE OR REPLACE INDEXTYPE power_idxtype
FOR
   Power_Equals(PowerDemand_Typ, NUMBER, NUMBER),
   Power_GreaterThan(PowerDemand_Typ, NUMBER, NUMBER),
   Power_LessThan(PowerDemand_Typ, NUMBER, NUMBER),
   Power_EqualsAny(PowerDemand_Typ, NUMBER),
   Power_GreaterThanAny(PowerDemand_Typ, NUMBER),
   Power_LessThanAny(PowerDemand_Typ, NUMBER)
USING power_idxtype_im;