SQL Thinking

小至尖尖發表於2024-07-15

s2下半年我在內部有一次部門級別的技術分享會,以本文內容分享為主。
其實有很多人問過我相同的問題,遇到需要改寫的慢sql,不知道怎麼改,改好了以後也不知道等不等價?不等價了也不知道錯在哪?這個要怎麼破?
其實都是因為絕大多數人沒有做過開發,看不懂sql,不會寫sql,沒有sql思維,下面透過幾個案例將sql思維給引出來。


1、row_number() over() 邏輯實現

這種排名的視窗函式(row_number()、rank()、dense_rank())據我所知,除了在oracle上大資料量執行效能表現得不錯,其他各種資料庫(包括列式儲存的ap庫),效能都表現都很一般。
所以如果在專案上遇到row_number() over() 效能不理想的情況下,可以嘗試進行改寫最佳化。
PS:當然可以透過加並行hint來提升效率,但是我們這篇文章主要是講如何將資料庫提供的邏輯透過另外一種方式來實現,提升自己sql程式碼思維。

-- student 資料量
obclient [YZJ]> select count(1) from student;
+----------+
| COUNT(1) |
+----------+
|   500001 |
+----------+
1 row in set (0.002 sec)


-- 案例sql
-- S.CLASS : 學科、包含null值
-- S.S_DATE: 入學時間
SELECT * FROM (
    SELECT 
    S.*,
    row_number() OVER(PARTITION BY S.CLASS ORDER BY S.S_DATE DESC) RN
 FROM student S
 ) WHERE RN = 1;

