SELECT V_IDENTIFIER_LOOKUP.UID_V_IDENTIFIER_LOOKUP AS "UID", V_IDENTIFIER_LOOKUP.ABA, V_IDENTIFIER_LOOKUP.ADDRESS1, V_IDENTIFIER_LOOKUP.ADDRESS2, V_IDENTIFIER_LOOKUP.ADDRESS3, V_IDENTIFIER_LOOKUP.ADDRESS4, V_IDENTIFIER_LOOKUP.ALIAS, V_IDENTIFIER_LOOKUP.CITY, V_IDENTIFIER_LOOKUP.COUNTRYCODE, V_IDENTIFIER_LOOKUP.CUST_CODE, V_IDENTIFIER_LOOKUP.CUST_NAME, V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN, V_IDENTIFIER_LOOKUP.IDENTIFIER, V_IDENTIFIER_LOOKUP.IDENTIFIER_TYPE, PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- V_IDENTIFIER_LOOKUP.ID_TYPE_SWIFT, V_IDENTIFIER_LOOKUP.OFFICE, V_IDENTIFIER_LOOKUP.ORIGIN_PROFILE_ID, V_IDENTIFIER_LOOKUP.ORIGIN_PROFILE_UID_COL_NAME, V_IDENTIFIER_LOOKUP.PROPRIETARY, V_IDENTIFIER_LOOKUP.STATE, V_IDENTIFIER_LOOKUP.SWIFT_ID, V_IDENTIFIER_LOOKUP.UID_ORIGIN_PROFILE, V_IDENTIFIER_LOOKUP.ZIP, V_IDENTIFIER_LOOKUP.LL_CUST_NAME, V_IDENTIFIER_LOOKUP.LL_ADDRESS1, V_IDENTIFIER_LOOKUP.LL_ADDRESS2, V_IDENTIFIER_LOOKUP.LL_ADDRESS3, V_IDENTIFIER_LOOKUP.LL_ADDRESS4, V_IDENTIFIER_LOOKUP.LL_CITY, PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- V_IDENTIFIER_LOOKUP.LL_ZIPCODE, V_IDENTIFIER_LOOKUP.LL_STATE, V_IDENTIFIER_LOOKUP.LL_BRANCH, V_IDENTIFIER_LOOKUP.LL_POBOX FROM V_IDENTIFIER_LOOKUP V_IDENTIFIER_LOOKUP, ( SELECT MAX(uid_v_identifier_lookup) KEEP (DENSE_RANK FIRST ORDER BY V_IDENTIFIER_LOOKUP.HEAD_OFFICE_IN DESC) uid_v_identifier_lookup FROM v_identifier_lookup WHERE ( (UPPER (V_IDENTIFIER_LOOKUP.CITY) LIKE 'CHARLOTTE%' ESCAPE '\' OR UPPER (V_IDENTIFIER_LOOKUP.LL_CITY) LIKE 'CHARLOTTE%' ESCAPE '\') AND PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- (V_IDENTIFIER_LOOKUP.STATE LIKE 'NC%' ESCAPE '\' OR V_IDENTIFIER_LOOKUP.LL_STATE LIKE 'NC%' ESCAPE '\') AND V_IDENTIFIER_LOOKUP.COUNTRYCODE LIKE 'US%' ESCAPE '\') GROUP BY identifier_type, identifier) b WHERE V_IDENTIFIER_LOOKUP.uid_v_identifier_lookup = b.uid_v_identifier_lookup AND ( (UPPER (V_IDENTIFIER_LOOKUP.CITY) LIKE 'CHARLOTTE%' ESCAPE '\' OR UPPER (V_IDENTIFIER_LOOKUP.LL_CITY) LIKE 'CHARLOTTE%' ESCAPE '\') AND (V_IDENTIFIER_LOOKUP.STATE LIKE 'NC%' ESCAPE '\' OR V_IDENTIFIER_LOOKUP.LL_STATE LIKE 'NC%' ESCAPE '\') AND V_IDENTIFIER_LOOKUP.COUNTRYCODE PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- LIKE 'US%' ESCAPE '\') AND ROWNUM <= 1500 Plan hash value: 41870561 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5977 (100)| | | 1 | COUNT STOPKEY | | | | | | | 2 | HASH JOIN | | 1 | 2043 | 5977 (1)| 00:01:12 | | 3 | VIEW | | 10 | 640 | 2985 (1)| 00:00:36 | PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 4 | SORT GROUP BY | | 10 | 900 | 2985 (1)| 00:00:36 | | 5 | VIEW | | 10 | 900 | 2984 (1)| 00:00:36 | | 6 | UNION-ALL | | | | | | | 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 450 | 38 (29)| 00:00:01 | | 8 | INDEX FULL SCAN | IX_SWIFTID | 231 | | 37 (30)| 00:00:01 | | 9 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 453 | 827 (1)| 00:00:10 | | 10 | INDEX FULL SCAN | IX_CUSTOMRS_ABA | 1155K| | 26 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 450 | 67 (2)| 00:00:01 | | 12 | INDEX FULL SCAN | IX_CUSTOMRS_ALIAS | 57789 | | 27 (4)| 00:00:01 | | 13 | NESTED LOOPS | | 7 | 3423 | 2052 (1)| 00:00:25 | | 14 | NESTED LOOPS | | 7 | 3381 | 2052 (1)| 00:00:25 | PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 15 | TABLE ACCESS BY INDEX ROWID| CUSTOMRS | 5 | 2225 | 2040 (1)| 00:00:25 | | 16 | INDEX RANGE SCAN | IX_CUSTOMRS | 331K| | 1810 (1)| 00:00:22 | | 17 | TABLE ACCESS BY INDEX ROWID| NCC | 1 | 38 | 3 (0)| 00:00:01 | | 18 | INDEX RANGE SCAN | IX_NCC_2 | 1 | | 2 (0)| 00:00:01 | | 19 | INDEX UNIQUE SCAN | IX_COUNTRY_NCC_2 | 1 | 6 | 0 (0)| | | 20 | VIEW | | 10 | 19790 | 2991 (1)| 00:00:36 | | 21 | UNION-ALL | | | | | | | 22 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 1873 | 38 (29)| 00:00:01 | | 23 | INDEX FULL SCAN | IX_SWIFTID | 231 | | 37 (30)| 00:00:01 | | 24 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 1873 | 827 (1)| 00:00:10 | | 25 | INDEX FULL SCAN | IX_CUSTOMRS_ABA | 1155K| | 26 (0)| 00:00:01 | PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 26 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 1 | 1873 | 67 (2)| 00:00:01 | | 27 | INDEX FULL SCAN | IX_CUSTOMRS_ALIAS | 57789 | | 27 (4)| 00:00:01 | | 28 | NESTED LOOPS | | | | | | | 29 | NESTED LOOPS | | 7 | 13545 | 2059 (1)| 00:00:25 | | 30 | NESTED LOOPS | | 7 | 13482 | 2052 (1)| 00:00:25 | | 31 | TABLE ACCESS BY INDEX ROWID | CUSTOMRS | 5 | 9320 | 2040 (1)| 00:00:25 | | 32 | INDEX RANGE SCAN | IX_CUSTOMRS | 331K| | 1810 (1)| 00:00:22 | | 33 | TABLE ACCESS BY INDEX ROWID | NCC | 1 | 62 | 3 (0)| 00:00:01 | | 34 | INDEX RANGE SCAN | IX_NCC_2 | 1 | | 2 (0)| 00:00:01 | | 35 | INDEX UNIQUE SCAN | IX_COUNTRY_NCC_2 | 1 | | 0 (0)| | | 36 | TABLE ACCESS BY INDEX ROWID | COUNTRY_NCC | 1 | 9 | 1 (0)| 00:00:01 | PLAN_TABLE_OUTPUT