Re: Yet another "why is my index not used" question

>> Jonathan,  Based on his WHERE Clause - no amount of fiddling is going to 
>> fix his problem until he gives the optimizer something other than "is not 
>> null" to work with- it is still going to do a FTS.

you are correct - my apologies - that's what I get for trying to solve problems so late....

Dimitre:

I am curious about a CBO "hint" I have used in the past - again just wondering if the effects are still there...

What happens if you modify the query like this:

set autot traceo exp stat
SELECT * FROM t WHERE
vmenmag=18 AND vmemois=200810 and
vmenmag=18 AND vmemois=200810;                 

In most apps, I would not use this, but, where this has been used before, the query has executed anywhere from 50-500% faster. What happens is that CBO determines that it **should** use any index that has these columns that are unique or the leading edge because of the weighting in the "where-clause".

If you really want to get your "geek" fix, try reading the World Intellectual Property Organization (WIPO) patent request for at least one of the patents associated with the CBO:  >>http://www.wipo.int/pctdb/en/wo.jsp?wo=1993014464&IA=US1993000775&DISPLAY=STATUS

The inventor Gennady ANTOSHENKOV, [sadly] was one of the computer world's most talented developers/inventors in our time. A search of his name should find additional "light" reading on the subject.

Here is the abstract:
Abstract:
A random sample is obtained from an inverted tree data structure by maintaining a cardinality estimate for each intermediate node in the tree, and selecting a leaf node at random by descending from the root node and performing an acceptance/rejection selection at each intermediate node weighted proportional to the cardinality estimates of the children and weighted inversely proportional to the cardinality estimate of the intermediate node. Even though the selection of an intermediate node is based upon only an estimate of the true cardinality of the intermediate node, the error in the estimate does not cause bias in the overall sampling method because any bias in the selection of the intermediate node is canceled by an opposite bias in the selection of the child of the intermediate node. The cardinality estimates are maintained when a leaf is inserted or deleted from the data structure by checking whether the insertion or deletion causes the cardinality estimate of a parent of the leaf node to differ from the actual cardinality by a predetermined error bound, and if so, the cardinality estimate of the parent is updated, and the checking and updating process is escalated up the tree. The error bound is adjustable for balancing rejection rate during sampling against I/O overhead of cardinality maintenance during database updates. Received on Wed Jan 28 2009 - 22:13:38 CST