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，略