Release 2 (8.1.6)
Part Number A76937-01
 Library |
 Product |
 Contents |
 Index |
| Oracle 8i Data Cartridge Developer's Guide Release 2 (8.1.6) Part Number A76937-01 |
|
Power Demand Cartridge Example , 5 of 8
This section explains the parts of the power demand cartridge as they relate to extensible optimization. Explanatory text and code segments are mixed.
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:
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 ); /
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.
Table 11-5The 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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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 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;
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|