SQL 書寫規範

說明

對於目前HIS 系統的SQL 書寫，存在不少不合理的地方，主要表現如下

1. 謂詞條件中過多的使用函式

2.SQL 中存在部分巢狀子查詢

3.SQL 查詢中存在過多的使用複雜的巢狀檢視

4. 關聯表格主外來鍵型別不一致

巢狀檢視查詢

資料庫中存在一條資源消耗嚴重的SQL ：

select xz_lx ,

yp_mc ,

gg ,

max_sl ,

max_jl ,

max_ts ,

max_sl_mzk ,

max_jl_mzk ,

max_ts_mzk ,

ys_bm_id ,

a.kf_id ,

a.yp_bh

from drug_limit_info a, dictmanage.v_dict_drug b

where a.yp_bh = b.yp_bh

order by xz_lx

在該查詢中 dictmanage.v_dict_drug b 對應為一張檢視，檢視查詢導致該SQL較為封閉且查詢複雜，導致該SQL執行計劃較為複雜，因為在查詢中巢狀了複雜檢視，導致CBO並不能合理的生成執行計劃。容易出現執行計劃紊亂現象。類似上述的SQL還有很對。我們建議對以上的查詢進行重寫，去除檢視，透過union等聯合查詢進行SQL重寫。類似這樣的SQL資料庫中存有很多，導致CBO不能很好的最佳化執行計劃，只能按部就班的執行。

巢狀子查詢

select distinct ( a.jz_kh ),

d.pat_name ,

case

when d.xb_id = '1' then

'?'

when d.xb_id = '2' then

'?'

else

'??'

end as xb ,

case

when floor ( months_between ( sysdate , d.CS_RQ ) / 12 ) > 3 then

to_char ( floor ( months_between ( sysdate , d.CS_RQ ) / 12 )) || '??'

else

his.fun_getage ( d.CS_RQ )

end as nl ,

c.zflx_mc ,

max ( a.yz_qrsj ) yzqrsj ,

max ( a.yzjsrq ) yzjsrq

from his.outpat_order a , dictmanage.dict_pay_type c , his.pat_info d

where a.pat_id = d.pat_id

and a.pay_type = c.zflx_id (+)

and a.yfstatus = '1'

and a.tfbz in ( '0' , '6' )

and a.cflx_id = '3'

and a.YZ_STATUS = '4'

and a.winid in (select f.win_id

from dictmanage.dict_machine_cfg f

where machine = :in_machine)

group by a.jz_kh , d.pat_name , d.xb_id , d.CS_RQ , c.zflx_mc

order by yzjsrq asc

上述SQL ，由於在謂詞條件中巢狀了子查詢，導致CBO 不能很好的選擇執行計劃，只能順序執行，且in 這種操作效率更低，建議對該SQL 進行改寫，透過關聯表，讓CBO 能夠更好的選擇執行計劃。如上的SQL 在資料庫中還是存在很多，一定程度上導致了多個執行計劃的產生， 建議對資料庫中存在子查詢的 SQL 儘量進行改寫 。

改寫後如下：

select distinct ( a.jz_kh ),

d.pat_name ,

case

when d.xb_id = '1' then

'?'

when d.xb_id = '2' then

'?'

else

'??'

end as xb ,

case

when floor ( months_between ( sysdate , d.CS_RQ ) / 12 ) > 3 then

to_char ( floor ( months_between ( sysdate , d.CS_RQ ) / 12 )) || '??'

else

his.fun_getage ( d.CS_RQ )

end as nl ,

c.zflx_mc ,

max ( a.yz_qrsj ) yzqrsj ,

max ( a.yzjsrq ) yzjsrq

from his.outpat_order a , dictmanage.dict_pay_type c , his.pat_info d dictmanage.dict_machine_cfg f

where a.pat_id = d.pat_id

and a.pay_type = c.zflx_id (+)

and a.yfstatus = '1'

and a.tfbz in ( '0' , '6' )

and a.cflx_id = '3'

and a.YZ_STATUS = '4'

and a.winid = f.win_id

and f.machine = :in_machine

group by a.jz_kh , d.pat_name , d.xb_id , d.CS_RQ , c.zflx_mc

order by yzjsrq asc

謂詞條件過多使用函式

所謂的謂詞條件過多的使用函式，並不是說謂詞條件中不允許使用函式，而是說在謂詞條件中，我們儘量的將條件放到謂詞條件的後面，當然，能避免就儘量避免去用函式。比如

