[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的情况。要出现两个索引都使用的情况才行。
--//不知道有什么好的方法解决该问题。