Today i saw this question on the OTN forum.
Hi All,
I need to execute a query like this :
SELECT * FROM ORDERS WHERE APPROVE_DATE IS NULL
I read anywhere that this will cause unnecessary FTS so that I should create function based index.
I have tried one below , but not sure that this is correct approach :
CREATE INDEX idx_1
ON ORDERS (NVL(APPROVE_DATE, '01-JAN-1900'));
SELECT * FROM ORDERS WHERE NVL(APPROVE_DATE, '01-JAN-1900') = '01-JAN-1900'
Is this a correct approach ?
Thank you,
xtanto
This is what i gave in reply. I prepared a simple test case to show that an index be USED as well as IGNORED.
A SQL_TRACE output will explain clearly what Justin has stated.
I have created a table T based on all_objects.
SQL> desc t
Name Null? Type
----------------------------------------- -------- -----------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
CASE I
SQL> select count(1) from t
2 /
COUNT(1)
----------
934320
SQL> select count(1) from t where created is null
2 /
COUNT(1)
----------
2376
The number of null values in CREATED column is proportionately very small.
Now i execute the query without function based index.
select *
from t
where created is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.09 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 160 0.04 0.10 0 12662 0 2376
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 162 0.04 0.19 0 12662 0 2376
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: ALL_ROWS
2376 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T' (TABLE)
And here is the query that uses the function based index
select *
from t
where nvl(created,to_date('01-01-1900','DD-MM-YYYY')) = to_date('01-01-1900','DD-MM-YYYY')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 160 0.01 0.01 0 698 0 2376
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 162 0.03 0.01 0 698 0 2376
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: ALL_ROWS
2376 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'T' (TABLE)
2376 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'T_FN_IDX' (INDEX)
Its very obvious from the above output that the Function Based Index as increased the performance.
CASE II
SQL> select count(1) from t
2 /
COUNT(1)
----------
934320
SQL> select count(1) from t where created is null
2 /
COUNT(1)
----------
202168
Now the null values in the CREATED column is proportionately large than the first test case.
Now lets see without using the function based index
select *
from t
where created is null
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13479 0.46 0.71 2 25832 0 202168
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13481 0.46 0.71 2 25832 0 202168
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: ALL_ROWS
202168 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T' (TABLE)
Now iam trying to use the function based index
select *
from t
where nvl(created,to_date('01-01-1900','DD-MM-YYYY')) = to_date('01-01-1900','DD-MM-YYYY')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13479 0.54 0.84 0 33826 0 202168
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 13481 0.54 0.84 0 33826 0 202168
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: ALL_ROWS
202168 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T' (TABLE)
Its obvious from the result that oracle has decided to go for a FULL TABLE SCAN even when an index was available.
So just having a function based index is not going to increase the query performance. There are lot of other factors to be considered as stated above.
The original link to the OTN Thread is
http://forums.oracle.com/forums/thread.jspa?threadID=825995&tstart=0
Thanks,
Karthick.
