[20150715]一条sql语句的优化.txt
--生产系统发现一条语句。
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
--第1眼看到的感觉真的很想骂人，什么能没有where条件呢？
--我把这个表拷贝过来。这个表占用1G多1点，在测试环境执行看看：
-- copy from system/xxxx@ip:1521/tyt create presc_check using select * from dmd.presc_check;
SCOTT@test> set autot traceonly
SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
4164454 rows updated.
Execution Plan
----------------------------------------------------------
Plan hash value: 1940686096
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 3670K| 7008M| 34762 (1)| 00:00:01 |
| 1 | UPDATE | PRESC_CHECK | | | | |
| 2 | TABLE ACCESS FULL| PRESC_CHECK | 3670K| 7008M| 34762 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
1104 recursive calls
4301528 db block gets
257057 consistent gets
127892 physical reads
1278411184 redo size
849 bytes sent via SQL*Net to client
829 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4164454 rows processed
--看看redo size=1278411184/1024/1024/1024=1.1906131953001223388 1.2G.
--删除表重新拷贝看看。
SCOTT@test> select count(*) from presc_check where diagnosisreplace(diagnosis,',慢性病drugs','') ;
COUNT(*)
----------
0
--可以发现没有1条是满足diagnosisreplace(diagnosis,',慢性病drugs',''),全部都是相等的。也许是我导出的时间，因为这个作业
--是晚上3:40分执行的。但是至少说明满足diagnosisreplace(diagnosis,',慢性病drugs','')条件的很少。
--开发至少应该写成这样：
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where t.diagnosisreplace(t.diagnosis,',慢性病drugs','');
--但是这样依旧无法避开全表扫描。必须建立一个函数索引减少扫描的记录。利用instr函数就可以解决这个问题。
create index if_presc_check_diag on presc_check( instr(diagnosis,',慢性病drugs',1) );
SCOTT@test> select instr(diagnosis,',慢性病drugs',1) from presc_check where rownumINSTR(DIAGNOSIS,',慢性病DRUGS',1)
---------------------------------
0
0
0
--正常instr(diagnosis,',慢性病drugs',1)都是0，这样索引会包含大量0的数据，而这些是不需要的，仅仅需要保留大于0的信息，利用
--索引不保留null的特性。应该建立如下索引：
create index if_presc_check_diag on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)）;
--这样就可以保留diagnosisreplace(diagnosis,',慢性病drugs','')不等的记录。而且索引很小。
--最终修改语句改为:
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1;
--测试：
SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1;
0 rows updated.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 0r2b2dphxpapc, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1
Plan hash value: 1940686096
-----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 34981 (100)| |
| 1 | UPDATE | PRESC_CHECK | | | | |
|* 2 | TABLE ACCESS FULL| PRESC_CHECK | 33816 | 64M| 34981 (1)| 00:00:01 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER(DECODE(INSTR("DIAGNOSIS",',慢性病drugs',1),0,NULL,'1'))=1)
Note
-----
- dynamic sampling used for this statement (level=2)
--奇怪还是没用索引。哦忘记分析表了，分析看看。
exec sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 'presc_check',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false)
--问题依旧。奇怪了。
--仔细看过滤条件：
2 - filter(TO_NUMBER(DECODE(INSTR("DIAGNOSIS",',慢性病drugs',1),0,NULL,'1'))=1)
--什么会这样。
SCOTT@test> create index if_presc_check_diag on presc_check( to_number(decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)));
Index created.
SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1;
0 rows updated.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0r2b2dphxpapc, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)=1
Plan hash value: 4008913632
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | PRESC_CHECK | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PRESC_CHECK | 41645 | 1260K| 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IF_PRESC_CHECK_DIAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / T@UPD$1
3 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."SYS_NC00070$"=1)
--实际上也可以建立这样的索引：
SCOTT@test> drop index if_presc_check_diag ;
Index dropped.
SCOTT@test> create index if_presc_check_diag on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1'));
Index created.
SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1')='1';
0 rows updated.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 22ujr6zwy4asn, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1')='1'
Plan hash value: 4008913632
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | PRESC_CHECK | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PRESC_CHECK | 41645 | 813K| 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IF_PRESC_CHECK_DIAG | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / T@UPD$1
3 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."SYS_NC00070$"='1')
--看来以后要注意这种情况的隐式转换。实际上问题出在decode(instr(diagnosis,',慢性病drugs',1),0,NULL,1)里面的NULL，oracle
--缺省认为是字符串类型null，而不是数字类型的null，改成如下：
create index if_presc_check_diagx on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1));
SCOTT@test> update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1)=1;
0 rows updated.
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f5b0rzmyd2uzh, child number 0
-------------------------------------
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','')
where decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1)=1
Plan hash value: 2418858926
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 1 (100)| |
| 1 | UPDATE | PRESC_CHECK | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| PRESC_CHECK | 41645 | 1260K| 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IF_PRESC_CHECK_DIAGX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / T@UPD$1
3 - UPD$1 / T@UPD$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."SYS_NC00071$"=1)
--当然应该选择这个索引更佳：
create index if_presc_check_diag on presc_check( decode(instr(diagnosis,',慢性病drugs',1),0,NULL,'1'));
--应该字符类型'1'仅仅占用1个字节，而数字1占用两个字节，并且从写法讲这样写decode(instr(diagnosis,',慢性病drugs',1),0,cast(NULL as number),1)=1不是很好。
