[20150814]使用use_concat提示.txt
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create view v_deptemp as SELECT dept.dname,dept.loc,emp.*
FROM dept, emp
WHERE dept.deptno = EMP.DEPTNO ;
$ cat az.sql
SELECT
FROM v_deptemp
WHERE (:a = 1 AND v_deptemp.dname = 'SALES')
OR (:a = 2 AND v_deptemp.loc = ' ')
OR (:a = 3 AND v_deptemp.ename = ' ')
OR (:a = 4 AND v_deptemp.empno = 0)
OR (:a = 5 AND v_deptemp.hiredate = '0000/01/01')
OR (:a = 6 AND v_deptemp.sal = 0);
Plan hash value: 4009592485
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| | | | |
| 1 | MERGE JOIN | | 1 | 58 | 7 (15)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 5 | 100 | 3 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 5 | | 1 (0)| 00:00:01 | | | |
|* 4 | FILTER | | | | | | | | |
|* 5 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 6 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
~~~~~~~~~~~~~~~~
2 - SEL$F5BB74E1 / DEPT@SEL$2
3 - SEL$F5BB74E1 / DEPT@SEL$2
6 - SEL$F5BB74E1 / EMP@SEL$2
--很明显这个不是我需要的，假设每个分支都很好。加入提示：
$ cat az.sql
SELECT /*+ use_concat */*
FROM v_deptemp
WHERE (:a = 1 AND v_deptemp.dname = 'SALES')
OR (:a = 2 AND v_deptemp.loc = ' ')
OR (:a = 3 AND v_deptemp.ename = ' ')
OR (:a = 4 AND v_deptemp.empno = 0)
OR (:a = 5 AND v_deptemp.hiredate = '0000/01/01')
OR (:a = 6 AND v_deptemp.sal = 0);
SCOTT@test> @az
no rows selected
SCOTT@test> @dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6tmz7kggcs6xz, child number 0
-------------------------------------
SELECT /*+ use_concat */* FROM v_deptemp WHERE (:a = 1 AND
v_deptemp.dname = 'SALES') OR (:a = 2 AND v_deptemp.loc = ' ')
OR (:a = 3 AND v_deptemp.ename = ' ') OR (:a = 4 AND
v_deptemp.empno = 0) OR (:a = 5 AND v_deptemp.hiredate =
'0000/01/01') OR (:a = 6 AND v_deptemp.sal = 0)
Plan hash value: 1137838836
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| | | | |
| 1 | CONCATENATION | | | | | | | | |
|* 2 | FILTER | | | | | | | | |
| 3 | NESTED LOOPS | | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 58 | 4 (0)| 00:00:01 | | | |
|* 5 | TABLE ACCESS FULL | EMP | 1 | 38 | 3 (0)| 00:00:01 | | | |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 | | | |
|* 8 | FILTER | | | | | | | | |
| 9 | NESTED LOOPS | | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | | | |
|* 12 | INDEX RANGE SCAN | I_EMP_HIREDATE | 1 | | 1 (0)| 00:00:01 | | | |
|* 13 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 | | | |
|* 15 | FILTER | | | | | | | | |
| 16 | NESTED LOOPS | | 1 | 58 | 2 (0)| 00:00:01 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 38 | 1 (0)| 00:00:01 | | | |
|* 18 | INDEX UNIQUE SCAN | PK_EMP_EMPNO | 1 | | 0 (0)| | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | DEPT | 5 | 100 | 1 (0)| 00:00:01 | | | |
|* 20 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | | | |
|* 21 | FILTER | | | | | | | | |
| 22 | NESTED LOOPS | | | | | | | | |
| 23 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 | | | |
|* 24 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | | | |
|* 25 | INDEX RANGE SCAN | I_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | | | |
|* 26 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | | | |
| 27 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 | | | |
|* 28 | FILTER | | | | | | | | |
|* 29 | HASH JOIN | | 2 | 116 | 6 (17)| 00:00:01 | 921K| 921K| |
| 30 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 | | | |
|* 31 | INDEX RANGE SCAN | I_DEPT_LOC | 1 | | 1 (0)| 00:00:01 | | | |
|* 32 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
|* 33 | FILTER | | | | | | | | |
|* 34 | HASH JOIN | | 5 | 290 | 6 (17)| 00:00:01 | 921K| 921K| |
|* 35 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 2 (0)| 00:00:01 | | | |
|* 36 | INDEX RANGE SCAN | I_DEPT_DNAME | 1 | | 1 (0)| 00:00:01 | | | |
|* 37 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
5 - SEL$F5BB74E1_1 / EMP@SEL$2
6 - SEL$F5BB74E1_1 / DEPT@SEL$2
7 - SEL$F5BB74E1_1 / DEPT@SEL$2
11 - SEL$F5BB74E1_2 / EMP@SEL$F5BB74E1_2
12 - SEL$F5BB74E1_2 / EMP@SEL$F5BB74E1_2
13 - SEL$F5BB74E1_2 / DEPT@SEL$F5BB74E1_2
14 - SEL$F5BB74E1_2 / DEPT@SEL$F5BB74E1_2
17 - SEL$F5BB74E1_3 / EMP@SEL$F5BB74E1_3
18 - SEL$F5BB74E1_3 / EMP@SEL$F5BB74E1_3
19 - SEL$F5BB74E1_3 / DEPT@SEL$F5BB74E1_3
20 - SEL$F5BB74E1_3 / DEPT@SEL$F5BB74E1_3
24 - SEL$F5BB74E1_4 / EMP@SEL$F5BB74E1_4
25 - SEL$F5BB74E1_4 / EMP@SEL$F5BB74E1_4
26 - SEL$F5BB74E1_4 / DEPT@SEL$F5BB74E1_4
27 - SEL$F5BB74E1_4 / DEPT@SEL$F5BB74E1_4
30 - SEL$F5BB74E1_5 / DEPT@SEL$F5BB74E1_5
31 - SEL$F5BB74E1_5 / DEPT@SEL$F5BB74E1_5
32 - SEL$F5BB74E1_5 / EMP@SEL$F5BB74E1_5
35 - SEL$F5BB74E1_6 / DEPT@SEL$F5BB74E1_6
36 - SEL$F5BB74E1_6 / DEPT@SEL$F5BB74E1_6
37 - SEL$F5BB74E1_6 / EMP@SEL$F5BB74E1_6
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE_LEAF(@"SEL$F5BB74E1_1")
USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(1))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
OUTLINE_LEAF(@"SEL$F5BB74E1_2")
OUTLINE_LEAF(@"SEL$F5BB74E1_3")
OUTLINE_LEAF(@"SEL$F5BB74E1_4")
OUTLINE_LEAF(@"SEL$F5BB74E1_5")
OUTLINE_LEAF(@"SEL$F5BB74E1_6")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
FULL(@"SEL$F5BB74E1_1" "EMP"@"SEL$2")
INDEX(@"SEL$F5BB74E1_1" "DEPT"@"SEL$2" ("DEPT"."DEPTNO"))
INDEX_RS_ASC(@"SEL$F5BB74E1_2" "EMP"@"SEL$F5BB74E1_2" ("EMP"."HIREDATE"))
INDEX(@"SEL$F5BB74E1_2" "DEPT"@"SEL$F5BB74E1_2" ("DEPT"."DEPTNO"))
INDEX_RS_ASC(@"SEL$F5BB74E1_3" "EMP"@"SEL$F5BB74E1_3" ("EMP"."EMPNO"))
INDEX_RS_ASC(@"SEL$F5BB74E1_3" "DEPT"@"SEL$F5BB74E1_3" ("DEPT"."DEPTNO"))
INDEX_RS_ASC(@"SEL$F5BB74E1_4" "EMP"@"SEL$F5BB74E1_4" ("EMP"."ENAME"))
INDEX(@"SEL$F5BB74E1_4" "DEPT"@"SEL$F5BB74E1_4" ("DEPT"."DEPTNO"))
INDEX_RS_ASC(@"SEL$F5BB74E1_5" "DEPT"@"SEL$F5BB74E1_5" ("DEPT"."LOC"))
FULL(@"SEL$F5BB74E1_5" "EMP"@"SEL$F5BB74E1_5")
INDEX_RS_ASC(@"SEL$F5BB74E1_6" "DEPT"@"SEL$F5BB74E1_6" ("DEPT"."DNAME"))
FULL(@"SEL$F5BB74E1_6" "EMP"@"SEL$F5BB74E1_6")
LEADING(@"SEL$F5BB74E1_1" "EMP"@"SEL$2" "DEPT"@"SEL$2")
LEADING(@"SEL$F5BB74E1_2" "EMP"@"SEL$F5BB74E1_2" "DEPT"@"SEL$F5BB74E1_2")
LEADING(@"SEL$F5BB74E1_3" "EMP"@"SEL$F5BB74E1_3" "DEPT"@"SEL$F5BB74E1_3")
LEADING(@"SEL$F5BB74E1_4" "EMP"@"SEL$F5BB74E1_4" "DEPT"@"SEL$F5BB74E1_4")
LEADING(@"SEL$F5BB74E1_5" "DEPT"@"SEL$F5BB74E1_5" "EMP"@"SEL$F5BB74E1_5")
LEADING(@"SEL$F5BB74E1_6" "DEPT"@"SEL$F5BB74E1_6" "EMP"@"SEL$F5BB74E1_6")
USE_NL(@"SEL$F5BB74E1_1" "DEPT"@"SEL$2")
NLJ_BATCHING(@"SEL$F5BB74E1_1" "DEPT"@"SEL$2")
USE_NL(@"SEL$F5BB74E1_2" "DEPT"@"SEL$F5BB74E1_2")
NLJ_BATCHING(@"SEL$F5BB74E1_2" "DEPT"@"SEL$F5BB74E1_2")
USE_NL(@"SEL$F5BB74E1_3" "DEPT"@"SEL$F5BB74E1_3")
USE_NL(@"SEL$F5BB74E1_4" "DEPT"@"SEL$F5BB74E1_4")
NLJ_BATCHING(@"SEL$F5BB74E1_4" "DEPT"@"SEL$F5BB74E1_4")
USE_HASH(@"SEL$F5BB74E1_5" "EMP"@"SEL$F5BB74E1_5")
USE_HASH(@"SEL$F5BB74E1_6" "EMP"@"SEL$F5BB74E1_6")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:A=6)
5 - filter("EMP"."SAL"=0)
6 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
8 - filter(:A=5)
11 - filter((LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0)))
12 - access("EMP"."HIREDATE"='0000/01/01')
13 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
15 - filter(:A=4)
17 - filter(((LNNVL(:A=5) OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
18 - access("EMP"."EMPNO"=0)
20 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
21 - filter(:A=3)
24 - filter(((LNNVL(:A=4) OR LNNVL("EMP"."EMPNO"=0)) AND (LNNVL(:A=5) OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND
(LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
25 - access("EMP"."ENAME"=' ')
26 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
28 - filter(:A=2)
29 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
31 - access("DEPT"."LOC"=' ')
32 - filter(((LNNVL(:A=3) OR LNNVL("EMP"."ENAME"=' ')) AND (LNNVL(:A=4) OR LNNVL("EMP"."EMPNO"=0)) AND (LNNVL(:A=5)
OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
33 - filter(:A=1)
34 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
35 - filter((LNNVL(:A=2) OR LNNVL("DEPT"."LOC"=' ')))
36 - access("DEPT"."DNAME"='SALES')
37 - filter(((LNNVL(:A=3) OR LNNVL("EMP"."ENAME"=' ')) AND (LNNVL(:A=4) OR LNNVL("EMP"."EMPNO"=0)) AND (LNNVL(:A=5)
OR LNNVL("EMP"."HIREDATE"='0000/01/01')) AND (LNNVL(:A=6) OR LNNVL("EMP"."SAL"=0))))
--这样每个分析能确定索引就ok了。
--好了讲解生产系统遇到的情况：(注:提示是我后面加的)
select /*+ USE_CONCAT */ V_EMR_YW_JZJL.JZHM,
V_EMR_YW_JZJL.BRBH,
V_EMR_YW_JZJL.BRXM,
V_EMR_YW_JZJL.BRXB,
V_EMR_YW_JZJL.CSRQ,
V_EMR_YW_JZJL.BRXZ,
V_EMR_YW_JZJL.KSDM,
V_EMR_YW_JZJL.CWHM,
V_EMR_YW_JZJL.YSDM,
V_EMR_YW_JZJL.HLJB,
V_EMR_YW_JZJL.RYZD,
V_EMR_YW_JZJL.RYQK,
V_EMR_YW_JZJL.RYRQ,
V_EMR_YW_JZJL.CYRQ as outdata,
V_EMR_YW_JZJL.CYBZ as outflag,
V_EMR_YW_JZJL.ZDMC,
V_EMR_YW_JZJL.RYNL AS BRNL,
:"SYS_B_00" AS XQBZ,
:"SYS_B_01" as QFBR,
V_EMR_YW_JZJL.BQDM ,
V_EMR_YW_JZJL.ZKZT ,
V_EMR_YW_JZJL.ZGQK ,
V_EMR_YW_JZJL.ZLXZ as GroupId,
V_EMR_YW_JZJL.JLLX AS VisitType,
:"SYS_B_02" AS OtherFlag,
:"SYS_B_03" AS ChargeTypeName,
:"SYS_B_04" AS ColumnArea,
:"SYS_B_05" AS ZXZT,
:"SYS_B_06" AS BJ,
:"SYS_B_07" AS QCBZ,
:"SYS_B_08" AS CRTX,
:"SYS_B_09" AS KJYW,
V_EMR_YW_JZJL.MZHM,
V_EMR_YW_JZJL.ZYHM,
V_EMR_YW_JZJL.CWHM AS CWHM_NEW,
V_EMR_YW_JZJL.JZXH,/*add lizj 2012.7.24*/
CASE
WHEN REGEXP_LIKE(V_EMR_YW_JZJL.CWHM, :"SYS_B_10", :"SYS_B_11") THEN
lpad(V_EMR_YW_JZJL.CWHM,:"SYS_B_12",:"SYS_B_13")
ELSE lpad(V_EMR_YW_JZJL.CWHM,:"SYS_B_14",:"SYS_B_15")
END AS PLSX,
:"SYS_B_16" AS doctorname ,
V_EMR_YW_JZJL.LGBZ
FROM V_EMR_YW_JZJL
WHERE
( :"SYS_B_17" = :"SYS_B_18" AND V_EMR_YW_JZJL.CYBZ = :"SYS_B_19" AND V_EMR_YW_JZJL.YSDM = :"SYS_B_20" ) OR
( :"SYS_B_21" = :"SYS_B_22" AND V_EMR_YW_JZJL.CYBZ = :"SYS_B_23" AND V_EMR_YW_JZJL.ZLXZ = :"SYS_B_24" AND (:"SYS_B_25" = :"SYS_B_26" OR :"SYS_B_27" IS NULL OR V_EMR_YW_JZJL.YSDM = :"SYS_B_28") ) OR
( :"SYS_B_29" = :"SYS_B_30" AND V_EMR_YW_JZJL.CYBZ = :"SYS_B_31" AND V_EMR_YW_JZJL.KSDM = :"SYS_B_32" AND (:"SYS_B_33" = :"SYS_B_34" OR :"SYS_B_35" IS NULL OR V_EMR_YW_JZJL.YSDM = :"SYS_B_36") ) OR
( :"SYS_B_37" = :"SYS_B_38" AND V_EMR_YW_JZJL.CYBZ = :"SYS_B_39" ) OR
( :"SYS_B_40" = :"SYS_B_41" AND (V_EMR_YW_JZJL.CYBZ = :"SYS_B_42" or ( V_EMR_YW_JZJL.CYBZ = :"SYS_B_43" AND V_EMR_YW_JZJL.CYRQ >= sysdate - :"SYS_B_44"))
AND ( ( EXISTS ( SELECT :"SYS_B_45" FROM YS_ZY_HZJL
WHERE ( V_EMR_YW_JZJL.JZHM = YS_ZY_HZJL.JZHM ) AND
( YS_ZY_HZJL.JSBZ = :"SYS_B_46" OR YS_ZY_HZJL.JSBZ IS NULL) AND
( YS_ZY_HZJL.HZYS LIKE :"SYS_B_47" )
)
)
OR
( EXISTS ( SELECT :"SYS_B_48" FROM YS_ZY_HZSQ, YS_ZY_HZYQ
WHERE ( V_EMR_YW_JZJL.JZHM = YS_ZY_HZSQ.JZHM ) AND
( YS_ZY_HZYQ.SQXH = YS_ZY_HZSQ.SQXH ) AND
((:"SYS_B_49" = :"SYS_B_50") OR
((YS_ZY_HZSQ.JSBZ = :"SYS_B_51" OR YS_ZY_HZSQ.JSBZ IS NULL) AND :"SYS_B_52" = :"SYS_B_53" ) OR
( YS_ZY_HZSQ.JSBZ = :"SYS_B_54" AND :"SYS_B_55" = :"SYS_B_56" )) AND
( YS_ZY_HZSQ.ZFBZ = :"SYS_B_57" OR YS_ZY_HZSQ.ZFBZ IS NULL) AND
( YS_ZY_HZSQ.TJBZ = :"SYS_B_58" ) AND
( YS_ZY_HZYQ.DXLX = :"SYS_B_59" AND YS_ZY_HZYQ.YQDX IN (:"SYS_B_60") )
)
)
)
) OR
( :"SYS_B_61" = :"SYS_B_62" AND V_EMR_YW_JZJL.CYBZ = :"SYS_B_63" AND (V_EMR_YW_JZJL.YSDM = :"SYS_B_64" OR V_EMR_YW_JZJL.KSDM IN (:"SYS_B_65")) AND V_EMR_YW_JZJL.CYRQ >= TO_DATE(:"SYS_B_66",:"SYS_B_67") AND (NOT EXISTS(SELECT * FROM ZY_BRRY WHERE ZY_BRRY.ZYH = CAST(V_EMR_YW_JZJL.JZHM as int) AND ZY_BRRY.CYPB = :"SYS_B_68"))) OR
( :"SYS_B_69" = :"SYS_B_70" AND ( V_EMR_YW_JZJL.CYBZ = :"SYS_B_71" OR (V_EMR_YW_JZJL.CYBZ = :"SYS_B_72" AND V_EMR_YW_JZJL.CYRQ >= TO_DATE(:"SYS_B_73",:"SYS_B_74") ) ) AND
( :"SYS_B_75" = :"SYS_B_76" OR :"SYS_B_77" IS NULL OR V_EMR_YW_JZJL.YSDM = :"SYS_B_78") AND
EXISTS (SELECT :"SYS_B_79" FROM ZY_CWSZ WHERE ZY_CWSZ.ZYH = cast(V_EMR_YW_JZJL.JZHM as int) AND ZY_CWSZ.KSDM = cast(:"SYS_B_80" as int) )
) OR
( :"SYS_B_81" = :"SYS_B_82" AND EXISTS (SELECT :"SYS_B_83" FROM YS_ZY_ZKJL WHERE JZHM = V_EMR_YW_JZJL.JZHM AND YKS IN (:"SYS_B_84") AND ZKSJ >= TO_DATE(:"SYS_B_85",:"SYS_B_86")) ) OR
( :"SYS_B_87" = :"SYS_B_88" AND V_EMR_YW_JZJL.CYBZ = :"SYS_B_89" AND (V_EMR_YW_JZJL.YSDM = :"SYS_B_90") AND V_EMR_YW_JZJL.CYRQ >= TO_DATE(:"SYS_B_91",:"SYS_B_92") AND NOT EXISTS(SELECT * FROM ZY_BRRY WHERE ZY_BRRY.ZYH = CAST(V_EMR_YW_JZJL.JZHM as int) AND ZY_BRRY.CYPB = :"SYS_B_93")) OR
( :"SYS_B_94" = :"SYS_B_95" AND V_EMR_YW_JZJL.JZHM = :"SYS_B_96" )
;
--很恐怖吧！实际上应该程序没有绑定变量，我修改了参数cursor_sharing=force,这样原来的0 =0 变成了 :"SYS_B_94" = :"SYS_B_95"。实际上后面的条件如果带入文字如下：
WHERE
( 0 = 1 AND V_EMR_YW_JZJL.CYBZ = 0 AND V_EMR_YW_JZJL.YSDM = '177731' ) OR
( 0 = 2 AND V_EMR_YW_JZJL.CYBZ = 0 AND V_EMR_YW_JZJL.ZLXZ = '177731' AND ('NULL' = 'NULL' OR 'NULL' IS NULL OR V_EMR_YW_JZJL.YSDM = 'NULL') ) OR
( 0 = 3 AND V_EMR_YW_JZJL.CYBZ = 0 AND V_EMR_YW_JZJL.KSDM = '177731' AND ('NULL' = 'NULL' OR 'NULL' IS NULL OR V_EMR_YW_JZJL.YSDM = 'NULL') ) OR
( 0 = 4 AND V_EMR_YW_JZJL.CYBZ = 0 ) OR
( 0 = 5 AND (V_EMR_YW_JZJL.CYBZ = 0 or ( V_EMR_YW_JZJL.CYBZ = 1 AND V_EMR_YW_JZJL.CYRQ >= sysdate - 2))
AND ( ( EXISTS ( SELECT 00 FROM YS_ZY_HZJL
WHERE ( V_EMR_YW_JZJL.JZHM = YS_ZY_HZJL.JZHM ) AND
( YS_ZY_HZJL.JSBZ = 0 OR YS_ZY_HZJL.JSBZ IS NULL) AND
( YS_ZY_HZJL.HZYS LIKE '177731' )
)
)
OR
( EXISTS ( SELECT 00 FROM YS_ZY_HZSQ, YS_ZY_HZYQ
WHERE ( V_EMR_YW_JZJL.JZHM = YS_ZY_HZSQ.JZHM ) AND
( YS_ZY_HZYQ.SQXH = YS_ZY_HZSQ.SQXH ) AND
((0 = 0) OR
((YS_ZY_HZSQ.JSBZ = 0 OR YS_ZY_HZSQ.JSBZ IS NULL) AND 0 = 1 ) OR
( YS_ZY_HZSQ.JSBZ = 1 AND 0 = 2 )) AND
( YS_ZY_HZSQ.ZFBZ = 0 OR YS_ZY_HZSQ.ZFBZ IS NULL) AND
( YS_ZY_HZSQ.TJBZ = 1 ) AND
( YS_ZY_HZYQ.DXLX = 1 AND YS_ZY_HZYQ.YQDX IN ('0') )
)
)
)
) OR
( 0 = 6 AND V_EMR_YW_JZJL.CYBZ = 1 AND (V_EMR_YW_JZJL.YSDM = '177731' OR V_EMR_YW_JZJL.KSDM IN ('0')) AND V_EMR_YW_JZJL.CYRQ >= TO_DATE('1-01-1900 00:00:00','DD-MM-YYYY HH24:MI:SS') AND (NOT EXISTS(SELECT * FROM ZY_BRRY WHERE ZY_BRRY.ZYH = CAST(V_EMR_YW_JZJL.JZHM as int) AND ZY_BRRY.CYPB = 99))) OR
( 0 = 7 AND ( V_EMR_YW_JZJL.CYBZ = 0 OR (V_EMR_YW_JZJL.CYBZ = 1 AND V_EMR_YW_JZJL.CYRQ >= TO_DATE('1-01-1900 00:00:00','DD-MM-YYYY HH24:MI:SS') ) ) AND
( 'NULL' = 'NULL' OR 'NULL' IS NULL OR V_EMR_YW_JZJL.YSDM = 'NULL') AND
EXISTS (SELECT 00 FROM ZY_CWSZ WHERE ZY_CWSZ.ZYH = cast(V_EMR_YW_JZJL.JZHM as int) AND ZY_CWSZ.KSDM = cast('177731' as int) )
) OR
( 0 = 8 AND EXISTS (SELECT 00 FROM YS_ZY_ZKJL WHERE JZHM = V_EMR_YW_JZJL.JZHM AND YKS IN ('0') AND ZKSJ >= TO_DATE('1-01-1900 00:00:00','DD-MM-YYYY HH24:MI:SS')) ) OR
( 0 = 9 AND V_EMR_YW_JZJL.CYBZ = 1 AND (V_EMR_YW_JZJL.YSDM = '177731') AND V_EMR_YW_JZJL.CYRQ >= TO_DATE('1-01-1900 00:00:00','DD-MM-YYYY HH24:MI:SS') AND NOT EXISTS(SELECT * FROM ZY_BRRY WHERE ZY_BRRY.ZYH = CAST(V_EMR_YW_JZJL.JZHM as int) AND ZY_BRRY.CYPB = 99)) OR
( 0 = 0
AND V_EMR_YW_JZJL.JZHM = '177731' )
;
--很明显这样语句仅仅执行其中1个分支，其它不会执行(短路)，很明显如果这样执行，很浪费共享池，因为原始文字变量。当然在参数
--cursor_sharing=force情况下，可以使用CURSOR_SHARING_EXACT提示来避免转换，这样执行会快一些。
--当时我认为在代码中加入/*+ CURSOR_SHARING_EXACT */是最简单的修改模式。
--不过我还是想尝试使用提示/*+ USE_CONCAT */看看，发现并没有效果。我仔细看了前面的outline生成的内容如下：
USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(1))
--我使用@dpc '' outline检查：
Plan hash value: 33007253
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 5911 (100)| | 1 |00:00:00.27 | 13689 | | | |
|* 1 | FILTER | | 1 | | | | | | 1 |00:00:00.27 | 13689 | | | |
|* 2 | HASH JOIN | | 1 | 175K| 22M| 6376K| 5911 (1)| 00:01:11 | 102K|00:00:00.25 | 13689 | 13M| 3461K| 13M (0)|
| 3 | TABLE ACCESS STORAGE FULL | ZY_BRRY | 1 | 176K| 4303K| | 3201 (1)| 00:00:39 | 176K|00:00:00.11 | 11767 | 1025K| 1025K| |
| 4 | TABLE ACCESS STORAGE FULL | YS_ZY_JZJL | 1 | 175K| 18M| | 1373 (1)| 00:00:17 | 102K|00:00:00.02 | 1922 | 1025K| 1025K| |
|* 5 | TABLE ACCESS BY INDEX ROWID | YS_ZY_ZKJL | 0 | 1 | 19 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 6 | INDEX RANGE SCAN | IDX_YS_ZY_ZKJL_JZHM | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 7 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZJL | 0 | 1 | 153 | | 0 (0)| | 0 |00:00:00.01 | 0 | | | |
|* 8 | INDEX RANGE SCAN | IDX_YS_ZY_HZJL_HZYS | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 9 | NESTED LOOPS | | 0 | 1 | 33 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 10 | NESTED LOOPS | | 0 | 1 | 33 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| YS_ZY_HZSQ | 0 | 1 | 21 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 12 | INDEX RANGE SCAN | IDX_YS_ZY_HZSQ_JZHM | 0 | 3 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 13 | INDEX RANGE SCAN | IDX_YS_ZY_HZYQ_SQXH | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 14 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZYQ | 0 | 1 | 12 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | ZY_CWSZ | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 16 | INDEX RANGE SCAN | I_ZY_CWSZ_ZYH | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 17 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 18 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 19 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 20 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
3 - SEL$F5BB74E1 / ZY_BRRY@SEL$2
4 - SEL$F5BB74E1 / YS_ZY_JZJL@SEL$2
5 - SEL$7 / YS_ZY_ZKJL@SEL$7
6 - SEL$7 / YS_ZY_ZKJL@SEL$7
7 - SEL$3 / YS_ZY_HZJL@SEL$3
8 - SEL$3 / YS_ZY_HZJL@SEL$3
9 - SEL$4
11 - SEL$4 / YS_ZY_HZSQ@SEL$4
12 - SEL$4 / YS_ZY_HZSQ@SEL$4
13 - SEL$4 / YS_ZY_HZYQ@SEL$4
14 - SEL$4 / YS_ZY_HZYQ@SEL$4
15 - SEL$6 / ZY_CWSZ@SEL$6
16 - SEL$6 / ZY_CWSZ@SEL$6
17 - SEL$5 / ZY_BRRY@SEL$5
18 - SEL$5 / ZY_BRRY@SEL$5
19 - SEL$8 / ZY_BRRY@SEL$8
20 - SEL$8 / ZY_BRRY@SEL$8
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$7")
OUTLINE_LEAF(@"SEL$8")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "YS_ZY_JZJL"@"SEL$2")
FULL(@"SEL$F5BB74E1" "ZY_BRRY"@"SEL$2")
LEADING(@"SEL$F5BB74E1" "YS_ZY_JZJL"@"SEL$2" "ZY_BRRY"@"SEL$2")
USE_HASH(@"SEL$F5BB74E1" "ZY_BRRY"@"SEL$2")
SWAP_JOIN_INPUTS(@"SEL$F5BB74E1" "ZY_BRRY"@"SEL$2")
INDEX_RS_ASC(@"SEL$8" "ZY_BRRY"@"SEL$8" ("ZY_BRRY"."ZYH"))
INDEX_RS_ASC(@"SEL$7" "YS_ZY_ZKJL"@"SEL$7" ("YS_ZY_ZKJL"."JZHM"))
INDEX_RS_ASC(@"SEL$6" "ZY_CWSZ"@"SEL$6" ("ZY_CWSZ"."ZYH"))
INDEX_RS_ASC(@"SEL$5" "ZY_BRRY"@"SEL$5" ("ZY_BRRY"."ZYH"))
INDEX_RS_ASC(@"SEL$4" "YS_ZY_HZSQ"@"SEL$4" ("YS_ZY_HZSQ"."JZHM"))
INDEX(@"SEL$4" "YS_ZY_HZYQ"@"SEL$4" ("YS_ZY_HZYQ"."SQXH"))
LEADING(@"SEL$4" "YS_ZY_HZSQ"@"SEL$4" "YS_ZY_HZYQ"@"SEL$4")
USE_NL(@"SEL$4" "YS_ZY_HZYQ"@"SEL$4")
NLJ_BATCHING(@"SEL$4" "YS_ZY_HZYQ"@"SEL$4")
INDEX_RS_ASC(@"SEL$3" "YS_ZY_HZJL"@"SEL$3" ("YS_ZY_HZJL"."HZYS"))
END_OUTLINE_DATA
*/
--我发现一个奇怪的现象： 1 - SEL$F5BB74E1，与前面的别名是一样的。仔细看前面的下划线~部分。
--我把语句修改如下：(我不理解后面的8 ... 什么意思，反正我可以一个一个测试,我在itpub问过没人解答)
-- select /*+ USE_CONCAT */ V_EMR_YW_JZJL.JZHM,
select /*+ USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(1)) */ V_EMR_YW_JZJL.JZHM,
V_EMR_YW_JZJL.BRBH,
V_EMR_YW_JZJL.BRXM,
...
--执行以后，奇迹出现了!
Plan hash value: 4080391344
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 23696 (100)| | 1 |00:00:00.01 | 6 | | | |
| 1 | CONCATENATION | | 1 | | | | | | 1 |00:00:00.01 | 6 | | | |
|* 2 | FILTER | | 1 | | | | | | 1 |00:00:00.01 | 6 | | | |
| 3 | NESTED LOOPS | | 1 | 1 | 137 | | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 1 | 1 | 112 | | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 5 | INDEX UNIQUE SCAN | PK_YS_ZY_JZJL | 1 | 1 | | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
| 6 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 1 | 1 | 25 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | | | |
|* 7 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 1 | 1 | | | 0 (0)| | 1 |00:00:00.01 | 2 | 1025K| 1025K| |
|* 8 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 9 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 10 | NESTED LOOPS | | 0 | 81 | 11097 | | 236 (0)| 00:00:03 | 0 |00:00:00.01 | 0 | | | |
| 11 | NESTED LOOPS | | 0 | 81 | 11097 | | 236 (0)| 00:00:03 | 0 |00:00:00.01 | 0 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 0 | 81 | 9072 | | 155 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 14 | BITMAP AND | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 15 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 16 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_YSDM | 0 | 247 | | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 17 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 18 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_CYBZ | 0 | 247 | | | 132 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 19 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 20 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 25 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 21 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 22 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 23 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 24 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 25 | HASH JOIN | | 0 | 175K| 22M| 6376K| 5909 (1)| 00:01:11 | 0 |00:00:00.01 | 0 | 16M| 6201K| |
| 26 | TABLE ACCESS STORAGE FULL | ZY_BRRY | 0 | 176K| 4303K| | 3201 (1)| 00:00:39 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 27 | TABLE ACCESS STORAGE FULL | YS_ZY_JZJL | 0 | 175K| 18M| | 1371 (1)| 00:00:17 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 28 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 29 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 30 | TABLE ACCESS BY INDEX ROWID | YS_ZY_ZKJL | 0 | 1 | 19 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 31 | INDEX RANGE SCAN | IDX_YS_ZY_ZKJL_JZHM | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 32 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 33 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 34 | HASH JOIN | | 0 | 5763 | 771K| | 4572 (1)| 00:00:55 | 0 |00:00:00.01 | 0 | 2529K| 2248K| |
|* 35 | TABLE ACCESS STORAGE FULL | YS_ZY_JZJL | 0 | 5763 | 630K| | 1370 (1)| 00:00:17 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 36 | TABLE ACCESS STORAGE FULL | ZY_BRRY | 0 | 176K| 4303K| | 3201 (1)| 00:00:39 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 37 | TABLE ACCESS BY INDEX ROWID | YS_ZY_ZKJL | 0 | 1 | 19 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 38 | INDEX RANGE SCAN | IDX_YS_ZY_ZKJL_JZHM | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 39 | TABLE ACCESS BY INDEX ROWID | ZY_CWSZ | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 40 | INDEX RANGE SCAN | I_ZY_CWSZ_ZYH | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 41 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 42 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 43 | NESTED LOOPS | | 0 | 1093 | 146K| | 1480 (1)| 00:00:18 | 0 |00:00:00.01 | 0 | | | |
| 44 | NESTED LOOPS | | 0 | 1093 | 146K| | 1480 (1)| 00:00:18 | 0 |00:00:00.01 | 0 | | | |
|* 45 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 0 | 1093 | 119K| | 387 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
| 46 | BITMAP CONVERSION TO ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 47 | BITMAP AND | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 48 | BITMAP OR | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 49 | BITMAP CONVERSION FROM ROWIDS| | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 50 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_YSDM | 0 | 58378 | | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 51 | BITMAP CONVERSION FROM ROWIDS| | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 52 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_KSDM | 0 | 58378 | | | 8 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 53 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 54 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_CYBZ | 0 | 58378 | | | 132 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 55 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 56 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 25 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 57 | TABLE ACCESS BY INDEX ROWID | ZY_CWSZ | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 58 | INDEX RANGE SCAN | I_ZY_CWSZ_ZYH | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 59 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 60 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 61 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 62 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 63 | HASH JOIN | | 0 | 58444 | 7819K| 6376K| 5226 (1)| 00:01:03 | 0 |00:00:00.01 | 0 | 16M| 6201K| |
| 64 | TABLE ACCESS STORAGE FULL | ZY_BRRY | 0 | 176K| 4303K| | 3201 (1)| 00:00:39 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 65 | TABLE ACCESS STORAGE FULL | YS_ZY_JZJL | 0 | 58444 | 6392K| | 1371 (1)| 00:00:17 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 66 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 8 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 67 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 68 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZJL | 0 | 1 | 153 | | 0 (0)| | 0 |00:00:00.01 | 0 | | | |
|* 69 | INDEX RANGE SCAN | IDX_YS_ZY_HZJL_HZYS | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 70 | NESTED LOOPS | | 0 | 1 | 33 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 71 | NESTED LOOPS | | 0 | 1 | 33 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 72 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZSQ | 0 | 1 | 21 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 73 | INDEX RANGE SCAN | IDX_YS_ZY_HZSQ_JZHM | 0 | 3 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 74 | INDEX RANGE SCAN | IDX_YS_ZY_HZYQ_SQXH | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 75 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZYQ | 0 | 1 | 12 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 76 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 77 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 78 | HASH JOIN | | 0 | 58378 | 7810K| 6376K| 5174 (1)| 00:01:03 | 0 |00:00:00.01 | 0 | 16M| 6201K| |
| 79 | TABLE ACCESS STORAGE FULL | ZY_BRRY | 0 | 176K| 4303K| | 3201 (1)| 00:00:39 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 80 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 0 | 58378 | 6385K| | 1320 (1)| 00:00:16 | 0 |00:00:00.01 | 0 | | | |
|* 81 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_CYBZ | 0 | 58378 | | | 132 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 82 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZJL | 0 | 1 | 153 | | 0 (0)| | 0 |00:00:00.01 | 0 | | | |
|* 83 | INDEX RANGE SCAN | IDX_YS_ZY_HZJL_HZYS | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 84 | NESTED LOOPS | | 0 | 1 | 33 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
| 85 | NESTED LOOPS | | 0 | 1 | 33 | | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 86 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZSQ | 0 | 1 | 21 | | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 87 | INDEX RANGE SCAN | IDX_YS_ZY_HZSQ_JZHM | 0 | 3 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 88 | INDEX RANGE SCAN | IDX_YS_ZY_HZYQ_SQXH | 0 | 1 | | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|* 89 | TABLE ACCESS BY INDEX ROWID | YS_ZY_HZYQ | 0 | 1 | 12 | | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|* 90 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 91 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 92 | NESTED LOOPS | | 0 | 69 | 9453 | | 466 (0)| 00:00:06 | 0 |00:00:00.01 | 0 | | | |
| 93 | NESTED LOOPS | | 0 | 69 | 9453 | | 466 (0)| 00:00:06 | 0 |00:00:00.01 | 0 | | | |
|* 94 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 0 | 69 | 7728 | | 397 (0)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
| 95 | BITMAP CONVERSION TO ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 96 | BITMAP AND | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 97 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|* 98 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_KSDM | 0 | 58378 | | | 9 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 99 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|*100 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_CYBZ | 0 | 58378 | | | 132 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|*101 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 102 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 25 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|*103 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|*104 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 105 | NESTED LOOPS | | 0 | 51 | 6987 | | 383 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
| 106 | NESTED LOOPS | | 0 | 51 | 6987 | | 383 (1)| 00:00:05 | 0 |00:00:00.01 | 0 | | | |
|*107 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 0 | 51 | 5712 | | 332 (1)| 00:00:04 | 0 |00:00:00.01 | 0 | | | |
| 108 | BITMAP CONVERSION TO ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 109 | BITMAP AND | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 110 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|*111 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_ZLXZ | 0 | 2576 | | | 7 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 112 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|*113 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_CYBZ | 0 | 2576 | | | 132 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|*114 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 115 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 25 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
|*116 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|*117 | FILTER | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 118 | NESTED LOOPS | | 0 | 82 | 11234 | | 237 (0)| 00:00:03 | 0 |00:00:00.01 | 0 | | | |
| 119 | NESTED LOOPS | | 0 | 82 | 11234 | | 237 (0)| 00:00:03 | 0 |00:00:00.01 | 0 | | | |
|*120 | TABLE ACCESS BY INDEX ROWID | YS_ZY_JZJL | 0 | 82 | 9184 | | 155 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | | | |
| 121 | BITMAP CONVERSION TO ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 122 | BITMAP AND | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
| 123 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|*124 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_YSDM | 0 | 247 | | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 125 | BITMAP CONVERSION FROM ROWIDS | | 0 | | | | | | 0 |00:00:00.01 | 0 | | | |
|*126 | INDEX RANGE SCAN | IDX_YS_ZY_JZJL_CYBZ | 0 | 247 | | | 132 (0)| 00:00:02 | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
|*127 | INDEX UNIQUE SCAN | PK_ZY_BRRY | 0 | 1 | | | 0 (0)| | 0 |00:00:00.01 | 0 | 1025K| 1025K| |
| 128 | TABLE ACCESS BY INDEX ROWID | ZY_BRRY | 0 | 1 | 25 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--^_^。我运气好，猜对了。
--下面的剩下的就是使用sql profile交换执行计划就ok了。
--另外我把参数 /*+ USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(1)) */里面的8换成1,2,3,4,5,6,7。
--outline看到的提示是：
USE_CONCAT(@"SEL$F5BB74E1" OR_PREDICATES(1))
--如果8换成8,9,10,..99,注：没有全部测试，仅仅测试部分数字。
--outline看到的提示是：
USE_CONCAT(@"SEL$F5BB74E1" 8 OR_PREDICATES(1))
--如果8换成100
--outline看到的提示是：
USE_CONCAT(@"SEL$F5BB74E1" OR_PREDICATES(1))
--估计8表示某种级别,而且执行计划也没有变化。不知道那位知道告诉我。
