一個簡單的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 where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) +(select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) from dual;
或者使用with
with
a as (select count(*) as cout1 from TEST_ONLINE where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and ),
b as (select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') )
select a.count1+b.count2 from a,b;
在目前滿足條件的情況下,效能差別應該不大。如果CN為非唯一性約束,這個問題還是需要好好斟酌一下了,如果在LOGIN_TIME,LOGOUT_TIME上有索引還是需要避免使用日期的二次格式化,而且在這個基礎上,我應該在末尾使用group by而不是rownum=1了。
這樣語句可能就變成了下面的形式。
select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE 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;
還有其它更多的改進方法,暫且討論到這裡。
開啟郵件,看到的語句是下面這樣的形式。
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 where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) +(select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') ) from dual;
或者使用with
with
a as (select count(*) as cout1 from TEST_ONLINE where CN='xxx' and to_char(LOGIN_TIME,'yyyymmdd') = to_char(sysdate,'yyyymmdd') and ),
b as (select count(*) as cout2 from TEST_USER_CENTER where CN='xxx' and to_char(LAST_LOGOUT,'yyyymmdd') = to_char(sysdate,'yyyymmdd') )
select a.count1+b.count2 from a,b;
在目前滿足條件的情況下,效能差別應該不大。如果CN為非唯一性約束,這個問題還是需要好好斟酌一下了,如果在LOGIN_TIME,LOGOUT_TIME上有索引還是需要避免使用日期的二次格式化,而且在這個基礎上,我應該在末尾使用group by而不是rownum=1了。
這樣語句可能就變成了下面的形式。
select a.cout1+b.cout2 from (select count(*) as cout1 from TEST_ONLINE 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;
還有其它更多的改進方法,暫且討論到這裡。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29734436/viewspace-2104252/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 一個簡單的MVP模式案例MVP模式
- greenplum 簡單sql優化案例SQL優化
- 一個簡單案例的Vue2.0原始碼Vue原始碼
- 記一個簡單的sql題:思維擴散SQL
- sql使用cursor寫一個簡單的迴圈<轉>SQL
- 一個left join SQL 簡單優化分析SQL優化
- 一個關於SQL隱碼攻擊的簡單例子SQL單例
- 記一個SQL優化案例SQL優化
- CSS樣式案例(2)-製作一個簡單的登入介面CSS
- MySQL幾個簡單SQL的優化MySql優化
- 一個簡單的bigfile tablespace無法擴充套件的案例處理套件
- 超級簡單的sql入門(一)SQL
- shell指令碼的一則簡單運用案例指令碼
- 一個簡單的 PWA 指南
- 一個簡簡單單的紅點系統框架框架
- PLSQL訪問網頁簡單案例(一)SQL網頁
- 簡單對比MySQL和Oracle中的一個sql解析細節MySqlOracle
- 【SQL】【遷移】寫了一個簡單的sequence遷移指令碼SQL指令碼
- drools的簡單入門案例
- Velocity案例(簡單的Hello)
- 一個簡單的載入動畫(一)動畫
- 簡單記錄幾個有用的sql查詢SQL
- sql簡單入門的一些操作SQL
- SQL*Plus的簡單使用之一(轉)SQL
- Go的第一個Hello程式 簡簡單單 - 快快樂樂Go
- 一個基於django框架的SQL 稽核系統二次開發Django框架SQL
- 網易雲簡單案例
- 一條執行了3天的"簡單"的sqlSQL
- 分享一個簡單的redis限流Redis
- 一個簡單的 Amqp 封裝MQ封裝
- 一個簡單的區塊鏈區塊鏈
- 擼一個簡單的MVVM例子MVVM
- 一個簡單的自定義Collection
- 一個簡單的守護程式
- 一個簡單的樹查詢
- 一個簡單的解密程式 (轉)解密
- 一個簡單的iptable的應用
- 簡單寫一個eventbus