select distinct a.jzkh ,

b.pat_name ,

c.yp_mc ,

a.cf_lsh_id ,

a.yzzx_lsh_id ,

c.psjgsj ,

ceil (( a.ps_jssj - a.ps_kssj ) * 24 * 60 ) psygsj ,

d.yzjl_id ,

a.fin_mxid ,

case nvl ( f.brlx , '0' )

when '1' then

'??' || f.room || '(' || f.cwh || ')'

when '3' then

'??' || f.room || '(' || f.cwh || ')'

else

'???'

end brlx_mc

from his.outpat_order_act_detail a ,

his.pat_info b ,

dictmanage.drug_info c ,

his.outpat_order d ,

his.pat_observe_info f

where a.pat_id = b.pat_id

and a.sfxm_id = c.yp_id

and a.mzyzjl_id = d.yzjl_id

and a.jzlsh_id = f.jz_lsh_id (+)

and a.psjg_id <> '0'

and a.ps_jssj is not null

and a. fs_rq is null

and ceil((sysdate - a.ps_kssj) * 24 * 60) > psjgsj

and a.zxks_id = : ksid

從上面的SQL 看 a.ps_kssj 欄位是日期函式， ceil((sysdate - a.ps_kssj) * 24 * 60)這部分計算的是一個分鐘的整數，而 psjgsj是一個整數字段，在這裡ceil（）函式完全是沒有必要的，就算有需要用到函式，我們也不能把函式放在謂詞部分，而是要放在>號後面來進行計算，這裡，我們完全可以在 a.ps_kssj欄位上新增一個索引，並把

and ceil((sysdate - a.ps_kssj) * 24 * 60) > psjgsj

改寫成

a.ps_kssj < sysdate - c.psjgsj/60/24

經過驗證，以上改寫是成立的，如下驗證：

SQL> select count(*) from his.outpat_order_act_detail a,dictmanage.drug_info c where a.ps_kssj < sysdate - c.psjgsj/60/24;

COUNT(*)

----------

28081264

SQL> select count(*) from his.outpat_order_act_detail a,dictmanage.drug_info c where ceil((sysdate - a.ps_kssj) * 24 * 60) > c.psjgsj;

COUNT(*)

----------

28081264

在SQL的 寫法中，我們需要儘量的避免在謂詞中出現函式等，真的需要，我們也應該儘量放在謂詞的條件中，類似的SQL資料庫中還有不少。

關聯表格主外來鍵型別不一致

關聯表格主外來鍵不一致也會導致索引無法正常使用，很多時候，開發人員往往喜歡將明明是number 型別的欄位做成varchar2 型別的，從而導致表之間關聯出現問題。如：

update his.outpat_order

set psjg_id =

(select case

when psjg_id = ps_fsjg then

psjg_id

else

'4'

end psjg

from his.outpat_order_act_detail

where yzzx_lsh_id = 476253

and ly = '1'),

zhgxsj = sysdate,

zhgxr = '2899'

where YZJL_ID = (select mzyzjl_id

from his.outpat_order_act_detail

where yzzx_lsh_id = 476253)

outpat_order.YZJL_ID varchar 型別outpat_order_act_detail.mzyzjl_id 為number 型別

我們可以考慮改造該欄位，使得型別匹配，當然，我們也可以使用to_char 或者to_number 函式來隱式轉換

update his.outpat_order

set psjg_id =

(select case

when psjg_id = ps_fsjg then

psjg_id

else

'4'

end psjg

from his.outpat_order_act_detail

where yzzx_lsh_id = 476253

and ly = '1'),

zhgxsj = sysdate,

zhgxr = '2899'

where YZJL_ID = (select to_char(mzyzjl_id)

from his.outpat_order_act_detail

where yzzx_lsh_id = 476253)

當然，上面這條SQL 同樣的存在子查詢SQL 的問題，我們同樣建議改造成表關聯查詢。

條件篩選性較差

儘可能的調整業務邏輯避免過多的使用<>,NOT NULL 之類的條件，導致查詢只能進行大規模的全表掃，相應cpu 使用率，磁碟的繁忙程度都加劇了。建議新增篩選性更強的條件。

避免select for update 操作

在生產環境規範開發人員的語句規範性，儘量少或者避免for update 的使用，它會導致大量TX 鎖的產生，影響整體效能。

業務高峰期的DDL 操作

