Guilty by association

[Mladen Gogala]

In Oracle 10g there was a way to associate a custom costing function with
an object like type, table, index, function or package using something
called "ODCI". ODCI is a part of data cartridge and there are several SYS-
owned functions which have to be used in order to create a custom costing
type. In essence, one would create rather complex type and associate it
with an object using the "associate statistics" command. Adrian
Billington and my former colleague Joc Senegacnik have been writing
about it extensively. This was to be used when Oracle couldn't gather
statistics by itself, for a pair of columns or a function of a column. In
Oracle 11G there is something called "extended db statistics". Christian
Antognini and Jonathan Lewis have covered it extensively, among others. I
guess that this means that creating monstrosities like here is no longer
necessary:

http://www.oracle-developer.net/display.php?id=427

However, the "ASSOCIATE STATISTICS" can still be used for the procedural
objects. It allows associating default cost per execution with the
function and defining default selectivity for expressions like

funct(column)=CONST

Strictly speaking, that expression can be resolved by creating an
additional computed column, with "GENERATED ALWAYS AS (.....) VIRTUAL
clause (Oracle 11G) or simply populating it by trigger (10G style) but if
that was not done, than associating CPU, IO and network cost with the
function as well as the default selectivity can still be helpful. Those
simple cases for associating statistics have long been my favorite method
of tuning the where conditions like above and that still applies in
version 11G. I believe that this type of "price fixing" can still be
useful in the version 11G.
Did I miss any of the new features here? Did anybody else here use that
type of "price fixing"?



--
http://mgogala.byethost5.com