[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不是很好。