Provide a dbms_xplan output for the query using the following. Create your plan table (if it doesn't exist) before running this. This will prompt you for the owner of the Plan Table which you will need to provide. You will need to paste your SQL statement into the section where it says paste your sql statement ;) The statement identifier is ANY statement identifier of your choice - it can be "Blah" or "Foobar" or whatever. Only used to pull it back out of the plan table (though this script truncates the plan_table before each execution - you can comment that out if desired) Execute in SQLPlus. ---begin script--- set lines 2000 set pages 5000 set head on set verify on set feed on set serveroutput off var statement_id varchar2(500); prompt Provide ANY Statement Identifier of your choice begin select '&any_statement_identifier' into :statement_id from dual; end; / -- -- comment the below out if you -- do not want to truncate your plan_table -- truncate table &&owner..plan_table / explain plan set statement_id=':statement_id' into &&owner..PLAN_TABLE For -- -- Paste -- Your -- Sql Statement Here -- / select * from table(dbms_xplan.display('&&owner..PLAN_TABLE', ':statement_id', 'ADVANCED, IOSTATS, ROWS, COST, PARTITION, PARALLEL, PREDICATE, PROJECTION, ALIAS, REMOTE, NOTE')) / --- end script --- -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of rajugaru.vij@xxxxxxxxx Sent: Tuesday, April 23, 2013 6:24 AM To: Oracle-L@xxxxxxxxxxxxx Subject: Function based nvl index Hi, I have a situation where my function based index is not getting used. Its an NVL based functional index NVL(gender,0) =0 Is my where clause. And I have a index on NVL(gender,0) It was getting used previously, but not now. I use the some in update and select queries both the cases, index is not getting used. Using 10g R2 Any thoughts? Sent on my BlackBerry(r) from Vodafone-- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l