How to rewrite exist to left join
This article mainly introduces how to rewrite exist into left join, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.
-- 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 ('09Compact 01way 2017 0000 VOGO 00GRAPHY MMUnix HH24:MI:SS')
AND to_date ('09Compact 11Compact 2017 0000VOGROGRAPH 00GRAPHYY 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" =' Venture 20151118')
35-access ("APPLICATION_ID" = 401AND "ID_FLEX_CODE" = 'MCAT' AND "ID_FLEX_NUM" = 101AND "LANGUAGE" = USERENV (' LANG') AND
"ZD_EDITION_NAME" = 'Venture 20151118 (1137')
36-filter ("ZD_EDITION_NAME" = 'Venture 20151118 / 1137')
37-access ("STRUCTURE_ID" = 101)
38-access ("LOOKUP_TYPE" = 'ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID" = 700AND "SECURITY_GROUP_ID" = 0 AND
"LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118) 1137')
Filter ("CONTROL_LEVEL" = TO_NUMBER ("LOOKUP_CODE") AND "LANGUAGE" = USERENV ('LANG') AND "SECURITY_GROUP_ID" = 0 AND
"ZD_EDITION_NAME" = 'Venture 20151118 (1137')
40-filter ("STRUCTURE_ID" = 101AND "ZD_EDITION_NAME" = 'Venture 20151118')
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" =' Venture 20151118 "1137')
45-access ("CATEGORY_ID" = "CATEGORY_ID" AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 "1137')
46-filter ("GS". "DATE_DRAWN" > = TO_DATE ('2017-09-01 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" = SYSDATE! AND "P". "EFFECTIVE_START_DATE" = SYSDATEY!)
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" = SYSDATEY!)
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" > = 550AND "LOOKUP_CODE" = "SSD". "DISPOSITION" AND
"SECURITY_GROUP_ID" = 0 AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 '1137' AND "VIEW_APPLICATION_ID" = 550AND "LOOKUP_CODE" = "GSR". "EVALUATION_IND" AND
"SECURITY_GROUP_ID" = 0 AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 '1137' AND "VIEW_APPLICATION_ID" = 550AND "SECURITY_GROUP_ID" = 0 AND
"LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Villa 20151118 '1137' AND "VIEW_APPLICATION_ID" = TO_DATE (' 2017-01-01 00 AND 00V, 'syyyy-mm-dd hh34:mi:ss') AND
"GS". "SAMPLE_TYPE" ='I 'AND "GS". "DATE_DRAWN" = SYSDATE! AND "P". "EFFECTIVE_START_DATE" = SYSDATEY!)
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" > = 550AND "LOOKUP_CODE" = "SSD". "DISPOSITION" AND
"SECURITY_GROUP_ID" = 0 AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 '1137' AND "VIEW_APPLICATION_ID" = SYSDATE! AND "P". "EFFECTIVE_START_DATE" = SYSDATEY!)
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" =' Venture 20151118) 1137')
Filter ("CONTROL_LEVEL" = TO_NUMBER ("LOOKUP_CODE") AND "LANGUAGE" = USERENV ('LANG') AND "SECURITY_GROUP_ID" = 0 AND
"ZD_EDITION_NAME" = 'Venture 20151118 (1137')
Rewrite exsits into left join
Eliminate predicate push and Cartesian product, not 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" > = 550AND "SECURITY_GROUP_ID" = 0 AND
"LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Villa 20151118 '1137' AND "VIEW_APPLICATION_ID" = TO_DATE (' 2017-01-01 00 GS, '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" = SYSDATE! AND "P". "EFFECTIVE_START_DATE" = SYSDATEY!)
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" =' Venture 20151118')
55-access ("APPLICATION_ID" = 401AND "ID_FLEX_CODE" = 'MCAT' AND "ID_FLEX_NUM" = 101AND "LANGUAGE" = USERENV (' LANG') AND
"ZD_EDITION_NAME" = 'Venture 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" = 'Venture 20151118 / 1137')
59-access ("CATEGORY_SET_ID" = "CATEGORY_SET_ID" AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 "1137')
60-filter ("SEGMENT1" = '15' AND "STRUCTURE_ID" = 101)
61-access ("MIC". "CATEGORY_ID" = "CATEGORY_ID" AND "ZD_EDITION_NAME" = 'Venture 20151118 / 1137')
62-access ("CATEGORY_ID" = "CATEGORY_ID" AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 "1137')
63-access ("LOOKUP_TYPE" = 'ITEM_CONTROL_LEVEL_GUI' AND "VIEW_APPLICATION_ID" = 700AND "SECURITY_GROUP_ID" = 0 AND
"LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118) 1137')
Filter ("CONTROL_LEVEL" = TO_NUMBER ("LOOKUP_CODE") AND "LANGUAGE" = USERENV ('LANG') AND "SECURITY_GROUP_ID" = 0 AND
"ZD_EDITION_NAME" = 'Venture 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" > = 550AND "LOOKUP_CODE" = "GSR". "EVALUATION_IND" AND
"SECURITY_GROUP_ID" = 0 AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 '1137' AND "VIEW_APPLICATION_ID" = SYSDATE! AND "P". "EFFECTIVE_START_DATE" = SYSDATEY!)
78-access ("GR". "TEST_ID" = "B". "TEST_ID")
79-access ("B". "TEST_ID" = "T". "TEST_ID" AND "T". "LANGUAGE" = USERENV ('LANG'))
-- there is null after left join. In order to remove null to ensure data consistency, write an external query and filter it again.
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 ('01Compact 01way 2017 0000 VOGRO 00GRAPE 00GRAPHYY HH24:MI:SS')
AND to_date ('09Compact 11Compact 2017 0000VOGROGRAPH 00GRAPHYY 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" > = 550AND "SECURITY_GROUP_ID" = 0 AND
"LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Villa 20151118 '1137' AND "VIEW_APPLICATION_ID" = TO_DATE (' 2017-01-01 00 GS, '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" = SYSDATE! AND "P". "EFFECTIVE_START_DATE" = SYSDATEY!)
53-access ("P". "PERSON_ID" = "F". "EMPLOYEE_ID" AND "P". "EFFECTIVE_END_DATE" > = SYSDATE! AND "P". "EFFECTIVE_START_DATE" = SYSDATEY!)
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" =' Venture 20151118')
68-access ("APPLICATION_ID" = 401AND "ID_FLEX_CODE" = 'MCAT' AND "ID_FLEX_NUM" = 101AND "LANGUAGE" = USERENV (' LANG') AND
"ZD_EDITION_NAME" = 'Venture 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" =' Venture 20151118) 1137')
Filter ("LANGUAGE" = USERENV ('LANG') AND "SECURITY_GROUP_ID" = 0 AND "ZD_EDITION_NAME" =' Venture 20151118 / 1137')
70-access ("STRUCTURE_ID" = 101)
71-filter ("CONTROL_LEVEL" = TO_NUMBER ("LOOKUP_CODE") AND "ZD_EDITION_NAME" = 'Venture 20151118) 1137')
73-filter ("STRUCTURE_ID" = 101 AND "ZD_EDITION_NAME" = 'Venture 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" =' Venture 20151118 "1137')
78-access ("CATEGORY_ID" = "CATEGORY_ID" AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 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" > = 550AND "LOOKUP_CODE" = "GSR". "EVALUATION_IND" AND
"SECURITY_GROUP_ID" = 0 AND "LANGUAGE" = USERENV ('LANG') AND "ZD_EDITION_NAME" =' Venture 20151118 '1137' AND "VIEW_APPLICATION_ID"
