[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的话，将执行全表扫描。返回全部的结果在实际的环境没有任何意义。
--//可以这样的开发者的写的代码越多破坏力越强。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2884848/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
