Index scan on Null able column

Issue: Tune the below SQL script.

Comments: The statement is having the two conditions in the WHERE clause. One it is checking the number and another one is checking the NULL value.
Scripts:
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836' or NAME IS NULL
Explain plan: It is going for the TABLE ACCESS FULL.
PlanSELECT STATEMENT CHOOSE Cost: 156 Bytes: 81 Cardinality: 1
1 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1
Comments: We know that, we have to create index on the “NAME” field.
create index NAME_IDX on CUSTOMER_ORDER_TUNE(NAME)

Comments: Now we check the explain plan for the script.
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836' or NAME IS NULL
Explain plan: Still it is going for the TABLE ACCESS FULL because the script is checking the NULL.

Plan
SELECT STATEMENT CHOOSE Cost: 156 Bytes: 81 Cardinality: 1
1 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1

Comments: Let we divide the SQL statement as a two part and we check the explain plan.

select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836'
union all
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME IS NULL
Explain plan: Good! Now partially it is going for the index scan. The second part of the query is going for the FULL TABLE access. The reason is, because the NAME is null able, the index on only on name and entirely null keys are NOT entered into b*tree indexes.

Plan
SELECT STATEMENT CHOOSE Cost: 158 Bytes: 162 Cardinality: 2
4 UNION-ALL
2 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 2 Bytes: 81 Cardinality: 11 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 1 Cardinality: 1
3 TABLE ACCESS FULL SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 156 Bytes: 81 Cardinality: 1


Comments: Still it is not going for the index scan. So we drop the index and create it on different way.
drop index NAME_IDX
create index NAME_IDX on CUSTOMER_ORDER_TUNE(NAME,0)
Comments: Now we check the explain plan for the script.

select * FROM CUSTOMER_ORDER_TUNE WHERE NAME = '0000003836'
union all
select * FROM CUSTOMER_ORDER_TUNE WHERE NAME IS NULL
Explain plan: Wow! Now it is going for the index scan and cost also very less.

Plan
SELECT STATEMENT CHOOSE Cost: 4 Bytes: 162 Cardinality: 2
5 UNION-ALL
2 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 3 Bytes: 81 Cardinality: 1
1 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 2 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID SLSFORCE.CUSTOMER_ORDER_TUNE Cost: 1 Bytes: 81 Cardinality: 1
3 INDEX RANGE SCAN NON-UNIQUE SLSFORCE.NAME_IDX Cost: 1 Cardinality: 1