时间：2021-07-01 10:21:17 帮助过：5人阅读
执行计划信息如下：
1 Plan Hash Value : 2018468880 2 3 ------------------------------------------------------------------------------------------------------------------------ 4 | Id | Operation | Name | Rows | Bytes | Cost | Time | 5 ------------------------------------------------------------------------------------------------------------------------ 6 | 0 | SELECT STATEMENT | | 1 | 6096 | 543 | 00:00:07 | 7 | * 1 | COUNT STOPKEY | | | | | | 8 | 2 | VIEW | | 1 | 6096 | 543 | 00:00:07 | 9 | * 3 | SORT GROUP BY STOPKEY | | 1 | 463 | 543 | 00:00:07 | 10 | 4 | NESTED LOOPS SEMI | | 1 | 463 | 542 | 00:00:07 | 11 | 5 | NESTED LOOPS SEMI | | 1 | 405 | 404 | 00:00:05 | 12 | 6 | NESTED LOOPS SEMI | | 1 | 389 | 403 | 00:00:05 | 13 | 7 | NESTED LOOPS | | 1 | 374 | 402 | 00:00:05 | 14 | 8 | NESTED LOOPS | | 1 | 113 | 401 | 00:00:05 | 15 | 9 | NESTED LOOPS | | 1 | 102 | 333 | 00:00:04 | 16 | 10 | SORT UNIQUE | | 187 | 9537 | 16 | 00:00:01 | 17 | * 11 | TABLE ACCESS BY INDEX ROWID | MDM_RELATIONSYNFLAG | 187 | 9537 | 16 | 00:00:01 | 18 | * 12 | INDEX RANGE SCAN | INDEX_RELATIONSYNFLAG_REL | 195 | | 3 | 00:00:01 | 19 | * 13 | TABLE ACCESS BY INDEX ROWID | MDM_HCZXBZ_COMBRELATIONLOG | 1 | 51 | 333 | 00:00:04 | 20 | 14 | BITMAP CONVERSION TO ROWIDS | | | | | | 21 | 15 | BITMAP AND | | | | | | 22 | 16 | BITMAP CONVERSION FROM ROWIDS | | | | | | 23 | * 17 | INDEX RANGE SCAN | INDEX_HCZXBZ_COMBRELATIONLOG_D | 35 | | 1 | 00:00:01 | 24 | 18 | BITMAP CONVERSION FROM ROWIDS | | | | | | 25 | * 19 | INDEX RANGE SCAN | INDEX_HCZXBZ_COMBRELATIONLOG_F | 35 | | 2 | 00:00:01 | 26 | * 20 | TABLE ACCESS BY INDEX ROWID | MDM_HCZXBZ_MODIFYRECORDLOG | 1 | 11 | 68 | 00:00:01 | 27 | * 21 | INDEX RANGE SCAN | MDM_HCZXBZ_MODIFYRECORDLOG_A | 3709 | | 10 | 00:00:01 | 28 | 22 | TABLE ACCESS BY INDEX ROWID | MDM_HCCSGL_CODE | 1 | 261 | 1 | 00:00:01 | 29 | * 23 | INDEX UNIQUE SCAN | PK_HCCSGL_CODE | 1 | | 0 | 00:00:01 | 30 | * 24 | INDEX RANGE SCAN | UK_MDM_HCZXBZ_SYNSEQ_SYNSEQ | 135 | 2025 | 1 | 00:00:01 | 31 | * 25 | INDEX RANGE SCAN | UK_MDM_HCCSGL_SYNSEQ_SYNSEQ | 3380 | 54080 | 1 | 00:00:01 | 32 | * 26 | TABLE ACCESS BY INDEX ROWID | MDM_RELATIONSYNFLAG | 185 | 10730 | 138 | 00:00:02 | 33 | * 27 | INDEX RANGE SCAN | INDEX_RELATIONSYNFLAG_REL | 2044 | | 11 | 00:00:01 | 34 ------------------------------------------------------------------------------------------------------------------------ 35 36 Predicate Information (identified by operation id): 37 ------------------------------------------ 38 * 1 - filter(ROWNUM<20) 39 * 3 - filter(ROWNUM<20) 40 * 11 - filter("K"."ERRORNUM"=0) 41 * 12 - access("K"."RELATIONTABLENAME"=‘SP_DRAWS_LIMSA‘) 42 * 13 - filter("A"."FILTERID" IS NULL AND "A"."COMBSYSCODE"=‘HCCSGL‘ AND "A"."PARENTCOMBSYSCODE"=‘HCCSFA‘ AND "A"."ROOTCODEID"=TO_NUMBER("K"."CODE1")) 43 * 17 - access("A"."PARENTCODEID"=TO_NUMBER("K"."CODE2")) 44 * 19 - access("K"."CODE3"="A"."PARENTCODE") 45 * 20 - filter(TO_NUMBER("F"."MODIFYTYPE")=4 AND "A"."RELATIONID"="F"."RELATIONID") 46 * 21 - access("F"."LIMSA"=1) 47 * 23 - access("A"."CODEID"="B"."CODEID") 48 * 24 - access("SYNSEQ"=334248 AND "X"."CODEID"="A"."ROOTCODEID" AND "X"."SYNFLAG"=‘0‘ AND "X"."ERRORNUM"=0) 49 * 25 - access("X"."SYNSEQ"=334227 AND "X"."CODEID"="A"."CODEID" AND "X"."SYNFLAG"=‘0‘ AND "X"."ERRORNUM"=0) 50 * 26 - filter("K"."CODE4" IS NOT NULL AND "K"."ERRORNUM"=0 AND "A"."ROOTCODEID"=TO_NUMBER("K"."CODE1") AND "A"."PARENTCODEID"=TO_NUMBER("K"."CODE2") AND "A"."CODEID"=TO_NUMBER("K"."CODE3") AND 51 "K"."CODE4"="A"."PARENTCODE") 52 * 27 - access("K"."RELATIONTABLENAME"=‘SP_TESTS_LIMSA‘)
由于查询字段中并没有对表mdm_hczxbz_modifyrecordlog f表字段的查询，可以使用局部范围扫描mdm_hczxbz_modifyrecordlog f表，同时修正上面的类型转换，修改后语句如下：
1 select * 2 from (select distinct a.rootcodeid SP_CODE, 3 a.parentcodeid DRAWNO, 4 a.codeid TESTCODE, 5 a.parentcode PROFILE, 6 b.desc2 SP_TESTNO, 7 b.desc3 SP_TESTNO_ENG, 8 b.remark COMMENTS, 9 b.version VERSION, 10 b.freezeflag STATUS, 11 a.relationid 12 from mdm_hczxbz_combrelationlog a, mdm_hccsgl_code B 13 where a.combsyscode = ‘HCCSGL‘ 14 AND A.PARENTCOMBSYSCODE = ‘HCCSFA‘ 15 and a.codeid = b.codeid 16 and a.filterid is null 17 and exists (select 1 18 from mdm_hczxbz_synflag x 19 where x.codeid = a.rootcodeid 20 and x.synflag = ‘0‘ 21 and x.errornum = 0 22 and synseq = 334248) 23 and exists (select 1 24 from mdm_relationsynflag k 25 where k.code1 = a.rootcodeid 26 and k.code2 = a.parentcodeid 27 and k.code3 = a.parentcode 28 and k.errornum = 0 29 and k.relationtablename = ‘SP_DRAWS_LIMSA‘) 30 and exists (select 1 31 from mdm_hccsgl_synflag x 32 where x.codeid = a.codeid 33 and x.synflag = ‘0‘ 34 and x.errornum = 0 35 and x.synseq = 334227) 36 and exists (select 1 37 from mdm_relationsynflag k 38 where k.code1 = a.rootcodeid 39 and k.code2 = a.parentcodeid 40 and k.code3 = a.codeid 41 and k.code4 = a.parentcode 42 and k.errornum = 0 43 and k.relationtablename = ‘SP_TESTS_LIMSA‘) 44 and exists (select null 45 from mdm_hczxbz_modifyrecordlog f 46 where a.relationid = f.relationid 47 AND f.LIMSA = 1 48 AND f.modifytype = 4)) 49 where rownum < 20
如上，对mdm_hczxbz_modifyrecordlog的查询，改为exists实现了局部范围扫描。这时候的查询计划，cost为437。
Plan Hash Value : 3906393145 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6096 | 437 | 00:00:06 | | * 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 1 | 6096 | 437 | 00:00:06 | | * 3 | SORT GROUP BY STOPKEY | | 1 | 463 | 437 | 00:00:06 | | 4 | NESTED LOOPS SEMI | | 1 | 463 | 436 | 00:00:06 | | 5 | NESTED LOOPS SEMI | | 1 | 405 | 298 | 00:00:04 | | * 6 | HASH JOIN SEMI | | 1 | 389 | 297 | 00:00:04 | | 7 | NESTED LOOPS | | 40 | 13520 | 281 | 00:00:04 | | 8 | NESTED LOOPS | | 67 | 13520 | 281 | 00:00:04 | | * 9 | HASH JOIN SEMI | | 67 | 5159 | 242 | 00:00:03 | | 10 | NESTED LOOPS | | 91 | 5642 | 237 | 00:00:03 | | 11 | NESTED LOOPS | | 126 | 5642 | 237 | 00:00:03 | | 12 | SORT UNIQUE | | 84 | 924 | 68 | 00:00:01 | | * 13 | TABLE ACCESS BY INDEX ROWID | MDM_HCZXBZ_MODIFYRECORDLOG | 84 | 924 | 68 | 00:00:01 | | * 14 | INDEX RANGE SCAN | MDM_HCZXBZ_MODIFYRECORDLOG_A | 3709 | | 10 | 00:00:01 | | * 15 | INDEX RANGE SCAN | INDEX_HCZXBZ_COMBRELATIONLOG_B | 3 | | 1 | 00:00:01 | | * 16 | TABLE ACCESS BY INDEX ROWID | MDM_HCZXBZ_COMBRELATIONLOG | 1 | 51 | 4 | 00:00:01 | | * 17 | INDEX RANGE SCAN | UK_MDM_HCZXBZ_SYNSEQ_SYNSEQ | 135 | 2025 | 5 | 00:00:01 | | * 18 | INDEX UNIQUE SCAN | PK_HCCSGL_CODE | 1 | | 0 | 00:00:01 | | 19 | TABLE ACCESS BY INDEX ROWID | MDM_HCCSGL_CODE | 1 | 261 | 1 | 00:00:01 | | * 20 | TABLE ACCESS BY INDEX ROWID | MDM_RELATIONSYNFLAG | 187 | 9537 | 16 | 00:00:01 | | * 21 | INDEX RANGE SCAN | INDEX_RELATIONSYNFLAG_REL | 195 | | 3 | 00:00:01 | | * 22 | INDEX RANGE SCAN | UK_MDM_HCCSGL_SYNSEQ_SYNSEQ | 3380 | 54080 | 1 | 00:00:01 | | * 23 | TABLE ACCESS BY INDEX ROWID | MDM_RELATIONSYNFLAG | 173 | 10034 | 138 | 00:00:02 | | * 24 | INDEX RANGE SCAN | INDEX_RELATIONSYNFLAG_REL | 2044 | | 11 | 00:00:01 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter(ROWNUM<20) * 3 - filter(ROWNUM<20) * 6 - access("A"."ROOTCODEID"=TO_NUMBER("K"."CODE1") AND "A"."PARENTCODEID"=TO_NUMBER("K"."CODE2") AND "K"."CODE3"="A"."PARENTCODE") * 9 - access("X"."CODEID"="A"."ROOTCODEID") * 13 - filter(TO_NUMBER("F"."MODIFYTYPE")=4) * 14 - access("F"."LIMSA"=1) * 15 - access("A"."RELATIONID"="F"."RELATIONID") * 16 - filter("A"."FILTERID" IS NULL AND "A"."COMBSYSCODE"=‘HCCSGL‘ AND "A"."PARENTCOMBSYSCODE"=‘HCCSFA‘) * 17 - access("SYNSEQ"=334248 AND "X"."SYNFLAG"=‘0‘ AND "X"."ERRORNUM"=0) * 17 - filter("X"."SYNFLAG"=‘0‘ AND "X"."ERRORNUM"=