15 Power Demand Cartridge Example

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, 
     env sys.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexDrop(ia sys.ODCIIndexInfo, env sys.ODCIEnv)
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexStart(sctx IN OUT power_idxtype_im,
                                 ia sys.ODCIIndexInfo,
                                 op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
                                 strt NUMBER, stop NUMBER,
                                 cmppos NUMBER, cmpval NUMBER, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexStart(sctx IN OUT power_idxtype_im,
                                 ia sys.ODCIIndexInfo,
                                 op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
                                 strt NUMBER, stop NUMBER,
                                 cmpval NUMBER, env sys.ODCIEnv)
     RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexFetch(nrows NUMBER, rids OUT sys.ODCIRidList, 
     env sys.ODCIEnv) RETURN NUMBER,
  MEMBER FUNCTION ODCIIndexClose (env sys.ODCIEnv) RETURN NUMBER,
  STATIC FUNCTION ODCIIndexInsert(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  newval PowerDemand_Typ, env sys.ODCIEnv)
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexDelete(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  oldval PowerDemand_Typ, env sys.ODCIEnv)
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexUpdate(ia sys.ODCIIndexInfo, rid VARCHAR2,
                                  oldval PowerDemand_Typ, 
                                  newval PowerDemand_Typ, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIIndexGetMetadata(ia sys.ODCIIndexInfo, 
                                       expversion VARCHAR2, 
                                       newblock OUT PLS_INTEGER, 
                                       env sys.ODCIEnv) 
     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. To specify the current version of these interfaces, the ODCIGetInterfaces routine must return'SYS.ODCIINDEX2' in the OUT parameter.

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

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, 
                                   env sys.ODCIEnv)
     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 for each row) becomes 100 rows in the table (one row for each 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, env sys.ODCIEnv)
     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(sctx IN OUT power_idxtype_im, 
        ia sys.ODCIIndexInfo,
        op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
        strt NUMBER, stop NUMBER,
        cmppos NUMBER, cmpval NUMBER, env sys.ODCIEnv ) 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.
    stcx := 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(sctx IN OUT power_idxtype_im, 
        ia sys.ODCIIndexInfo,
        op sys.ODCIPredInfo, qi sys.ODCIQueryInfo,
        strt NUMBER, stop NUMBER,
        cmpval NUMBER, env sys.ODCIEnv ) 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;

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_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, 
                                 env sys.ODCIEnv)
   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 (env sys.ODCIEnv) 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, env sys.ODCIEnv) 
       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, env sys.ODCIEnv) 
     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, env sys.ODCIEnv) 
       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, 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 Oracle Database 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, env sys.ODCIEnv) 
  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;

Type Definition

The following statement creates the power_statistics object type. This object type's ODCI methods are used to collect and delete statistics about columns and indexes, compute selectivities of predicates with operators or functions, and to compute costs of domain indexes and functions. The curnum attribute is a dummy attribute that is not used.

