RE: Function based nvl index
Date: Tue, 23 Apr 2013 09:06:49 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8879D82AF7C_at_NADCWPMSGCMS10.hca.corpad.net>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rajugaru.vij_at_gmail.com
Sent: Tuesday, April 23, 2013 6:24 AM
To: Oracle-L_at_freelists.org
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--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Apr 23 2013 - 16:06:49 CEST