一個簡單的sql稽核案例

今天開發的同學發來一封郵件，希望我幫忙對一個sql語句做一個評估。他們也着急要用，但是為了穩妥起見，還是希望我來稽核一下，這是一個好的習慣。

打開郵件，看到的語句是下面這樣的形式。

select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE where CN='' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and rownum = 1) a,(select count(*) as cout2 from TEST_USER_CENTER where CN='' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and rownum = 1) b;

看到這個語句，确實需要稽核。

首先從sql語句結構上來說，實在不夠好。

如果兩個子查詢的結果集條數大于1，很可能走笛卡爾積，貌似開發的同學也注意到了這一點，在兩個子查詢的末尾都加了rownum=1的字樣，這樣就肯定能夠保證語句能夠始終有1條以内的記錄顯示。是以這個語句看起來可以調整的空間不大。

但是我們做sql稽核，也離不開表的屬性資訊。這兩個表是OLTP的資料表，裡面會有大量的實時資料變化，看看兩個子查詢中的過濾條件，是根據日期來作為機關統計的，而一個核心字段就是CN了。看到這種情況，如果每日存在大量的資料，使用to_char(LAST_LOGOUT,'yyyymmdd')這種方式肯定是有弊端，但是看需求是想精确到日為機關的資料，那麼在這種情況下的關鍵就是CN了。

帶着疑問繼續檢視，發現CN在兩個表中都是主鍵，那麼這種情況就好辦多了。對于日期帶來的困擾，其實影響不大，而且根據資料的分布，一個CN對應的資料是唯一性的，那麼使用rownum=1就有些多餘了，然後再來看日期的過濾，有了CN的唯一性限制過濾，資料要麼有比對的是1條，要麼就是沒有比對的0條。

結果也是顯而易見，明白了這一點，這個時候看起來思路就清晰多了，這個查詢的結果應該是在0~2之間。

對于這個語句有了更深入一步的認識，我們就來簡單的改造一下。

這樣的形式：

select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE

where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') =

to_char(sysdate,'yyyymmdd')) a,(select count(*) as cout2

from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') =

to_char(sysdate,'yyyymmdd') ) b;

或者：

select  (select count(*) as cout1 from TEST_ONLINE

to_char(sysdate,'yyyymmdd') ) +(select count(*) as cout2

to_char(sysdate,'yyyymmdd') )  from dual;

或者使用with

with

a as (select count(*) as cout1 from TEST_ONLINE

to_char(sysdate,'yyyymmdd') and ),

b as (select count(*) as cout2

to_char(sysdate,'yyyymmdd') )

select a.count1+b.count2 from a,b;

在目前滿足條件的情況下，性能差别應該不大。如果CN為非唯一性限制，這個問題還是需要好好斟酌一下了,如果在LOGIN_TIME,LOGOUT_TIME上有索引還是需要避免使用日期的二次格式化，而且在這個基礎上，我應該在末尾使用group by而不是rownum=1了。

這樣語句可能就變成了下面的形式。

where CN='xxx' and LOGIN_TIME between trunc(sysdate) and to_date(sysdate,'yyyy-mm-dd hh24:mi:ss')  group by LOGIN_TIME) a,(select count(*) as cout2

from TEST_USER_CENTER where CN='xxx' and LOGOUT_TIME between trunc(sysdate) and to_date(sysdate,'yyyy-mm-dd hh24:mi:ss')

group by LOGOUT_TIME) b;

還有其它更多的改進方法，暫且讨論到這裡。