27 rows in set (0.639 sec)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================================                                                                                              |
| |ID|OPERATOR             |NAME           |EST.ROWS|EST.TIME(us)|                                                                                              |
| ----------------------------------------------------------------                                                                                              |
| |0 |SUBPLAN SCAN         |ANONYMOUS_VIEW1|4       |691395      |                                                                                              |
| |1 |└─WINDOW FUNCTION    |               |500001  |682867      |                                                                                              |
| |2 |  └─PARTITION SORT   |               |500001  |249986      |                                                                                              |
| |3 |    └─TABLE FULL SCAN|S              |500001  |46352       |                                                                                              |
| ================================================================                                                                                              |
| Outputs & filters:                                                                                                                                            |
| -------------------------------------                                                                                                                         |
|   0 - output([.ID], [.NAME], [.AGE], [.SEX], [.S_DATE], [.CLASS], [.RN]), filter([.RN = 1]), rowset=256                                                       |
|       access([.RN], [.ID], [.NAME], [.AGE], [.SEX], [.S_DATE], [.CLASS])                                                                                      |
|   1 - output([S.ID], [S.NAME], [S.AGE], [S.SEX], [S.S_DATE], [S.CLASS], [T_WIN_FUN_ROW_NUMBER()]), filter(nil), rowset=256                                    |
|       win_expr(T_WIN_FUN_ROW_NUMBER()), partition_by([S.CLASS]), order_by([S.S_DATE, DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED  |
|       FOLLOWING)                                                                                                                                              |
|   2 - output([S.ID], [S.NAME], [S.AGE], [S.SEX], [S.S_DATE], [S.CLASS]), filter(nil), rowset=256                                                              |
|       sort_keys([HASH(S.CLASS), ASC], [S.CLASS, ASC], [S.S_DATE, DESC])                                                                                       |
|   3 - output([S.ID], [S.NAME], [S.AGE], [S.SEX], [S.S_DATE], [S.CLASS]), filter(nil), rowset=256                                                              |
|       access([S.ID], [S.NAME], [S.AGE], [S.SEX], [S.S_DATE], [S.CLASS]), partitions(p0)                                                                       |
|       is_index_back=false, is_global_index=false,                                                                                                             |
|       range_key([S.ID]), range(MIN ; MAX)always true                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 rows in set (0.004 sec)

1.1、改寫方式一

改寫視窗函式的邏輯,必須要將表關聯2次或者2次+,需要將另外一張關聯的表理解成一個“滑動”的視窗。
一般有兩種改法:

  • 方法1、相同表 join 兩次。
  • 方法2、表量子查詢實現,將子查詢的那張表理解成一個“滑動”的視窗。
SELECT s.* FROM student s   -- 原表
INNER JOIN 
    (
      
    SELECT 
      class, 
      max(s_date) max_date   
      FROM student
    GROUP BY  class 
    ) s1 -- 滑動的視窗表
    ON decode(s.class,NULL,'aab',s.class) = decode(s1.class,NULL,'aab',s1.class)
       -- decode 處理 s表和s1視窗表的分組列class
        AND s.s_date = s1.max_date  
		-- s表的s_date和視窗表s1的最大的日期去比較

        WHERE exists(
          
            SELECT 1 FROM student s2 where s1.max_date = s2.s_date GROUP BY s2.class having MAX(s2.ID) = S.ID
      /* exists 這段比較抽象,我也是想了一會才實現邏輯。
	     實現 row_number() 唯一排名的資料
      */
        );


27 rows in set (0.038 sec)

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==============================================================================================                                                                        |
| |ID|OPERATOR                          |NAME                            |EST.ROWS|EST.TIME(us)|                                                                        |
| ----------------------------------------------------------------------------------------------                                                                        |
| |0 |SUBPLAN FILTER                    |                                |4       |28745       |                                                                        |
| |1 |├─NESTED-LOOP JOIN                |                                |8       |28555       |                                                                        |
| |2 |│ ├─SUBPLAN SCAN                  |S1                              |26      |27712       |                                                                        |
| |3 |│ │ └─MERGE GROUP BY              |                                |26      |27712       |                                                                        |
| |4 |│ │   └─TABLE FULL SCAN           |STUDENT(IDX_CLASS_SDATE_STUDENT)|500001  |14409       |                                                                        |
| |5 |│ └─DISTRIBUTED TABLE RANGE SCAN  |S(IDX_S_DATE_CALSS_ID)          |1       |32          |                                                                        |
| |6 |└─LIMIT                           |                                |1       |27          |                                                                        |
| |7 |  └─MERGE GROUP BY                |                                |1       |27          |                                                                        |
| |8 |    └─DISTRIBUTED TABLE RANGE SCAN|S2(IDX_S_DATE_CALSS_ID)         |56      |23          |                                                                        |
| ==============================================================================================                                                                        |
| Outputs & filters:                                                                                                                                                    |
| -------------------------------------                                                                                                                                 |
|   0 - output([S.ID], [S.NAME], [S.AGE], [S.SEX], [S.S_DATE], [S.CLASS]), filter([(T_OP_EXISTS, subquery(1))]), rowset=256                                             |
|       exec_params_([S1.MAX_DATE(:0)], [S.ID(:1)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false                                                         |
|   1 - output([S1.MAX_DATE], [S.ID], [S.NAME], [S.AGE], [S.SEX], [S.S_DATE], [S.CLASS]), filter(nil), rowset=256                                                       |
|       conds(nil), nl_params_([S1.CLASS(:4)], [S1.MAX_DATE(:5)]), use_batch=false                                                                                      |
|   2 - output([S1.CLASS], [S1.MAX_DATE]), filter(nil), rowset=256                                                                                                      |
|       access([S1.CLASS], [S1.MAX_DATE])                                                                                                                               |
|   3 - output([STUDENT.CLASS], [T_FUN_MAX(STUDENT.S_DATE)]), filter(nil), rowset=256                                                                                   |
|       group([STUDENT.CLASS]), agg_func([T_FUN_MAX(STUDENT.S_DATE)])                                                                                                   |
|   4 - output([STUDENT.CLASS], [STUDENT.S_DATE]), filter(nil), rowset=256                                                                                              |
|       access([STUDENT.CLASS], [STUDENT.S_DATE]), partitions(p0)                                                                                                       |
|       is_index_back=false, is_global_index=false,                                                                                                                     |
|       range_key([STUDENT.CLASS], [STUDENT.S_DATE], [STUDENT.ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true                                                         |
|   5 - output([S.ID], [S.CLASS], [S.S_DATE], [S.NAME], [S.AGE], [S.SEX]), filter([ora_decode(cast(S.CLASS, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('aab',    |
|        VARCHAR2(3 BYTE)), cast(S.CLASS, VARCHAR2(3 BYTE))) = ora_decode(cast(:4, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('aab', VARCHAR2(3 BYTE)), cast(:4, |
|        VARCHAR2(3 BYTE)))]), rowset=256                                                                                                                               |
|       access([S.ID], [S.CLASS], [S.S_DATE], [S.NAME], [S.AGE], [S.SEX]), partitions(p0)                                                                               |
|       is_index_back=true, is_global_index=false, filter_before_indexback[true],                                                                                       |
|       range_key([S.S_DATE], [S.CLASS], [S.ID]), range(MIN ; MAX),                                                                                                     |
|       range_cond([S.S_DATE = :5])                                                                                                                                     |
|   6 - output([1]), filter(nil), rowset=256                                                                                                                            |
|       limit(1), offset(nil)                                                                                                                                           |
|   7 - output(nil), filter([T_FUN_MAX(S2.ID) = :1]), rowset=256                                                                                                        |
|       group([S2.CLASS]), agg_func([T_FUN_MAX(S2.ID)])                                                                                                                 |
|   8 - output([S2.ID], [S2.CLASS]), filter(nil), rowset=256                                                                                                            |
|       access([S2.ID], [S2.CLASS]), partitions(p0)                                                                                                                     |
|       is_index_back=false, is_global_index=false,                                                                                                                     |
|       range_key([S2.S_DATE], [S2.CLASS], [S2.ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true,                                                                       |
|       range_cond([:0 = S2.S_DATE])                                                                                                                                    |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
43 rows in set (0.008 sec)

1.2、改寫方式二

SELECT S1.*
FROM student S1
INNER JOIN 
    (
      
    SELECT 
      decode(class,null,'aab',class) class, 
      MAX(S2.S_DATE) MAX_S_DATE,
      MAX(ID) KEEP (DENSE_RANK LAST ORDER BY S_DATE) MAX_ID
    FROM student S2
    GROUP BY  S2.CLASS
    
    /*
      改寫邏輯:
        1、decode(class,null,'aab',class) class,  提前處理null值。如果有其他案例謂詞過濾條件要轉換列名,
           例如:upper(列名) = upper(:1) ,可以提前在先寫個select括起來內表處理欄位,弄個別名,然後外面再過濾。
      
           select * from (
            select upper(col) col1 from tb 
           ) where col1 = upper(:1);

        2、MAX(S2.S_DATE) MAX_S_DATE 不多說,找最大日期,等價 desc

        3、MAX(ID) KEEP (DENSE_RANK LAST ORDER BY S_DATE) MAX_ID 等價於上個sql 的 exists邏輯,
          利用keep 唯一的特性,取出不同組裡面最大的 id + ORDER BY S_DATE LAST 最後的唯一資料。

        相當於 MAX_S_DATE,和 MAX(id) keep 兩個列的資料都是一條 ,相當於rn=1。
      
      
    */

      
    ) S2
    ON decode(S1.CLASS,null,'aab',S1.CLASS) = S2.CLASS     /* 處理關聯列 */
        AND S1.ID = S2.MAX_ID ; 
       -- AND S1.S_DATE = S2.MAX_S_DATE;   

    /* 其實用S1.ID = S2.MAX_ID 關聯就行,
     S1.S_DATE = S2.MAX_S_DATE 這個寫不寫都行,如果想邏輯更嚴謹點可以寫上。
    */



27 rows in set (0.114 sec)


+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                       |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==============================================================================                                                                                   |
| |ID|OPERATOR               |NAME                       |EST.ROWS|EST.TIME(us)|                                                                                   |
| ------------------------------------------------------------------------------                                                                                   |
| |0 |NESTED-LOOP JOIN       |                           |2       |44008       |                                                                                   |
| |1 |├─SUBPLAN SCAN         |S2                         |26      |43466       |                                                                                   |
| |2 |│ └─MERGE GROUP BY     |                           |26      |43466       |                                                                                   |
| |3 |│   └─TABLE FULL SCAN  |S2(IDX_CLASS_SDATE_STUDENT)|500001  |22423       |                                                                                   |
| |4 |└─DISTRIBUTED TABLE GET|S1                         |1       |21          |                                                                                   |
| ==============================================================================                                                                                   |
| Outputs & filters:                                                                                                                                               |
| -------------------------------------                                                                                                                            |
|   0 - output([S1.ID], [S1.NAME], [S1.AGE], [S1.SEX], [S1.S_DATE], [S1.CLASS]), filter(nil), rowset=256                                                           |
|       conds(nil), nl_params_([S2.CLASS(:3)], [S2.MAX_S_DATE(:4)], [S2.MAX_ID(:5)]), use_batch=false                                                              |
|   1 - output([S2.CLASS], [S2.MAX_S_DATE], [S2.MAX_ID]), filter(nil), rowset=256                                                                                  |
|       access([S2.CLASS], [S2.MAX_S_DATE], [S2.MAX_ID])                                                                                                           |
|   2 - output([ora_decode(cast(S2.CLASS, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('aab', VARCHAR2(3 BYTE)), cast(S2.CLASS, VARCHAR2(3 BYTE)))],          |
|       [T_FUN_MAX(S2.S_DATE)], [T_FUN_KEEP_MAX(S2.ID) order_items(S2.S_DATE)]), filter(nil), rowset=256                                                           |
|       group([S2.CLASS]), agg_func([T_FUN_MAX(S2.S_DATE)], [T_FUN_KEEP_MAX(S2.ID) order_items(S2.S_DATE)])                                                        |
|   3 - output([S2.ID], [S2.CLASS], [S2.S_DATE]), filter(nil), rowset=256                                                                                          |
|       access([S2.ID], [S2.CLASS], [S2.S_DATE]), partitions(p0)                                                                                                   |
|       is_index_back=false, is_global_index=false,                                                                                                                |
|       range_key([S2.CLASS], [S2.S_DATE], [S2.ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true                                                                   |
|   4 - output([S1.ID], [S1.CLASS], [S1.S_DATE], [S1.NAME], [S1.AGE], [S1.SEX]), filter([S1.S_DATE = :4], [ora_decode(cast(S1.CLASS, VARCHAR2(1 BYTE)), cast(NULL, |
|        VARCHAR2(0 )), cast('aab', VARCHAR2(3 BYTE)), cast(S1.CLASS, VARCHAR2(3 BYTE))) = :3]), rowset=256                                                        |
|       access([S1.ID], [S1.CLASS], [S1.S_DATE], [S1.NAME], [S1.AGE], [S1.SEX]), partitions(p0)                                                                    |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false,false],                                                                          |
|       range_key([S1.ID]), range(MIN ; MAX),                                                                                                                      |
|       range_cond([S1.ID = :5])                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
28 rows in set (0.011 sec)

row_number() over() 的邏輯還可以用表量子查詢來實現,不過效能會更慢,實現起來很麻煩,就不在這裡演示。


2、keep() 函式邏輯實現

某些資料庫可能不支援keep函式,或者keep函式的效能並不理想,我們也可以嘗試透過另外一種方式來實現keep函式的邏輯。

2.1、keep 形式一

SELECT 
  s1.class,
  max(s1.s_date) MAX_S_DATE,
  MAX(name) KEEP (DENSE_RANK LAST ORDER BY s1.s_date) max_name,
  min(s1.s_date) MIN_S_DATE,
  MIN(name) KEEP (DENSE_RANK FIRST ORDER BY s1.s_date) min_name
 FROM student s1 
GROUP BY
    s1.class;

27 rows in set (0.495 sec)


+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================                                                                                                                                                   |
| |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                                                                                                                                                   |
| ---------------------------------------------------                                                                                                                                                   |
| |0 |MERGE GROUP BY     |    |26      |144304      |                                                                                                                                                   |
| |1 |└─PARTITION SORT   |    |500001  |107784      |                                                                                                                                                   |
| |2 |  └─TABLE FULL SCAN|S1  |500001  |22309       |                                                                                                                                                   |
| ===================================================                                                                                                                                                   |
| Outputs & filters:                                                                                                                                                                                    |
| -------------------------------------                                                                                                                                                                 |
|   0 - output([S1.CLASS], [T_FUN_MAX(S1.S_DATE)], [T_FUN_KEEP_MAX(S1.NAME) order_items(S1.S_DATE)], [T_FUN_MIN(S1.S_DATE)], [T_FUN_KEEP_MIN(S1.NAME) order_items(S1.S_DATE)]), filter(nil), rowset=256 |
|       group([S1.CLASS]), agg_func([T_FUN_MAX(S1.S_DATE)], [T_FUN_KEEP_MAX(S1.NAME) order_items(S1.S_DATE)], [T_FUN_MIN(S1.S_DATE)], [T_FUN_KEEP_MIN(S1.NAME)                                          |
|       order_items(S1.S_DATE)])                                                                                                                                                                        |
|   1 - output([S1.CLASS], [S1.S_DATE], [S1.NAME]), filter(nil), rowset=256                                                                                                                             |
|       sort_keys([HASH(S1.CLASS), ASC], [S1.CLASS, ASC])                                                                                                                                               |
|   2 - output([S1.CLASS], [S1.S_DATE], [S1.NAME]), filter(nil), rowset=256                                                                                                                             |
|       access([S1.CLASS], [S1.S_DATE], [S1.NAME]), partitions(p0)                                                                                                                                      |
|       is_index_back=false, is_global_index=false,                                                                                                                                                     |
|       range_key([S1.ID]), range(MIN ; MAX)always true                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.004 sec)

2.2、keep 形式一改寫標量子查詢

改寫邏輯很簡單,先分組找出最大、最小的 日期,然後透過日期 + class 外部標量子查詢找出最大、最小的name。

SELECT 
  CLASS,
  MAX_S_DATE,
  (SELECT max(NAME) FROM student s2 WHERE 
       decode(s1.CLASS,null,'bb',s1.CLASS) = decode(s2.CLASS,null,'bb',s2.CLASS) AND s2.S_DATE = s1.MAX_S_DATE) max_name,
  MIN_S_DATE,
  (SELECT min(NAME) FROM student s2 WHERE 
       decode(s1.CLASS,null,'bb',s1.CLASS) = decode(s2.CLASS,null,'bb',s2.CLASS) AND s2.S_DATE = s1.MIN_S_DATE) min_name
 FROM (
SELECT 
  CLASS,
  max(S_DATE) MAX_S_DATE,
  min(S_DATE) MIN_S_DATE
 FROM student 
GROUP BY
    CLASS 
) s1 ;


27 rows in set (0.040 sec)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ============================================================================================                                                                               |
| |ID|OPERATOR                        |NAME                            |EST.ROWS|EST.TIME(us)|                                                                               |
| --------------------------------------------------------------------------------------------                                                                               |
| |0 |SUBPLAN FILTER                  |                                |26      |36802       |                                                                               |
| |1 |├─MERGE GROUP BY                |                                |26      |35450       |                                                                               |
| |2 |│ └─TABLE FULL SCAN             |STUDENT(IDX_CLASS_SDATE_STUDENT)|500001  |14409       |                                                                               |
| |3 |├─SCALAR GROUP BY               |                                |1       |26          |                                                                               |
| |4 |│ └─DISTRIBUTED TABLE RANGE SCAN|S2(IDX_S_DATE_CALSS_ID)         |1       |26          |                                                                               |
| |5 |└─SCALAR GROUP BY               |                                |1       |26          |                                                                               |
| |6 |  └─DISTRIBUTED TABLE RANGE SCAN|S2(IDX_S_DATE_CALSS_ID)         |1       |26          |                                                                               |
| ============================================================================================                                                                               |
| Outputs & filters:                                                                                                                                                         |
| -------------------------------------                                                                                                                                      |
|   0 - output([STUDENT.CLASS], [T_FUN_MAX(STUDENT.S_DATE)], [subquery(1)], [T_FUN_MIN(STUDENT.S_DATE)], [subquery(2)]), filter(nil), rowset=256                             |
|       exec_params_([STUDENT.CLASS(:0)], [T_FUN_MAX(STUDENT.S_DATE)(:1)], [STUDENT.CLASS(:2)], [T_FUN_MIN(STUDENT.S_DATE)(:3)]), onetime_exprs_(nil), init_plan_idxs_(nil), |
|        use_batch=false                                                                                                                                                     |
|   1 - output([STUDENT.CLASS], [T_FUN_MAX(STUDENT.S_DATE)], [T_FUN_MIN(STUDENT.S_DATE)]), filter(nil), rowset=256                                                           |
|       group([STUDENT.CLASS]), agg_func([T_FUN_MAX(STUDENT.S_DATE)], [T_FUN_MIN(STUDENT.S_DATE)])                                                                           |
|   2 - output([STUDENT.CLASS], [STUDENT.S_DATE]), filter(nil), rowset=256                                                                                                   |
|       access([STUDENT.CLASS], [STUDENT.S_DATE]), partitions(p0)                                                                                                            |
|       is_index_back=false, is_global_index=false,                                                                                                                          |
|       range_key([STUDENT.CLASS], [STUDENT.S_DATE], [STUDENT.ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true                                                              |
|   3 - output([T_FUN_MAX(S2.NAME)]), filter(nil), rowset=256                                                                                                                |
|       group(nil), agg_func([T_FUN_MAX(S2.NAME)])                                                                                                                           |
|   4 - output([S2.NAME]), filter([ora_decode(cast(:0, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('bb', VARCHAR2(2 BYTE)), cast(:0, VARCHAR2(2 BYTE)))                |
|       = ora_decode(cast(S2.CLASS, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('bb', VARCHAR2(2 BYTE)), cast(S2.CLASS, VARCHAR2(2 BYTE)))]), rowset=256               |
|       access([S2.ID], [S2.CLASS], [S2.NAME]), partitions(p0)                                                                                                               |
|       is_index_back=true, is_global_index=false, filter_before_indexback[true],                                                                                            |
|       range_key([S2.S_DATE], [S2.CLASS], [S2.ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true,                                                                            |
|       range_cond([S2.S_DATE = :1])                                                                                                                                         |
|   5 - output([T_FUN_MIN(S2.NAME)]), filter(nil), rowset=256                                                                                                                |
|       group(nil), agg_func([T_FUN_MIN(S2.NAME)])                                                                                                                           |
|   6 - output([S2.NAME]), filter([ora_decode(cast(:2, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('bb', VARCHAR2(2 BYTE)), cast(:2, VARCHAR2(2 BYTE)))                |
|       = ora_decode(cast(S2.CLASS, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('bb', VARCHAR2(2 BYTE)), cast(S2.CLASS, VARCHAR2(2 BYTE)))]), rowset=256               |
|       access([S2.ID], [S2.CLASS], [S2.NAME]), partitions(p0)                                                                                                               |
|       is_index_back=true, is_global_index=false, filter_before_indexback[true],                                                                                            |
|       range_key([S2.S_DATE], [S2.CLASS], [S2.ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true,                                                                            |
|       range_cond([S2.S_DATE = :3])                                                                                                                                         |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
38 rows in set (0.008 sec)

2.3、keep 形式一改寫left join

改成 left join 也是按照標量的邏輯,都比較簡單。

SELECT s1.CLASS,
       s1.MAX_S_DATE,
       max(CASE WHEN s2.S_DATE = s1.MAX_S_DATE THEN s2.NAME END)  max_name,
       s1.MIN_S_DATE,
       min(CASE WHEN s2.S_DATE = s1.MIN_S_DATE THEN s2.NAME END ) min_name
 FROM (

SELECT 
  CLASS,
  max(S_DATE) MAX_S_DATE,
  min(S_DATE) MIN_S_DATE
 FROM student 
GROUP BY
    CLASS 
    )s1 LEFT JOIN  student s2 ON decode(s1.CLASS,null,'bb',s1.CLASS) = decode(s2.CLASS,null,'bb',s2.CLASS)
    GROUP BY 
    s1.CLASS,
       s1.MAX_S_DATE,
       s1.MIN_S_DATE;

27 rows in set (0.213 sec)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ===================================================================================                                                                                |
| |ID|OPERATOR               |NAME                            |EST.ROWS|EST.TIME(us)|                                                                                |
| -----------------------------------------------------------------------------------                                                                                |
| |0 |HASH GROUP BY          |                                |17      |200679      |                                                                                |
| |1 |└─HASH OUTER JOIN      |                                |65001   |193221      |                                                                                |
| |2 |  ├─SUBPLAN SCAN       |S1                              |26      |35450       |                                                                                |
| |3 |  │ └─MERGE GROUP BY   |                                |26      |35450       |                                                                                |
| |4 |  │   └─TABLE FULL SCAN|STUDENT(IDX_CLASS_SDATE_STUDENT)|500001  |14409       |                                                                                |
| |5 |  └─TABLE FULL SCAN    |S2                              |500001  |22309       |                                                                                |
| ===================================================================================                                                                                |
| Outputs & filters:                                                                                                                                                 |
| -------------------------------------                                                                                                                              |
|   0 - output([S1.CLASS], [S1.MAX_S_DATE], [T_FUN_MAX(CASE WHEN S2.S_DATE = S1.MAX_S_DATE THEN S2.NAME ELSE NULL END)], [S1.MIN_S_DATE], [T_FUN_MIN(CASE            |
|       WHEN S2.S_DATE = S1.MIN_S_DATE THEN S2.NAME ELSE NULL END)]), filter(nil), rowset=256                                                                        |
|       group([S1.CLASS]), agg_func([T_FUN_MAX(CASE WHEN S2.S_DATE = S1.MAX_S_DATE THEN S2.NAME ELSE NULL END)], [T_FUN_MIN(CASE WHEN S2.S_DATE = S1.MIN_S_DATE      |
|       THEN S2.NAME ELSE NULL END)])                                                                                                                                |
|   1 - output([S1.CLASS], [S2.S_DATE], [S2.NAME], [S1.MAX_S_DATE], [S1.MIN_S_DATE]), filter(nil), rowset=256                                                        |
|       equal_conds([ora_decode(cast(S1.CLASS, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('bb', VARCHAR2(2 BYTE)), cast(S1.CLASS, VARCHAR2(2 BYTE)))          |
|       = ora_decode(cast(S2.CLASS, VARCHAR2(1 BYTE)), cast(NULL, VARCHAR2(0 )), cast('bb', VARCHAR2(2 BYTE)), cast(S2.CLASS, VARCHAR2(2 BYTE)))]), other_conds(nil) |
|   2 - output([S1.CLASS], [S1.MAX_S_DATE], [S1.MIN_S_DATE]), filter(nil), rowset=256                                                                                |
|       access([S1.CLASS], [S1.MAX_S_DATE], [S1.MIN_S_DATE])                                                                                                         |
|   3 - output([STUDENT.CLASS], [T_FUN_MAX(STUDENT.S_DATE)], [T_FUN_MIN(STUDENT.S_DATE)]), filter(nil), rowset=256                                                   |
|       group([STUDENT.CLASS]), agg_func([T_FUN_MAX(STUDENT.S_DATE)], [T_FUN_MIN(STUDENT.S_DATE)])                                                                   |
|   4 - output([STUDENT.CLASS], [STUDENT.S_DATE]), filter(nil), rowset=256                                                                                           |
|       access([STUDENT.CLASS], [STUDENT.S_DATE]), partitions(p0)                                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                  |
|       range_key([STUDENT.CLASS], [STUDENT.S_DATE], [STUDENT.ID]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true                                                      |
|   5 - output([S2.CLASS], [S2.S_DATE], [S2.NAME]), filter(nil), rowset=256                                                                                          |
|       access([S2.CLASS], [S2.S_DATE], [S2.NAME]), partitions(p0)                                                                                                   |
|       is_index_back=false, is_global_index=false,                                                                                                                  |
|       range_key([S2.ID]), range(MIN ; MAX)always true                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
31 rows in set (0.005 sec)

2.4、keep 形式二

SELECT 
  DEPARTMENT_ID,
  HIRE_DATE,
  MAX(FIRST_NAME) KEEP (DENSE_RANK LAST ORDER BY HIRE_DATE)  over(PARTITION BY DEPARTMENT_ID) max_name,
  MIN(FIRST_NAME) KEEP (DENSE_RANK FIRST ORDER BY HIRE_DATE) over(PARTITION BY DEPARTMENT_ID) min_name
 FROM EMPLOYEES ;

 107 rows in set (0.002 sec)

2.5、keep 形式二改寫left join

SELECT 
  e1.DEPARTMENT_ID,
  e1.HIRE_DATE,
  e2.max_name,
  e2.min_name
FROM EMPLOYEES e1 LEFT JOIN (
SELECT DEPARTMENT_ID,
       MAX(CASE WHEN max_rn = 1 THEN FIRST_NAME ELSE NULL END) max_name,
       MIN(CASE WHEN min_rn = 1 THEN FIRST_NAME ELSE NULL END) min_name
     
 FROM (
    SELECT 
      FIRST_NAME,
      HIRE_DATE,
      DEPARTMENT_ID,
      DENSE_RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE DESC) max_rn,
      DENSE_RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE ) min_rn

   
    FROM  EMPLOYEES
)
GROUP BY DEPARTMENT_ID

) e2 ON   // 最後關聯
decode(e1.DEPARTMENT_ID, null, 'aaa', e1.DEPARTMENT_ID) =
                                   decode(e2.DEPARTMENT_ID, null, 'aaa', e2.DEPARTMENT_ID);
                                   
107 rows in set (0.007 sec)

2.6、keep 形式二改寫標量子查詢


SELECT DEPARTMENT_ID,
         HIRE_DATE,
    (SELECT e2.max_name
    FROM 
        (SELECT DEPARTMENT_ID,
        max(FIRST_NAME) max_name
        FROM 
            (SELECT x.*,
         DENSE_RANK() OVER(PARTITION BY DEPARTMENT_ID
            ORDER BY  HIRE_DATE DESC) max_rn
            FROM EMPLOYEES x )
            WHERE max_rn = 1
            GROUP BY  DEPARTMENT_ID ) e2
            WHERE decode(e1.department_id, null, 'aaa', e1.department_id) = decode(e2.department_id, null, 'aaa', e2.department_id) ) max_name, 
            (SELECT e2.min_name
            FROM 
                (SELECT DEPARTMENT_ID,
        min(FIRST_NAME) min_name
                FROM 
                    (SELECT x.*,
         DENSE_RANK() OVER(PARTITION BY DEPARTMENT_ID
                    ORDER BY  HIRE_DATE ) min_rn
                    FROM EMPLOYEES x )
                    WHERE min_rn = 1
                    GROUP BY  DEPARTMENT_ID ) e2
                    WHERE decode(e1.department_id, null, 'aaa', e1.department_id) = decode(e2.department_id, null, 'aaa', e2.department_id) ) min_name
                FROM EMPLOYEES e1;

107 rows in set (0.030 sec)

/* 改SQL 邏輯等價上面join sql, 如果會寫join版本改成表量子查詢是更簡單的操作 */

3、rank() over() 函式邏輯實現

rank() over() 函式會跳號,如果符合over()邏輯的資料有兩條的話,那麼這兩條會並列第一,然後第二條資料會加二(即顯示第三)。
這個函式在業務邏輯上比較少使用到,但是我們也可以嘗試使用其他邏輯來實現rank() over() 函式的功能。
**** 注意:以下實現的rank() over() 邏輯的程式碼效能,會比原來rank() over() 的效能更差,這裡只做功能實現,不考慮效能問題 ****

SELECT * FROM (
SELECT 
  FIRST_NAME,
  DEPARTMENT_ID,
  HIRE_DATE,
   RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE DESC) MAX_RN
 FROM EMPLOYEES )  WHERE DEPARTMENT_ID = 80;

 34 rows in set (0.004 sec)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================                                                                                                     |
| |ID|OPERATOR           |NAME     |EST.ROWS|EST.TIME(us)|                                                                                                     |
| --------------------------------------------------------                                                                                                     |
| |0 |WINDOW FUNCTION    |         |34      |42          |                                                                                                     |
| |1 |└─SORT             |         |34      |16          |                                                                                                     |
| |2 |  └─TABLE FULL SCAN|EMPLOYEES|34      |10          |                                                                                                     |
| ========================================================                                                                                                     |
| Outputs & filters:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   0 - output([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE], [T_WIN_FUN_RANK()]), filter(nil), rowset=256                          |
|       win_expr(T_WIN_FUN_RANK()), partition_by(nil), order_by([EMPLOYEES.HIRE_DATE, DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED  |
|       FOLLOWING)                                                                                                                                             |
|   1 - output([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE]), filter(nil), rowset=256                                              |
|       sort_keys([EMPLOYEES.HIRE_DATE, DESC])                                                                                                                 |
|   2 - output([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE]), filter([EMPLOYEES.DEPARTMENT_ID = 80]), rowset=256                   |
|       access([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE]), partitions(p0)                                                       |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                            |
|       range_key([EMPLOYEES.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.004 sec)

3.1、改寫方式一:標量子查詢邏輯實現

 SELECT 
  e.FIRST_NAME,
  e.DEPARTMENT_ID,
  e.HIRE_DATE,
  (
    SELECT count(HIRE_DATE) + 1 FROM EMPLOYEES e1 WHERE 
      DECODE(e1.DEPARTMENT_ID,NULL,'8080',e1.DEPARTMENT_ID) = DECODE(e.DEPARTMENT_ID,NULL,'8080',e.DEPARTMENT_ID) AND 
      e1.HIRE_DATE > e.HIRE_DATE
  ) MAX_RN
 FROM EMPLOYEES  e  WHERE e.DEPARTMENT_ID = 80;

/*
  重點是在標量子查詢內的 e1.HIRE_DATE > e.HIRE_DATE 邏輯,可以將 e1 表理解成一個滑動的視窗。  
*/
 
 
34 rows in set (0.006 sec)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================================                                                                                                           |
| |ID|OPERATOR                 |NAME|EST.ROWS|EST.TIME(us)|                                                                                                           |
| ---------------------------------------------------------                                                                                                           |
| |0 |MERGE GROUP BY           |    |34      |29          |                                                                                                           |
| |1 |└─NESTED-LOOP OUTER JOIN |    |34      |22          |                                                                                                           |
| |2 |  ├─TABLE FULL SCAN      |E   |34      |11          |                                                                                                           |
| |3 |  └─MATERIAL             |    |1       |11          |                                                                                                           |
| |4 |    └─TABLE FULL SCAN    |E1  |1       |11          |                                                                                                           |
| =========================================================                                                                                                           |
| Outputs & filters:                                                                                                                                                  |
| -------------------------------------                                                                                                                               |
|   0 - output([E.FIRST_NAME], [E.DEPARTMENT_ID], [E.HIRE_DATE], [T_FUN_COUNT(E1.HIRE_DATE) + 1]), filter(nil), rowset=256                                            |
|       group([E.EMPLOYEE_ID]), agg_func([T_FUN_COUNT(E1.HIRE_DATE)])                                                                                                 |
|   1 - output([E.EMPLOYEE_ID], [E.FIRST_NAME], [E.DEPARTMENT_ID], [E.HIRE_DATE], [E1.HIRE_DATE]), filter(nil), rowset=256                                            |
|       conds([E1.HIRE_DATE > E.HIRE_DATE]), nl_params_(nil), use_batch=false                                                                                         |
|   2 - output([E.EMPLOYEE_ID], [E.DEPARTMENT_ID], [E.FIRST_NAME], [E.HIRE_DATE]), filter([E.DEPARTMENT_ID = 80]), rowset=256                                         |
|       access([E.EMPLOYEE_ID], [E.DEPARTMENT_ID], [E.FIRST_NAME], [E.HIRE_DATE]), partitions(p0)                                                                     |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                   |
|       range_key([E.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                       |
|   3 - output([E1.HIRE_DATE]), filter(nil), rowset=256                                                                                                               |
|   4 - output([E1.HIRE_DATE]), filter([ora_decode(cast(E1.DEPARTMENT_ID, VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )), cast('8080', VARCHAR2(40 BYTE)),             |
|       cast(E1.DEPARTMENT_ID, VARCHAR2(40 BYTE))) = ora_decode(cast(cast(80, NUMBER(4, 0)), VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )), cast('8080', VARCHAR2(40  |
|       BYTE)), cast(cast(80, NUMBER(4, 0)), VARCHAR2(40 BYTE)))]), rowset=256                                                                                        |
|       access([E1.DEPARTMENT_ID], [E1.HIRE_DATE]), partitions(p0)                                                                                                    |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                   |
|       range_key([E1.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
26 rows in set (0.006 sec)

3.2、改寫方式二:LEFT JOIN 邏輯實現

會寫標量子查詢的情況下,改寫成LEFT JOIN 是很簡單的事,邏輯一樣不再贅述。

 SELECT 
  e.FIRST_NAME,
  e.DEPARTMENT_ID,
  e.HIRE_DATE,
  count(e1.HIRE_DATE) + 1 MAX_RN
 FROM EMPLOYEES  e LEFT JOIN EMPLOYEES e1 ON (
  DECODE(e1.DEPARTMENT_ID,NULL,'8080',e1.DEPARTMENT_ID) = DECODE(e.DEPARTMENT_ID,NULL,'8080',e.DEPARTMENT_ID) AND  e1.HIRE_DATE > e.HIRE_DATE)
  WHERE e.DEPARTMENT_ID = 80
  GROUP BY 
    e.FIRST_NAME,
  e.DEPARTMENT_ID,
  e.HIRE_DATE;

  34 rows in set (0.012 sec)


  +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ==========================================================                                                                                                           |
| |ID|OPERATOR                 |NAME |EST.ROWS|EST.TIME(us)|                                                                                                           |
| ----------------------------------------------------------                                                                                                           |
| |0 |HASH GROUP BY            |     |34      |49          |                                                                                                           |
| |1 |└─NESTED-LOOP OUTER JOIN |     |34      |33          |                                                                                                           |
| |2 |  ├─SUBPLAN SCAN         |VIEW1|34      |22          |                                                                                                           |
| |3 |  │ └─HASH GROUP BY      |     |34      |22          |                                                                                                           |
| |4 |  │   └─TABLE FULL SCAN  |E    |34      |10          |                                                                                                           |
| |5 |  └─MATERIAL             |     |1       |11          |                                                                                                           |
| |6 |    └─SUBPLAN SCAN       |VIEW2|1       |11          |                                                                                                           |
| |7 |      └─HASH GROUP BY    |     |1       |11          |                                                                                                           |
| |8 |        └─TABLE FULL SCAN|E1   |1       |11          |                                                                                                           |
| ==========================================================                                                                                                           |
| Outputs & filters:                                                                                                                                                   |
| -------------------------------------                                                                                                                                |
|   0 - output([VIEW1.E.FIRST_NAME], [VIEW1.E.DEPARTMENT_ID], [VIEW1.E.HIRE_DATE], [T_FUN_COUNT_SUM(VIEW1.T_FUN_COUNT(*) * CASE WHEN VIEW2.T_FUN_COUNT(E1.HIRE_DATE)   |
|       IS NOT NULL THEN VIEW2.T_FUN_COUNT(E1.HIRE_DATE) ELSE 0 END) + 1]), filter(nil), rowset=256                                                                    |
|       group([VIEW1.E.HIRE_DATE], [VIEW1.E.FIRST_NAME]), agg_func([T_FUN_COUNT_SUM(VIEW1.T_FUN_COUNT(*) * CASE WHEN VIEW2.T_FUN_COUNT(E1.HIRE_DATE) IS NOT            |
|       NULL THEN VIEW2.T_FUN_COUNT(E1.HIRE_DATE) ELSE 0 END)])                                                                                                        |
|   1 - output([VIEW1.E.HIRE_DATE], [VIEW1.E.FIRST_NAME], [VIEW1.E.DEPARTMENT_ID], [VIEW2.T_FUN_COUNT(E1.HIRE_DATE)], [VIEW1.T_FUN_COUNT(*)]), filter(nil), rowset=256 |
|       conds([VIEW2.E1.HIRE_DATE > VIEW1.E.HIRE_DATE]), nl_params_(nil), use_batch=false                                                                              |
|   2 - output([VIEW1.E.DEPARTMENT_ID], [VIEW1.E.HIRE_DATE], [VIEW1.E.FIRST_NAME], [VIEW1.T_FUN_COUNT(*)]), filter(nil), rowset=256                                    |
|       access([VIEW1.E.DEPARTMENT_ID], [VIEW1.E.HIRE_DATE], [VIEW1.E.FIRST_NAME], [VIEW1.T_FUN_COUNT(*)])                                                             |
|   3 - output([E.DEPARTMENT_ID], [E.HIRE_DATE], [E.FIRST_NAME], [T_FUN_COUNT(*)]), filter(nil), rowset=256                                                            |
|       group([E.HIRE_DATE], [E.FIRST_NAME]), agg_func([T_FUN_COUNT(*)])                                                                                               |
|   4 - output([E.DEPARTMENT_ID], [E.HIRE_DATE], [E.FIRST_NAME]), filter([E.DEPARTMENT_ID = 80]), rowset=256                                                           |
|       access([E.DEPARTMENT_ID], [E.HIRE_DATE], [E.FIRST_NAME]), partitions(p0)                                                                                       |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                    |
|       range_key([E.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                        |
|   5 - output([VIEW2.T_FUN_COUNT(E1.HIRE_DATE)], [VIEW2.E1.HIRE_DATE]), filter(nil), rowset=256                                                                       |
|   6 - output([VIEW2.E1.HIRE_DATE], [VIEW2.T_FUN_COUNT(E1.HIRE_DATE)]), filter(nil), rowset=256                                                                       |
|       access([VIEW2.E1.HIRE_DATE], [VIEW2.T_FUN_COUNT(E1.HIRE_DATE)])                                                                                                |
|   7 - output([E1.HIRE_DATE], [T_FUN_COUNT(E1.HIRE_DATE)]), filter(nil), rowset=256                                                                                   |
|       group([E1.DEPARTMENT_ID], [E1.HIRE_DATE]), agg_func([T_FUN_COUNT(E1.HIRE_DATE)])                                                                               |
|   8 - output([E1.DEPARTMENT_ID], [E1.HIRE_DATE]), filter([ora_decode(cast(E1.DEPARTMENT_ID, VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )), cast('8080',              |
|       VARCHAR2(40 BYTE)), cast(E1.DEPARTMENT_ID, VARCHAR2(40 BYTE))) = ora_decode(cast(cast(80, NUMBER(4, 0)), VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )),        |
|       cast('8080', VARCHAR2(40 BYTE)), cast(cast(80, NUMBER(4, 0)), VARCHAR2(40 BYTE)))]), rowset=256                                                                |
|       access([E1.DEPARTMENT_ID], [E1.HIRE_DATE]), partitions(p0)                                                                                                     |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                    |
|       range_key([E1.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
40 rows in set (0.011 sec)

4、 dense_rank() over() 函式邏輯實現

dense_rank() over() 函式不會跳號,如果符合over()邏輯的資料有兩條的話,那麼這兩條會並列第一,然後第二條資料會遞增加一(即顯示第二)。
這個函式在業務邏輯上也比較少用到,但是比rank()用得多,但是我們也可以嘗試使用其他邏輯來實現dense_rank() over() 函式的功能。
**** 注意:以下實現的dense_rank() over() 邏輯的程式碼效能,會比原來dense_rank() over() 的效能更差,這裡只做功能實現,不考慮效能問題 ****

SELECT * FROM (
SELECT 
  FIRST_NAME,
  DEPARTMENT_ID,
  HIRE_DATE,
   dense_rank() OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE DESC) MAX_RN
 FROM EMPLOYEES )  WHERE DEPARTMENT_ID = 80;

 34 rows in set (0.001 sec)

 +--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ========================================================                                                                                                     |
| |ID|OPERATOR           |NAME     |EST.ROWS|EST.TIME(us)|                                                                                                     |
| --------------------------------------------------------                                                                                                     |
| |0 |WINDOW FUNCTION    |         |107     |115         |                                                                                                     |
| |1 |└─PARTITION SORT   |         |107     |33          |                                                                                                     |
| |2 |  └─TABLE FULL SCAN|EMPLOYEES|107     |9           |                                                                                                     |
| ========================================================                                                                                                     |
| Outputs & filters:                                                                                                                                           |
| -------------------------------------                                                                                                                        |
|   0 - output([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE], [T_WIN_FUN_DENSE_RANK()]), filter(nil), rowset=256                    |
|       win_expr(T_WIN_FUN_DENSE_RANK()), partition_by([EMPLOYEES.DEPARTMENT_ID]), order_by([EMPLOYEES.HIRE_DATE, DESC]), window_type(RANGE), upper(UNBOUNDED  |
|       PRECEDING), lower(UNBOUNDED FOLLOWING)                                                                                                                 |
|   1 - output([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE]), filter(nil), rowset=256                                              |
|       sort_keys([HASH(EMPLOYEES.DEPARTMENT_ID), ASC], [EMPLOYEES.DEPARTMENT_ID, ASC], [EMPLOYEES.HIRE_DATE, DESC])                                           |
|   2 - output([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE]), filter(nil), rowset=256                                              |
|       access([EMPLOYEES.FIRST_NAME], [EMPLOYEES.DEPARTMENT_ID], [EMPLOYEES.HIRE_DATE]), partitions(p0)                                                       |
|       is_index_back=false, is_global_index=false,                                                                                                            |
|       range_key([EMPLOYEES.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.004 sec)

4.1、改寫方式一:標量子查詢邏輯實現

 SELECT 
  e.FIRST_NAME,
  e.DEPARTMENT_ID,
  e.HIRE_DATE,
  (SELECT COUNT(DISTINCT HIRE_DATE) +1 FROM EMPLOYEES e1 WHERE DECODE(e1.DEPARTMENT_ID,NULL,'8080',e1.DEPARTMENT_ID) = DECODE(e.DEPARTMENT_ID,NULL,'8080',e.DEPARTMENT_ID) AND e1.HIRE_DATE > e.HIRE_DATE ) MAX_RN
 FROM EMPLOYEES e WHERE DEPARTMENT_ID = 80 ;

 /* 
 COUNT(DISTINCT HIRE_DATE)  邏輯是求   e1.HIRE_DATE > e.HIRE_DATE 符合這個邏輯的 e1表的 HIRE_DATE 資料,等價 dense_rank。
 */

34 rows in set (0.006 sec)

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =========================================================                                                                                                           |
| |ID|OPERATOR                 |NAME|EST.ROWS|EST.TIME(us)|                                                                                                           |
| ---------------------------------------------------------                                                                                                           |
| |0 |MERGE GROUP BY           |    |34      |29          |                                                                                                           |
| |1 |└─NESTED-LOOP OUTER JOIN |    |34      |22          |                                                                                                           |
| |2 |  ├─TABLE FULL SCAN      |E   |34      |11          |                                                                                                           |
| |3 |  └─MATERIAL             |    |1       |11          |                                                                                                           |
| |4 |    └─TABLE FULL SCAN    |E1  |1       |11          |                                                                                                           |
| =========================================================                                                                                                           |
| Outputs & filters:                                                                                                                                                  |
| -------------------------------------                                                                                                                               |
|   0 - output([E.FIRST_NAME], [E.DEPARTMENT_ID], [E.HIRE_DATE], [T_FUN_COUNT(distinct E1.HIRE_DATE) + 1]), filter(nil), rowset=256                                   |
|       group([E.EMPLOYEE_ID]), agg_func([T_FUN_COUNT(distinct E1.HIRE_DATE)])                                                                                        |
|   1 - output([E.EMPLOYEE_ID], [E.FIRST_NAME], [E.DEPARTMENT_ID], [E.HIRE_DATE], [E1.HIRE_DATE]), filter(nil), rowset=256                                            |
|       conds([E1.HIRE_DATE > E.HIRE_DATE]), nl_params_(nil), use_batch=false                                                                                         |
|   2 - output([E.EMPLOYEE_ID], [E.DEPARTMENT_ID], [E.FIRST_NAME], [E.HIRE_DATE]), filter([E.DEPARTMENT_ID = 80]), rowset=256                                         |
|       access([E.EMPLOYEE_ID], [E.DEPARTMENT_ID], [E.FIRST_NAME], [E.HIRE_DATE]), partitions(p0)                                                                     |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                   |
|       range_key([E.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                       |
|   3 - output([E1.HIRE_DATE]), filter(nil), rowset=256                                                                                                               |
|   4 - output([E1.HIRE_DATE]), filter([ora_decode(cast(E1.DEPARTMENT_ID, VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )), cast('8080', VARCHAR2(40 BYTE)),             |
|       cast(E1.DEPARTMENT_ID, VARCHAR2(40 BYTE))) = ora_decode(cast(cast(80, NUMBER(4, 0)), VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )), cast('8080', VARCHAR2(40  |
|       BYTE)), cast(cast(80, NUMBER(4, 0)), VARCHAR2(40 BYTE)))]), rowset=256                                                                                        |
|       access([E1.DEPARTMENT_ID], [E1.HIRE_DATE]), partitions(p0)                                                                                                    |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                   |
|       range_key([E1.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
26 rows in set (0.006 sec)

4.2、改寫方式二:LEFT JOIN 邏輯實現

會寫標量子查詢的情況下,改寫成LEFT JOIN 是很簡單的事,邏輯一樣不再贅述。

 SELECT 
  e.FIRST_NAME,
  e.DEPARTMENT_ID,
  e.HIRE_DATE,
  count(DISTINCT e1.HIRE_DATE) + 1 MAX_RN
 FROM EMPLOYEES  e LEFT JOIN EMPLOYEES e1 ON (
  DECODE(e1.DEPARTMENT_ID,NULL,'8080',e1.DEPARTMENT_ID) = DECODE(e.DEPARTMENT_ID,NULL,'8080',e.DEPARTMENT_ID) AND  e1.HIRE_DATE > e.HIRE_DATE)
  WHERE e.DEPARTMENT_ID = 80
  GROUP BY 
  e.FIRST_NAME,
  e.DEPARTMENT_ID,
  e.HIRE_DATE;

  34 rows in set (0.006 sec)

  +---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =====================================================                                                                                                               |
| |ID|OPERATOR             |NAME|EST.ROWS|EST.TIME(us)|                                                                                                               |
| -----------------------------------------------------                                                                                                               |
| |0 |MERGE GROUP BY       |    |34      |76          |                                                                                                               |
| |1 |└─PARTITION SORT     |    |34      |70          |                                                                                                               |
| |2 |  └─HASH OUTER JOIN  |    |34      |62          |                                                                                                               |
| |3 |    ├─TABLE FULL SCAN|E   |34      |10          |                                                                                                               |
| |4 |    └─TABLE FULL SCAN|E1  |107     |9           |                                                                                                               |
| =====================================================                                                                                                               |
| Outputs & filters:                                                                                                                                                  |
| -------------------------------------                                                                                                                               |
|   0 - output([E.FIRST_NAME], [E.DEPARTMENT_ID], [E.HIRE_DATE], [T_FUN_COUNT(distinct E1.HIRE_DATE) + 1]), filter(nil), rowset=256                                   |
|       group([E.FIRST_NAME], [E.HIRE_DATE]), agg_func([T_FUN_COUNT(distinct E1.HIRE_DATE)])                                                                          |
|   1 - output([E.FIRST_NAME], [E.HIRE_DATE], [E.DEPARTMENT_ID], [E1.HIRE_DATE]), filter(nil), rowset=256                                                             |
|       sort_keys([HASH(E.FIRST_NAME, E.HIRE_DATE), ASC], [E.FIRST_NAME, ASC], [E.HIRE_DATE, ASC])                                                                    |
|   2 - output([E.FIRST_NAME], [E.HIRE_DATE], [E.DEPARTMENT_ID], [E1.HIRE_DATE]), filter(nil), rowset=256                                                             |
|       equal_conds([ora_decode(cast(E1.DEPARTMENT_ID, VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )), cast('8080', VARCHAR2(40 BYTE)), cast(E1.DEPARTMENT_ID,         |
|        VARCHAR2(40 BYTE))) = ora_decode(cast(E.DEPARTMENT_ID, VARCHAR2(1048576 )), cast(NULL, VARCHAR2(0 )), cast('8080', VARCHAR2(40 BYTE)), cast(E.DEPARTMENT_ID, |
|        VARCHAR2(40 BYTE)))]), other_conds([E1.HIRE_DATE > E.HIRE_DATE])                                                                                             |
|   3 - output([E.DEPARTMENT_ID], [E.HIRE_DATE], [E.FIRST_NAME]), filter([E.DEPARTMENT_ID = 80]), rowset=256                                                          |
|       access([E.DEPARTMENT_ID], [E.HIRE_DATE], [E.FIRST_NAME]), partitions(p0)                                                                                      |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                   |
|       range_key([E.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                       |
|   4 - output([E1.DEPARTMENT_ID], [E1.HIRE_DATE]), filter(nil), rowset=256                                                                                           |
|       access([E1.DEPARTMENT_ID], [E1.HIRE_DATE]), partitions(p0)                                                                                                    |
|       is_index_back=false, is_global_index=false,                                                                                                                   |
|       range_key([E1.EMPLOYEE_ID]), range(MIN ; MAX)always true                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
27 rows in set (0.005 sec)

5、 總結

SQL語句雖然很簡單,但是要培養出SQL思維可不是一件容易的事,還是得多看,多寫,多思考。
這個是要透過不同的開發需求日積月累形成的思維邏輯,而不是一蹴而就。

相關文章