#优化方法1：
#增加函数索引，将object_name!='SJ'和object_name IS NULL的保存在索引中
create index scott.idx_tb_sj01_04 on scott.tb_sj01(decode(object_name,null,1,'SJ',null,2));
drop index scott.idx_tb_sj01_04;
#原语句改写为：
select * from scott.tb_sj01 t where decode(object_name,null,1,'SJ',null,2)=2;
#优化后，COST=4，consistent gets=4
SQL> select * from scott.tb_sj01 t where decode(object_name,null,1,'SJ',null,2)=2;
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3453712045
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 869 | 71258 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 71258 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_04 | 20 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(DECODE("OBJECT_NAME",NULL,1,'SJ',NULL,2)=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1938 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
#优化方法2：
#增加函数索引，只让object_name!='SJ'的保存在索引中
create index scott.idx_tb_sj01_05 on scott.tb_sj01(case when object_name!='SJ' then 1 else null end);
#原语句改写为：
select * from scott.tb_sj01 t where (case when object_name!='SJ' then 1 else null end)=1;
#优化后，COST=3，consistent gets=4
SQL> select * from scott.tb_sj01 t where (case when object_name!='SJ' then 1 else null end)=1;
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 376302892
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 869 | 71258 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 71258 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_05 | 11 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE WHEN "OBJECT_NAME"<>'SJ' THEN 1 ELSE NULL END =1)