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