Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Question Unanswered: function based indexes - Any ideas?

    Hi,

    Anyone else ever have this problem?

    SALES
    sal_salesperson_name
    sal_order_no

    ORDER
    order_no
    car_refno

    CAR
    car_refno
    car_type

    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
    select sal_salesperson_name,
    substr(CAR_TYPE(sal_order_no),1,20) car_type
    from sales

    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?

    Any Ideas?
    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    What's the value of query_rewrite_integrity ?
    I guess it's STALE_TOLERATED ... if so, change it to TRUSTED.

    hth
    al

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •