[20150715]一條sql語句的優化.txt

[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  diagnosis<>replace(diagnosis,',慢性病drugs','') ;
  COUNT(*)
----------
         0

--可以發現沒有1條是滿足diagnosis<>replace(diagnosis,',慢性病drugs',''),全部都是相等的。也許是我匯出的時間，因為這個作業
--是晚上3:40分執行的。但是至少說明滿足diagnosis<>replace(diagnosis,',慢性病drugs','')條件的很少。

--開發至少應該寫成這樣：
update presc_check t set t.diagnosis=replace(t.diagnosis,',慢性病drugs','') where t.diagnosis<>replace(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 rownum<=3;
INSTR(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)）;
--這樣就可以保留diagnosis<>replace(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不是很好。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1733688/，如需轉載，請註明出處，否則將追究法律責任。