在資料庫執行期間，特別是業務高峰期的時候，建議不要進行一些新增索引，修改表結構的DDL操作進行。同樣的，在業務高峰期的時候，我們也儘量不要去進行一些系統的操作，比如新增表空間，資料檔案等等。

資料庫規範化管理

目前來看，系統的索引和表都在同一個表空間中，並沒有很好的做到索引和表分離，索引和表分離。在業務量小的情況下，我們將索引和表放在同一個表空間中並沒有太大的關係，但是，隨著業務資料的增長，我們還是建議將表和索引放在不同的表空間中：

1. 提高效能：分離後，索引和表對應的表空間儘量放在不同的LUN 或者磁碟上，把不同型別的IO 分離，一定程度上提高IO 效能

2. 便於管理：索引和表存放在不同的表空間下，當對應的索引表空間損壞後，一定程度上我們只需要重建索引即可，不會造成資料丟失。

31 HIS INDEX PARTITION TP_HIS42

26 HIS TABLE DICTSPACES

31 HIS INDEX PARTITION TP_HIS45

31 HIS INDEX PARTITION TP_HIS52

16 HIS TABLE PARTITION TP_HIS06

16 HIS TABLE PARTITION TP_HIS07

16 HIS TABLE PARTITION TP_HIS12

16 HIS TABLE PARTITION TP_HIS16

16 HIS TABLE PARTITION TP_HIS23

16 HIS TABLE PARTITION TP_HIS26

16 HIS TABLE PARTITION TP_HIS28

16 HIS TABLE PARTITION TP_HIS41

16 HIS TABLE PARTITION TP_HIS61

31 HIS INDEX PARTITION TP_HIS02

31 HIS INDEX PARTITION TP_HIS16

31 HIS INDEX PARTITION TP_HIS32

31 HIS INDEX PARTITION TP_HIS35

4 HIS LOBINDEX XMLDATA

31 HIS INDEX PARTITION TP_HIS29

31 HIS INDEX PARTITION TP_HIS44

16 HIS TABLE PARTITION TP_HIS02

16 HIS TABLE PARTITION TP_HIS05

16 HIS TABLE PARTITION TP_HIS14

16 HIS TABLE PARTITION TP_HIS36

16 HIS TABLE PARTITION TP_HIS37

16 HIS TABLE PARTITION TP_HIS39

16 HIS TABLE PARTITION TP_HIS40

16 HIS TABLE PARTITION TP_HIS42

16 HIS TABLE PARTITION TP_HIS49

16 HIS TABLE PARTITION TP_HIS53

16 HIS TABLE PARTITION TP_HIS59

31 HIS INDEX PARTITION TP_HIS10

31 HIS INDEX PARTITION TP_HIS12

31 HIS INDEX PARTITION TP_HIS14

31 HIS INDEX PARTITION TP_HIS58

225 HIS TABLE XMLDATA

26 HIS INDEX DICTSPACES

31 HIS INDEX PARTITION TP_HIS19

31 HIS INDEX PARTITION TP_HIS20

31 HIS INDEX PARTITION TP_HIS28

16 HIS TABLE PARTITION TP_HIS11

16 HIS TABLE PARTITION TP_HIS17

16 HIS TABLE PARTITION TP_HIS30

16 HIS TABLE PARTITION TP_HIS34

16 HIS TABLE PARTITION TP_HIS35

16 HIS TABLE PARTITION TP_HIS44

16 HIS TABLE PARTITION TP_HIS45

16 HIS TABLE PARTITION TP_HIS58

16 HIS TABLE PARTITION TP_HIS62

16 HIS TABLE PARTITION TP_HIS63

16 HIS TABLE PARTITION TP_HIS64

31 HIS INDEX PARTITION TP_HIS09

31 HIS INDEX PARTITION TP_HIS11

31 HIS INDEX PARTITION TP_HIS15

31 HIS INDEX PARTITION TP_HIS36

31 HIS INDEX PARTITION TP_HIS59

31 HIS INDEX PARTITION TP_HIS60

31 HIS INDEX PARTITION TP_HIS62

31 HIS INDEX PARTITION TP_HIS43

31 HIS INDEX PARTITION TP_HIS46

31 HIS INDEX PARTITION TP_HIS48

31 HIS INDEX PARTITION TP_HIS50

16 HIS TABLE PARTITION TP_HIS24

16 HIS TABLE PARTITION TP_HIS27