CREATE OR REPLACE TYPE power_statistics AS OBJECT
(
  curnum NUMBER,
  STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
     RETURN NUMBER,
  STATIC FUNCTION ODCIStatsCollect(col sys.ODCIColInfo,
     options sys.ODCIStatsOptions, rawstats OUT RAW, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIStatsDelete(col sys.ODCIColInfo, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIStatsCollect(ia sys.ODCIIndexInfo,
     options sys.ODCIStatsOptions, rawstats OUT RAW, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIStatsDelete(ia sys.ODCIIndexInfo, env sys.ODCIEnv) 
     RETURN NUMBER,
  STATIC FUNCTION ODCIStatsSelectivity(pred sys.ODCIPredInfo,
     sel OUT NUMBER, args sys.ODCIArgDescList, strt NUMBER, stop NUMBER,
     object PowerDemand_Typ, cell NUMBER, value NUMBER, env sys.ODCIEnv) 
     RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsSelectivity, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsSelectivity(pred sys.ODCIPredInfo,
     sel OUT NUMBER, args sys.ODCIArgDescList, strt NUMBER, stop NUMBER,
     object PowerDemand_Typ, value NUMBER, env sys.ODCIEnv) RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsSelectivity, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo,
     sel NUMBER, cost OUT sys.ODCICost, qi sys.ODCIQueryInfo,
     pred sys.ODCIPredInfo, args sys.ODCIArgDescList,
     strt NUMBER, stop NUMBER, cmppos NUMBER, cmpval NUMBER, env sys.ODCIEnv)
     RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsIndexCost, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo,
     sel NUMBER, cost OUT sys.ODCICost, qi sys.ODCIQueryInfo,
     pred sys.ODCIPredInfo, args sys.ODCIArgDescList,
     strt NUMBER, stop NUMBER, cmpval NUMBER, env sys.ODCIEnv) RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsIndexCost, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
     cost OUT sys.ODCICost, args sys.ODCIArgDescList,
     object PowerDemand_Typ, cell NUMBER, value NUMBER, env sys.ODCIEnv) 
     RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS),
  STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
     cost OUT sys.ODCICost, args sys.ODCIArgDescList,
     object PowerDemand_Typ, value NUMBER, env sys.ODCIEnv) RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS)
  STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
     cost OUT sys.ODCICost, args sys.ODCIArgDescList,
     object PowerDemand_Typ, cell NUMBER, value NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
     BEGIN
       -- This is the cost for functions on a specific cell; simply
       -- use the cost for functions on any cell.
       RETURN ODCIStatsFunctionCost(func, cost, args, object, value, env);
     END;
);
/

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

Each member function is described in a separate section, but the function definitions 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. There is only one set of the extensible optimizer interface routines, called SYS.ODCISTATS, but the server supports multiple versions of them for backward compatibility. To specify the current version of the routines, function ODCIGetInterfaces must specify SYS.ODCISTATS2 in the OUT, ODCIObjectList parameter.

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

ODCIStatsCollect Method (for PowerDemand_Typ columns)

The ODCIStatsCollect function collects statistics for columns whose datatype is the PowerDemand_Typ object type. The statistics are collected for each cell in the column over all power grid readings. For a given cell, the statistics collected are the minimum and maximum power grid readings, and the number of non-null readings.

The function takes the column information as an object parameter whose type is SYS.ODCICOLINFO. The type attributes include the table name, column name, and so on. Options specified in the DBMS_STATS package command used to collect the column statistics are also passed in as parameters. Since the power demand cartridge uses a table to store the statistics, the output parameter rawstats is not used in this cartridge.

STATIC FUNCTION ODCIStatsCollect(col sys.ODCIColInfo,
                                   options sys.ODCIStatsOptions,
                                   rawstats OUT RAW, env sys.ODCIEnv)
     RETURN NUMBER IS
     cnum                INTEGER;
     stmt                VARCHAR2(1000);
     junk                INTEGER;

     cval                NUMBER;
     colname             VARCHAR2(30) := rtrim(ltrim(col.colName, '"'), '"');
     statsexists         BOOLEAN := FALSE;
     pdemands            PowerDemand_Tab%ROWTYPE;
     user_defined_stats  PowerCartUserStats%ROWTYPE;
     CURSOR c1(tname VARCHAR2, cname VARCHAR2) IS
       SELECT * FROM PowerCartUserStats
       WHERE tab = tname
         AND col = cname;
     CURSOR c2 IS
       SELECT * FROM PowerDemand_Tab;

  BEGIN
    sys.ODCIColInfoDump(col);
    sys.ODCIStatsOptionsDump(options);

    IF (col.TableSchema IS NULL OR col.TableName IS NULL
        OR col.ColName IS NULL) THEN
      RETURN ODCIConst.Error;
    END IF;

    dbms_output.put_line('ODCIStatsCollect>>>>>');
    dbms_output.put_line('**** Analyzing column '
                         || col.TableSchema
                         || '.' || col.TableName
                         || '.' || col.ColName);

    -- Check if statistics exist for this column
    FOR user_defined_stats IN c1(col.TableName, colname) LOOP
      statsexists := TRUE;
      EXIT;
    END LOOP;

The function checks whether statistics for this column already exist. If so, it initializes them to NULL; otherwise, it creates statistics for each of the 100 cells and initializes them to NULL.

