[20220329]是否開發寫錯sql語句.txt

--//春節前寫的http://blog.itpub.net/267265/viewspace-2851445/ => [20220109]開發不應該這樣寫SQL語句.txt
--//節後在最佳化時遇到類似的語句，好煩，我感覺開發有可能其真實的意思表達錯誤。

1.環境：
> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

2.分析：
> @ sql_id cv74fusm9zzwx

--SQL_ID = cv74fusm9zzwx

SELECT MS_CF01.JZXH,
...
    FROM MS_CF01
   WHERE MS_CF01.ZFPB = 0 AND
                 (      MS_CF01.BRID = :al_brid or :al_brid = 0 )AND
                 ( MS_CF01.JZXH = :al_jzxh Or :al_jzxh = 0 ) AND (MS_CF01.JGID = :al_jgid)
;

> @ bind_cap cv74fusm9zzwx ''
SQL_ID        CHILD_NUMBER WAS NAME     POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------- -------- ---------- ------------------- --------------- ------------
cv74fusm9zzwx            0 YES :AL_BRID        1         22 2022-03-29 16:27:23 NUMBER          4XXXYYYY
                           YES :AL_JZXH        3         22 2022-03-29 16:27:23 NUMBER          0
                           YES :AL_JGID        5         22 2022-03-29 16:27:23 NUMBER          3

--//我當時的最佳化總有一個分支選擇全表掃描，今天仔細看我發現開發可能寫錯了。
--//打一個比方加入資料存在brid =1111 , jzxh=2222 這麼一條記錄，帶入這樣的條件肯定能查詢到記錄。
--//而如果帶入:AL_BRID =1111 ,:AL_JZXH = 0 也可以找到1條記錄。
--//而如果帶入:AL_BRID =0 ,:AL_JZXH = 2222 也可以找到1條記錄。
--//而如果帶入:AL_BRID =1111 ,:AL_JZXH = 1111，這樣反而有可能找不到記錄。
--//而如果帶入:AL_BRID =2222 ,:AL_JZXH = 2222，這樣反而有可能找不到記錄。
--//我感覺開發當時一定沒有繞出來，開發想要實現的輸入任何一個值，查詢到記錄。

--//我感覺開發可能真實的意思是執行如下：
SELECT MS_CF01.JZXH,
...
    FROM MS_CF01
   WHERE MS_CF01.ZFPB = 0 AND
                 (      MS_CF01.BRID = :al_brid and :al_brid <> 0 or MS_CF01.JZXH = :al_jzxh and :al_jzxh <> 0 )
            AND (MS_CF01.JGID = :al_jgid)
;

--//:al_brid <> 0之類有點多餘，實際上這樣寫也可以。
SELECT MS_CF01.JZXH,
...
    FROM MS_CF01
   WHERE MS_CF01.ZFPB = 0 AND
                 (      MS_CF01.BRID = :al_brid or MS_CF01.JZXH = :al_jzxh )
            AND (MS_CF01.JGID = :al_jgid)
;

--//我想這才是開發想要實現的功能,實際上不仔細分析自己也很容易繞進去。

3.補充使用提示的最佳化：
--//使用如下提示：

/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(2)) */
Plan hash value: 4210354365
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                             | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |       | 2471 (100)|          |      1 |00:00:00.06 |    9159 |
|   1 | CONCATENATION                        |                |      1 |        |       |            |          |      1 |00:00:00.06 |    9159 |
|* 2 |   FILTER                              |                |      1 |        |       |            |          |      1 |00:00:00.06 |    9159 |
|* 3 |    TABLE ACCESS FULL                  | MS_CF01        |      1 |   3229 | 93641 | 2465   (1)| 00:00:01 |      1 |00:00:00.06 |    9159 |
|* 4 |   FILTER                              |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|* 5 |    TABLE ACCESS BY INDEX ROWID BATCHED| MS_CF01        |      0 |      1 |    29 |     6   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 6 |     INDEX RANGE SCAN                  | I_MS_CF01_BRID |      0 |      3 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------------------------------------

/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(5)) */
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                             | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |       | 2468 (100)|          |      1 |00:00:00.06 |    9159 |
|   1 | CONCATENATION                        |                |      1 |        |       |            |          |      1 |00:00:00.06 |    9159 |
|* 2 |   FILTER                              |                |      1 |        |       |            |          |      1 |00:00:00.06 |    9159 |
|* 3 |    TABLE ACCESS FULL                  | MS_CF01        |      1 |   3231 | 93699 | 2464   (1)| 00:00:01 |      1 |00:00:00.06 |    9159 |
|* 4 |   FILTER                              |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|* 5 |    TABLE ACCESS BY INDEX ROWID BATCHED| MS_CF01        |      0 |      1 |    29 |     4   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
|* 6 |     INDEX RANGE SCAN                  | I_MS_CF01_JZXH |      0 |      1 |       |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------------------------------------------------------------