16 HIS TABLE PARTITION TP_HIS43

16 HIS TABLE PARTITION TP_HIS54

16 HIS TABLE PARTITION TP_HIS56

31 HIS INDEX PARTITION TP_HIS01

31 HIS INDEX PARTITION TP_HIS04

31 HIS INDEX PARTITION TP_HIS17

31 HIS INDEX PARTITION TP_HIS37

31 HIS INDEX PARTITION TP_HIS54

31 HIS INDEX PARTITION TP_HIS56

31 HIS INDEX PARTITION TP_HIS57

3 HIS TABLE PARTITION XMLDATA

31 HIS INDEX PARTITION TP_HIS22

31 HIS INDEX PARTITION TP_HIS23

31 HIS INDEX PARTITION TP_HIS26

16 HIS TABLE PARTITION TP_HIS03

16 HIS TABLE PARTITION TP_HIS09

16 HIS TABLE PARTITION TP_HIS15

16 HIS TABLE PARTITION TP_HIS19

16 HIS TABLE PARTITION TP_HIS29

16 HIS TABLE PARTITION TP_HIS38

16 HIS TABLE PARTITION TP_HIS48

31 HIS INDEX PARTITION TP_HIS07

31 HIS INDEX PARTITION TP_HIS31

31 HIS INDEX PARTITION TP_HIS38

31 HIS INDEX PARTITION TP_HIS55

31 HIS INDEX PARTITION TP_HIS40

31 HIS INDEX PARTITION TP_HIS21

31 HIS INDEX PARTITION TP_HIS47

31 HIS INDEX PARTITION TP_HIS53

16 HIS TABLE PARTITION TP_HIS01

16 HIS TABLE PARTITION TP_HIS08

16 HIS TABLE PARTITION TP_HIS13

16 HIS TABLE PARTITION TP_HIS18

16 HIS TABLE PARTITION TP_HIS33

16 HIS TABLE PARTITION TP_HIS50

16 HIS TABLE PARTITION TP_HIS52

16 HIS TABLE PARTITION TP_HIS57

16 HIS TABLE PARTITION TP_HIS60

31 HIS INDEX PARTITION TP_HIS05

31 HIS INDEX PARTITION TP_HIS08

31 HIS INDEX PARTITION TP_HIS13

31 HIS INDEX PARTITION TP_HIS18

31 HIS INDEX PARTITION TP_HIS34

31 HIS INDEX PARTITION TP_HIS63

31 HIS INDEX PARTITION TP_HIS64

4 HIS LOBSEGMENT XMLDATA

31 HIS INDEX PARTITION TP_HIS25

31 HIS INDEX PARTITION TP_HIS30

31 HIS INDEX PARTITION TP_HIS51

16 HIS TABLE PARTITION TP_HIS20

16 HIS TABLE PARTITION TP_HIS21

16 HIS TABLE PARTITION TP_HIS22

16 HIS TABLE PARTITION TP_HIS25

16 HIS TABLE PARTITION TP_HIS31

16 HIS TABLE PARTITION TP_HIS47

16 HIS TABLE PARTITION TP_HIS51

31 HIS INDEX PARTITION TP_HIS06

452 HIS INDEX XMLDATA

31 HIS INDEX PARTITION TP_HIS41

31 HIS INDEX PARTITION TP_HIS24

31 HIS INDEX PARTITION TP_HIS27

31 HIS INDEX PARTITION TP_HIS49

16 HIS TABLE PARTITION TP_HIS04

16 HIS TABLE PARTITION TP_HIS10

16 HIS TABLE PARTITION TP_HIS32

16 HIS TABLE PARTITION TP_HIS46

16 HIS TABLE PARTITION TP_HIS55

31 HIS INDEX PARTITION TP_HIS03

31 HIS INDEX PARTITION TP_HIS33

31 HIS INDEX PARTITION TP_HIS39

31 HIS INDEX PARTITION TP_HIS61

135 rows selected.

總結說明

總的來說，當前資料庫中亟需改進的是大量的SQL的寫法規範性的問題，我們需要儘快的解決SQL寫法問題。

資料庫系統操作規範及SQL書寫建議

SQL 書寫規範

說明

巢狀檢視查詢

巢狀子查詢

謂詞條件過多使用函式

關聯表格主外來鍵型別不一致

條件篩選性較差

避免select for update 操作

業務高峰期的DDL 操作

資料庫規範化管理

總結說明

相關文章