Re: Function based index with <>
- From: Tony Adolph <tony.adolph.dba@xxxxxxxxx>
- To: spikey.mcmarbles@xxxxxxxxx
- Date: Thu, 20 Aug 2009 14:42:57 +1200
You need to use the *same expression* in your query, e.g
select id, flag
from t
where
case when flag <> 'Y' then flag end = 'N';
14:41:07 TONY@billdev1> explain plan for
14:41:12 2 select id, flag
14:41:12 3 from t
14:41:12 4 where case when flag <> 'Y' then flag end = 'N';
Explained.
14:41:13 TONY@billdev1> @xplan
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 242607798
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 600 | 17 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 100 | 600 | 17 (0)|
00:00:01 |
|* 2 | *INDEX RANGE SCAN | I | 100 *| | 1 (0)|
00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE WHEN "FLAG"<>'Y' THEN "FLAG" END ='N')
14 rows selected.
HTH
Tony
Other related posts:
