这篇文章主要介绍了exist如何改写为left join，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。
--3vm7pkp5cb69g
select * from gv$sql where sql_id = '3vm7pkp5cb69g';
explain plan for
SELECT GS.ORG_ID,
GS.ORGANIZATION_ID,
GS.SAMPLE_ID,
GS.SAMPLE_NO,
GS.SAMPLE_DESC,
GS.LOT_NUMBER,
GS.INVENTORY_ITEM_ID,
GS.DATE_DRAWN,
MSI.SEGMENT1 ITEM_NUM,
MSI.DESCRIPTION ITEM_NAME,
P1.LAST_NAME SAMPLER_USER,
GH.MEANING HEADER_STATUS,
GR.RESULT_ID,
GR.TEST_ID,
GR.SEQ,
GQT.TEST_CODE,
GQT.TEST_DESC,
GST.MIN_VALUE_NUM,
GST.TARGET_VALUE_NUM,
GST.MAX_VALUE_NUM,
GR.RESULT_VALUE_NUM,
GR.RESULT_DATE,
P2.LAST_NAME TESTER_USER,
GL.MEANING END_RESULT,
COUNT(1) OVER(PARTITION BY GS.SAMPLE_ID) SL
FROM GMD_SAMPLES GS,
GMD_RESULTS GR,
GEM_LOOKUPS GL,
GEM_LOOKUPS GH,
GMD_SAMPLE_SPEC_DISP SSD,
GMD_EVENT_SPEC_DISP GES,
GMD_SAMPLING_EVENTS SE,
GMD_SPEC_TESTS GST,
GMD_SPECIFICATIONS GSP,
GMD_SPEC_RESULTS GSR,
GMD_QC_TESTS GQT,
MTL_SYSTEM_ITEMS_VL MSI,
(SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
FROM PER_ALL_PEOPLE_F P, FND_USER F
WHERE P.PERSON_ID = F.EMPLOYEE_ID
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P1,
(SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
FROM PER_ALL_PEOPLE_F P, FND_USER F
WHERE P.PERSON_ID = F.EMPLOYEE_ID
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P2
WHERE GR.SAMPLE_ID = GS.SAMPLE_ID
AND (GS.SAMPLE_TYPE = 'I')
AND GS.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
AND GES.SPEC_ID = GST.SPEC_ID
AND GR.TEST_ID = GST.TEST_ID
AND GR.TEST_ID = GQT.TEST_ID
AND GS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND GS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND GL.LOOKUP_TYPE = 'GMD_QC_EVALUATION'
AND GL.LOOKUP_CODE = GSR.EVALUATION_IND
AND GH.LOOKUP_CODE = SSD.DISPOSITION
AND GH.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
AND GSR.RESULT_ID = GR.RESULT_ID
AND GS.SAMPLER_ID = P1.USER_ID
AND GR.TESTER_ID = P2.USER_ID
AND GES.EVENT_SPEC_DISP_ID = SSD.EVENT_SPEC_DISP_ID
AND SSD.SAMPLE_ID = GS.SAMPLE_ID
AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND SE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
AND GST.TEST_ID = GR.TEST_ID
AND GES.SPEC_ID = GSP.SPEC_ID
AND GES.SPEC_ID = GST.SPEC_ID
AND EXISTS (SELECT 1
FROM MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_V MCS,
MTL_CATEGORIES MC
WHERE MIC.ORGANIZATION_ID = GS.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = GS.INVENTORY_ITEM_ID
AND MCS.STRUCTURE_ID = 101
AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MC.SEGMENT1 = '15')
AND GS.ORGANIZATION_ID = 1083
AND GS.DATE_DRAWN BETWEEN to_date('09/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND to_date('09/11/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND GS.INVENTORY_ITEM_ID = NVL(null, GS.INVENTORY_ITEM_ID)
AND GS.LOT_NUMBER = NVL(null, GS.LOT_NUMBER)
AND GS.SAMPLE_NO = NVL(null, GS.SAMPLE_NO);
select * from table(dbms_xplan.display);
select name,WAS_CAPTURED,DATATYPE_STRING,VALUE_STRING,INST_ID from
gv$sql_bind_capture where sql_id='3vm7pkp5cb69g';
Plan hash value: 4065178589
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 576 | 14067 (3)| 00:00:01 |
| 1 | WINDOW SORT | | 1 | 576 | 14067 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 576 | 14066 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 576 | 14066 (3)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 503 | 14063 (3)| 00:00:01 |
| 5 | JOIN FILTER CREATE | :BF0000 | 1 | 494 | 94 (2)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 494 | 94 (2)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 494 | 94 (2)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 421 | 91 (2)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 395 | 90 (2)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 364 | 89 (2)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 333 | 86 (2)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 323 | 85 (2)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 309 | 84 (2)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 291 | 82 (2)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 256 | 79 (2)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 225 | 76 (2)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 215 | 75 (2)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 209 | 74 (2)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 200 | 73 (2)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 195 | 73 (2)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 179 | 71 (2)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 160 | 68 (2)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 121 | 66 (2)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 99 | 64 (2)| 00:00:01 |
| 25 | VIEW | VW_SQ_1 | 1 | 10 | 20 (0)| 00:00:01 |
| 26 | HASH UNIQUE | | 1 | 251 | | |
| 27 | NESTED LOOPS SEMI | | 1 | 251 | 20 (0)| 00:00:01 |
| 28 | NESTED LOOPS SEMI | | 1 | 226 | 20 (0)| 00:00:01 |
| 29 | NESTED LOOPS | | 1 | 200 | 20 (0)| 00:00:01 |
| 30 | MERGE JOIN CARTESIAN | | 3 | 540 | 9 (0)| 00:00:01 |
| 31 | NESTED LOOPS SEMI | | 1 | 150 | 4 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 1 | 94 | 2 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 1 | 64 | 1 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_U1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_TL_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_B | 1 | 30 | 1 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | MTL_CATEGORY_SETS_B_N1 | 1 | | 0 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 56 | 2 (0)| 00:00:01 |
| 39 | BUFFER SORT | | 9 | 270 | 7 (0)| 00:00:01 |
|* 40 | BATCHED TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B | 9 | 270 | 5 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | MTL_CATEGORIES_B_N1 | 57 | | 1 (0)| 00:00:01 |
| 42 | TCHED TABLE ACCESS BY INDEX ROWID BA | MTL_ITEM_CATEGORIES | 1 | 20 | 7 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_N3 | 19 | | 2 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_TL_U1 | 17 | 442 | 0 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL_U1 | 2100 | 52500 | 0 (0)| 00:00:01 |
|* 46 | TABLE ACCESS BY INDEX ROWID BATCHED | GMD_SAMPLES | 1 | 89 | 43 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | GMD_SAMPLES_N2 | 49 | | 2 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 22 | 2 (0)| 00:00:01 |
|* 49 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 |
| 50 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_TL | 1 | 39 | 2 (0)| 00:00:01 |
|* 51 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_TL_U1 | 1 | | 1 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID BATCHED | GMD_EVENT_SPEC_DISP | 1 | 19 | 3 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | GMD_EVENT_SPEC_DISP_N1 | 1 | | 2 (0)| 00:00:01 |
| 54 | TABLE ACCESS BY INDEX ROWID | GMD_SAMPLE_SPEC_DISP | 1 | 16 | 2 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | GMD_SAMPLE_SPEC_DISP_PK | 1 | | 1 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 5 | 0 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_TL_PK | 1 | 9 | 1 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | GMD_SAMPLING_EVENTS_PK | 1 | 6 | 1 (0)| 00:00:01 |
| 59 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
| 61 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 62 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
|* 63 | TABLE ACCESS BY INDEX ROWID BATCHED | GMD_RESULTS | 4 | 140 | 3 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | GMD_RESULTS_N1 | 5 | | 2 (0)| 00:00:01 |
| 65 | TABLE ACCESS BY INDEX ROWID | GMD_SPEC_TESTS_B | 1 | 18 | 2 (0)| 00:00:01 |
|* 66 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_B_PK | 1 | | 1 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_TL_PK | 1 | 14 | 1 (0)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 69 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
| 70 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
| 72 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_B | 1 | 31 | 1 (0)| 00:00:01 |
|* 73 | INDEX UNIQUE SCAN | GMD_QC_TESTS_B_PK | 1 | | 0 (0)| 00:00:01 |
| 74 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_TL | 1 | 26 | 1 (0)| 00:00:01 |
|* 75 | INDEX UNIQUE SCAN | GMD_QC_TESTS_TL_PK | 1 | | 0 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 2 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 73 | 3 (0)| 00:00:01 |
| 78 | JOIN FILTER USE | :BF0000 | 4965K| 42M| 13909 (2)| 00:00:01 |
|* 79 | TABLE ACCESS STORAGE FULL | GMD_SPEC_RESULTS | 4965K| 42M| 13909 (2)| 00:00:01 |
|* 80 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 2 (0)| 00:00:01 |
| 81 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 73 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
34 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
35 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
"ZD_EDITION_NAME"='V_20151118_1137')
36 - filter("ZD_EDITION_NAME"='V_20151118_1137')
37 - access("STRUCTURE_ID"=101)
38 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
40 - filter("STRUCTURE_ID"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
41 - access("SEGMENT1"='15')
43 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "MIC"."CATEGORY_ID"="CATEGORY_ID" AND "MIC"."ORGANIZATION_ID"=1083)
44 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
45 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
46 - filter("GS"."DATE_DRAWN">=TO_DATE(' 2017-09-01 00:00:00', 'syyyy-mm-dd hh34:mi:ss') AND "ITEM_1"="GS"."ORGANIZATION_ID" AND
"GS"."ORGANIZATION_ID"=1083 AND "GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd
hh34:mi:ss'))
47 - access("ITEM_2"="GS"."INVENTORY_ITEM_ID")
filter("GS"."LOT_NUMBER" IS NOT NULL AND "GS"."INVENTORY_ITEM_ID" IS NOT NULL)
49 - access("GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=1083)
51 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
52 - filter("GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
53 - access("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
55 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID" AND "SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
56 - access("GES"."SPEC_ID"="B"."SPEC_ID")
57 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
58 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
60 - access("GS"."SAMPLER_ID"="F"."USER_ID")
62 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
63 - filter("GR"."TESTER_ID" IS NOT NULL)
64 - access("GR"."SAMPLE_ID"="GS"."SAMPLE_ID")
66 - access("GES"."SPEC_ID"="B"."SPEC_ID" AND "GR"."TEST_ID"="B"."TEST_ID")
67 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
69 - access("GR"."TESTER_ID"="F"."USER_ID")
71 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
73 - access("GR"."TEST_ID"="B"."TEST_ID")
75 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
76 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="SSD"."DISPOSITION" AND
"SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LOOKUP_CODE"="SSD"."DISPOSITION" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
79 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
80 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="GSR"."EVALUATION_IND" AND
"SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LOOKUP_CODE"="GSR"."EVALUATION_IND" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
exsits 导致产生 笛卡尔积 改写成 left join
消除了笛卡尔积，去null
Plan hash value: 1210765236
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 817 | 14062 (3)| 00:00:01 |
| 1 | WINDOW SORT | | 1 | 817 | 14062 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 817 | 14061 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 761 | 14059 (3)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 730 | 14056 (3)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 720 | 14055 (3)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 694 | 14054 (3)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 663 | 14053 (3)| 00:00:01 |
|* 8 | HASH JOIN | | 1 | 628 | 14051 (3)| 00:00:01 |
| 9 | JOIN FILTER CREATE | :BF0000 | 1 | 619 | 82 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 619 | 82 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 619 | 82 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 546 | 79 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 540 | 78 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 524 | 76 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 510 | 75 (0)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 492 | 72 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 483 | 71 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 478 | 71 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 459 | 68 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 428 | 65 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 418 | 64 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 379 | 62 (0)| 00:00:01 |
| 23 | NESTED LOOPS | | 1 | 357 | 60 (0)| 00:00:01 |
| 24 | NESTED LOOPS | | 1 | 268 | 17 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 1 | 248 | 10 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 1 | 223 | 10 (0)| 00:00:01 |
| 27 | MERGE JOIN CARTESIAN | | 1 | 197 | 10 (0)| 00:00:01 |
| 28 | MERGE JOIN CARTESIAN | | 1 | 167 | 5 (0)| 00:00:01 |
| 29 | NESTED LOOPS | | 1 | 137 | 4 (0)| 00:00:01 |
| 30 | NESTED LOOPS | | 1 | 64 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_U1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_TL_U1 | 1 | 34 | 0 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 73 | 3 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 2 (0)| 00:00:01 |
| 35 | BUFFER SORT | | 1 | 30 | 2 (0)| 00:00:01 |
|* 36 | TCHED TABLE ACCESS BY INDEX ROWID BA | MTL_CATEGORY_SETS_B | 1 | 30 | 1 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | MTL_CATEGORY_SETS_B_N1 | 1 | | 0 (0)| 00:00:01 |
| 38 | BUFFER SORT | | 9 | 270 | 9 (0)| 00:00:01 |
|* 39 | CHED TABLE ACCESS BY INDEX ROWID BAT | MTL_CATEGORIES_B | 9 | 270 | 5 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | MTL_CATEGORIES_B_N1 | 57 | | 1 (0)| 00:00:01 |
|* 41 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_TL_U1 | 1 | 26 | 0 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL_U1 | 1 | 25 | 0 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_ITEM_CATEGORIES | 1 | 20 | 7 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_N3 | 19 | | 2 (0)| 00:00:01 |
|* 45 | TABLE ACCESS BY INDEX ROWID BATCHED | GMD_SAMPLES | 1 | 89 | 43 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | GMD_SAMPLES_N2 | 49 | | 2 (0)| 00:00:01 |
| 47 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 22 | 2 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 |
| 49 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_TL | 1 | 39 | 2 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_TL_U1 | 1 | | 1 (0)| 00:00:01 |
| 51 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
| 53 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 54 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
|* 55 | TABLE ACCESS BY INDEX ROWID BATCHED | GMD_EVENT_SPEC_DISP | 1 | 19 | 3 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | GMD_EVENT_SPEC_DISP_N1 | 1 | | 2 (0)| 00:00:01 |
|* 57 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 5 | 0 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_TL_PK | 1 | 9 | 1 (0)| 00:00:01 |
| 59 | TABLE ACCESS BY INDEX ROWID BATCHED | GMD_SPEC_TESTS_B | 5 | 90 | 3 (0)| 00:00:01 |
|* 60 | INDEX RANGE SCAN | GMD_SPEC_TESTS_B_PK | 5 | | 2 (0)| 00:00:01 |
|* 61 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_TL_PK | 1 | 14 | 1 (0)| 00:00:01 |
| 62 | TABLE ACCESS BY INDEX ROWID | GMD_SAMPLE_SPEC_DISP | 1 | 16 | 2 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | GMD_SAMPLE_SPEC_DISP_PK | 1 | | 1 (0)| 00:00:01 |
|* 64 | INDEX UNIQUE SCAN | GMD_SAMPLING_EVENTS_PK | 1 | 6 | 1 (0)| 00:00:01 |
|* 65 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 2 (0)| 00:00:01 |
| 66 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 73 | 3 (0)| 00:00:01 |
| 67 | JOIN FILTER USE | :BF0000 | 4965K| 42M| 13909 (2)| 00:00:01 |
|* 68 | TABLE ACCESS STORAGE FULL | GMD_SPEC_RESULTS | 4965K| 42M| 13909 (2)| 00:00:01 |
|* 69 | TABLE ACCESS BY INDEX ROWID | GMD_RESULTS | 1 | 35 | 2 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | GMD_RESULTS_PK | 1 | | 1 (0)| 00:00:01 |
| 71 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_B | 1 | 31 | 1 (0)| 00:00:01 |
|* 72 | INDEX UNIQUE SCAN | GMD_QC_TESTS_B_PK | 1 | | 0 (0)| 00:00:01 |
| 73 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_TL | 1 | 26 | 1 (0)| 00:00:01 |
|* 74 | INDEX UNIQUE SCAN | GMD_QC_TESTS_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 75 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 76 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
|* 79 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 56 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("LOOKUP_CODE"="GSR"."EVALUATION_IND")
31 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
32 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
"ZD_EDITION_NAME"='V_20151118_1137')
34 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
36 - filter("ZD_EDITION_NAME"='V_20151118_1137')
37 - access("STRUCTURE_ID"=101)
39 - filter("STRUCTURE_ID"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
40 - access("SEGMENT1"='15')
41 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
42 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
44 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "MIC"."CATEGORY_ID"="CATEGORY_ID" AND "MIC"."ORGANIZATION_ID"=1083)
45 - filter("GS"."ORGANIZATION_ID"=1083 AND "GS"."DATE_DRAWN">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh34:mi:ss') AND
"GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
46 - access("MIC"."INVENTORY_ITEM_ID"="GS"."INVENTORY_ITEM_ID")
filter("GS"."LOT_NUMBER" IS NOT NULL AND "GS"."INVENTORY_ITEM_ID" IS NOT NULL)
48 - access("GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=1083)
50 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
52 - access("GS"."SAMPLER_ID"="F"."USER_ID")
54 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
55 - filter("GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
56 - access("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
57 - access("GES"."SPEC_ID"="B"."SPEC_ID")
58 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
60 - access("GES"."SPEC_ID"="B"."SPEC_ID")
61 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
63 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID" AND "SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
64 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
65 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="SSD"."DISPOSITION" AND
"SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LOOKUP_CODE"="SSD"."DISPOSITION" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
68 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."EVALUATION_IND"))
filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."EVALUATION_IND"))
69 - filter("GR"."SAMPLE_ID"="GS"."SAMPLE_ID" AND "GR"."TEST_ID"="B"."TEST_ID" AND "GR"."TESTER_ID" IS NOT NULL)
70 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
72 - access("GR"."TEST_ID"="B"."TEST_ID")
74 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
76 - access("GR"."TESTER_ID"="F"."USER_ID")
78 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
79 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
exsits 改写成 left join
消除了谓词推入和笛卡尔积，不去null
Plan hash value: 655267779
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 570 | 18359 (3)| 00:00:01 |
| 1 | WINDOW SORT | | 1 | 570 | 18359 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 570 | 18358 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 570 | 18358 (3)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 544 | 18357 (3)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 513 | 18356 (3)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 482 | 18353 (3)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 472 | 18352 (3)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 458 | 18351 (3)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 440 | 18349 (3)| 00:00:01 |
|* 10 | HASH JOIN | | 1 | 367 | 18346 (3)| 00:00:01 |
| 11 | JOIN FILTER CREATE | :BF0000 | 1 | 358 | 4376 (2)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 358 | 4376 (2)| 00:00:01 |
| 13 | NESTED LOOPS | | 5 | 358 | 4376 (2)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 323 | 4373 (2)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 319 | 4366 (2)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 288 | 4363 (2)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 278 | 4362 (2)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 272 | 4361 (2)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 263 | 4360 (2)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 258 | 4360 (2)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 219 | 4358 (2)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 197 | 4356 (2)| 00:00:01 |
|* 23 | HASH JOIN | | 9 | 1602 | 4338 (2)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 1 | 73 | 4 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 3 (0)| 00:00:01 |
|* 26 | HASH JOIN | | 1461 | 149K| 4334 (2)| 00:00:01 |
| 27 | JOIN FILTER CREATE | :BF0001 | 1461 | 126K| 1989 (1)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID BATCHED| GMD_SAMPLES | 1461 | 126K| 1989 (1)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | GMD_SAMPLES_U1 | 3947 | | 21 (0)| 00:00:01 |
| 30 | JOIN FILTER USE | :BF0001 | 1105K| 16M| 2332 (3)| 00:00:01 |
|* 31 | TABLE ACCESS STORAGE FULL | GMD_SAMPLE_SPEC_DISP | 1105K| 16M| 2332 (3)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | GMD_EVENT_SPEC_DISP | 1 | 19 | 2 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | GMD_EVENT_SPEC_DISP_PK | 1 | | 1 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 22 | 2 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_TL | 1 | 39 | 2 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_TL_U1 | 1 | | 1 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 5 | 0 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_TL_PK | 1 | 9 | 1 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | GMD_SAMPLING_EVENTS_PK | 1 | 6 | 1 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
| 43 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
| 45 | VIEW PUSHED PREDICATE | | 1 | 4 | 7 (0)| 00:00:01 |
|* 46 | FILTER | | | | | |
| 47 | NESTED LOOPS | | 1 | 251 | 7 (0)| 00:00:01 |
| 48 | NESTED LOOPS | | 1 | 195 | 5 (0)| 00:00:01 |
| 49 | NESTED LOOPS | | 1 | 170 | 5 (0)| 00:00:01 |
| 50 | NESTED LOOPS | | 1 | 140 | 4 (0)| 00:00:01 |
| 51 | NESTED LOOPS | | 1 | 114 | 4 (0)| 00:00:01 |
| 52 | NESTED LOOPS | | 1 | 84 | 3 (0)| 00:00:01 |
| 53 | NESTED LOOPS | | 1 | 64 | 1 (0)| 00:00:01 |
|* 54 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_U1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_TL_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_U1 | 1 | 20 | 2 (0)| 00:00:01 |
|* 57 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_B | 1 | 30 | 1 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_B_U1 | 1 | | 0 (0)| 00:00:01 |
|* 59 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_TL_U1 | 1 | 26 | 0 (0)| 00:00:01 |
|* 60 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B | 1 | 30 | 1 (0)| 00:00:01 |
|* 61 | INDEX UNIQUE SCAN | MTL_CATEGORIES_B_U1 | 1 | | 0 (0)| 00:00:01 |
|* 62 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL_U1 | 1 | 25 | 0 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 56 | 2 (0)| 00:00:01 |
|* 64 | INDEX RANGE SCAN | GMD_RESULTS_N1 | 5 | | 2 (0)| 00:00:01 |
|* 65 | TABLE ACCESS BY INDEX ROWID | GMD_RESULTS | 4 | 140 | 3 (0)| 00:00:01 |
| 66 | JOIN FILTER USE | :BF0000 | 4965K| 42M| 13909 (2)| 00:00:01 |
|* 67 | TABLE ACCESS STORAGE FULL | GMD_SPEC_RESULTS | 4965K| 42M| 13909 (2)| 00:00:01 |
| 68 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 1 | 73 | 3 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 2 (0)| 00:00:01 |
| 70 | TABLE ACCESS BY INDEX ROWID | GMD_SPEC_TESTS_B | 1 | 18 | 2 (0)| 00:00:01 |
|* 71 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_B_PK | 1 | | 1 (0)| 00:00:01 |
|* 72 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_TL_PK | 1 | 14 | 1 (0)| 00:00:01 |
| 73 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 74 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
| 75 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
| 77 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_B | 1 | 31 | 1 (0)| 00:00:01 |
|* 78 | INDEX UNIQUE SCAN | GMD_QC_TESTS_B_PK | 1 | | 0 (0)| 00:00:01 |
|* 79 | INDEX UNIQUE SCAN | GMD_QC_TESTS_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 80 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_TL | 1 | 26 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
23 - access("LOOKUP_CODE"="SSD"."DISPOSITION")
25 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
26 - access("SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
28 - filter("GS"."DATE_DRAWN">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh34:mi:ss') AND "GS"."LOT_NUMBER" IS NOT NULL AND
"GS"."INVENTORY_ITEM_ID" IS NOT NULL AND "GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd
hh34:mi:ss'))
29 - access("GS"."ORGANIZATION_ID"=1083)
31 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"SSD"."SAMPLE_ID"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"SSD"."SAMPLE_ID"))
32 - filter("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID" AND "GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
33 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID")
35 - access("GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND "ORGANIZATION_ID"=1083)
37 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
38 - access("GES"."SPEC_ID"="B"."SPEC_ID")
39 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
40 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
42 - access("GS"."SAMPLER_ID"="F"."USER_ID")
44 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
46 - filter("GS"."ORGANIZATION_ID"=1083)
54 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
55 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
"ZD_EDITION_NAME"='V_20151118_1137')
56 - access("MIC"."ORGANIZATION_ID"=1083 AND "MIC"."INVENTORY_ITEM_ID"="GS"."INVENTORY_ITEM_ID")
57 - filter("STRUCTURE_ID"=101)
58 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "ZD_EDITION_NAME"='V_20151118_1137')
59 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
60 - filter("SEGMENT1"='15' AND "STRUCTURE_ID"=101)
61 - access("MIC"."CATEGORY_ID"="CATEGORY_ID" AND "ZD_EDITION_NAME"='V_20151118_1137')
62 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
63 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
64 - access("GR"."SAMPLE_ID"="GS"."SAMPLE_ID")
65 - filter("GR"."TESTER_ID" IS NOT NULL)
67 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
69 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="GSR"."EVALUATION_IND" AND
"SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LOOKUP_CODE"="GSR"."EVALUATION_IND" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
71 - access("GES"."SPEC_ID"="B"."SPEC_ID" AND "GR"."TEST_ID"="B"."TEST_ID")
72 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
74 - access("GR"."TESTER_ID"="F"."USER_ID")
76 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
78 - access("GR"."TEST_ID"="B"."TEST_ID")
79 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
--left join 之后 有 null，为了去掉null 保证数据一致性，写了外部查询再过滤一次
sql
select * from gv$sql where sql_id = '3vm7pkp5cb69g';
explain plan for
with t_view as (SELECT MIC.ORGANIZATION_ID,MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_V MCS,
MTL_CATEGORIES MC
WHERE 1=1
AND MCS.STRUCTURE_ID = 101
AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MC.SEGMENT1 = '15')
select t1.* from (
SELECT t.ORGANIZATION_ID ORGANIZATION_ID_1,
t.INVENTORY_ITEM_ID INVENTORY_ITEM_ID_1 ,
GS.ORG_ID,
GS.ORGANIZATION_ID,
GS.SAMPLE_ID,
GS.SAMPLE_NO,
GS.SAMPLE_DESC,
GS.LOT_NUMBER,
GS.INVENTORY_ITEM_ID,
GS.DATE_DRAWN,
MSI.SEGMENT1 ITEM_NUM,
MSI.DESCRIPTION ITEM_NAME,
P1.LAST_NAME SAMPLER_USER,
GH.MEANING HEADER_STATUS,
GR.RESULT_ID,
GR.TEST_ID,
GR.SEQ,
GQT.TEST_CODE,
GQT.TEST_DESC,
GST.MIN_VALUE_NUM,
GST.TARGET_VALUE_NUM,
GST.MAX_VALUE_NUM,
GR.RESULT_VALUE_NUM,
GR.RESULT_DATE,
P2.LAST_NAME TESTER_USER,
GL.MEANING END_RESULT,
COUNT(1) OVER(PARTITION BY GS.SAMPLE_ID) SL
FROM (GMD_SAMPLES GS left join t_view t on t.ORGANIZATION_ID = GS.ORGANIZATION_ID
AND t.INVENTORY_ITEM_ID =GS.INVENTORY_ITEM_ID --and t.ORGANIZATION_ID is not null
-- and t.INVENTORY_ITEM_ID is not null
),
GMD_RESULTS GR,
GEM_LOOKUPS GL,
GEM_LOOKUPS GH,
GMD_SAMPLE_SPEC_DISP SSD,
GMD_EVENT_SPEC_DISP GES,
GMD_SAMPLING_EVENTS SE,
GMD_SPEC_TESTS GST,
GMD_SPECIFICATIONS GSP,
GMD_SPEC_RESULTS GSR,
GMD_QC_TESTS GQT,
MTL_SYSTEM_ITEMS_VL MSI,
(SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
FROM PER_ALL_PEOPLE_F P, FND_USER F
WHERE P.PERSON_ID = F.EMPLOYEE_ID
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P1,
(SELECT P.LAST_NAME, F.USER_ID, F.USER_NAME
FROM PER_ALL_PEOPLE_F P, FND_USER F
WHERE P.PERSON_ID = F.EMPLOYEE_ID
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND
NVL(P.EFFECTIVE_END_DATE, SYSDATE + 1)) P2
WHERE GR.SAMPLE_ID = GS.SAMPLE_ID
--and t.ORGANIZATION_ID is not null
--and t.INVENTORY_ITEM_ID is not null
AND (GS.SAMPLE_TYPE = 'I')
AND GS.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
AND GES.SPEC_ID = GST.SPEC_ID
AND GR.TEST_ID = GST.TEST_ID
AND GR.TEST_ID = GQT.TEST_ID
AND GS.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND GS.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND GL.LOOKUP_TYPE = 'GMD_QC_EVALUATION'
AND GL.LOOKUP_CODE = GSR.EVALUATION_IND
AND GH.LOOKUP_CODE = SSD.DISPOSITION
AND GH.LOOKUP_TYPE = 'GMD_QC_SAMPLE_DISP'
AND GSR.RESULT_ID = GR.RESULT_ID
AND GS.SAMPLER_ID = P1.USER_ID
AND GR.TESTER_ID = P2.USER_ID
AND GES.EVENT_SPEC_DISP_ID = SSD.EVENT_SPEC_DISP_ID
AND SSD.SAMPLE_ID = GS.SAMPLE_ID
AND GES.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND SE.SAMPLING_EVENT_ID = GES.SAMPLING_EVENT_ID
AND GST.TEST_ID = GR.TEST_ID
AND GES.SPEC_ID = GSP.SPEC_ID
AND GES.SPEC_ID = GST.SPEC_ID
/*AND EXISTS (SELECT 1
FROM MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_V MCS,
MTL_CATEGORIES MC
WHERE MIC.ORGANIZATION_ID = GS.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID = GS.INVENTORY_ITEM_ID
AND MCS.STRUCTURE_ID = 101
AND MIC.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID
AND MCS.STRUCTURE_ID = MC.STRUCTURE_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MC.SEGMENT1 = '15')*/
AND GS.ORGANIZATION_ID = 1083
AND GS.DATE_DRAWN BETWEEN to_date('01/01/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND to_date('09/11/2017 00:00:00','MM/DD/YYYY HH24:MI:SS')
AND GS.INVENTORY_ITEM_ID = NVL(null, GS.INVENTORY_ITEM_ID)
AND GS.LOT_NUMBER = NVL(null, GS.LOT_NUMBER)
AND GS.SAMPLE_NO = NVL(null, GS.SAMPLE_NO)) t1
where t1.ORGANIZATION_ID_1 is not null
and t1.INVENTORY_ITEM_ID_1 is not null;
Plan hash value: 1361232557
---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 872 | 16692 (2)| 00:00:01 |
|* 1 | VIEW | | 1 | 872 | 16692 (2)| 00:00:01 |
| 2 | WINDOW SORT | | 1 | 576 | 16692 (2)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 576 | 16691 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 576 | 16691 (2)| 00:00:01 |
|* 5 | HASH JOIN | | 1 | 503 | 16688 (2)| 00:00:01 |
| 6 | JOIN FILTER CREATE | :BF0000 | 1 | 494 | 2719 (1)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 494 | 2719 (1)| 00:00:01 |
|* 8 | HASH JOIN OUTER | | 1 | 480 | 2718 (1)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 470 | 2698 (1)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 470 | 2698 (1)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 444 | 2697 (1)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 413 | 2696 (1)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 382 | 2693 (1)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 351 | 2690 (1)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 341 | 2689 (1)| 00:00:01 |
| 16 | NESTED LOOPS | | 1 | 323 | 2687 (1)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 288 | 2684 (1)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 279 | 2683 (1)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 269 | 2682 (1)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 263 | 2681 (1)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 258 | 2681 (1)| 00:00:01 |
|* 22 | HASH JOIN | | 1 | 239 | 2679 (1)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 1 | 73 | 4 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 3 (0)| 00:00:01 |
| 25 | NESTED LOOPS | | 76 | 12616 | 2675 (1)| 00:00:01 |
| 26 | NESTED LOOPS | | 76 | 12616 | 2675 (1)| 00:00:01 |
| 27 | NESTED LOOPS | | 76 | 11400 | 2447 (1)| 00:00:01 |
|* 28 | HASH JOIN | | 76 | 8436 | 2295 (1)| 00:00:01 |
| 29 | ED TABLE ACCESS BY INDEX ROWID BATCH | MTL_SYSTEM_ITEMS_B | 1163 | 25586 | 306 (1)| 00:00:01 |
|* 30 | INDEX SKIP SCAN | MTL_SYSTEM_ITEMS_B_N8 | 1163 | | 6 (0)| 00:00:01 |
|* 31 | ED TABLE ACCESS BY INDEX ROWID BATCH | GMD_SAMPLES | 1461 | 126K| 1989 (1)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | GMD_SAMPLES_U1 | 3947 | | 21 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_TL | 1 | 39 | 2 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_TL_U1 | 1 | | 1 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | GMD_SAMPLE_SPEC_DISP_N1 | 1 | | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | GMD_SAMPLE_SPEC_DISP | 1 | 16 | 3 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | GMD_EVENT_SPEC_DISP | 1 | 19 | 2 (0)| 00:00:01 |
|* 38 | INDEX UNIQUE SCAN | GMD_EVENT_SPEC_DISP_PK | 1 | | 1 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_B_PK | 1 | 5 | 0 (0)| 00:00:01 |
|* 40 | INDEX UNIQUE SCAN | GMD_SAMPLING_EVENTS_PK | 1 | 6 | 1 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 42 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | GMD_SPECIFICATIONS_TL_PK | 1 | 9 | 1 (0)| 00:00:01 |
|* 44 | TABLE ACCESS BY INDEX ROWID BATCHED | GMD_RESULTS | 4 | 140 | 3 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | GMD_RESULTS_N1 | 5 | | 2 (0)| 00:00:01 |
| 46 | TABLE ACCESS BY INDEX ROWID | GMD_SPEC_TESTS_B | 1 | 18 | 2 (0)| 00:00:01 |
|* 47 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_B_PK | 1 | | 1 (0)| 00:00:01 |
| 48 | TABLE ACCESS BY INDEX ROWID | FND_USER | 1 | 10 | 1 (0)| 00:00:01 |
|* 49 | INDEX UNIQUE SCAN | FND_USER_U1 | 1 | | 0 (0)| 00:00:01 |
| 50 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
| 52 | TABLE ACCESS BY INDEX ROWID BATCHED | PER_ALL_PEOPLE_F | 1 | 31 | 3 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | PER_PEOPLE_F_PK | 1 | | 2 (0)| 00:00:01 |
| 54 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_B | 1 | 31 | 1 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | GMD_QC_TESTS_B_PK | 1 | | 0 (0)| 00:00:01 |
|* 56 | INDEX UNIQUE SCAN | GMD_QC_TESTS_TL_PK | 1 | | 0 (0)| 00:00:01 |
| 57 | TABLE ACCESS BY INDEX ROWID | GMD_QC_TESTS_TL | 1 | 26 | 1 (0)| 00:00:01 |
| 58 | VIEW | | 1 | 10 | 20 (0)| 00:00:01 |
| 59 | NESTED LOOPS | | 1 | 251 | 20 (0)| 00:00:01 |
| 60 | NESTED LOOPS | | 1 | 226 | 20 (0)| 00:00:01 |
| 61 | NESTED LOOPS | | 1 | 200 | 20 (0)| 00:00:01 |
| 62 | MERGE JOIN CARTESIAN | | 3 | 540 | 9 (0)| 00:00:01 |
| 63 | NESTED LOOPS | | 1 | 150 | 4 (0)| 00:00:01 |
| 64 | NESTED LOOPS | | 1 | 150 | 4 (0)| 00:00:01 |
| 65 | NESTED LOOPS | | 1 | 120 | 3 (0)| 00:00:01 |
| 66 | NESTED LOOPS | | 1 | 64 | 1 (0)| 00:00:01 |
|* 67 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_U1 | 1 | 30 | 1 (0)| 00:00:01 |
|* 68 | INDEX UNIQUE SCAN | FND_ID_FLEX_STRUCTURES_TL_U1 | 1 | 34 | 0 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 56 | 2 (0)| 00:00:01 |
|* 70 | INDEX RANGE SCAN | MTL_CATEGORY_SETS_B_N1 | 1 | | 0 (0)| 00:00:01 |
|* 71 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORY_SETS_B | 1 | 30 | 1 (0)| 00:00:01 |
| 72 | BUFFER SORT | | 9 | 270 | 8 (0)| 00:00:01 |
|* 73 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_CATEGORIES_B | 9 | 270 | 5 (0)| 00:00:01 |
|* 74 | INDEX RANGE SCAN | MTL_CATEGORIES_B_N1 | 57 | | 1 (0)| 00:00:01 |
| 75 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_ITEM_CATEGORIES | 1 | 20 | 7 (0)| 00:00:01 |
|* 76 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_N3 | 19 | | 2 (0)| 00:00:01 |
|* 77 | INDEX UNIQUE SCAN | MTL_CATEGORY_SETS_TL_U1 | 1 | 26 | 0 (0)| 00:00:01 |
|* 78 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL_U1 | 1 | 25 | 0 (0)| 00:00:01 |
|* 79 | INDEX UNIQUE SCAN | GMD_SPEC_TESTS_TL_PK | 1 | 14 | 1 (0)| 00:00:01 |
| 80 | JOIN FILTER USE | :BF0000 | 4965K| 42M| 13909 (2)| 00:00:01 |
|* 81 | TABLE ACCESS STORAGE FULL | GMD_SPEC_RESULTS | 4965K| 42M| 13909 (2)| 00:00:01 |
|* 82 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | | 2 (0)| 00:00:01 |
| 83 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 73 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."ORGANIZATION_ID_1" IS NOT NULL AND "T1"."INVENTORY_ITEM_ID_1" IS NOT NULL)
5 - access("GSR"."RESULT_ID"="GR"."RESULT_ID")
8 - access("T"."ORGANIZATION_ID"(+)="GS"."ORGANIZATION_ID" AND "T"."INVENTORY_ITEM_ID"(+)="GS"."INVENTORY_ITEM_ID")
22 - access("LOOKUP_CODE"="SSD"."DISPOSITION")
24 - access("LOOKUP_TYPE"='GMD_QC_SAMPLE_DISP' AND "VIEW_APPLICATION_ID">=550 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
28 - access("GS"."ORGANIZATION_ID"="ORGANIZATION_ID" AND "GS"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID")
30 - access("ORGANIZATION_ID"=1083)
filter("ORGANIZATION_ID"=1083)
31 - filter("GS"."DATE_DRAWN">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh34:mi:ss') AND "GS"."LOT_NUMBER" IS NOT NULL AND
"GS"."INVENTORY_ITEM_ID" IS NOT NULL AND "GS"."SAMPLE_TYPE"='I' AND "GS"."DATE_DRAWN"<=TO_DATE(' 2017-09-11 00:00:00', 'syyyy-mm-dd
hh34:mi:ss'))
32 - access("GS"."ORGANIZATION_ID"=1083)
34 - access("INVENTORY_ITEM_ID"="T"."INVENTORY_ITEM_ID" AND "T"."ORGANIZATION_ID"=1083 AND "T"."LANGUAGE"=USERENV('LANG'))
35 - access("SSD"."SAMPLE_ID"="GS"."SAMPLE_ID")
37 - filter("GS"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID" AND "GES"."SPEC_USED_FOR_LOT_ATTRIB_IND"='Y')
38 - access("GES"."EVENT_SPEC_DISP_ID"="SSD"."EVENT_SPEC_DISP_ID")
39 - access("GES"."SPEC_ID"="B"."SPEC_ID")
40 - access("SE"."SAMPLING_EVENT_ID"="GES"."SAMPLING_EVENT_ID")
42 - access("GS"."SAMPLER_ID"="F"."USER_ID")
43 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
44 - filter("GR"."TESTER_ID" IS NOT NULL)
45 - access("GR"."SAMPLE_ID"="GS"."SAMPLE_ID")
47 - access("GES"."SPEC_ID"="B"."SPEC_ID" AND "GR"."TEST_ID"="B"."TEST_ID")
49 - access("GR"."TESTER_ID"="F"."USER_ID")
51 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
53 - access("P"."PERSON_ID"="F"."EMPLOYEE_ID" AND "P"."EFFECTIVE_END_DATE">=SYSDATE@! AND "P"."EFFECTIVE_START_DATE"<=SYSDATE@!)
filter("P"."EFFECTIVE_END_DATE">=SYSDATE@!)
55 - access("GR"."TEST_ID"="B"."TEST_ID")
56 - access("B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
67 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
68 - access("APPLICATION_ID"=401 AND "ID_FLEX_CODE"='MCAT' AND "ID_FLEX_NUM"=101 AND "LANGUAGE"=USERENV('LANG') AND
"ZD_EDITION_NAME"='V_20151118_1137')
69 - access("LOOKUP_TYPE"='ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID"=700 AND "SECURITY_GROUP_ID"=0 AND
"LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
filter("LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND "ZD_EDITION_NAME"='V_20151118_1137')
70 - access("STRUCTURE_ID"=101)
71 - filter("CONTROL_LEVEL"=TO_NUMBER("LOOKUP_CODE") AND "ZD_EDITION_NAME"='V_20151118_1137')
73 - filter("STRUCTURE_ID"=101 AND "ZD_EDITION_NAME"='V_20151118_1137')
74 - access("SEGMENT1"='15')
76 - access("MIC"."CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "MIC"."CATEGORY_ID"="CATEGORY_ID" AND "MIC"."ORGANIZATION_ID"=1083)
77 - access("CATEGORY_SET_ID"="CATEGORY_SET_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
78 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137')
79 - access("B"."SPEC_ID"="T"."SPEC_ID" AND "B"."TEST_ID"="T"."TEST_ID" AND "T"."LANGUAGE"=USERENV('LANG'))
81 - storage("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
filter("GSR"."EVALUATION_IND" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"GSR"."RESULT_ID"))
82 - access("LOOKUP_TYPE"='GMD_QC_EVALUATION' AND "VIEW_APPLICATION_ID">=550 AND "LOOKUP_CODE"="GSR"."EVALUATION_IND" AND
"SECURITY_GROUP_ID"=0 AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='V_20151118_1137' AND "VIEW_APPLICATION_ID"<=559)
filter("LOOKUP_CODE"="GSR"."EVALUATION_IND" AND "LANGUAGE"=USERENV('LANG') AND "SECURITY_GROUP_ID"=0 AND
"ZD_EDITION_NAME"='V_20151118_1137')
感谢你能够认真阅读完这篇文章，希望小编分享的“exist如何改写为left join”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
