Query CBO Cost - Hints

There is a debate I'm having on metalink about a case where OP had the following problem:

Oracle 9.2.0.5, CBO used with very low value of optimizer_index_cost_adj (1):

delete from table; shows a cost of 81 with a bitmap index being used.

delete /*+ index(table btree_idx) */ from table; shows a cost of 1 with a
b*tree index being used.

Now I understand that the optimizer_index_cost_adj value is too low .. and that a full table scan might be better.

However, I'm of the opinion that the CBO itself should have accounted for the b*tree index in its costs and since it appears to be of a lower cost, the b*tree index should have been used in the first instance (i.e. without hinting).

To which a subsequent poster refers me to the following post on Tom's site:

http://asktom.oracle.com/pls/ask/f?p=4950:8:5440244398791053756::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:313416745628

I agree to Tom's points there. However, I'm not sure if my deduction is flawed even after reading the thread.

• Does the CBO show lower numbers for the cost if I hint the query .. vs the same access plan non-hinted? Internally the CBO might be lowering the cost to respect the hint. However, should the OP have seen a lower cost after the index hint?

  If yes .. then can anyone create a test case which shows this (assuming
  the test case result is not as a result of a bug .. )

• Hints like FIRST_ROWS, cardinality etc. might lower the cost shown. Or changing optimizer_*, sort_* etc. parameters also can lower the cost. However, should the index hint lower the cost shown?

Since I've not seen Tom replying to metalink threads .. I thought he might
rely here (Thanks Tom if you do reply)...

Thanks,

Anurag Received on Fri Apr 08 2005 - 16:09:31 CDT