Oracle SQL Optimization Example---using function index

In the AWR report, it was found that a SQL is inefficient:

Select Batch_status from T_batch_info

where batch_status= ' 0 '

and sys_id= ' STM06 ';

View execution Plan Discovery the query takes advantage of the index, which contains the Batch_status field, but with the following SQL query:

Select Batch_status,count (*) from T_batch_info
GROUP BY Batch_status

There are few values found for the Batch_status field:

Batch_status COUNT (*)

0 40350

1 4237357
2 1227
3 433515

Before the SQL query condition is batch_status= ' 0 ', it only has more than 40,000 data, and most of them are 1, there is 400多万条 data, so although the index, but the use of low efficiency,

To solve this problem, a function index is created, with the null value not being indexed,

Create INDEX Ix_t_batch_info on T_batch_info (Decode (Batch_status, ' 1 ', NULL, ' 2 ', NULL, ' 3 ', null,batch_status)) online;

And the query statement is changed to:

Select Batch_status from T_batch_info

where Decode (Batch_status, ' 1 ', NULL, ' 2 ', NULL, ' 3 ', null,batch_status) = ' 0 '

and sys_id= ' STM06 ';

Proven to be more efficient.

Oracle SQL Optimization Example---using function index