CBO use of Indexes
I have a table, MTL_ITEM_CATEGORIES with an index containing the following columns:
ORGANIZATION_ID
INVENTORY_ITEM_ID
CATEGORY_SET_ID
CATEGORY_ID
I would think that the following query:
select count(*)
from
oe_order_headers_all oh,
oe_order_lines_all ol,
mtl_system_items_b msi,
mtl_item_categories ic,
mtl_categories_b c
where oh.header_id = ol.header_id
and msi.inventory_item_id = ol.inventory_item_id
and msi.organization_id = ol.ship_from_org_id
and oh.booked_date > '01-SEP-2009'
and ic.inventory_item_id = msi.inventory_item_id
and ic.organization_id = msi.organization_id
and ic.category_set_id = 1
and ic.category_id = c.category_id
would use the index, but it does not. In general, I have found that when table access is based on a combination of column joins and a constant, CBO doesn't seem to like to use them together for index lookup. In my example 2 elements of the join are based on columns in the MSI table, while the third is a constant. I tried replacing the constant with a bind variable and it still ignores the index. Is this a feature of the CBO?