Release 8.1.5
A68002-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Data Cartridge Developer's Guide Release 8.1.5 A68002-01 |
|
Query Optimization is the process of choosing the most efficient way to execute a SQL statement. When the cost-based optimizer was offered for the first time with Oracle7, Oracle supported only standard relational data. The introduction of objects with Oracle8i extended the supported datatypes and functions. The Extensible Indexing feature discussed in the previous chapter, introduces user-defined access methods.
|
For more information regarding optimization see:
|
The extensible optimizer feature allows authors of user-defined functions and indexes to create statistics collection, selectivity, and cost functions that are used by the optimizer in choosing a query plan. The optimizer cost model is extended to integrate information supplied by the user. Specifically, you now can:
ANALYZE command to invoke user-defined statistics collection and deletion functions.
Please note that only the cost-based optimizer has been enhanced; Oracle has not altered the operation of the rule-based optimizer.
The optimizer generates an execution plan for a SQL DML statement: SELECT, INSERT, UPDATE, or DELETE. For simplicity, we describe the generation of an execution plan in terms of a SELECT statement, but the same process applies to other DML statements.
An execution plan includes an access method for each table in the FROM clause, and an ordering, called the join order, of the tables in the FROM clause. System-defined access methods include indexes, hash clusters, and table scans. The optimizer chooses a plan by generating a set of join orders, or permutations, by computing the cost of each, and then by selecting the process with the lowest cost. For each table in the join order, the optimizer computes the cost of each possible access method and join method and chooses the one with the lowest cost. The cost of the join order is the sum of the access method and join method costs. The costs are calculated using algorithms which together comprise the cost model. The cost model includes varying level of detail about the physical environment in which the query is executed.
The optimizer uses statistics about the objects referenced in the query to compute the selectivity and costs. The statistics are gathered using the ANALYZE command. The selectivity of a predicate is the fraction of rows in a table that is chosen by the predicate. It is a number between 0 and 1.
The Extensible Indexing feature allows users to define new operators, indextypes, and domain indexes. For user-defined operators and domain indexes, the Extensible Optimizer feature allows you to control the three main components used by the optimizer to select an execution plan:
In the following sections, we describe each of these components in greater detail.
Statistics are collected using the ANALYZE command. Statistics can be collected for tables and indexes. In general, the more accurate the statistics, the better the execution plan generated by the optimizer. We call the statistics generated by the current ANALYZE command standard statistics. However, with the addition of user-defined domain indexes in Oracle8i.release 8.1.5, the standard ANALYZE statement cannot generate any statistics on the domain index since the database does not know the index storage structure.
The Extensible Optimizer feature lets you define statistics collection functions for domain indexes and columns. The extension to the ANALYZE command has the effect that whenever a domain index is analyzed, a call is made to the user-specified statistics collection function. The database does not know the representation and meaning of the user-collected statistics.
In addition to domain indexes, Oracle supports user-defined statistics collection functions for individual columns of a table, and for user-defined datatypes. In the former case, whenever a column is analyzed, the user-defined statistics collection function is called to collect statistics in addition to any standard statistics that the database collects. If a statistics collection function exists for a datatype, it is called for each column of the table being analyzed that has the required type.
Thus, the Extensible Optimizer feature extends ANALYZE to allow user-defined statistics collection functions for domain indexes, indextypes, datatypes, and individual table columns.
The cost of evaluating a user-defined function depends on the algorithm and the statistical properties of its arguments. It is not practical to store statistics for all possible combinations of columns that could be used as arguments for all functions. Therefore, Oracle maintains only statistics on individual columns. It is also possible that function costs depend on the different statistical properties of each argument. Every column could require statistics for every argument position of every applicable function. Oracle does not support such a proliferation of statistics and cost functions because it would decrease performance.
A user-defined function to drop statistics is required whenever there is a user-defined statistics collection function; it is called by ANALYZE DELETE.
Since domain indexes cannot be partitioned in Oracle8i, release 8.1.5, a user-defined statistics collection function collects only global statistics on the non-partitioned index.
When an ANALYZE command specifies a list of partitions, this information is not passed to user-defined statistics collection functions.
The optimizer uses statistics to calculate the selectivity of predicates. The selectivity is the fraction of rows in a table that is chosen by the predicate. It is a number between 0 and 1. The selectivity of a predicate is used to estimate the cost of a particular access method; it is also used to determine the optimal join order. A poor choice of join order by the optimizer could result in a very expensive execution plan.
Currently, the optimizer uses a standard algorithm to estimate the selectivity of selection and join predicates. However, the algorithm does not always work well in cases in which predicates contain functions or type methods. In addition, in Oracle8i, release 8.1.5, 1 predicates can contain user-defined operators about which the optimizer does not have any information. In that case the optimizer cannot compute an accurate selectivity.
For greater control over the optimizer's selectivity estimation, this feature lets you specify user-defined selectivity functions for predicates containing user-defined operators, stand-alone functions, package functions, or type methods. The user-defined selectivity function is called by the optimizer whenever it encounters a predicate with one of the following forms:
operator(...) relational_operator <constant> <constant> relational_operator operator(...) operator(...) LIKE <constant>
where
operator(...) is a user-defined operator, stand-alone function, package function, or type method,
relational_operator is one of {<, <=, =, >=, >}, and
<constant> is a constant value expression or bind variable.
For such cases, users can define selectivity functions associated with operator(...). The arguments to operator can be columns, constants, bind variables, or attribute references. When optimizer encounters such a predicate, it calls the user-defined selectivity function and passes the entire predicate as an argument (including the operator, function, or type method and its arguments, the relational operator relational_operator, and the constant expression or bind variable). The return value of the user-defined selectivity function must be expressed as a percent, and be between 0 and 100 inclusive; the optimizer ignores values outside this range.
Wherever possible, the optimizer uses user-defined selectivity values. However, this is not possible in the following cases:
+ 3 relational_operator <constant>
In each of these cases, the optimizer uses heuristics to estimate the selectivity.
The optimizer estimates the cost of various access paths to choose an optimal plan. For example, it computes the cost of using an index and a full table scan to choose between the two. However, with regard to domain indexes, the optimizer does not know the internal storage structure of the index, and so it cannot compute a good estimate of the cost of a domain index.
For greater flexibility, the cost model has been extended to let you define costs for domain indexes and user-defined stand-alone functions, package functions, and type methods. The user-defined costs can be in the form of default costs that the optimizer looks up, or they can be full-fledged cost functions which the optimizer calls to compute the cost.
As is the case with user-defined selectivity, user-defined cost is optional. If no user-defined cost is available, the optimizer uses heuristics to compute an estimate. However, in the absence of sufficient useful information about the storage structures in user-defined domain indexes and functions, such estimates can be very inaccurate and result in the choice of a sub-optimal execution plan.
User-defined cost functions for domain indexes are called by the optimizer only if a domain index is a valid access path for a user-defined operator (for details regarding when this is true, see the discussion of user-defined indexing in the previous chapter). User-defined cost functions for functions, methods and domain indexes are only called when a predicate has one of the following forms:
operator(...) relational_operator <constant> <constant> relational_operator operator(...) operator(...)LIKE<constant>
where
operator(...) is a user-defined operator, stand-alone function, package function, or type method,
relational_operator is one of {<, <=, =, >=, >}, and
<constant> is a constant value expression or bind variable.
This is, of course, identical to the conditions for user-defined selectivity functions.
User-defined cost functions can return three cost values, each value representing the cost of a single execution of a function or domain index implementation:
CPU -- the number of machine instructions executed by the function or domain index implementation. This does not include the overhead of invoking the function.
I/O -- the number of data blocks read by the function or domain index implementation. For a domain index, this does not include accesses to the Oracle table. The multiblock I/O factor is not passed to the user-defined cost functions.
NETWORK -- the number of data blocks transmitted. This is valid for distributed queries as well as functions and domain index implementations. For Oracle8i this cost component is not used and is ignored; however, as described below, the user is required to stipulate a value so that backward compatibility is facilitated when this feature is introduced.
The optimizer computes a composite cost from these cost values.
The cost of a query is a function of the cost values discussed above. These values can be combined in one of three ways depending on the settings of optimizer initialization parameters. The setting determines the function that is minimized.
optimizer_mode is first_rows, the resource cost of returning a single row is minimized. The optimizer mode is passed to user-defined cost functions.
optimizer_percent_parallel is not zero, a combination of throughput and resource costs is minimized.
You can compute and store user-defined statistics for domain indexes and columns. These statistics are in addition to the standard statistics that are already collected by ANALYZE. User-defined selectivity and cost functions for functions and domain indexes can use standard and user-defined statistics in their computation. The internal representation of these statistics need not be known to Oracle; we only require that you provide methods for their access. You are solely responsible for defining the representation of such statistics and for maintaining them. It is important to note that user-collected statistics are only used by user-defined selectivity and cost functions; the optimizer use s only its standard statistics.
User-defined statistics collection, selectivity, and cost functions must be defined in a user-defined type. This type must have a form similar to a system-defined interface called ODCIStats (Oracle Data Cartridge Interface Statistics) defined as follows:
CREATE INTERFACE ODCIStats AS ( -- Function to get current interface FUNCTION ODCIGetInterfaces(ifclist OUT ODCIObjectList) return NUMBER, -- User-defined statistics functions FUNCTION ODCIStatsCollect(col ODCIColInfo, options ODCIStatsOptions, statistics OUT RAW) return NUMBER, FUNCTION ODCIStatsCollect(ia ODCIIndexInfo, options ODCIStatsOptions, statistics OUT RAW) return NUMBER, FUNCTION ODCIStatsDelete(col ODCIColInfo) return NUMBER, FUNCTION ODCIStatsDelete(ia ODCIIndexInfo) return NUMBER, -- User-defined selectivity function FUNCTION ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER, args ODCIArgDescList, start <function_return_type>, stop <function_return_type>, <list of function arguments>) return NUMBER, -- User-defined cost function for functions and type methods FUNCTION ODCIStatsFunctionCost(func ODCIFuncInfo, cost OUT ODCICost,
args ODCIArgDescList, <list of function arguments>) return NUMBER, -- User-defined cost function for domain indexes FUNCTION ODCIStatsIndexCost(ia ODCIIndexInfo, sel NUMBER, cost OUT ODCICost, qi ODCIQueryInfo, pred ODCIPredInfo, args ODCIArgDescList, start <operator_return_type>, stop <operator_return_type>, <list of operator value arguments>) return NUMBER )
You can define a new object type, referred to as a statistics type, with a subset of functions from ODCIStats. Since user-defined statistics collection, selectivity, and cost functions are all optional, a statistics type need not contain all the functions in ODCIStats.
The types of the parameters of statistics type methods are system-defined ODCI (Oracle Data Cartridge Interface) datatypes. Some of them are described in the reference to Extensible Indexing, and the rest are described in the reference chapter detailing the Extensible Optimizer.
The selectivity and cost functions must not change any database or package state. To that end, they must be defined with appropriate purity level pragmas, otherwise the optimizer will not call them.
Depending on the object that user-defined statistics are being associated with, not all the functions defined in a statistics type will be used. The table below lists the functions and default statistics that will be used by the optimizer.
There are two user-defined statistics collection functions, one for collecting statistics and the other for deleting them.
The first, ODCIStatsCollect, is used to collect user-defined statistics; its interface depends on whether a column or domain index is being analyzed. It is called when analyzing a column of a table or a domain index and takes two parameters:
col for the column being analyzed, or
ia for the domain index being analyzed;
options for options specified in the ANALYZE command (e.g., the sample size when ANALYZE ESTIMATE is used).
As mentioned, the database does not interpret statistics collected by ODCIStatsCollect. You can store output in a user-managed format or in a dictionary table (described in the Extensible Optimizer reference) provided for the purpose. The statistics collected by the ODCIStatsCollect functions are returned in the output parameter, statistics, as a RAW datatype.
When an ANALYZE DELETE command is issued, user-collected statistics are deleted by calling the ODCIStatsDelete function whose interface depends on whether the statistics for a column or domain index are being dropped. It takes a single parameter: col, for the column whose user-defined statistics need to be deleted, or ia, for the domain index whose statistics are to be deleted.
If a user-defined ODCIStatsCollect function is present in a statistics type, the corresponding ODCIStatsDelete function must also be present.
The return values of the ODCIStatsCollect and ODCIStatsDelete functions must be Success (indicating success), Error (indicating an error), or Warning (indicating a warning); these return values are defined in a system package ODCIConst (described in the Extensible Optimizer reference).
You will recall that user-defined selectivity functions are used only for predicates of the following forms:
operator(...) relational_operator <constant> <constant> relational_operator operator(...) operator(...) LIKE <constant>
A user-defined selectivity function, ODCIStatsSelectivity, takes five sets of input parameters that describe the predicate:
pred describing the function operator and the relational operator relational_operator;
args describing the start and stop values (i.e., <constant>) of the function and the actual arguments to the function operator;
start whose datatype is the same as that of the function's return value, describing the start value of the function;
stop whose datatype is the same as that of the function's return value, describing the stop value of the function;
operator.
The computed selectivity is returned in the output parameter sel, in whole numbers, as a percentage, between 0 and 100, inclusive. The optimizer ignores invalid values.
The return value of the ODCIStatsSelectivity function must be
As an example, consider a function myFunction defined as follows:
myFunction (a NUMBER, b VARCHAR2(10)) return NUMBER
A user-defined selectivity function for the function myFunction would be as follows:
ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER, args ODCIArgDescList, start NUMBER, stop NUMBER, a NUMBER, b VARCHAR2(10)) return NUMBER
If the function myFunction is called with literal arguments, e.g.,
myFunction (2, 'TEST') > 5
then the selectivity function is called as follows:
ODCIStatsSelectivity(<ODCIPredInfo constructor>, sel, <ODCIArgDescList constructor>, 5, NULL, 2, 'TEST')
If, on the other hand, the function myFunction is called with some non-literals e.g.,
myFunction(Test_tab.col_a, 'TEST')> 5
where col_a is a column in table Test_tab, then the selectivity function is called as follows:
ODCIStatsSelectivity(<ODCIPredInfo constructor>, sel, <ODCIArgDescList constructor>, 5, NULL, NULL, 'TEST')
In other words, the start, stop, and function argument values are passed to the selectivity function only if they are literals; otherwise they are NULL. The ODCIArgDescList descriptor describes all its following arguments.
As already mentioned, user-defined cost functions are only used for predicates of the following forms:
operator(...) relational_operator <constant> <constant> relational_operator operator(...) operator(...) LIKE <constant>
You can define a function, ODCIStatsFunctionCost, for computing the cost of stand-alone functions, package functions, or type methods. This function takes three sets of input parameters describing the predicate:
func describing the function operator;
args describing the actual arguments to the function operator;
operator.
The ODCIStatsFunctionCost function returns its computed cost in the cost parameter. As mentioned, the returned cost can have two components -- CPU and I/O -- which are combined by the optimizer to compute a composite cost. The costs returned by user-defined cost functions must be positive whole numbers. Invalid values are ignored by the optimizer.
The return value of the ODCIStatsFunctionCost function must be
Consider a function myFunction defined as follows:
myFunction (a NUMBER, b VARCHAR2(10)) return NUMBER
A user-defined cost function for the function myFunction would be coded as follows:
ODCIStatsFunctionCost(func ODCIFuncInfo, cost OUT ODCICost, args ODCIArgDescList, a NUMBER, b VARCHAR2(10)) return NUMBER
If the function myFunction is called with literal arguments, e.g.,
myFunction(2, 'TEST') > 5,
then the cost function is called as follows:
ODCIStatsFunctionCost(<ODCIFuncInfo constructor>, cost, <ODCIArgDescList constructor>, 2, 'TEST')
If, on the other hand, the function myFunction is called with some non-literals, e.g.,
myFunction(Test_tab.col_a, 'TEST') > 5
where col_a is a column in table Test_tab, then the cost function is called as follows:
ODCIStatsFunctionCost(<ODCIFuncInfo constructor>, cost, <ODCIArgDescList constructor>, NULL, 'TEST')
In other words, function argument values are passed to the cost function only if they are literals; otherwise they are NULL. The ODCIArgDescList descriptor describes all its following arguments.
User-defined cost functions for domain indexes are used for the same type of predicates mentioned previously, except that operator must be a user-defined operator for which a valid domain index access path exists.
The ODCIStatsIndexCost function takes eight sets of parameters:
ia describing the domain index;
sel representing the user-computed selectivity of the predicate;
qi containing additional information about the query;
pred describing the predicate;
args describing the start and stop values (i.e., <constant>) of the operator and the actual arguments to the operator operator;
start, whose datatype is the same as that of the operator's return value, describing the start value of the operator;
stop whose datatype is the same as that of the operator's return value, describing the stop value of the operator; and
operator. The value arguments of an operator are the arguments excluding the first argument.
The computed cost of the domain index is returned in the output parameter, cost.
ODCIStatsIndexCost returns
Consider an operator
Contains(a_stringVARCHAR2(2000),b_stringVARCHAR2(10))
that returns 1 or 0 depending on whether or not the string b_string is contained in the string a_string. Further, assume that the operator is implemented by a domain index. A user-defined index cost function for this domain index would be coded as follows:
ODCIStatsIndexCost(ia ODCIIndexInfo, sel NUMBER, cost OUT ODCICost, qi ODCIQueryInfo, pred ODCIPredInfo, args ODCIArgDescList, start NUMBER, stop NUMBER, b_string VARCHAR2(10)) return NUMBER
Note that the first argument, a_string, of Contains does not appear as a parameter of ODCIStatsIndexCost. This is because the first argument to an operator must be a column for the domain index to be used, and this column information is passed in via the ODCIIndexInfo parameter. Only the operator arguments after the first (i.e., the "value" arguments) must appear as parameters to the ODCIStatsIndexCost function.
If the operator is called, e.g.,
Contains(Test_tab.col_c,'TEST') <= 1
then the index cost function is called as follows:
ODCIStatsIndexCost(<ODCIIndexInfo constructor>, sel, cost, <ODCIQueryInfo constructor>, <ODCIPredInfo constructor>, <ODCIArgDescList constructor>, NULL, 1, 'TEST')
In other words, the start, stop, and operator argument values are passed to the index cost function only if they are literals; otherwise they are NULL. The ODCIArgDescList descriptor describes all its following arguments.
Statistics types act as interfaces for user-defined functions that influence the choice of an execution plan by the optimizer. However, for the optimizer to be able to use a statistics type requires a mechanism to bind the statistics type to a database object (column, stand-alone function, object type, index, indextype or package). This is the function of the new ASSOCIATE STATISTICS command. The following sections describe this command in more detail.
User-defined statistics functions are relevant for columns (both standard SQL datatypes and object types) and domain indexes. Statistics types used to collect user-defined statistics need not have the ODCIStatsSelectivity, ODCIStatsFunctionCost, and ODCIStatsIndexCost functions (they are ignored). The sections below describe how column and index user-defined statistics are collected.
User-collected statistics can either be stored in some predefined dictionary tables or users could create their own tables. The latter approach requires that privileges on these tables be administered properly, backup and restoration of these tables be done along with other dictionary tables, and point-in-time recovery considerations be resolved.
To ease the administration overhead, a predefined table, USTATS$, is created where you can store statistics. These statistics are not interpreted by the system; they are used by user-defined selectivity and cost functions. In addition to using these predefined tables, nothing prevents you from creating and administering your own tables to store the statistics. Details on the USTATS$ table are given in the Extensible Optimizer reference.
Consider a table Test_tab defined as follows:
CREATE TABLE Test_tab ( col_a NUMBER, col_b typ1, col_c VARCHAR2(2000) )
where typ1 is an object type. Suppose that stat is a statistics type with ODCIStatsCollect and ODCIStatsDelete functions. User-defined statistics are collected by the ANALYZE command for the column col_b if we bind a statistics type with the column as follows:
ASSOCIATE STATISTICS WITH COLUMNS Test_tab.col_b USING stat
A list of columns can be associated with the statistics type stat. Note that Oracle supports only associations with top-level columns, not attributes of object types; if you wish, the ODCIStatsCollect function can collect individual attribute statistics by traversing the column.
Another way to collect user-defined statistics is to declare an association with a datatype as follows:
ASSOCIATE STATISTICS WITH TYPES typ1 USING stat_typ1
which declares stat_typ1 as the statistics type for the type typ1. When the table Test_tab is analyzed with this association, user-defined statistics are collected for the column col_b using the ODCIStatsCollect function of statistics type stat_typ1.
Individual column associations always have precedence over associations with types. Thus, in the above example, if both ASSOCIATE STATISTICS commands are issued, ANALYZE would use the statistics type stat (and not stat_typ1) to collect user-defined statistics for column col_b. It is also important to note that standard statistics, if possible, are collected along with user-defined statistics.
User-defined statistics are deleted using the ODCIStatsDelete function from the same statistics type that was used to collect the statistics.
Associations defined by the ASSOCIATE STATISTICS command are stored in a new dictionary table called ASSOCIATION$ (details are given in the Extensible Optimizer reference).
Only user-defined datatypes can have statistics types associated with them; you cannot declare associations for standard SQL datatypes.
A domain index has an indextype. A statistics type for a domain index is defined by associating it either with the index or its indextype. Consider the following example using the table Test_tab we defined earlier:
CREATE INDEX Test_indx ON Test_tab(col_a) INDEXTYPE IS indtype PARAMETERS('example'); CREATE OPERATOR userOp BINDING (NUMBER) RETURN NUMBER USING userOp_func; CREATE INDEXTYPE indtype FOR userOp(NUMBER) USING imptype;
Here, indtype is the indextype, userOp is a user-defined operator supported by indtype, userOp_func is the functional implementation of userOp, and imptype is the implementation type of the indextype indtype.
A statistics type stat_Test_indx can be associated with the index Test_indx as follows:
ASSOCIATE STATISTICS WITH INDEXES Test_indx USING stat_Test_indx
When the domain index Test_indx is analyzed, user-defined statistics for the index are collected by calling the ODCIStatsCollect function of stat_Test_indx.
If a statistics type association is not defined for a specific index, Oracle looks for a statistics type association for the indextype of the index. In the above example, a statistics type stat_indtype can be associated with the indextype indtype as follows:
ASSOCIATE STATISTICS WITH INDEXTYPES indtype USING stat_indtype
When the domain index Test_indx is analyzed and no statistics type association has been defined for the index Test_indx, then user-defined statistics for the index are collected by calling the ODCIStatsCollect function of stat_indtype.
Thus, individual domain index associations always have precedence over associations with the corresponding indextypes.
Domain index statistics are dropped using the ODCIStatsDelete function from the same statistics type that was used to collect the statistics.
Selectivity functions are used by the optimizer to compute the selectivity of predicates in a query. The predicates must have one of the appropriate forms and can contain user-defined operators, stand-alone functions, package functions, or type methods. Selectivity computation for each is described below.
Consider the example laid out earlier, and suppose that the following association is declared:
ASSOCIATE STATISTICS WITH FUNCTIONS userOp_func USING stat_userOp_func
Now, if the following predicate
userOp(Test_tab.col_a) = 1
is encountered, the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_userOp_func that is associated with the functional implementation of the userOp_func of the userOp operator.
If the association
ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_MyFunction
is declared for a stand-alone function myFunction, then the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_myFunction for the following predicate (for instance):
myFunction(Test_tab.col_a, 'TEST') = 1.
If the association
ASSOCIATE STATISTICS WITH PACKAGES Demo_pack USING stat_Demo_pack
is declared for a package Demo_pack, then the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_Demo_pack for the following predicate (for instance):
Demo_pack.myDemoPackFunction(Test_tab.col_a, 'TEST') = 1
where myDemoPackFunction is a function in Demo_pack.
If the association
ASSOCIATE STATISTICS WITH TYPES Example_typ USING stat_Example_typ
is declared for a type Example_typ, then the optimizer calls the ODCIStatsSelectivity function (if present) in the statistics type stat_Example_typ for the following predicate (for instance):
myExampleTypMethod(Test_tab.col_b) = 1
where myExampleTypMethod is a method in Example_typ.
An alternative to selectivity functions is user-defined default selectivity. The default selectivity is a value (between 0% and 100%) that is looked up by the optimizer instead of calling a selectivity function. Default selectivities can be used for predicates with user-defined operators, stand-alone functions, package functions, or type methods.
The following command:
ASSOCIATE STATISTICS WITH FUNCTIONS myFunction DEFAULT SELECTIVITY 20
declares that the following predicate, for instance,
myFunction(Test_tab.col_a) = 1
always has a selectivity of 20 percent (or 0.2) regardless of the parameters of myFunction, or the comparison operator "=", or the constant "1". The optimizer uses this default selectivity instead of calling a selectivity function.
An association can be declared using either a statistics type or a default selectivity, but not both. Thus, the following statement is illegal:
ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_myFunction DEFAULT SELECTIVITY 20
The following are some more examples of default selectivity declarations:
ASSOCIATE STATISTICS WITH PACKAGES Demo_pack DEFAULT SELECTIVITY 20 ASSOCIATE STATISTICS WITH TYPES Example_typ DEFAULT SELECTIVITY 20
The optimizer uses user-defined cost functions to compute the cost of predicates in a query. The predicates must have one of the forms listed earlier and can contain user-defined operators, stand-alone functions, package functions, or type methods. In addition, user-defined cost functions are also used to compute the cost of domain indexes. Cost computation for each is described below.
Consider the example outlined above, and suppose that the following associations are declared:
ASSOCIATE STATISTICS WITH INDEXES Test_indx USING stat_Test_indx ASSOCIATE STATISTICS WITH FUNCTIONS userOp USING stat_userOp_func
Consider the following predicate:
userOp(Test_tab.col_a) = 1.
If the domain index Test_indx implementing userOp is being evaluated, the optimizer calls the ODCIStatsIndexCost function (if present) in the statistics type stat_Test_indx. If the domain index is not used, however, the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_userOp to compute the cost of the functional implementation of the operator userOp.
If the association
ASSOCIATE STATISTICS WITH FUNCTIONS myFunction USING stat_myFunction
is declared for a stand-alone function myFunction, then the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_myFunction for the following predicate (for instance):
myFunction(Test_tab.col_a, 'TEST') = 1
User-defined function costs do not influence the choice of access methods; they are only used for ordering predicates (described in the Extensible Optimizer reference).
If the association
ASSOCIATE STATISTICS WITH PACKAGES Demo_pack USING stat_Demo_pack;
is declared for a package Demo_pack, then the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_Demo_pack for the following predicate (for instance):
Demo_pack.myDemoPackFunction(Test_tab.col_a) = 1
where myDemoPackFunction is a function in Demo_pack.
If the association
ASSOCIATE STATISTICS WITH TYPES Example_typ USING stat_Example_typ;
is declared for a type Example_typ, then the optimizer calls the ODCIStatsFunctionCost function (if present) in the statistics type stat_Example_typ for the following predicate:
myExampleTypMethod(Test_tab.col_b) = 1
where myExampleTypMethod is a method in Example_typ.
Like default selectivity, default costs can be used for predicates with user-defined operators, stand-alone functions, package functions, or type methods. So, the following command
ASSOCIATE STATISTICS WITH INDEXES Test_indx DEFAULT COST (100, 5, 0)
declares that using the domain index Test_indx to implement the following predicate (to select one example)
userOp(Test_tab.col_a) = 1
always has a CPU cost of 100, I/O of 5, and network of 0 (the network cost is ignored in Oracle8i ) regardless of the parameters of userOp, the comparison operator "=", or the constant "1". The optimizer uses this default cost instead of calling an ODCIStatsIndexCost cost function.
You can declare an association using either a statistics type or a default cost, not both. Thus, the following statement is illegal:
ASSOCIATE STATISTICS WITH INDEXES Test_indx USING stat_Test_indx DEFAULT COST (100, 5, 0)
The following are some more examples of default cost declarations:
ASSOCIATE STATISTICS WITH FUNCTIONS myFunction DEFAULT COST (100, 5, 0) ASSOCIATE STATISTICS WITH PACKAGES Demo_pack DEFAULT COST (100, 5, 0) ASSOCIATE STATISTICS WITH TYPES Example_typ DEFAULT COST (100, 5, 0) ASSOCIATE STATISTICS WITH INDEXTYPES indtype DEFAULT COST (100, 5, 0)
In the absence of an ORDERED_PREDICATES hint (described), predicates (except those used for index keys) will be evaluated in the order specified by the following rules:
WHERE clause.
WHERE clause.
WHERE clause (e.g., predicates transitively generated by the optimizer) are evaluated next.
WHERE clause.
The dependency model reflects the actions that are taken when you issue any of the SQL commands described in the table.
A statistics type is defined as a regular object type. Since object types must have at least one attribute, a statistics type also must have at least one attribute. This will be a dummy attribute, however, since it will never be set or accessed.
In Oracle8i domain indexes are non-partitioned and serial. The optimizer computes the composite cost of a domain index access path assuming a serial execution.
Oracle's distributed implementation does not support adding functions to the remote capabilities list. All functions referencing remote tables are executed as filters. The placement of the filters occurs outside the optimizer. The cost model reflects this implementation and does not attempt to optimize placement of these predicates.
Since predicates are not shipped to the remote site, you cannot use domain indexes on remote tables. Therefore, the DESCRIBE protocol is unchanged, and remote domain indexes are not visible from the local site.
The cost of execution of the queries remains the same with the extensible optimizer if the same plan is chosen. If a different plan is chosen, the execution time should be better assuming that the user-defined cost, selectivity, and statistics collection functions are accurate. In light of this, you are strongly encouraged to provide statistics collection, selectivity, and cost functions for user-defined structures because the optimizer defaults can be inaccurate and lead to an expensive execution plan.
