PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 3hudfbhatf1jh, child number 0 ------------------------------------- SELECT /* Leading */ d.document_id, ad.seq_num, dct.user_name category_description, d.description document_description, datatype_name, txt.short_text, d.usage_type_descr USAGE, det.user_entity_name, ad.entity_name, d.security_type, Decode (d.security_type, 1, 'Organization', 2, 'Set of Books', 3, 'Business Unit', 4, 'None', 'None') security_type_desc, d.security_id, ( CASE WHEN d.security_type IN ( 1, 3 ) THEN hou.name WHEN d.security_type = 2 THEN gsb.short_name ELSE NULL END ) owner, d.start_date_active, d.end_date_active, d.creation_date, d.created_by, det.user_entity_prompt, txt.media_id, ad.pk1_ Plan hash value: 3126174845 ----------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 356M(100)| | | 1 | SORT ORDER BY | | 3412M| 2345G| | 356M (1)|999:59:59 | | 2 | CONCATENATION | | | | | | | | 3 | MERGE JOIN CARTESIAN | | 3372M| 2317G| | 15M (1)| 51:03:12 | |* 4 | HASH JOIN RIGHT OUTER | | 48M| 33G| | 2031K (1)| 06:46:15 | |* 5 | INDEX SKIP SCAN | FND_DOCUMENT_DATATYPES_U1 | 9 | 54 | | 1 (0)| 00:00:01 | |* 6 | HASH JOIN | | 48M| 33G| | 2030K (1)| 06:46:11 | |* 7 | INDEX SKIP SCAN | FND_DOCUMENT_DATATYPES_U2 | 9 | 171 | | 1 (0)| 00:00:01 | |* 8 | HASH JOIN RIGHT OUTER | | 32M| 21G| | 2030K (1)| 06:46:07 | | 9 | VIEW | FND_DOCUMENT_ENTITIES_VL | 681 | 107K| | 32 (0)| 00:00:01 | | 10 | NESTED LOOPS | | 681 | 58566 | | 32 (0)| 00:00:01 | |* 11 | HASH JOIN | | 441 | 34839 | | 32 (0)| 00:00:01 | | 12 | TABLE ACCESS FULL | FND_DOCUMENT_ENTITIES | 441 | 9702 | | 6 (0)| 00:00:01 | |* 13 | TABLE ACCESS FULL | FND_DOCUMENT_ENTITIES_TL | 441 | 25137 | | 26 (0)| 00:00:01 | |* 14 | INDEX UNIQUE SCAN | FND_APPLICATION_TL_U1 | 2 | 14 | | 0 (0)| | |* 15 | HASH JOIN RIGHT OUTER | | 21M| 10G| 1540M| 2030K (1)| 06:46:04 | | 16 | TABLE ACCESS FULL | FND_ATTACHED_DOCUMENTS | 14M| 1380M| | 69373 (1)| 00:13:53 | |* 17 | HASH JOIN | | 20M| 8854M| 409M| 1433K (1)| 04:46:39 | |* 18 | TABLE ACCESS FULL | FND_DOCUMENTS_TL | 13M| 250M| | 702K (2)| 02:20:31 | |* 19 | HASH JOIN | | 13M| 5478M| 2455M| 430K (2)| 01:26:10 | | 20 | TABLE ACCESS FULL | FND_DOCUMENTS_SHORT_TEXT | 12M| 2312M| | 99187 (1)| 00:19:51 | |* 21 | FILTER | | | | | | | |* 22 | HASH JOIN RIGHT OUTER | | 13M| 2982M| | 53933 (7)| 00:10:48 | | 23 | INDEX FAST FULL SCAN | FND_APPLICATION_TL_U1 | 3976 | 27832 | | 4 (0)| 00:00:01 | |* 24 | HASH JOIN | | 30M| 6587M| | 52708 (5)| 00:10:33 | |* 25 | TABLE ACCESS FULL | FND_DOCUMENT_CATEGORIES_TL | 325 | 9425 | | 18 (0)| 00:00:01 | |* 26 | HASH JOIN | | 31M| 5787M| | 52489 (5)| 00:10:30 | | 27 | TABLE ACCESS FULL | FND_DOCUMENT_CATEGORIES | 328 | 3608 | | 4 (0)| 00:00:01 | |* 28 | HASH JOIN | | 31M| 5461M| | 52284 (4)| 00:10:28 | |* 29 | INDEX FAST FULL SCAN | FND_DOCUMENT_CATEGORIES_TL_U1 | 325 | 2925 | | 6 (0)| 00:00:01 | |* 30 | HASH JOIN | | 31M| 5242M| | 52076 (4)| 00:10:25 | | 31 | INDEX FULL SCAN | FND_DOCUMENT_CATEGORIES_U1 | 328 | 1968 | | 1 (0)| 00:00:01 | |* 32 | HASH JOIN | | 31M| 5062M| | 51872 (3)| 00:10:23 | |* 33 | TABLE ACCESS FULL | GL_LEDGERS | 22 | 484 | | 3 (0)| 00:00:01 | |* 34 | HASH JOIN | | 78M| 10G| | 51361 (3)| 00:10:17 | | 35 | MERGE JOIN CARTESIAN | | 191 | 19482 | | 185 (1)| 00:00:03 | |* 36 | HASH JOIN | | 19 | 950 | | 109 (1)| 00:00:02 | | 37 | TABLE ACCESS BY INDEX ROWID| HR_ALL_ORGANIZATION_UNITS_TL | 19 | 798 | | 92 (0)| 00:00:02 | |* 38 | INDEX SKIP SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 8 | | | 88 (0)| 00:00:02 | |* 39 | TABLE ACCESS FULL | HR_ALL_ORGANIZATION_UNITS | 129 | 1032 | | 17 (6)| 00:00:01 | | 40 | BUFFER SORT | | 10 | 520 | | 168 (0)| 00:00:03 | | 41 | TABLE ACCESS BY INDEX ROWID| FND_LOOKUP_VALUES | 10 | 520 | | 4 (0)| 00:00:01 | |* 42 | INDEX RANGE SCAN | IDX$$_1487A0001 | 10 | | | 2 (0)| 00:00:01 | |* 43 | TABLE ACCESS FULL | FND_DOCUMENTS | 4130K| 177M| | 50668 (2)| 00:10:09 | | 44 | BUFFER SORT | | 69 | 276 | | 15M (1)| 51:03:12 | | 45 | INDEX FAST FULL SCAN | MTL_PARAMETERS_U1 | 69 | 276 | | 0 (0)| | | 46 | NESTED LOOPS | | 40M| 27G| | 3746K (1)| 12:29:15 | |* 47 | HASH JOIN RIGHT OUTER | | 1888K| 1289M| | 1173K (1)| 03:54:47 | |* 48 | INDEX SKIP SCAN | FND_DOCUMENT_DATATYPES_U1 | 9 | 54 | | 1 (0)| 00:00:01 | |* 49 | HASH JOIN | | 1888K| 1278M| | 1173K (1)| 03:54:47 | |* 50 | INDEX SKIP SCAN | FND_DOCUMENT_DATATYPES_U2 | 9 | 171 | | 1 (0)| 00:00:01 | |* 51 | HASH JOIN RIGHT OUTER | | 1258K| 829M| | 1173K (1)| 03:54:47 | | 52 | VIEW | FND_DOCUMENT_ENTITIES_VL | 681 | 107K| | 32 (0)| 00:00:01 | | 53 | NESTED LOOPS | | 681 | 58566 | | 32 (0)| 00:00:01 | |* 54 | HASH JOIN | | 441 | 34839 | | 32 (0)| 00:00:01 | | 55 | TABLE ACCESS FULL | FND_DOCUMENT_ENTITIES | 441 | 9702 | | 6 (0)| 00:00:01 | |* 56 | TABLE ACCESS FULL | FND_DOCUMENT_ENTITIES_TL | 441 | 25137 | | 26 (0)| 00:00:01 | |* 57 | INDEX UNIQUE SCAN | FND_APPLICATION_TL_U1 | 2 | 14 | | 0 (0)| | |* 58 | HASH JOIN OUTER | | 815K| 411M| 337M| 1173K (1)| 03:54:46 | |* 59 | HASH JOIN | | 808K| 328M| 208M| 1011K (1)| 03:22:14 | |* 60 | HASH JOIN | | 522K| 202M| 112M| 277K (1)| 00:55:34 | |* 61 | FILTER | | | | | | | |* 62 | HASH JOIN RIGHT OUTER | | 523K| 106M| | 51079 (2)| 00:10:13 | | 63 | INDEX FAST FULL SCAN | FND_APPLICATION_TL_U1 | 3976 | 27832 | | 4 (0)| 00:00:01 | |* 64 | HASH JOIN | | 1191K| 234M| | 51027 (2)| 00:10:13 | |* 65 | TABLE ACCESS FULL | FND_DOCUMENT_CATEGORIES_TL | 325 | 9425 | | 18 (0)| 00:00:01 | |* 66 | HASH JOIN | | 1202K| 203M| | 51001 (2)| 00:10:13 | | 67 | TABLE ACCESS FULL | FND_DOCUMENT_CATEGORIES | 328 | 3608 | | 4 (0)| 00:00:01 | |* 68 | HASH JOIN | | 1202K| 190M| | 50990 (2)| 00:10:12 | |* 69 | INDEX FAST FULL SCAN | FND_DOCUMENT_CATEGORIES_TL_U1 | 325 | 2925 | | 6 (0)| 00:00:01 | |* 70 | HASH JOIN | | 1213K| 181M| | 50976 (2)| 00:10:12 | | 71 | INDEX FULL SCAN | FND_DOCUMENT_CATEGORIES_U1 | 328 | 1968 | | 1 (0)| 00:00:01 | |* 72 | HASH JOIN | | 1213K| 174M| | 50967 (2)| 00:10:12 | | 73 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 10 | 520 | | 5 (0)| 00:00:01 | |* 74 | INDEX RANGE SCAN | IDX$$_1487A0001 | 10 | | | 3 (0)| 00:00:01 | |* 75 | HASH JOIN | | 1226K| 115M| | 50954 (2)| 00:10:12 | | 76 | NESTED LOOPS | | 10 | 540 | | 109 (1)| 00:00:02 | |* 77 | HASH JOIN | | 19 | 950 | | 109 (1)| 00:00:02 | | 78 | TABLE ACCESS BY INDEX ROWID| HR_ALL_ORGANIZATION_UNITS_TL | 19 | 798 | | 92 (0)| 00:00:02 | |* 79 | INDEX SKIP SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 8 | | | 88 (0)| 00:00:02 | |* 80 | TABLE ACCESS FULL | HR_ALL_ORGANIZATION_UNITS | 129 | 1032 | | 17 (6)| 00:00:01 | |* 81 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | 4 | | 0 (0)| | |* 82 | TABLE ACCESS FULL | FND_DOCUMENTS | 8260K| 354M| | 50792 (2)| 00:10:10 | | 83 | TABLE ACCESS FULL | FND_DOCUMENTS_SHORT_TEXT | 12M| 2312M| | 99187 (1)| 00:19:51 | |* 84 | TABLE ACCESS FULL | FND_DOCUMENTS_TL | 13M| 250M| | 702K (2)| 02:20:31 | | 85 | TABLE ACCESS FULL | FND_ATTACHED_DOCUMENTS | 14M| 1380M| | 69373 (1)| 00:13:53 | |* 86 | TABLE ACCESS FULL | GL_LEDGERS | 21 | 462 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("C"."DEFAULT_DATATYPE_ID"="DD"."DATATYPE_ID") 5 - access("DD"."LANGUAGE"=USERENV('LANG')) filter("DD"."LANGUAGE"=USERENV('LANG')) 6 - access("D"."DATATYPE_ID"="DD"."DATATYPE_ID") 7 - access("DD"."LANGUAGE"=USERENV('LANG')) filter("DD"."LANGUAGE"=USERENV('LANG')) 8 - access("AD"."ENTITY_NAME"="DET"."DATA_OBJECT_CODE") 11 - access("FDE"."DATA_OBJECT_CODE"="TL"."DATA_OBJECT_CODE") 13 - filter("TL"."LANGUAGE"=USERENV('LANG')) 14 - access("FDE"."APPLICATION_ID"="FATL"."APPLICATION_ID" AND "FATL"."LANGUAGE"=USERENV('LANG')) 15 - access("D"."DOCUMENT_ID"="AD"."DOCUMENT_ID") 17 - access("D"."DOCUMENT_ID"="DL"."DOCUMENT_ID") 18 - filter("DL"."LANGUAGE"=USERENV('LANG')) 19 - access("D"."MEDIA_ID"="TXT"."MEDIA_ID") 21 - filter(("APPTL"."LANGUAGE" IS NULL OR "APPTL"."LANGUAGE"=USERENV('LANG'))) 22 - access("C"."APPLICATION_ID"="APPTL"."APPLICATION_ID") 24 - access("C"."CATEGORY_ID"="T"."CATEGORY_ID") 25 - filter("T"."LANGUAGE"=USERENV('LANG')) 26 - access("C"."CATEGORY_ID"="D"."CATEGORY_ID") 28 - access("C"."CATEGORY_ID"="CL"."CATEGORY_ID") 29 - filter("CL"."LANGUAGE"=USERENV('LANG')) 30 - access("D"."CATEGORY_ID"="C"."CATEGORY_ID") 32 - access("LGR"."LEDGER_ID"="D"."SECURITY_ID") 33 - filter(("OBJECT_TYPE_CODE"='L' AND NVL("COMPLETE_FLAG",'Y')='Y')) 34 - access("D"."USAGE_TYPE"="L"."LOOKUP_CODE") 36 - access("HAO"."ORGANIZATION_ID"="HAOTL"."ORGANIZATION_ID") 38 - access("HAOTL"."LANGUAGE"=USERENV('LANG')) filter(("HAOTL"."LANGUAGE"=USERENV('LANG') AND DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('H R_ALL_ORGANIZATION_UNITS',"HAOTL"."ORGANIZATION_ID"))='TRUE')) 39 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_B USINESS_GROUP_ID"())) 42 - access("L"."LOOKUP_TYPE"='ATCHMT_DOCUMENT_TYPE' AND "L"."LANGUAGE"=USERENV('LANG')) 43 - filter(("D"."SECURITY_TYPE"=2 AND "D"."SECURITY_ID" IS NOT NULL)) 47 - access("C"."DEFAULT_DATATYPE_ID"="DD"."DATATYPE_ID") 48 - access("DD"."LANGUAGE"=USERENV('LANG')) filter("DD"."LANGUAGE"=USERENV('LANG')) 49 - access("D"."DATATYPE_ID"="DD"."DATATYPE_ID") 50 - access("DD"."LANGUAGE"=USERENV('LANG')) filter("DD"."LANGUAGE"=USERENV('LANG')) 51 - access("AD"."ENTITY_NAME"="DET"."DATA_OBJECT_CODE") 54 - access("FDE"."DATA_OBJECT_CODE"="TL"."DATA_OBJECT_CODE") 56 - filter("TL"."LANGUAGE"=USERENV('LANG')) 57 - access("FDE"."APPLICATION_ID"="FATL"."APPLICATION_ID" AND "FATL"."LANGUAGE"=USERENV('LANG')) 58 - access("D"."DOCUMENT_ID"="AD"."DOCUMENT_ID") 59 - access("D"."DOCUMENT_ID"="DL"."DOCUMENT_ID") 60 - access("D"."MEDIA_ID"="TXT"."MEDIA_ID") 61 - filter(("APPTL"."LANGUAGE" IS NULL OR "APPTL"."LANGUAGE"=USERENV('LANG'))) 62 - access("C"."APPLICATION_ID"="APPTL"."APPLICATION_ID") 64 - access("C"."CATEGORY_ID"="T"."CATEGORY_ID") 65 - filter("T"."LANGUAGE"=USERENV('LANG')) 66 - access("C"."CATEGORY_ID"="D"."CATEGORY_ID") 68 - access("C"."CATEGORY_ID"="CL"."CATEGORY_ID") 69 - filter("CL"."LANGUAGE"=USERENV('LANG')) 70 - access("D"."CATEGORY_ID"="C"."CATEGORY_ID") 72 - access("D"."USAGE_TYPE"="L"."LOOKUP_CODE") 74 - access("L"."LOOKUP_TYPE"='ATCHMT_DOCUMENT_TYPE' AND "L"."LANGUAGE"=USERENV('LANG')) 75 - access("MP"."ORGANIZATION_ID"="D"."SECURITY_ID") 77 - access("HAO"."ORGANIZATION_ID"="HAOTL"."ORGANIZATION_ID") 79 - access("HAOTL"."LANGUAGE"=USERENV('LANG')) filter(("HAOTL"."LANGUAGE"=USERENV('LANG') AND DECODE("HR_SECURITY"."VIEW_ALL"(),'Y','TRUE',"HR_SECURITY"."SHOW_RECORD"('H R_ALL_ORGANIZATION_UNITS',"HAOTL"."ORGANIZATION_ID"))='TRUE')) 80 - filter("HAO"."BUSINESS_GROUP_ID"=DECODE("HR_GENERAL"."GET_XBG_PROFILE"(),'Y',"HAO"."BUSINESS_GROUP_ID","HR_GENERAL"."GET_B USINESS_GROUP_ID"())) 81 - access("MP"."ORGANIZATION_ID"="HAO"."ORGANIZATION_ID") 82 - filter(("D"."SECURITY_ID" IS NOT NULL AND INTERNAL_FUNCTION("D"."SECURITY_TYPE"))) 84 - filter("DL"."LANGUAGE"=USERENV('LANG')) 86 - filter(("OBJECT_TYPE_CODE"='L' AND NVL("COMPLETE_FLAG",'Y')='Y' AND (LNNVL("D"."SECURITY_TYPE"=2) OR LNNVL("LGR"."LEDGER_ID"="D"."SECURITY_ID") OR LNNVL("D"."SECURITY_ID" IS NOT NULL)))) 184 rows selected.