IF not statsexists THEN
      -- column statistics don't exist; create entries for
      -- each of the 100 cells
      cnum := dbms_sql.open_cursor;
      FOR i in 1..100 LOOP
        stmt := 'INSERT INTO PowerCartUserStats VALUES( '
             || '''' || col.TableName || ''', '
             || '''' || colname || ''', '
             || to_char(i) || ', '
             || 'NULL, NULL, NULL)';
        dbms_sql.parse(cnum, stmt, dbms_sql.native);
        junk := dbms_sql.execute(cnum);
      END LOOP;
      dbms_sql.close_cursor(cnum);
    ELSE
      -- column statistics exist; initialize to NULL
      cnum := dbms_sql.open_cursor;
      stmt := 'UPDATE PowerCartUserStats'
           || ' SET lo = NULL, hi = NULL, nrows = NULL'
           || ' WHERE tab = ' || col.TableName
           || ' AND col = ' || colname;
      dbms_sql.parse(cnum, stmt, dbms_sql.native);
      junk := dbms_sql.execute(cnum);
      dbms_sql.close_cursor(cnum);
    END IF;

The function collects statistics for the column by reading rows from the table that is being analyzed. This is done by constructing and executing a SQL statement.

-- For each cell position, the following statistics are collected:
    --   maximum value
    --   minimum value
    --   number of rows (excluding NULLs)
    cnum := dbms_sql.open_cursor;
    FOR i in 1..100 LOOP
      FOR pdemands IN c2 LOOP
        IF i BETWEEN pdemands.sample.CellDemandValues.FIRST AND
                     pdemands.sample.CellDemandValues.LAST THEN
          cval := pdemands.sample.CellDemandValues(i);
          stmt := 'UPDATE PowerCartUserStats SET '
               || 'lo = least(' || 'NVL(' || to_char(cval) || ', lo), '
               || 'NVL(' || 'lo, ' || to_char(cval) || ')), '
               || 'hi = greatest(' || 'NVL(' || to_char(cval) || ', hi), '
               || 'NVL(' || 'hi, ' || to_char(cval) || ')), '
               || 'nrows = decode(nrows, NULL, decode('
               || to_char(cval) || ', NULL, NULL, 1), decode('
               || to_char(cval) || ', NULL, nrows, nrows+1)) '
               || 'WHERE cpos = ' || to_char(i)
               || ' AND tab = ''' || col.TableName || ''''
               || ' AND col = ''' || colname || '''';
          dbms_sql.parse(cnum, stmt, dbms_sql.native);
          junk := dbms_sql.execute(cnum);
        END IF;
      END LOOP;
    END LOOP;

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

dbms_sql.close_cursor(cnum);

    rawstats := NULL;

    return ODCIConst.Success;

  END;

ODCIStatsDelete Method (for PowerDemand_Typ columns)

The ODCIStatsDelete function deletes statistics of columns whose datatype is the PowerDemand_Typ object type.

The function takes the column information as an object parameter whose type is SYS.ODCICOLINFO. The type attributes include the table name, column name, and so on.

STATIC FUNCTION ODCIStatsDelete(col sys.ODCIColInfo, env sys.ODCIEnv)
     RETURN NUMBER IS
     cnum                INTEGER;
     stmt                VARCHAR2(1000);
     junk                INTEGER;

     colname             VARCHAR2(30) := rtrim(ltrim(col.colName, '"'), '"');
     statsexists         BOOLEAN := FALSE;
     user_defined_stats  PowerCartUserStats%ROWTYPE;
     CURSOR c1(tname VARCHAR2, cname VARCHAR2) IS
       SELECT * FROM PowerCartUserStats
       WHERE tab = tname
         AND col = cname;
  BEGIN
    sys.ODCIColInfoDump(col);

    IF (col.TableSchema IS NULL OR col.TableName IS NULL
        OR col.ColName IS NULL) THEN
      RETURN ODCIConst.Error;
    END IF;

    dbms_output.put_line('ODCIStatsDelete>>>>>');
    dbms_output.put_line('**** Analyzing (delete) column '
                         || col.TableSchema
                         || '.' || col.TableName
                         || '.' || col.ColName);

The function verifies that statistics for the column exist by checking the statistics table. If statistics were not collected, then there is nothing to be done. If, however, statistics are present, it constructs and executes a SQL statement to delete the relevant rows from the statistics table.

-- Check if statistics exist for this column
    FOR user_defined_stats IN c1(col.TableName, colname) LOOP
      statsexists := TRUE;
      EXIT;
    END LOOP;

    -- If user-defined statistics exist, delete them
    IF statsexists THEN
      stmt := 'DELETE FROM PowerCartUserStats'
           || ' WHERE tab = ''' || col.TableName || ''''
           || ' AND col = ''' || colname || '''';
      cnum := dbms_sql.open_cursor;
      dbms_output.put_line('ODCIStatsDelete>>>>>');
      dbms_output.put_line('ODCIStatsDelete>>>>>' || stmt);
      dbms_sql.parse(cnum, stmt, dbms_sql.native);
      junk := dbms_sql.execute(cnum);
      dbms_sql.close_cursor(cnum);
    END IF;

    RETURN ODCIConst.Success;
  END;

ODCIStatsCollect Method (for power_idxtype Domain Indexes)

The ODCIStatsCollect function collects statistics for domain indexes whose indextype is power_idxtype. In the power demand cartridge, this function simply analyzes the index-organized table that stores the index 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 on. Options specified by the DBMS_STATS package are used to collect the index statistics are also passed in as parameters. The output parameter rawstats is not used.

STATIC FUNCTION ODCIStatsCollect (ia sys.ODCIIndexInfo,
     options sys.ODCIStatsOptions, rawstats OUT RAW, env sys.ODCIEnv)
     RETURN NUMBER IS
     stmt                VARCHAR2(1000);
 
 BEGIN
    -- To analyze a domain index, analyze the table that implements the index

    sys.ODCIIndexInfoDump(ia);
    sys.ODCIStatsOptionsDump(options);

    stmt := 'dbms_stats.gather_table_stats('
         || '''' || ia.IndexSchema || ''', '
         || '''' || ia.IndexName || '_pidx' || ''');';
    dbms_output.put_line('**** Analyzing index '
         || ia.IndexSchema || '.' || ia.IndexName);
    dbms_output.put_line('SQL Statement: ' || stmt); 
   EXECUTE IMMEDIATE 'BEGIN ' || stmt || ' END;';
    rawstats := NULL;

    RETURN ODCIConst.Success;
  END;

