Testing the Domain Index

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

Creating the Statistics Table (PowerCartUserStats)

The table PowerCartUserStats is used to store statistics about the hourly power grid readings. These statistics will be used by the method ODCIStatsSelectivity (described later) to estimate the selectivity of operator predicates. Because of the types of statistics collected, it is more convenient to use a separate table instead of letting Oracle store the statistics.

The PowerCartUserStats table contains the following columns:

• The table and column for which statistics are collected

• The cell for which the statistics are collected

• The minimum and maximum power demand for the given cell over all power grid readings

• The number of non-null readings for the given cell over all power grid readings

  CREATE TABLE PowerCartUserStats (
    -- Table for which statistics are collected
    tab VARCHAR2(30),
    -- Column for which statistics are collected
    col VARCHAR2(30),
    -- Cell position
    cpos NUMBER,
    -- Minimum power demand for the given cell
    lo NUMBER,
    -- Maximum power demand for the given cell
    hi NUMBER,
    -- Number of (non-null) power demands for the given cell
    nrows NUMBER
  );
  /
  

Creating the Extensible Optimizer Methods

The power demand cartridge creates an object type that specifies methods that will be used by the extensible optimizer. These methods are part of the ODCIStats (Oracle Data Cartridge Interface STATisticS) interface and they collectively define the methods that are called when an ANALYZE command is issued or when the optimizer is deciding on the best execution plan for a query.

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

ODCIStatsCollect

ODCIStatsDelete

ODCIStatsSelecti
vity

ODCIStatsIndexCo
st

ODCIStatsFunctio
nCost

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) RETURN NUMBER,
  STATIC FUNCTION ODCIStatsDelete(col sys.ODCIColInfo) RETURN NUMBER,
  STATIC FUNCTION ODCIStatsCollect(ia sys.ODCIIndexInfo,
     options sys.ODCIStatsOptions, rawstats OUT RAW) RETURN NUMBER,
  STATIC FUNCTION ODCIStatsDelete(ia sys.ODCIIndexInfo) 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) 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) 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)
     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) 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) 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) RETURN NUMBER,
     PRAGMA restrict_references(ODCIStatsFunctionCost, WNDS, WNPS)
);
/

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. In release 8.1, there is only one set of the extensible optimizer interface routines, called SYS.ODCISTATS1. Thus, in release 8.1, the ODCIGetInterfaces routine must return'SYS'.'ODCISTATS1' as one of the implemented interfaces.

  STATIC FUNCTION ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
     RETURN NUMBER IS
  BEGIN
     ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCISTATS1'));
     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 ANALYZE command used to collect the column statistics are also passed in as parameters. For example, if ANALYZE ESTIMATE is used, then the percentage or number of rows specified in the ANALYZE command is passed in to ODCIStatsCollect. 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)
     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 ODCIStatsCollect 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)
     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 in the ANALYZE command used to collect the index statistics are also passed in as parameters. For example, if ANALYZE ESTIMATE is used, then the percentage or number of rows is passed in. The output parameter rawstats is not used.

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

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

    stmt := 'ANALYZE TABLE '
         || ia.IndexSchema || '.' || ia.IndexName || '_pidx'
         || ' COMPUTE STATISTICS';

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

    cnum := dbms_sql.open_cursor;
    dbms_sql.parse(cnum, stmt, dbms_sql.native);
    junk := dbms_sql.execute(cnum);
    dbms_sql.close_cursor(cnum);

    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)
     RETURN NUMBER IS
     cnum                INTEGER;
     stmt                VARCHAR2(1000);
     junk                INTEGER;
  BEGIN
    -- To delete statistics for a domain index, simply delete the
    -- statistics for the table implementing the index

    sys.ODCIIndexInfoDump(ia);

    stmt := 'ANALYZE TABLE '
         || ia.IndexSchema || '.' || ia.IndexName || '_pidx'
         || ' DELETE STATISTICS';

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

    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;

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)
  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).

  ndv := hi-lo+1;

  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)
     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 (by issuing the ANALYZE command which will call ODCIStatsCollect for the column). 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;
    -- e.g., "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);
    RETURN ODCIConst.Success;
  END;

ODCIStatsSelectivity Method (for Any Queries)

The second definition of the ODCIStatsSelectivity function estimates the selectivity of operator or function predicates 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 percentage of rows in which any cell has the specified value. (This definition of ODCIStatsSelectivity differs from the definition in the preceding section in that it does not include the cell parameter.)

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 value parameter is the value in the cell specified by the function (Power_XxxxxAny_Func).

