Oracle ignoring function-based index - why?

Hi all,

The EXPLAIN PLAN for this:

update hse.colour set chs_uid = (select librarycolor_id from chs.librarycolor where upper(librarycolor.colorcode) = upper(colour.colcode));

is this:

Operation                      Object
------------------------------ ------------------------------
UPDATE STATEMENT ()
 UPDATE ()                     COLOUR
  TABLE ACCESS (FULL)          COLOUR
  TABLE ACCESS (FULL)          LIBRARYCOLOR

A full table scan on hse.colour is fine - I need to update every row - but, having created what I thought were the necessary supporting function-based indexes for this statement, I'm surprised to see that a full table scan will also be performed on chs.librarycolor. I created an index on UPPER (librarycolor.colorcode) and another on UPPER(colour.colcode) and I thought that would be enough to avoid a full table scan on hse.librarycolor.

I'm new to all of this, so go easy on me! The optimizer_mode is "choose" and I haven't - ever - generated stats for the tables. BTW, even reducing the statement down to this:

select librarycolor_id from chs.librarycolor where upper(librarycolor.colorcode) = 'AAA';

still causes a full table scan. Enterprise Mgr Console says the indexes are enabled and valid. What do I have to do to get Oracle to use my function-based indexes?

BTW, I'm running 9.2...

TIA
Nick Received on Fri Jul 22 2005 - 07:21:43 CDT