ODCIStatsDelete Method (for power_idxtype Domain Indexes)

The ODCIStatsDelete function deletes statistics for domain indexes whose indextype is power_idxtype. In the power demand cartridge, this function simply deletes the statistics of the index-organized table that stores the index 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 on.

STATIC FUNCTION ODCIStatsDelete(ia sys.ODCIIndexInfo, env sys.ODCIEnv)
     RETURN NUMBER IS
     stmt                VARCHAR2(1000);
  BEGIN
    -- To delete statistics for a domain index, delete the statistics for the
    -- table implementing the index

    sys.ODCIIndexInfoDump(ia);

    stmt := 'dbms_stats.delete_table_stats('
         || '''' || ia.IndexSchema || ''', '
         || '''' || ia.IndexName || '_pidx' || ''');';

    dbms_output.put_line('**** Analyzing (delete) index '
                         || ia.IndexSchema || '.' || ia.IndexName);
    dbms_output.put_line('SQL Statement: ' || stmt);

    EXECUTE IMMEDIATE 'BEGIN ' || stmt || ' END;';

    RETURN ODCIConst.Success;
  END;

ODCIStatsSelectivity Method (for Specific Queries)

The first definition of the ODCIStatsSelectivity function estimates the selectivity of operator or function predicates for Specific queries. For example, if a query asks for all instances where cell (3,7) has a value equal to 25, the function estimates the percentage of rows in which the given cell has the specified value. (This definition of ODCIStatsSelectivity differs from the definition in the next section in that it includes the cell parameter for the position of the cell.)

