ORACLE SQL過濾條件是IS NULL or !=的優化
通常情況下is null或者!=這些條件如果不是具有很強的過濾性，可以先關注其它的過濾條件。但有些SQL這兩種條件具有很強的過濾性，就可以考慮用以下方法。下面先討論is null的優化，再討論!=的優化，最後討論is null or !=一起使用的優化。
以下測試：
oracle version:11.2.0.4
#新建測試表
create table scott.tb_sj01 as select * from dba_objects;
#處理測試表中的資料
update scott.tb_sj01 set object_name=null where object_id<=10;
update scott.tb_sj01 set object_name='SJ' where object_id>20;
commit;
#收集表的統計資訊
begin
dbms_stats.gather_table_stats('scott','TB_SJ01');
end;
#檢視測試表中object_name欄位的資料分佈
#可以看出object_name欄位IS NULL的有9筆記錄;object_name !='SJ'有11筆記錄。
select nvl(object_name,'NULL') object_name,count(1) cnt
from scott.tb_sj01
group by nvl(object_name,'NULL')
order by 2 ;
/*
OBJECT_NAME CNT
ICOL$ 1
TS$ 1
OBJ$ 1
FILE$ 1
STDBY_LINK_CT6601SB 1
UNDO$ 1
FET$ 1
I_USER# 1
UET$ 1
SEG$ 1
IND$ 1
NULL 9
SJ 86880
*/
1.語句：select * from scott.tb_sj01 where object_name is null的優化
SQL> set autot trace
#優化前,COST=346,consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name is null;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 283620643
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 684 | 346 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 9 | 684 | 346 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size
1850 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)
9 rows processed
#優化方法1:
#增加索引，讓object_name IS NULL的也儲存在索引中
SQL> create index scott.idx_tb_sj01_01 on scott.tb_sj01(object_name,1);
#優化後，COST=3,consistent gets=5
SQL> select * from scott.tb_sj01 where object_name is null;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1042936765
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 684 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 9 | 684 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_01 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1850 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)
9 rows processed
#優化方法2:
#增加函式索引，只讓object_name IS NULL的儲存在索引中
create index scott.idx_tb_sj01_02 on scott.tb_sj01(decode(object_name,null,1));
#原語句改寫為：
select * from scott.tb_sj01 where decode(object_name,null,1)=1;
#優化後，COST=2，consistent gets=4
SQL> select * from scott.tb_sj01 where decode(object_name,null,1)=1;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 612345449
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 869 | 66044 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 66044 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_02 | 9 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(DECODE("OBJECT_NAME",NULL,1)=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1850 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)
9 rows processed
2.語句:select * from scott.tb_sj01 where object_name!='SJ'的優化
#優化前,COST=346，consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name!='SJ';
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 283620643
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 79650 | 5911K| 346 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 79650 | 5911K| 346 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"<>'SJ')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size
1979 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
#優化方法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)
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
3.語句：select * from scott.tb_sj01 where object_name is null or object_name !='SJ'的優化
#優化前,COST=346，consistent gets=1247
SQL> select * from scott.tb_sj01 where object_name is null or object_name !='SJ';
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 283620643
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 3280 | 346 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 40 | 3280 | 346 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"<>'SJ' OR "OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1247 consistent gets
0 physical reads
0 redo size
2403 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
#優化方法1：
#增加索引，將object_name!='SJ'和object_name IS NULL的儲存在索引中
create index scott.idx_tb_sj01_06 on scott.tb_sj01(decode(object_name,null,1,'SJ',null,1));
#語句修改為：
select * from scott.tb_sj01 t where decode(object_name,null,1,'SJ',null,1)=1;
#優化後,COST=3，consistent gets=6
SQL> select * from scott.tb_sj01 where decode(object_name,null,1,'SJ',null,1)=1;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 356892721
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| 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_06 | 20 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(DECODE("OBJECT_NAME",NULL,1,'SJ',NULL,1)=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
2362 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL> set autot off
#優化方法2，增加case when的函式索引，改寫語句，同語句2，略
以下測試：
oracle version:11.2.0.4
#新建測試表
create table scott.tb_sj01 as select * from dba_objects;
#處理測試表中的資料
update scott.tb_sj01 set object_name=null where object_id<=10;
update scott.tb_sj01 set object_name='SJ' where object_id>20;
commit;
#收集表的統計資訊
begin
dbms_stats.gather_table_stats('scott','TB_SJ01');
end;
#檢視測試表中object_name欄位的資料分佈
#可以看出object_name欄位IS NULL的有9筆記錄;object_name !='SJ'有11筆記錄。
select nvl(object_name,'NULL') object_name,count(1) cnt
from scott.tb_sj01
group by nvl(object_name,'NULL')
order by 2 ;
/*
OBJECT_NAME CNT
ICOL$ 1
TS$ 1
OBJ$ 1
FILE$ 1
STDBY_LINK_CT6601SB 1
UNDO$ 1
FET$ 1
I_USER# 1
UET$ 1
SEG$ 1
IND$ 1
NULL 9
SJ 86880
*/
1.語句：select * from scott.tb_sj01 where object_name is null的優化
SQL> set autot trace
#優化前,COST=346,consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name is null;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 283620643
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 684 | 346 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 9 | 684 | 346 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size
1850 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)
9 rows processed
#優化方法1:
#增加索引，讓object_name IS NULL的也儲存在索引中
SQL> create index scott.idx_tb_sj01_01 on scott.tb_sj01(object_name,1);
#優化後，COST=3,consistent gets=5
SQL> select * from scott.tb_sj01 where object_name is null;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1042936765
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 684 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 9 | 684 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_01 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1850 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)
9 rows processed
#優化方法2:
#增加函式索引，只讓object_name IS NULL的儲存在索引中
create index scott.idx_tb_sj01_02 on scott.tb_sj01(decode(object_name,null,1));
#原語句改寫為：
select * from scott.tb_sj01 where decode(object_name,null,1)=1;
#優化後，COST=2，consistent gets=4
SQL> select * from scott.tb_sj01 where decode(object_name,null,1)=1;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 612345449
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 869 | 66044 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 66044 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_02 | 9 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(DECODE("OBJECT_NAME",NULL,1)=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1850 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)
9 rows processed
2.語句:select * from scott.tb_sj01 where object_name!='SJ'的優化
#優化前,COST=346，consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name!='SJ';
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 283620643
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 79650 | 5911K| 346 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 79650 | 5911K| 346 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"<>'SJ')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size
1979 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
#優化方法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)
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
3.語句：select * from scott.tb_sj01 where object_name is null or object_name !='SJ'的優化
#優化前,COST=346，consistent gets=1247
SQL> select * from scott.tb_sj01 where object_name is null or object_name !='SJ';
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 283620643
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 3280 | 346 (1)| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 40 | 3280 | 346 (1)| 00:00:05 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"<>'SJ' OR "OBJECT_NAME" IS NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1247 consistent gets
0 physical reads
0 redo size
2403 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
#優化方法1：
#增加索引，將object_name!='SJ'和object_name IS NULL的儲存在索引中
create index scott.idx_tb_sj01_06 on scott.tb_sj01(decode(object_name,null,1,'SJ',null,1));
#語句修改為：
select * from scott.tb_sj01 t where decode(object_name,null,1,'SJ',null,1)=1;
#優化後,COST=3，consistent gets=6
SQL> select * from scott.tb_sj01 where decode(object_name,null,1,'SJ',null,1)=1;
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 356892721
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| 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_06 | 20 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(DECODE("OBJECT_NAME",NULL,1,'SJ',NULL,1)=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
2362 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL> set autot off
#優化方法2，增加case when的函式索引，改寫語句，同語句2，略
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2124429/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 通過新增條件優化SQL優化SQL
- 條件過濾檢索
- 一條sql的優化過程SQL優化
- 連線條件是兩個欄位“or”關係的SQL優化SQL優化
- Oracle 是分割槽表，但條件不帶分割槽條件的SQLOracleSQL
- 謂詞條件是is null走索引嗎？Null索引
- java8 多條件的filter過濾JavaFilter
- 一條SQL語句的優化過程SQL優化
- Oracle SQL效能優化的40條軍規OracleSQL優化
- MySQL全面瓦解7：查詢的過濾條件MySql
- 最佳化數倉業務檢視：過濾條件傳遞
- Java 條件表示式的優化Java優化
- SQL優化34條SQL優化
- AutoMapper如何全域性配置map條件過濾null值空值對所有對映起效APPNull
- CHECK約束中的NULL條件Null
- 《MySQL 入門教程》第 08 篇 過濾條件MySql
- Stream中filter過濾條件問題記錄Filter
- MongoDB之資料查詢（where條件過濾）MongoDB
- JN專案-查詢條件過濾特殊字元字元
- 自定義 Azure Table storage 查詢過濾條件
- 資料泵過濾匯出資料的where條件
- 【開發篇sql】 條件和表示式（三） Null詳解SQLNull
- MySQL 針對 like 條件的優化MySql優化
- SQL優化--用各種hints優化一條SQLSQL優化
- 一條sql語句的優化SQL優化
- 34. 過濾條件、多表查詢、子查詢
- 【SQL】SQL中if條件的使用SQL
- oracle sql優化OracleSQL優化
- oracle sql 優化OracleSQL優化
- 【TcaplusDB知識庫】條件過濾說明與更新
- PL/SQL 條件SQL
- 優化 JS 條件語句的 5 個技巧優化JS
- Oracle 在連線條件裡處理和比較 NULL 值OracleNull
- SQL中on條件與where條件的區別[轉]SQL
- mysql多條件過濾查詢之mysq高階查詢MySql
- 關於 hibernate 邏輯刪除 預設查詢過濾條件問題（java set 條件）Java
- Oracle SQL效能優化OracleSQL優化
- oracle sql 排序優化OracleSQL排序優化