RE: Function based nvl index

  • From: <Christopher.Taylor2@xxxxxxxxxxxx>
  • To: <rajugaru.vij@xxxxxxxxx>, <Oracle-L@xxxxxxxxxxxxx>
  • Date: Tue, 23 Apr 2013 09:06:49 -0500

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


Other related posts: