SELECT DISTINCT a.le_code, a.le_name, f.div_id, f.div_name, a.cust_ref_key, b.bill_accnt_key, c.address_key, i.address_key, b.active_flg, b.bill_accnt_num, g.telephone_number, f.org_id, a.curr_cust_key, a.curr_cust_group_key, NULL, f.sec_id, f.sec_name, f.sub_sec_id, f.sub_sec_name, f.sac_key, f.sac_name, SUBSTR (b.bill_accnt_num, 0, 2) district_code, SUBSTR (b.bill_accnt_num, 3) accnt_number, CASE WHEN j.cssaccount is null then 'BB' else 'PSTN' END accnt_type, a.cust_class, b.billing_account_key, f.team_key FROM layer1.customer a, layer1.account b, layer1.profile c, layer1.cust_ref d, layer1.cust_hierachy f, layer1.assets g, layer1.cust_addr i bowner.bower_view j WHERE a.cust_ref_key = d.cust_ref_key AND d.billing_account_key = b.billing_account_key AND b.billing_account_key = c.billing_account_key AND f.cust_group_key = a.curr_cust_group_key AND g.billing_account_key = b.billing_account_key AND a.cust_ref_key = i.cust_ref_key and j.cssaccount(+) = b.BILL_ACCNT_NUM AND a.deleted_flg = 'N' AND b.deleted_flg = 'N' AND c.deleted_flg = 'N' AND d.deleted_flg = 'N' AND f.active_flg = 'Y' AND a.active_flg = 'Y' AND g.deleted_flg = 'N' AND g.active_flg = 'Y' and a.CUST_CLASS = 'BUSINESS' AND a.cust_type_flag = 'CUS' and le_code is not null; PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- Plan hash value: 3626932623 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 31M| 11G| | 1906K (1)| 07:24:50 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10009 | 31M| 11G| | 1906K (1)| 07:24:50 | | | Q1,09 | P->S | QC (RAND) | | 3 | HASH UNIQUE | | 31M| 11G| 24G| 1906K (1)| 07:24:50 | | | Q1,09 | PCWP | | |* 4 | HASH JOIN | | 31M| 11G| | 1464K (1)| 05:41:39 | | | Q1,09 | PCWP | | | 5 | PX RECEIVE | | 3083K| 499M| | 6299 (1)| 00:01:29 | | | Q1,09 | PCWP | | | 6 | PX SEND HASH | :TQ10007 | 3083K| 499M| | 6299 (1)| 00:01:29 | | | Q1,07 | P->P | HASH | | 7 | PX BLOCK ITERATOR | | 3083K| 499M| | 6299 (1)| 00:01:29 | | | Q1,07 | PCWC | | |* 8 | TABLE ACCESS FULL | cust_hierachy | 3083K| 499M| | 6299 (1)| 00:01:29 | | | Q1,07 | PCWP | | | 9 | PX RECEIVE | | 18M| 3640M| | 1457K (1)| 05:40:10 | | | Q1,09 | PCWP | | | 10 | PX SEND HASH | :TQ10008 | 18M| 3640M| | 1457K (1)| 05:40:10 | | | Q1,08 | P->P | HASH | |* 11 | HASH JOIN RIGHT OUTER BUFFERED | | 18M| 3640M| | 1457K (1)| 05:40:10 | | | Q1,08 | PCWP | | | 12 | PX RECEIVE | | 1486K| 12M| | 6211 (1)| 00:01:27 | | | Q1,08 | PCWP | | | 13 | PX SEND BROADCAST | :TQ10004 | 1486K| 12M| | 6211 (1)| 00:01:27 | | | Q1,04 | P->P | BROADCAST | | 14 | PX BLOCK ITERATOR | | 1486K| 12M| | 6211 (1)| 00:01:27 | | | Q1,04 | PCWC | | |* 15 | TABLE ACCESS FULL | bower_view | 1486K| 12M| | 6211 (1)| 00:01:27 | | | Q1,04 | PCWP | | |* 16 | HASH JOIN | | 18M| 3485M| | 1451K (1)| 05:38:42 | | | Q1,08 | PCWP | | | 17 | PX RECEIVE | | 2109K| 277M| | 188K (2)| 00:43:54 | | | Q1,08 | PCWP | | | 18 | PX SEND HASH | :TQ10005 | 2109K| 277M| | 188K (2)| 00:43:54 | | | Q1,05 | P->P | HASH | |* 19 | HASH JOIN | | 2109K| 277M| | 188K (2)| 00:43:54 | | | Q1,05 | PCWP | | | 20 | PX RECEIVE | | 950K| 114M| | 160K (2)| 00:37:32 | | | Q1,05 | PCWP | | | 21 | PX SEND BROADCAST | :TQ10003 | 950K| 114M| | 160K (2)| 00:37:32 | | | Q1,03 | P->P | BROADCAST | |* 22 | HASH JOIN | | 950K| 114M| | 160K (2)| 00:37:32 | | | Q1,03 | PCWP | | | 23 | PX RECEIVE | | 950K| 96M| | 122K (2)| 00:28:42 | | | Q1,03 | PCWP | | | 24 | PX SEND BROADCAST | :TQ10002 | 950K| 96M| | 122K (2)| 00:28:42 | | | Q1,02 | P->P | BROADCAST | |* 25 | HASH JOIN | | 950K| 96M| | 122K (2)| 00:28:42 | | | Q1,02 | PCWP | | | 26 | PX RECEIVE | | 950K| 63M| | 84673 (2)| 00:19:46 | | | Q1,02 | PCWP | | | 27 | PX SEND BROADCAST LOCAL | :TQ10001 | 950K| 63M| | 84673 (2)| 00:19:46 | | | Q1,01 | P->P | BCST LOCAL | |* 28 | HASH JOIN | | 950K| 63M| | 84673 (2)| 00:19:46 | | | Q1,01 | PCWP | | | 29 | PX RECEIVE | | 74M| 989M| | 43683 (2)| 00:10:12 | | | Q1,01 | PCWP | | | 30 | PX SEND BROADCAST | :TQ10000 | 74M| 989M| | 43683 (2)| 00:10:12 | | | Q1,00 | P->P | BROADCAST | | 31 | PX BLOCK ITERATOR | | 74M| 989M| | 43683 (2)| 00:10:12 | 1 | 28 | Q1,00 | PCWC | | |* 32 | TABLE ACCESS FULL | cust_ref | 74M| 989M| | 43683 (2)| 00:10:12 | 1 | 28 | Q1,00 | PCWP | | | 33 | PX PARTITION HASH ALL | | 478K| 25M| | 40885 (1)| 00:09:33 | 1 | 32 | Q1,01 | PCWC | | |* 34 | TABLE ACCESS BY LOCAL INDEX ROWID| customer | 478K| 25M| | 40885 (1)| 00:09:33 | 1 | 32 | Q1,01 | PCWP | | |* 35 | INDEX RANGE SCAN | I_customer12 | 7909K| | | 3380 (1)| 00:00:48 | 1 | 32 | Q1,01 | PCWP | | | 36 | PX BLOCK ITERATOR | | 25M| 866M| | 38254 (2)| 00:08:56 | 1 | 32 | Q1,02 | PCWC | | |* 37 | TABLE ACCESS FULL | account | 25M| 866M| | 38254 (2)| 00:08:56 | 1 | 32 | Q1,02 | PCWP | | | 38 | PX BLOCK ITERATOR | | 26M| 506M| | 37790 (2)| 00:08:50 | 1 | 32 | Q1,03 | PCWC | | |* 39 | TABLE ACCESS FULL | profile | 26M| 506M| | 37790 (2)| 00:08:50 | 1 | 32 | Q1,03 | PCWP | | | 40 | PX BLOCK ITERATOR | | 82M| 947M| | 27225 (2)| 00:06:22 | 1 | 22 | Q1,05 | PCWC | | | 41 | TABLE ACCESS FULL | cust_addr | 82M| 947M| | 27225 (2)| 00:06:22 | 1 | 22 | Q1,05 | PCWP | | | 42 | PX RECEIVE | | 208M| 12G| | 1263K (1)| 04:54:44 | | | Q1,08 | PCWP | | | 43 | PX SEND HASH | :TQ10006 | 208M| 12G| | 1263K (1)| 04:54:44 | | | Q1,06 | P->P | HASH | | 44 | VIEW | assets | 208M| 12G| | 1263K (1)| 04:54:44 | | | Q1,06 | PCWP | | | 45 | UNION-ALL | | | | | | | | | Q1,06 | PCWP | | | 46 | PX BLOCK ITERATOR | | 22M| 284M| | 58057 (2)| 00:13:33 | 1 | 64 | Q1,06 | PCWC | | |* 47 | TABLE ACCESS FULL | assets_OV | 22M| 284M| | 58057 (2)| 00:13:33 | 1 | 64 | Q1,06 | PCWP | | | 48 | PX BLOCK ITERATOR | | 4693K| 67M| | 6451 (2)| 00:01:31 | 1 | 64 | Q1,06 | PCWC | | |* 49 | TABLE ACCESS FULL | assets_C2B | 4693K| 67M| | 6451 (2)| 00:01:31 | 1 | 64 | Q1,06 | PCWP | | | 50 | PX BLOCK ITERATOR | | 180M| 3449M| | 1198K (1)| 04:39:41 | 1 | 64 | Q1,06 | PCWC | | |* 51 | TABLE ACCESS FULL | assets_IRS | 180M| 3449M| | 1198K (1)| 04:39:41 | 1 | 64 | Q1,06 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------