--// OR_PREDICATES(N) 裡面的N視乎是指謂詞條件出現的順序。要麼採用前面的或條件，要麼採用最後的或條件，注意選擇索引不同。
--//總感覺還不夠智慧，如果ID=3,繼續在拆分就更好了。貼上使用JZXH索引的Predicate Information (identified by operation id):部分：
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:AL_JZXH=0)
   3 - filter((("MS_CF01"."BRID"=:AL_BRID OR :AL_BRID=0) AND "MS_CF01"."ZFPB"=0 AND "MS_CF01"."JGID"=:AL_JGID))
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   4 - filter(LNNVL(:AL_JZXH=0))
   5 - filter((("MS_CF01"."BRID"=:AL_BRID OR :AL_BRID=0) AND "MS_CF01"."ZFPB"=0 AND "MS_CF01"."JGID"=:AL_JGID))
   6 - access("MS_CF01"."JZXH"=:AL_JZXH)

--//ID=3步驟無法再拆分，或者我不知道如何實現。

--//如果寫成如下：
SELECT MS_CF01.JZXH,
...
    FROM MS_CF01
   WHERE
                 (      MS_CF01.BRID = :al_brid or :al_brid = 0 )
               AND MS_CF01.ZFPB = 0
               AND (MS_CF01.JGID = :al_jgid)
               AND ( MS_CF01.JZXH = :al_jzxh Or :al_jzxh = 0 )
;

--//使用以下提示就可以出現上述執行計劃：
/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) */
--//或者
/*+ USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(6)) */

--//順便記錄一下自己在除錯過程的一個錯誤，加入寫成如下：
SELECT
/*+
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(6))
*/
MS_CF01.JZXH,
         MS_CF01.BRID,
         MS_CF01.BRXM,
         MS_CF01.TYBZ
    FROM MS_CF01
--   WHERE MS_CF011.ZFPB = 0 AND
--       ( MS_CF01.BRID = :al_brid or :al_brid = 0 )
--AND (MS_CF01.JGID = :al_jgid)
--AND ( MS_CF01.JZXH = :al_jzxh Or :al_jzxh = 0 )
--;
~~~~~~~
   WHERE
         ( MS_CF01.BRID = :al_brid or :al_brid = 0 )
         and MS_CF01.ZFPB = 0
         AND (MS_CF01.JGID = :al_jgid)
         AND ( MS_CF01.JZXH = :al_jzxh Or :al_jzxh = 0 )
;

--//執行計劃是這樣：
Plan hash value: 2027657538
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation         | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |      1 |        |       | 2464 (100)|          |    332K|00:00:00.09 |   12410 |
|   1 | TABLE ACCESS FULL| MS_CF01 |      1 |    328K| 7386K| 2464   (1)| 00:00:01 |    332K|00:00:00.09 |   12410 |
-----------------------------------------------------------------------------------------------------------------------
--//差點以為我搞當機了呢。實際上註解的分號是有用的。

> @ sql_id 0h81q5v64zpf9
--SQL_ID = 0h81q5v64zpf9
SELECT
/*+
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(6))
*/
MS_CF01.JZXH,
         MS_CF01.BRID,
         MS_CF01.BRXM,
         MS_CF01.TYBZ
    FROM MS_CF01
--   WHERE MS_CF011.ZFPB = 0 AND
--               (      MS_CF01.BRID = :al_brid or :al_brid = 0 )
--AND (MS_CF01.JGID = :al_jgid)
--AND ( MS_CF01.JZXH = :al_jzxh Or :al_jzxh = 0 )
--;

--//注意前面下劃線註解行的分號，實際上分號是其作用的，等於沒有查詢條件，切記！！
--//自己寫一個小例子也可以驗證：
$ cat aa2.txt
select * from dept
--;
where deptno=10;

SCOTT@book> @ aa2.txt
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SP2-0734: unknown command beginning "where dept..." - rest of line ignored.

4.總結：
--//真心建議開發不要玩這樣的技巧，我在許多場合講過，國內的許多專案都是豆腐渣，如果還要加一個字首的話就是豆腐渣中的豆腐渣。
--//如果沒人講，開發者會把這種所謂的技巧從一個專案帶到另外的專案，在真實的環境操作者不會上返回大量資訊中檢視需要的資訊。
--//比如前面的例子如果兩個都輸入0的話，將執行全表掃描。返回全部的結果在實際的環境沒有任何意義。
--//可以這樣的開發者的寫的程式碼越多破壞力越強。

[20220329]是否開發寫錯sql語句.txt

相關文章