Hi,
I wanted null values to be indexed so I created the below index:
CREATE INDEX XXGL_JE_LINES_N3 ON GL_JE_LINES(NVL(ATTRIBUTE17,'-1')) TABLESPACE APPS_TS_TX_IDX COMPUTE STATISTICS;
I ran gather table stats for GL_JE_LINES however the explain plan on the the below query showed that the index is not being used:
EXPLAIN PLAN FOR
SELECT *
FROM gl_je_lines A
where nvl(ATTRIBUTE17,'-1') = '-1'
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33M| 7458M| 306K (4)|
|* 1 | TABLE ACCESS FULL| GL_JE_LINES | 33M| 7458M| 306K (4)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL("ATTRIBUTE17",'-1')='-1')
But the index IS being used on the below query:
EXPLAIN PLAN FOR
SELECT *
FROM gl_je_lines A
where nvl(ATTRIBUTE17,'-1') = '-2'
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 23432 | 95 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| GL_JE_LINES | 101 | 23432 | 95 (0)|
|* 2 | INDEX RANGE SCAN | XXGL_JE_LINES_N3 | 101 | | 15 (0)|
-------------------------------------------------------------------------------------
Why isnt the first query using the index?? Database version 10.2.0.5.0
Appreciate your help.