The selectivity for Any queries can be calculated as the complement of the probability that none of the cells has the specified value. Thus, if s[i] is the selectivity of the ith cell having the given value, then the selectivity of the Any function predicate can be estimated as:

    1 - (1-s[1])(1-s[2])...(1-s[100])

assuming that the value of each cell is independent of the values in other cells. This means that this version of the ODCIStatsSelectivity function (for Any queries) can compute its selectivity by calling the first definition of the ODCIStatsSelectivity function (for Specific queries).

  STATIC FUNCTION ODCIStatsSelectivity(pred sys.ODCIPredInfo,
     sel OUT NUMBER, args sys.ODCIArgDescList, strt NUMBER, stop NUMBER,
     object PowerDemand_Typ, value NUMBER)
     RETURN NUMBER IS
     cellsel             NUMBER;
     i                   NUMBER;
     specsel             NUMBER;
     newargs             sys.ODCIArgDescList
                           := sys.ODCIArgDescList(NULL, NULL, NULL,
                                                  NULL, NULL);
  BEGIN
    -- To compute selectivity for the ANY functions, call the
    -- selectivity function for the SPECIFIC functions.  For example,
    -- the selectivity of the ANY predicate
    --
    --     Power_EqualsAnyFunc(object, value) = 1
    --
    -- is computed as
    --
    --     1 - (1-s[1])(1-s[2])...(1-s[100])
    --
    -- where s[i] is the selectivity of the SPECIFIC predicate
    --
    --     Power_EqualsSpecific_Func(object, i, value) = 1
    --

    sel := 1;
    newargs(1) := args(1);
    newargs(2) := args(2);
    newargs(3) := args(3);
    newargs(4) := sys.ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL);
    newargs(5) := args(4);
    FOR i in 1..100 LOOP
      cellsel := NULL;
      specsel := power_statistics.ODCIStatsSelectivity(pred, cellsel,
                   newargs, strt, stop, object, i, value);
      IF specsel = ODCIConst.Success THEN
        sel := sel * (1 - cellsel/100);
      END IF;
    END LOOP;

    sel := sel*100;
    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)
     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);
  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)
     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);

    -- 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)
     RETURN NUMBER IS
     fname               VARCHAR2(30);
  BEGIN
    cost := sys.ODCICost(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 := 500;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSIF fname LIKE upper('Power_Equals%') THEN
      cost.CPUCost := 700;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSIF fname LIKE upper('Power_GreaterThan%') THEN
      cost.CPUCost := 100;
      cost.IOCost := 0;
      RETURN ODCIConst.Success;
    ELSE
      RETURN ODCIConst.Error;
    END IF;
  END;

Associating the Extensible Optimizer Methods with Database Objects

In order for the optimizer to use the methods defined in the power_statistics object type, they have to be associated with the appropriate database objects. The following statements do this.

-- Associate statistics type with types, indextypes, and functions
ASSOCIATE STATISTICS WITH TYPES PowerDemand_Typ USING power_statistics;
ASSOCIATE STATISTICS WITH INDEXTYPES power_idxtype USING power_statistics;
ASSOCIATE STATISTICS WITH FUNCTIONS
  Power_EqualsSpecific_Func,
  Power_GreaterThanSpecific_Func,
  Power_LessThanSpecific_Func,
  Power_EqualsAny_Func,
  Power_GreaterThanAny_Func,
  Power_LessThanAny_Func
  USING power_statistics;

Analyzing the Database Objects

Analyzing tables, columns, and indexes ensures that the optimizer has the relevant statistics to estimate accurate costs for various access paths and choose a good plan. Further, the selectivity and cost functions defined in the power_statistics object type rely on the presence of statistics. The following statements analyze the database objects and verify that statistics were indeed collected.

-- Analyze the table
ANALYZE TABLE PowerDemand_Tab COMPUTE STATISTICS;

-- Verify that user-defined statistics were collected
SELECT tab tablename, col colname, cpos, lo, hi, nrows
FROM PowerCartUserStats
WHERE nrows IS NOT NULL
ORDER BY cpos;

-- Delete the statistics
ANALYZE TABLE PowerDemand_Tab DELETE STATISTICS;

-- Verify that user-defined statistics were deleted
SELECT tab tablename, col colname, cpos, lo, hi, nrows
FROM PowerCartUserStats
WHERE nrows IS NOT NULL
ORDER BY cpos;

-- Re-analyze the table
ANALYZE TABLE PowerDemand_Tab COMPUTE STATISTICS;

-- Verify that user-defined statistics were re-collected
SELECT tab tablename, col colname, cpos, lo, hi, nrows
FROM PowerCartUserStats
WHERE nrows IS NOT NULL
ORDER BY cpos;