Sales has an associated view SALES_VIEW
As this view was uses a function, anyone who searched by CAR_TYPE, it was full table scanning the table SALES (1M+ rows).
create or replace view sales_view
I've created a function based index on a table SALES
create index car_func_1 on sales (substr(CAR_TYPE(sal_order_no),1,20));
CAR_TYPE is a deterministic function that reads from the ORDER Table to get the CAR_REFNO then links to the CAR table
When I .....
select sal_salesperson_name,car_type from sales_view where car_type like 'BMW%';
It correctly range scans the function based index, and returns say 4 rows (all salespersons that have sold BMW).
However when I insert 2 new ORDERS and SALES for BMW and do the same select, I still get 4 rows (by range scan of the FB index)??? This continues until I rebuild the function based index manually:
alter index car_func_1 rebuild;
When I do the select, it then returns 6 rows (by range scan of the FB index). It seems like it is range scanning the STALE function based index, until I rebuild it. Why doesn't Oracle rebuild the index each time a sale is made?