The pred parameter contains the function information (the functional implementation of an operator in an operator predicate); this parameter is an object instance of type SYS.ODCIPREDINFO. The selectivity is returned as a percentage in the sel output parameter. The args parameter (an object instance of type SYS.ODCIARGDESCLIST) contains a descriptor for each argument of the function as well as the start and stop values of the function. For example, an argument might be a column in which case the argument descriptor will contain the table name, column name, and so forth. The strt and stop parameters are the lower and upper boundary points for the function return value. If the function in a predicate contains a literal of type PowerDemand_Typ, the object parameter will contain the value in the form of an object constructor. The cell parameter is the cell position and the value parameter is the value in the cell specified by the function (PowerXxxxxSpecific_Func).

The selectivity is estimated by using a technique similar to that used for simple range predicates. For example, a simple estimate for the selectivity of a predicate like

c > v

is (M-v)/(M-m) where m and M are the minimum and maximum values, respectively, for the column c (as determined from the column statistics), provided the value v lies between m and M.

The get_selectivity function computes the selectivity of a simple range predicate given the minimum and maximum values of the column in the predicate. It assumes that the column values in the table are uniformly distributed between the minimum and maximum values.

CREATE FUNCTION get_selectivity(relop VARCHAR2, value NUMBER,
                                lo NUMBER, hi NUMBER, ndv NUMBER)
  RETURN NUMBER AS
  sel NUMBER := NULL;
  ndv NUMBER;
BEGIN
  -- This function computes the selectivity (as a percentage)
  -- of a predicate
  --             col <relop> <value>
  -- where <relop> is one of: =, !=, <, <=, >, >=
  --       <value> is one of: 0, 1
  -- lo and hi are the minimum and maximum values of the column in
  -- the table. This function performs a simplistic estimation of the
  -- selectivity by assuming that the range of distinct values of
  -- the column is distributed uniformly in the range lo..hi and that
  -- each distinct value occurs nrows/(hi-lo+1) times (where nrows is
  -- the number of rows).

  IF ndv IS NULL OR ndv <= 0 THEN
    RETURN 0;
  END IF;

  -- col != <value>
  IF relop = '!=' THEN
    IF value between lo and hi THEN
      sel := 1 - 1/ndv;
    ELSE
      sel := 1;
    END IF;

  -- col = <value>
  ELSIF relop = '=' THEN
    IF value between lo and hi THEN
      sel := 1/ndv;
    ELSE
      sel := 0;
    END IF;

  -- col >= <value>
  ELSIF relop = '>=' THEN
    IF lo = hi THEN
      IF value <= lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (hi-value)/(hi-lo) + 1/ndv;
    ELSIF value < lo THEN
      sel := 1;
    ELSE
      sel := 0;
    END IF;

  -- col < <value>
  ELSIF relop = '<' THEN
    IF lo = hi THEN
      IF value > lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (value-lo)/(hi-lo);
    ELSIF value < lo THEN
      sel := 0;
    ELSE
      sel := 1;
    END IF;

  -- col <= <value>
  ELSIF relop = '<=' THEN
    IF lo = hi THEN
      IF value >= lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (value-lo)/(hi-lo) + 1/ndv;
    ELSIF value < lo THEN
      sel := 0;
    ELSE
      sel := 1;
    END IF;

  -- col > <value>
  ELSIF relop = '>' THEN
    IF lo = hi THEN
      IF value < lo THEN
        sel := 1;
      ELSE
        sel := 0;
      END IF;
    ELSIF value between lo and hi THEN
      sel := (hi-value)/(hi-lo);
    ELSIF value < lo THEN
      sel := 1;
    ELSE
      sel := 0;
    END IF;

  END IF;

  RETURN least(100, ceil(100*sel));

END;
/

The ODCIStatsSelectivity function estimates the selectivity for function predicates which have constant start and stop values. Further, the first argument of the function in the predicate must be a column of type PowerDemand_Typ and the remaining arguments must be constants.

