In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-13 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
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"
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
