Some of my end users are testing our 12c windows database and we are encountering some issues with a query(ies) that never finish processing.
We can run the same query(ies) on 11g and they finish in about 10 minutes. The explain plan for 12c is showing estimated rows at 32P (im assuming that's petabyte) and the estimated bytes at 18,447P. I know their queries need to be fine tuned but to never finish is causing us a headache and I as the dba am receiving the "it works fine on 11g, it should work on 12c"
Any insite as to why this would work on 11g and not 12c would be appreciated
Plan hash value: 1531178925
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 119K(100)| |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1 | 1051 | | 119K (1)| 00:00:05 |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 1064 | | 119K (1)| 00:00:05 |
|* 4 | VIEW | | 1 | 1064 | | 119K (1)| 00:00:05 |
|* 5 | WINDOW SORT PUSHED RANK | | 32P| 15E| | 119K (1)| 00:00:05 |
|* 6 | FILTER | | | | | | |
|* 7 | HASH JOIN SEMI | | 2 | 2806 | | 118K (1)| 00:00:05 |
|* 8 | HASH JOIN SEMI | | 248 | 296K| 27M| 77888 (1)| 00:00:04 |
| 9 | VIEW | VW_FOJ_0 | 24803 | 27M| | 10563 (1)| 00:00:01 |
|* 10 | HASH JOIN FULL OUTER | | 24803 | 27M| | 10563 (1)| 00:00:01 |
| 11 | VIEW | | 912 | 500K| | 2829 (1)| 00:00:01 |
| 12 | HASH UNIQUE | | 912 | 287K| | 2829 (1)| 00:00:01 |
|* 13 | HASH JOIN | | 912 | 287K| | 2828 (1)| 00:00:01 |
|* 14 | HASH JOIN | | 912 | 276K| | 2608 (1)| 00:00:01 |
| 15 | TABLE ACCESS FULL | DIM_ASSIGNMENT_CATEGORY | 5 | 115 | | 3 (0)| 00:00:01 |
|* 16 | HASH JOIN | | 912 | 255K| | 2605 (1)| 00:00:01 |
|* 17 | TABLE ACCESS FULL | DIM_ASSIGN_STATUS | 20 | 500 | | 3 (0)| 00:00:01 |
|* 18 | HASH JOIN | | 956 | 244K| | 2602 (1)| 00:00:01 |
|* 19 | HASH JOIN RIGHT OUTER | | 632 | 149K| | 1356 (1)| 00:00:01 |
| 20 | TABLE ACCESS FULL | DIM_TENURE_STATUS | 4 | 80 | | 3 (0)| 00:00:01 |
|* 21 | HASH JOIN OUTER | | 632 | 137K| | 1353 (1)| 00:00:01 |
|* 22 | HASH JOIN OUTER | | 54a5 | 112K| | 1281 (1)| 00:00:01 |
|* 23 | HASH JOIN OUTER | | 545 | 105K| | 1062 (1)| 00:00:01 |
|* 24 | HASH JOIN | | 545 | 98K| | 842 (1)| 00:00:01 |
|* 25 | HASH JOIN | | 546 | 78624 | | 562 (1)| 00:00:01 |
|* 26 | HASH JOIN | | 546 | 71526 | | 342 (1)| 00:00:01 |
|* 27 | HASH JOIN | | 546 | 50778 | | 329 (1)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | DIM_RANK | 337 | 6403 | | 3 (0)| 00:00:01 |
|* 29 | HASH JOIN | | 546 | 40404 | | 326 (1)| 00:00:01 |
|* 30 | TABLE ACCESS FULL | DIM_RANK | 20 | 380 | | 3 (0)| 00:00:01 |
|* 31 | HASH JOIN | | 4095 | 219K| | 323 (1)| 00:00:01 |
|* 32 | TABLE ACCESS FULL | DIM_RANK | 1 | 19 | | 3 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | FCT_HSP_APPOINTMENT | 45040 | 1583K| | 320 (1)| 00:00:01 |
| 34 | TABLE ACCESS FULL | DIM_DIVISION | 1685 | 64030 | | 13 (0)| 00:00:01 |
| 35 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
|* 36 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 29118 | 1194K| | 280 (1)| 00:00:01 |
| 37 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 38 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 39 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 17438 | 170K| | 71 (0)| 00:00:01 |
| 40 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 44181 | 862K| | 1246 (1)| 00:00:01 |
| 41 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 42 | VIEW | | 23891 | 13M| | 7734 (1)| 00:00:01 |
| 43 | HASH GROUP BY | | 23891 | 8072K| 8320K| 7734 (1)| 00:00:01 |
|* 44 | HASH JOIN OUTER | | 23891 | 8072K| | 5968 (1)| 00:00:01 |
|* 45 | HASH JOIN | | 508 | 169K| | 3048 (1)| 00:00:01 |
| 46 | TABLE ACCESS FULL | DIM_ASSIGNMENT_CATEGORY | 5 | 115 | | 3 (0)| 00:00:01 |
|* 47 | HASH JOIN | | 508 | 157K| | 3045 (1)| 00:00:01 |
|* 48 | HASH JOIN | | 509 | 151K| | 2825 (1)| 00:00:01 |
|* 49 | TABLE ACCESS FULL | DIM_ASSIGN_STATUS | 20 | 500 | | 3 (0)| 00:00:01 |
|* 50 | HASH JOIN | | 533 | 145K| | 2822 (1)| 00:00:01 |
|* 51 | HASH JOIN OUTER | | 352 | 91520 | | 1576 (1)| 00:00:01 |
|* 52 | HASH JOIN OUTER | | 352 | 86944 | | 1356 (1)| 00:00:01 |
|* 53 | HASH JOIN RIGHT OUTER | | 352 | 82368 | | 1136 (1)| 00:00:01 |
| 54 | TABLE ACCESS FULL | DIM_TENURE_STATUS | 4 | 80 | | 3 (0)| 00:00:01 |
|* 55 | HASH JOIN | | 352 | 75328 | | 1133 (1)| 00:00:01 |
|* 56 | HASH JOIN | | 352 | 70752 | | 913 (1)| 00:00:01 |
|* 57 | HASH JOIN | | 545 | 98K| | 842 (1)| 00:00:01 |
|* 58 | HASH JOIN | | 546 | 78624 | | 562 (1)| 00:00:01 |
|* 59 | HASH JOIN | | 546 | 71526 | | 342 (1)| 00:00:01 |
|* 60 | HASH JOIN | | 546 | 50778 | | 329 (1)| 00:00:01 |
|* 61 | TABLE ACCESS FULL | DIM_RANK | 337 | 6403 | | 3 (0)| 00:00:01 |
|* 62 | HASH JOIN | | 546 | 40404 | | 326 (1)| 00:00:01 |
|* 63 | TABLE ACCESS FULL | DIM_RANK | 20 | 380 | | 3 (0)| 00:00:01 |
|* 64 | HASH JOIN | | 4095 | 219K| | 323 (1)| 00:00:01 |
|* 65 | TABLE ACCESS FULL| DIM_RANK | 1 | 19 | | 3 (0)| 00:00:01 |
| 66 | TABLE ACCESS FULL| FCT_HSP_APPOINTMENT | 45040 | 1583K| | 320 (1)| 00:00:01 |
| 67 | TABLE ACCESS FULL | DIM_DIVISION | 1685 | 64030 | | 13 (0)| 00:00:01 |
| 68 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
|* 69 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 29118 | 1194K| | 280 (1)| 00:00:01 |
| 70 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 17438 | 255K| | 71 (0)| 00:00:01 |
| 71 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 72 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 73 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 74 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 44181 | 862K| | 1246 (1)| 00:00:01 |
| 75 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 76 | TABLE ACCESS FULL | FCT_HSP_SALARY | 1271K| 6206K| | 2916 (1)| 00:00:01 |
| 77 | VIEW | VW_NSO_4 | 6732K| 494M| | 37605 (1)| 00:00:02 |
|* 78 | HASH JOIN | | 6732K| 609M| | 37605 (1)| 00:00:02 |
| 79 | VIEW | VW_NSO_1 | 15429 | 406K| | 14883 (1)| 00:00:01 |
| 80 | HASH UNIQUE | | 15429 | 798K| | 14883 (1)| 00:00:01 |
| 81 | HASH GROUP BY | | 15429 | 798K| 120M| 14883 (1)| 00:00:01 |
|* 82 | HASH JOIN RIGHT OUTER | | 2104K| 106M| | 5063 (1)| 00:00:01 |
| 83 | TABLE ACCESS FULL | FCT_HSP_APPOINTMENT | 45040 | 219K| | 320 (1)| 00:00:01 |
|* 84 | HASH JOIN OUTER | | 1363K| 62M| | 4738 (1)| 00:00:01 |
|* 85 | HASH JOIN RIGHT OUTER | | 26258 | 1102K| | 1818 (1)| 00:00:01 |
| 86 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 44181 | 215K| | 1246 (1)| 00:00:01 |
|* 87 | HASH JOIN | | 17352 | 643K| | 571 (1)| 00:00:01 |
|* 88 | HASH JOIN | | 16303 | 398K| | 291 (1)| 00:00:01 |
|* 89 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 16305 | 191K| | 71 (0)| 00:00:01 |
|* 90 | TABLE ACCESS FULL | DIM_DATE | 42506 | 539K| | 220 (1)| 00:00:01 |
|* 91 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 25927 | 329K| | 280 (1)| 00:00:01 |
| 92 | TABLE ACCESS FULL | FCT_HSP_SALARY | 1271K| 6206K| | 2916 (1)| 00:00:01 |
| 93 | VIEW | | 43638 | 2897K| | 22704 (1)| 00:00:01 |
| 94 | HASH GROUP BY | | 43638 | 4602K| 214M| 22704 (1)| 00:00:01 |
|* 95 | HASH JOIN RIGHT OUTER | | 1922K| 198M| | 5508 (1)| 00:00:01 |
| 96 | TABLE ACCESS FULL | FCT_HSP_APPOINTMENT | 45040 | 219K| | 320 (1)| 00:00:01 |
|* 97 | HASH JOIN RIGHT OUTER | | 1245K| 122M| | 5184 (1)| 00:00:01 |
| 98 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 44181 | 215K| | 1246 (1)| 00:00:01 |
|* 99 | HASH JOIN OUTER | | 823K| 76M| | 3934 (1)| 00:00:01 |
|*100 | HASH JOIN | | 15855 | 1439K| | 1014 (1)| 00:00:01 |
|*101 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 25927 | 329K| | 280 (1)| 00:00:01 |
|*102 | HASH JOIN | | 14896 | 1163K| | 734 (1)| 00:00:01 |
|*103 | TABLE ACCESS FULL | DIM_TENURE_STATUS | 4 | 80 | | 3 (0)| 00:00:01 |
|*104 | HASH JOIN OUTER | | 14896 | 872K| | 731 (1)| 00:00:01 |
|*105 | HASH JOIN OUTER | | 14896 | 683K| | 511 (1)| 00:00:01 |
|*106 | HASH JOIN | | 14896 | 494K| | 291 (1)| 00:00:01 |
|*107 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 14898 | 305K| | 71 (0)| 00:00:01 |
|*108 | TABLE ACCESS FULL | DIM_DATE | 42506 | 539K| | 220 (1)| 00:00:01 |
| 109 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 110 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 111 | TABLE ACCESS FULL | FCT_HSP_SALARY | 1271K| 6206K| | 2916 (1)| 00:00:01 |
| 112 | VIEW | VW_NSO_5 | 62M| 10G| | 40906 (1)| 00:00:02 |
|*113 | HASH JOIN | | 62M| 11G| | 40906 (1)| 00:00:02 |
| 114 | VIEW | VW_NSO_3 | 20733 | 546K| | 20913 (1)| 00:00:01 |
| 115 | HASH UNIQUE | | 20733 | 2125K| | 20913 (1)| 00:00:01 |
| 116 | HASH GROUP BY | | 20733 | 2125K| 199M| 20913 (1)| 00:00:01 |
|*117 | HASH JOIN RIGHT OUTER | | 1842K| 184M| | 5144 (1)| 00:00:01 |
| 118 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 44181 | 215K| | 1246 (1)| 00:00:01 |
|*119 | HASH JOIN RIGHT OUTER | | 1217K| 116M| | 3893 (1)| 00:00:01 |
| 120 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 17438 | 87190 | | 71 (0)| 00:00:01 |
|*121 | HASH JOIN OUTER | | 1034K| 93M| | 3818 (1)| 00:00:01 |
|*122 | HASH JOIN | | 19919 | 1750K| | 899 (1)| 00:00:01 |
|*123 | TABLE ACCESS FULL | DIM_DATE | 42506 | 539K| | 220 (1)| 00:00:01 |
|*124 | HASH JOIN RIGHT SEMI | | 19919 | 1497K| | 679 (1)| 00:00:01 |
|*125 | TABLE ACCESS FULL | DIM_HSP_APPOINTMENT | 44761 | 349K| | 76 (2)| 00:00:01 |
|*126 | HASH JOIN | | 20006 | 1348K| | 603 (1)| 00:00:01 |
|*127 | HASH JOIN | | 22520 | 791K| | 323 (1)| 00:00:01 |
|*128 | TABLE ACCESS FULL | DIM_APPOINTMENT_TYPE | 1 | 15 | | 3 (0)| 00:00:01 |
| 129 | TABLE ACCESS FULL | FCT_HSP_APPOINTMENT | 45040 | 923K| | 320 (1)| 00:00:01 |
|*130 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 25927 | 835K| | 280 (1)| 00:00:01 |
| 131 | TABLE ACCESS FULL | FCT_HSP_SALARY | 1271K| 6206K| | 2916 (1)| 00:00:01 |
| 132 | VIEW | | 299K| 49M| | 19830 (1)| 00:00:01 |
| 133 | HASH GROUP BY | | 299K| 64M| 66M| 19830 (1)| 00:00:01 |
|*134 | HASH JOIN OUTER | | 299K| 64M| | 5158 (1)| 00:00:01 |
|*135 | HASH JOIN RIGHT OUTER | | 6379 | 1376K| | 2238 (1)| 00:00:01 |
| 136 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 17438 | 87190 | | 71 (0)| 00:00:01 |
|*137 | HASH JOIN | | 5499 | 1159K| | 2167 (1)| 00:00:01 |
|*138 | TABLE ACCESS FULL | DIM_DIVISION | 344 | 13072 | | 13 (0)| 00:00:01 |
|*139 | HASH JOIN OUTER | | 6155 | 1069K| | 2154 (1)| 00:00:01 |
|*140 | HASH JOIN | | 4067 | 687K| | 907 (1)| 00:00:01 |
|*141 | HASH JOIN SEMI | | 4067 | 635K| | 688 (1)| 00:00:01 |
|*142 | HASH JOIN | | 4085 | 606K| | 612 (1)| 00:00:01 |
|*143 | HASH JOIN | | 4095 | 475K| | 332 (1)| 00:00:01 |
| 144 | TABLE ACCESS FULL | DIM_RANK | 339 | 6441 | | 3 (0)| 00:00:01 |
|*145 | HASH JOIN | | 4095 | 399K| | 329 (1)| 00:00:01 |
| 146 | TABLE ACCESS FULL | DIM_RANK | 339 | 6441 | | 3 (0)| 00:00:01 |
|*147 | HASH JOIN | | 4095 | 323K| | 326 (1)| 00:00:01 |
|*148 | TABLE ACCESS FULL | DIM_RANK | 2 | 38 | | 3 (0)| 00:00:01 |
|*149 | HASH JOIN | | 22520 | 1363K| | 323 (1)| 00:00:01 |
|*150 | TABLE ACCESS FULL | DIM_APPOINTMENT_TYPE | 1 | 15 | | 3 (0)| 00:00:01 |
| 151 | TABLE ACCESS FULL | FCT_HSP_APPOINTMENT | 45040 | 2067K| | 320 (1)| 00:00:01 |
|*152 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 29118 | 938K| | 280 (1)| 00:00:01 |
|*153 | TABLE ACCESS FULL | DIM_HSP_APPOINTMENT | 44761 | 349K| | 76 (2)| 00:00:01 |
| 154 | TABLE ACCESS FULL | DIM_DATE | 109K| 1391K| | 220 (1)| 00:00:01 |
| 155 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 44181 | 215K| | 1246 (1)| 00:00:01 |
| 156 | TABLE ACCESS FULL | FCT_HSP_SALARY | 1271K| 6206K| | 2916 (1)| 00:00:01 |
| 157 | NESTED LOOPS | | 2 | 190 | | 257 (1)| 00:00:01 |
| 158 | VIEW | VW_NSO_2 | 1 | 27 | | 2 (0)| 00:00:01 |
| 159 | SORT UNIQUE | | 15429 | 798K| | 20892 (1)| 00:00:01 |
| 160 | SORT GROUP BY | | 15429 | 798K| 195M| 20892 (1)| 00:00:01 |
|*161 | HASH JOIN RIGHT OUTER | | 3406K| 172M| | 5074 (1)| 00:00:01 |
| 162 | TABLE ACCESS FULL | FCT_HSP_APPOINTMENT | 45040 | 219K| | 320 (1)| 00:00:01 |
|*163 | HASH JOIN RIGHT OUTER | | 2207K| 101M| | 4745 (1)| 00:00:01 |
| 164 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 17438 | 87190 | | 71 (0)| 00:00:01 |
|*165 | HASH JOIN OUTER | | 1875K| 76M| | 4668 (1)| 00:00:01 |
|*166 | HASH JOIN | | 36116 | 1340K| | 1747 (1)| 00:00:01 |
|*167 | TABLE ACCESS FULL | DIM_DATE | 42506 | 539K| | 220 (1)| 00:00:01 |
|*168 | HASH JOIN | | 36131 | 882K| | 1526 (1)| 00:00:01 |
|*169 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 25927 | 329K| | 280 (1)| 00:00:01 |
|*170 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 39187 | 459K| | 1247 (1)| 00:00:01 |
| 171 | TABLE ACCESS FULL | FCT_HSP_SALARY | 1271K| 6206K| | 2916 (1)| 00:00:01 |
|*172 | VIEW | | 2 | 136 | | 255 (1)| 00:00:01 |
|*173 | FILTER | | | | | | |
| 174 | SORT GROUP BY | | 13622 | 1476K| 156M| 17346 (1)| 00:00:01 |
|*175 | HASH JOIN RIGHT OUTER | | 1362K| 144M| | 5067 (1)| 00:00:01 |
| 176 | TABLE ACCESS FULL | FCT_HSP_APPOINTMENT | 45040 | 219K| | 320 (1)| 00:00:01 |
|*177 | HASH JOIN OUTER | | 882K| 89M| | 4744 (1)| 00:00:01 |
|*178 | HASH JOIN RIGHT OUTER | | 16998 | 1676K| | 1824 (1)| 00:00:01 |
| 179 | TABLE ACCESS FULL | FCT_HSP_TENURE_EVENT | 17438 | 87190 | | 71 (0)| 00:00:01 |
|*180 | HASH JOIN | | 14442 | 1353K| | 1753 (1)| 00:00:01 |
| 181 | TABLE ACCESS FULL | DIM_ASSIGN_STATUS | 22 | 550 | | 3 (0)| 00:00:01 |
|*182 | HASH JOIN | | 14445 | 1001K| | 1749 (1)| 00:00:01 |
|*183 | HASH JOIN | | 14451 | 818K| | 1530 (1)| 00:00:01 |
|*184 | TABLE ACCESS FULL | DIM_HSP_EMPLOYEE | 25927 | 329K| | 280 (1)| 00:00:01 |
|*185 | HASH JOIN | | 15673 | 688K| | 1250 (1)| 00:00:01 |
|*186 | TABLE ACCESS FULL | DIM_ASSIGNMENT_CATEGORY | 2 | 46 | | 3 (0)| 00:00:01 |
|*187 | TABLE ACCESS FULL | FCT_HSP_ASSIGNMENT | 39187 | 841K| | 1247 (1)| 00:00:01 |
|*188 | TABLE ACCESS FULL | DIM_DATE | 42506 | 539K| | 220 (1)| 00:00:01 |
| 189 | TABLE ACCESS FULL | FCT_HSP_SALARY | 1271K| 6206K| | 2916 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10000001)
3 - filter(ROWNUM<=10000001)
4 - filter("D1"."C22"=1)
5 - filter(ROW_NUMBER() OVER ( PARTITION BY COALESCE("D1"."C3","D2"."C15"),COALESCE("D1"."C4","D2"."C6"),COALESCE("D1"
."C5","D2"."C11"),COALESCE("D1"."C6","D2"."C12"),COALESCE("D1"."C7","D2"."C7"),COALESCE("D1"."C8","D2"."C4"),COALESCE("D1
"."C9","D2"."C9"),COALESCE("D1"."C10","D2"."C5"),COALESCE("D1"."C11","D2"."C13"),COALESCE("D1"."C12","D2"."C14"),COALESCE
("D1"."C13","D2"."C8"),COALESCE("D1"."C14","D2"."C10") ORDER BY
COALESCE("D1"."C3","D2"."C15"),COALESCE("D1"."C4","D2"."C6"),COALESCE("D1"."C5","D2"."C11"),COALESCE("D1"."C6","D2"."C12"
),COALESCE("D1"."C7","D2"."C7"),COALESCE("D1"."C8","D2"."C4"),COALESCE("D1"."C9","D2"."C9"),COALESCE("D1"."C10","D2"."C5"
),COALESCE("D1"."C11","D2"."C13"),COALESCE("D1"."C12","D2"."C14"),COALESCE("D1"."C13","D2"."C8"),COALESCE("D1"."C14","D2"
."C10"))<=1)
6 - filter( IS NOT NULL)
7 - access("C1"=CASE WHEN ("D1"."C6" IS NOT NULL) THEN "D1"."C6" WHEN ("D2"."C12" IS NOT NULL) THEN "D2"."C12" END
||CAST(INTERNAL_FUNCTION("D2"."C2") AS VARCHAR ( 40 ) )||CASE WHEN ("D1"."C11" IS NOT NULL) THEN "D1"."C11" WHEN
("D2"."C13" IS NOT NULL) THEN "D2"."C13" END ||"D1"."C1"||"D1"."C2")
8 - access("C1"=CASE WHEN ("D1"."C6" IS NOT NULL) THEN "D1"."C6" WHEN ("D2"."C12" IS NOT NULL) THEN "D2"."C12" END
||CAST(INTERNAL_FUNCTION("D2"."C1") AS VARCHAR ( 40 ) )||CASE WHEN ("D1"."C3" IS NOT NULL) THEN "D1"."C3" WHEN
("D2"."C15" IS NOT NULL) THEN "D2"."C15" END )
10 - access(SYS_OP_MAP_NONNULL("D1"."C13")=SYS_OP_MAP_NONNULL("D2"."C8") AND
SYS_OP_MAP_NONNULL("D1"."C12")=SYS_OP_MAP_NONNULL("D2"."C14") AND
SYS_OP_MAP_NONNULL("D1"."C11")=SYS_OP_MAP_NONNULL("D2"."C13") AND
SYS_OP_MAP_NONNULL("D1"."C10")=SYS_OP_MAP_NONNULL("D2"."C5") AND
SYS_OP_MAP_NONNULL("D1"."C9")=SYS_OP_MAP_NONNULL("D2"."C9") AND
SYS_OP_MAP_NONNULL("D1"."C8")=SYS_OP_MAP_NONNULL("D2"."C4") AND
SYS_OP_MAP_NONNULL("D1"."C7")=SYS_OP_MAP_NONNULL("D2"."C7") AND
SYS_OP_MAP_NONNULL("D1"."C6")=SYS_OP_MAP_NONNULL("D2"."C12") AND
SYS_OP_MAP_NONNULL("D1"."C5")=SYS_OP_MAP_NONNULL("D2"."C11") AND
SYS_OP_MAP_NONNULL("D1"."C4")=SYS_OP_MAP_NONNULL("D2"."C6") AND "D1"."C14"="D2"."C10" AND
SYS_OP_MAP_NONNULL("D1"."C3")=SYS_OP_MAP_NONNULL("D2"."C15"))
13 - access("T13347"."HSP_ASSIGN_EFFECTIVE_KEY"="T13375"."DATE_KEY")
14 - access("T13318"."ASSIGNMENT_CATEGORY_KEY"="T13347"."HSP_ASSIGN_CATEGORY_KEY")
16 - access("T13314"."ASSIGN_STATUS_KEY"="T13347"."HSP_ASSIGN_STATUS_KEY")
17 - filter(("T13314"."ASSIGN_STATUS_DESC"<>'Terminated (Paid)' AND "T13314"."ASSIGN_STATUS_DESC"<>'Terminated
(Unpaid)'))
18 - access("T11345"."HSP_ATM_EMPLOYEE_KEY"="T13347"."HSP_EMPLOYEE_ID")
19 - access("T16427"."TENURE_STATUS_KEY"="T16494"."HSP_TEN_STATUS_TYPE_KEY")
21 - access("T11345"."HSP_ATM_EMPLOYEE_KEY"="T16494"."HSP_EMPLOYEE_ID")
22 - access("T11345"."HSP_BIRTH_DATE_KEY"="T11497"."DATE_KEY")
23 - access("T11345"."HSP_DATE_FIRST_HIRED_KEY"="T11511"."DATE_KEY")
24 - access("T11345"."HSP_ATM_EMPLOYEE_KEY"="T11361"."HSP_EMPLOYEE_ID")
25 - access("T11361"."HSP_APPOINT_EFFECTIVE_DT_KEY"="T11469"."DATE_KEY")
26 - access("T11361"."HSP_DIVISION_KEY"="T11539"."DIVISION_KEY")
27 - access("T11361"."HSP_RANK_TWO_KEY"="T11592"."RANK_KEY")
28 - filter("T11592"."RANK_NAME"<>'Scholar')
29 - access("T11361"."HSP_RANK_THREE_KEY"="T11598"."RANK_KEY")
30 - filter(("T11598"."RANK_NAME"='Visiting' OR "T11598"."RANK_NAME"='Visiting Adjunct' OR
"T11598"."RANK_NAME"='Visiting Adjunct Associate' OR "T11598"."RANK_NAME"='Visiting Assistant' OR
"T11598"."RANK_NAME"='Visiting Associate' OR "T11598"."RANK_NAME"='Visiting Clinical' OR "T11598"."RANK_NAME"='Visiting
Clinical Associate' OR "T11598"."RANK_NAME"='Visiting Demonstration' OR "T11598"."RANK_NAME"='Visiting Laboratory' OR
"T11598"."RANK_NAME"='Visiting Research' OR "T11598"."RANK_NAME"='Visiting Research Assistant' OR
"T11598"."RANK_NAME"='Visiting Research Associate' OR "T11598"."RANK_NAME"='Visiting Senior'))
31 - access("T11361"."HSP_RANK_ONE_KEY"="T11586"."RANK_KEY")
32 - filter("T11586"."RANK_NAME"='Faculty')
36 - filter("T11345"."HSP_CURRENT_FLG"='Y')
44 - access("T11345"."HSP_ATM_EMPLOYEE_KEY"="T18974"."HSP_EMPLOYEE_ID")
45 - access("T13318"."ASSIGNMENT_CATEGORY_KEY"="T13347"."HSP_ASSIGN_CATEGORY_KEY")
47 - access("T13347"."HSP_ASSIGN_EFFECTIVE_KEY"="T13375"."DATE_KEY")
48 - access("T13314"."ASSIGN_STATUS_KEY"="T13347"."HSP_ASSIGN_STATUS_KEY")
49 - filter(("T13314"."ASSIGN_STATUS_DESC"<>'Terminated (Paid)' AND "T13314"."ASSIGN_STATUS_DESC"<>'Terminated
(Unpaid)'))
50 - access("T11345"."HSP_ATM_EMPLOYEE_KEY"="T13347"."HSP_EMPLOYEE_ID")
51 - access("T11345"."HSP_BIRTH_DATE_KEY"="T11497"."DATE_KEY")
52 - access("T11345"."HSP_DATE_FIRST_HIRED_KEY"="T11511"."DATE_KEY")
53 - access("T16427"."TENURE_STATUS_KEY"="T16494"."HSP_TEN_STATUS_TYPE_KEY")
55 - access("T16494"."HSP_TEN_EFFECTIVE_DTE_KEY"="T16558"."DATE_KEY")
56 - access("T11345"."HSP_ATM_EMPLOYEE_KEY"="T16494"."HSP_EMPLOYEE_ID")
57 - access("T11345"."HSP_ATM_EMPLOYEE_KEY"="T11361"."HSP_EMPLOYEE_ID")
58 - access("T11361"."HSP_APPOINT_EFFECTIVE_DT_KEY"="T11469"."DATE_KEY")
59 - access("T11361"."HSP_DIVISION_KEY"="T11539"."DIVISION_KEY")
60 - access("T11361"."HSP_RANK_TWO_KEY"="T11592"."RANK_KEY")