STATIC FUNCTION ODCIStatsSelectivity(pred sys.ODCIPredInfo,
     sel OUT NUMBER, args sys.ODCIArgDescList, strt NUMBER, stop NUMBER,
     object PowerDemand_Typ, cell NUMBER, value NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
     fname               varchar2(30);
     relop               varchar2(2);
     lo                  NUMBER;
     hi                  NUMBER;
     nrows               NUMBER;
     colname             VARCHAR2(30);
     statsexists         BOOLEAN := FALSE;
     stats               PowerCartUserStats%ROWTYPE;
     CURSOR c1(cell NUMBER, tname VARCHAR2, cname VARCHAR2) IS
       SELECT * FROM PowerCartUserStats
       WHERE cpos = cell
         AND tab = tname
         AND col = cname;
  BEGIN
    -- compute selectivity only when predicate is of the form:
    --      fn(col, <cell>, <value>) <relop> <val>
    -- In all other cases, return an error and let the optimizer
    -- make a guess. We also assume that the function "fn" has
    -- a return value of 0, 1, or NULL.

    -- start value
    IF (args(1).ArgType != ODCIConst.ArgLit AND
        args(1).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- stop value
    IF (args(2).ArgType != ODCIConst.ArgLit AND
        args(2).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- first argument of function
    IF (args(3).ArgType != ODCIConst.ArgCol) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- second argument of function
    IF (args(4).ArgType != ODCIConst.ArgLit AND
        args(4).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    -- third argument of function
    IF (args(5).ArgType != ODCIConst.ArgLit AND
        args(5).ArgType != ODCIConst.ArgNull) THEN
      RETURN ODCIConst.Error;
    END IF;

    colname := rtrim(ltrim(args(3).colName, '"'), '"');

The first (column) argument of the function in the predicate must have statistics collected for it. If statistics have not been collected, ODCIStatsSelectivity returns an error status.

-- Check if the statistics table exists (we are using a
    -- user-defined table to store the user-defined statistics).
    -- Get user-defined statistics: MIN, MAX, NROWS
    FOR stats IN c1(cell, args(3).TableName, colname) LOOP
      -- Get user-defined statistics: MIN, MAX, NROWS
      lo := stats.lo;
      hi := stats.hi;
      nrows := stats.nrows;
      statsexists := TRUE;
      EXIT;
    END LOOP;

    -- If no user-defined statistics were collected, return error
    IF not statsexists THEN
      RETURN ODCIConst.Error;
    END IF;

Each Specific function predicate corresponds to an equivalent range predicate. For example, the predicate:

Power_EqualsSpecific_Func(col, 21, 25) = 0

which checks that the reading in cell 21 is not equal to 25, corresponds to the equivalent range predicate:

col[21] != 25

The ODCIStatsSelectivity function finds the corresponding range predicates for each Specific function predicate. There are several boundary cases where the selectivity can be immediately determined.

-- selectivity is 0 for "fn(col, <cell>, <value>) < 0"
    IF (stop = 0 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0) THEN
      sel := 0;
      RETURN ODCIConst.Success;
    END IF;

    -- selectivity is 0 for "fn(col, <cell>, <value>) > 1"
    IF (strt = 1 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0) THEN
      sel := 0;
      RETURN ODCIConst.Success;
    END IF;

    -- selectivity is 100% for "fn(col, <cell>, <value>) >= 0"
    IF (strt = 0 AND
        bitand(pred.Flags, ODCIConst.PredExactMatch) = 0 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStart) > 0) THEN
      sel := 100;
      RETURN ODCIConst.Success;
    END IF;

    -- selectivity is 100% for "fn(col, <cell>, <value>) <= 1"
    IF (stop = 1 AND
        bitand(pred.Flags, ODCIConst.PredExactMatch) = 0 AND
        bitand(pred.Flags, ODCIConst.PredIncludeStop) > 0) THEN
      sel := 100;
      RETURN ODCIConst.Success;
    END IF;

    -- get function name
    IF bitand(pred.Flags, ODCIConst.PredObjectFunc) > 0 THEN
      fname := pred.ObjectName;
    ELSE
      fname := pred.MethodName;
    END IF;

    -- convert prefix relational operator to infix:
    -- "Power_EqualsSpecific_Func(col, <cell>, <value>) = 1"
    -- becomes "col[<cell>] = <value>"

    --   Power_EqualsSpecific_Func(col, <cell>, <value>) = 0
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) <= 0
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) < 1
    -- can be transformed to
    --   col[<cell>] != <value>
    IF (fname LIKE upper('Power_Equals%') AND
        (stop = 0 OR
         (stop = 1 AND
          bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0))) THEN
      relop := '!=';

    --   Power_LessThanSpecific_Func(col, <cell>, <value>) = 0
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) <= 0
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) < 1
    -- can be transformed to
    --   col[<cell>] >= <value>
    ELSIF (fname LIKE upper('Power_LessThan%') AND
           (stop = 0 OR
            (stop = 1 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0))) THEN
      relop := '>=';

    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) = 0
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) <= 0
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) < 1
    -- can be transformed to
    --   col[<cell>] <= <value>
    ELSIF (fname LIKE upper('Power_GreaterThan%') AND
           (stop = 0 OR
            (stop = 1 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStop) = 0))) THEN
      relop := '<=';

    --   Power_EqualsSpecific_Func(col, <cell>, <value>) = 1
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) >= 1
    --   Power_EqualsSpecific_Func(col, <cell>, <value>) > 0
    -- can be transformed to
    --   col[<cell>] = <value>
    ELSIF (fname LIKE upper('Power_Equals%') AND
           (strt = 1 OR
            (strt = 0 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0))) THEN
      relop := '=';

    --   Power_LessThanSpecific_Func(col, <cell>, <value>) = 1
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) >= 1
    --   Power_LessThanSpecific_Func(col, <cell>, <value>) > 0
    -- can be transformed to
    --   col[<cell>] < <value>
    ELSIF (fname LIKE upper('Power_LessThan%') AND
           (strt = 1 OR
            (strt = 0 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0))) THEN
      relop := '<';

    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) = 1
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) >= 1
    --   Power_GreaterThanSpecific_Func(col, <cell>, <value>) > 0
    -- can be transformed to
    --   col[<cell>] > <value>
    ELSIF (fname LIKE upper('Power_GreaterThan%') AND
           (strt = 1 OR
            (strt = 0 AND
             bitand(pred.Flags, ODCIConst.PredIncludeStart) = 0))) THEN
      relop := '>';

    ELSE
      RETURN ODCIConst.Error;

    END IF;

