[20220109]開發不應該這樣寫SQL語句.txt

[20220109]開發不應該這樣寫SQL語句.txt

--//最近一段時間最佳化sql語句,遇到一個語句我開始以為我能夠最佳化它,結果仔細檢查嘗試後發現不行,在測試環境做一個例子演示出來看
--//看.

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試建立:
create table tx as select * from all_objects ;
create index i_tx_object_id on tx(object_id);
create index i_tx_data_object_id on tx(data_object_id);

SCOTT@book> update tx set data_object_id=-1 where data_object_id=0;
4 rows updated.

SCOTT@book> commit ;
Commit complete.
--//取消data_object_id=0的情況。
--//分析表.
SCOTT@book> @ gts tx
Gather Table Statistics for table tx...
PL/SQL procedure successfully completed.

3.語句:
SELECT object_id,data_object_id,object_name
  FROM tx
 WHERE (object_id      = :a or :a = 0)
   AND (data_object_id = :b or :b = 0);

--//正常情況帶入的引數是一個為0,另外1個不為0.透過這樣的方式實現雙查.
--//一開始看到語句我以為我可以最佳化該語句,實際上我做了許多嘗試,最終放棄.
--//這種寫法不知道算不算開發程式設計sql語句的一種技巧,實際上開發應該寫成如下,邏輯即簡單又明瞭.
SELECT object_id,data_object_id,object_name
  FROM tx
 WHERE ( object_id = :a or data_object_id = :b );

--//開發還喜歡寫成如下:
SELECT object_name
  FROM tx
 WHERE ( :v_choice    = 1 AND object_id      = :a)
   OR  ( :v_choice    = 2 AND data_object_id = :b );

4.當然如果不使用繫結變數,以下可以正常使用索引.

SELECT object_id,data_object_id,object_name
  FROM tx
 WHERE (object_id      = 0 or 0 = 0)
   AND (data_object_id = 10 or 10 = 0);

SELECT object_id,data_object_id,object_name
SELECT object_name
  FROM tx
 WHERE (object_id      = 10 or 10 = 0)
   AND (data_object_id = 0 or 0 = 0);

--//我看了以前的最佳化筆記，使用提示寫成如下：
/* Formatted on 2022/01/10 9:49:13 (QP5 v5.269.14213.34769) */
SELECT /*+   USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1) PREDICATE_REORDERS((2 3) (3 2))) */
      object_id, data_object_id, object_name
  FROM tx
 WHERE (object_id = :a OR :a = 0) AND (data_object_id = :b OR :b = 0);

Plan hash value: 3150342875
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |       |   340 (100)|          |      1 |00:00:00.02 |    1218 |
|   1 |  CONCATENATION               |                |      1 |        |       |            |          |      1 |00:00:00.02 |    1218 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TX             |      1 |      1 |    32 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN          | I_TX_OBJECT_ID |      1 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |
|*  4 |   FILTER                     |                |      1 |        |       |            |          |      1 |00:00:00.02 |    1216 |
|*  5 |    TABLE ACCESS FULL         | TX             |      1 |    849 | 27168 |   338   (1)| 00:00:05 |      1 |00:00:00.02 |    1216 |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("DATA_OBJECT_ID"=:B OR :B=0))
   3 - access("OBJECT_ID"=:A)
   4 - filter(:A=0)
   5 - filter((("DATA_OBJECT_ID"=:B OR :B=0) AND LNNVL("OBJECT_ID"=:A)))
 

Plan hash value: 3150342875
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |      1 |        |       |   340 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  CONCATENATION               |                |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TX             |      1 |      1 |    32 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN          | I_TX_OBJECT_ID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  4 |   FILTER                     |                |      1 |        |       |            |          |      0 |00:00:00.01 |       0 |
|*  5 |    TABLE ACCESS FULL         | TX             |      0 |    849 | 27168 |   338   (1)| 00:00:05 |      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------------------------------------------------
--//但是會出現兩種情況，一種情況還是全表掃描。比如帶入 a:=0,b:=10的情況。要出現兩個索引都使用的情況才行。
--//不知道有什麼好的方法解決該問題。