After the Specific function predicate is transformed into a simple range predicate, ODCIStatsSelectivity calls get_selectivity to compute the selectivity for the range predicate (and thus, equivalently, for the Specific function predicate). It returns with a success status.

sel := get_selectivity(relop, value, lo, hi, nrows);
    RETURN ODCIConst.Success;
  END;

ODCIStatsIndexCost Method (for Specific Queries)

The first definition of the ODCIStatsIndexCost function estimates the cost of the domain index for Specific queries. For example, if a query asks for all instances where cell (3,7) has a value equal to 25, the function estimates the cost of the domain index access path to evaluate this query. (This definition of ODCIStatsIndexCost differs from the definition in the next section in that it includes the cmppos parameter for the position of the cell.)

The ia parameter contains the index information (an object instance of type SYS.ODCIINDEXINFO). The sel parameter is the selectivity of the operator predicate as estimated by the ODCIStatsSelectivity function for Specific queries. The estimated cost is returned in the cost output parameter. The qi parameter contains some information about the query and its environment (for example, whether the ALL_ROWS or FIRST_ROWS optimizer mode is being used). The pred parameter contains the operator information (an object instance of type SYS.ODCIPREDINFO). The args parameter contains descriptors of the value arguments of the operator as well as the start and stop values of the operator. 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).

In the power demand cartridge, the domain index cost for Specific queries is the same as the domain index cost for Any queries, so this version of the ODCIStatsIndexCost function simply calls the second definition of the function (described in the next section).

STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo,
     sel NUMBER, cost OUT sys.ODCICost, qi sys.ODCIQueryInfo,
     pred sys.ODCIPredInfo, args sys.ODCIArgDescList,
     strt NUMBER, stop NUMBER, cmppos NUMBER, cmpval NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
  BEGIN
    -- This is the cost for queries on a specific cell; simply
    -- use the cost for queries on any cell.
    RETURN ODCIStatsIndexCost(ia, sel, cost, qi, pred, args,
                              strt, stop, cmpval, env);
  END;

ODCIStatsIndexCost Method (for Any Queries)

The second definition of the ODCIStatsIndexCost function estimates the cost of the domain index for Any queries. For example, if a query asks for all instances where any cell has a value equal to 25, the function estimates the cost of the domain index access path to evaluate this query. (This definition of ODCIStatsIndexCost differs from the definition in the preceding section in that it does not include the cmppos parameter.)

The ia parameter contains the index information (an object instance of type SYS.ODCIINDEXINFO). The sel parameter is the selectivity of the operator predicate as estimated by the ODCIStatsSelectivity function for Any queries. The estimated cost is returned in the cost output parameter. The qi parameter contains some information about the query and its environment (for example, whether the ALL_ROWS or FIRST_ROWS optimizer mode is being used). The pred parameter contains the operator information (an object instance of type SYS.ODCIPREDINFO). The args parameter contains descriptors of the value arguments of the operator as well as the start and stop values of the operator. 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_XxxxxAny).

The index cost is estimated as the number of blocks in the index-organized table implementing the index multiplied by the selectivity of the operator predicate times a constant factor.

STATIC FUNCTION ODCIStatsIndexCost(ia sys.ODCIIndexInfo,
     sel NUMBER, cost OUT sys.ODCICost, qi sys.ODCIQueryInfo,
     pred sys.ODCIPredInfo, args sys.ODCIArgDescList,
     strt NUMBER, stop NUMBER, cmpval NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
     ixtable             VARCHAR2(40);
     numblocks           NUMBER := NULL;
     get_table           user_tables%ROWTYPE;
     CURSOR c1(tab VARCHAR2) IS
       SELECT * FROM user_tables WHERE table_name = tab;
  BEGIN
    -- This is the cost for queries on any cell.

    -- To compute the cost of a domain index, multiply the
    -- number of blocks in the table implementing the index
    -- with the selectivity

    -- Return if we don't have predicate selectivity
    IF sel IS NULL THEN
      RETURN ODCIConst.Error;
    END IF;

    cost := sys.ODCICost(NULL, NULL, NULL, NULL);

    -- Get name of table implementing the domain index
    ixtable := ia.IndexName || '_pidx';

    -- Get number of blocks in domain index
    FOR get_table IN c1(upper(ixtable)) LOOP
      numblocks := get_table.blocks;
      EXIT;
    END LOOP;

    IF numblocks IS NULL THEN
      -- Exit if there are no user-defined statistics for the index
      RETURN ODCIConst.Error;
    END IF;

    cost.CPUCost := ceil(400*(sel/100)*numblocks);
    cost.IOCost := ceil(1.5*(sel/100)*numblocks);
    RETURN ODCIConst.Success;
  END;

ODCIStatsFunctionCost Method

The ODCIStatsFunctionCost function estimates the cost of evaluating a function (Power_XxxxxSpecific_Func or Power_XxxxxAny_Func).

The func parameter contains the function information; this parameter is an object instance of type SYS.ODCIFUNCINFO. The estimated cost is returned in the output cost parameter. The args parameter (an object instance of type SYS.ODCIARGDESCLIST) contains a descriptor for each argument of the function. If the function contains a literal of type PowerDemand_Typ as its first argument, the object parameter will contain the value in the form of an object constructor. The value parameter is the value in the cell specified by the function (PowerXxxxxSpecific_Func or Power_XxxxxAny_Func).

The function cost is simply estimated as some default value depending on the function name. Since the functions don't read any data from disk, the I/O cost is set to zero.

STATIC FUNCTION ODCIStatsFunctionCost(func sys.ODCIFuncInfo,
     cost OUT sys.ODCICost, args sys.ODCIArgDescList,
     object PowerDemand_Typ, value NUMBER, env sys.ODCIEnv)
     RETURN NUMBER IS
     fname               VARCHAR2(30);
  BEGIN
    cost := sys.ODCICost(NULL, NULL, NULL, NULL);

    -- Get function name
    IF  bitand(func.Flags, ODCIConst.ObjectFunc) > 0 THEN
      fname := func.ObjectName;
    ELSE
      fname := func.MethodName;
    END IF;

    IF fname LIKE upper('Power_LessThan%') THEN
      cost.CPUCost := 5000;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSIF fname LIKE upper('Power_Equals%') THEN
      cost.CPUCost := 7000;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSIF fname LIKE upper('Power_GreaterThan%') THEN
      cost.CPUCost := 5000;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSE
      RETURN ODCIConst.Error;
    END IF;
  END;