select  cmicus.customer_number,acct.account_number,cmactl.address_type,cmaddr.address_line1,cmaddr.city,cmaddr.city_abbreviation,demo.nationality_code,demo.gender_code,demo.date_of_birth,demo.place_of_birth,
        acct.current_bill_cycle,acct.last_bill_period,cmicus.id_type,cmicus.id_number,cmicus.city_code,cmicus.effective_date,
        cmicus.expiry_date as cust_expiry_date,acct.acc_expiry_date,sassip.customer_segment,sassip.customer_value_segment,
        cmcont.contact_type,cmcont.contact_number,cmcont.contact_name,cmcont.customer_job_desc
from    dm_icms_ll.customer_id_cmicus00 cmicus,
        dm_icms_ll.account_cmacct00 acct,
        dm_icms_ll.customer_segment_sassip sassip,
        dm_icms_ll.customer_demographic_sacmnt00 demo,
        dm_icms_ll.address_control_cmactl00 cmactl,
        dm_icms_ll.address_details_cmaddr00 cmaddr,
        dm_icms_ll.contact_details_cmcont00 cmcont,
        dm_icms_ll.service_type_info_blinfo blinfo
where   acct.acc_expiry_date > sysdate
and     cmicus.customer_number = acct.customer_number
and     cmicus.id_type = sassip.id_type
and     cmicus.id_number = sassip.id_number
and     acct.customer_number = demo.customer_number
and     acct.customer_number = cmactl.customer_number
and     cmactl.address_control_number = cmaddr.address_control_number
and     cmicus.customer_number = cmcont.customer_number
and     cmicus.customer_number = '180672184'

PLAN_TABLE_OUTPUT

Plan hash value: 3526741266
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                |    44M|    14G| 15753   (2)| 00:03:10 |
|   1 |  MERGE JOIN CARTESIAN                |                                |    44M|    14G| 15753   (2)| 00:03:10 |
|   2 |   MERGE JOIN CARTESIAN               |                                |     1 |   349 |     2   (0)| 00:00:01 |
|   3 |    MERGE JOIN CARTESIAN              |                                |     1 |   321 |     2   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                     |                                |       |       |            |          |
|   5 |      NESTED LOOPS                    |                                |     1 |   229 |     2   (0)| 00:00:01 |
|   6 |       MERGE JOIN CARTESIAN           |                                |     1 |   207 |     2   (0)| 00:00:01 |
|   7 |        MERGE JOIN CARTESIAN          |                                |     1 |   162 |     2   (0)| 00:00:01 |
|   8 |         NESTED LOOPS                 |                                |       |       |            |          |
|   9 |          NESTED LOOPS                |                                |     1 |   122 |     2   (0)| 00:00:01 |
|  10 |           TABLE ACCESS BY INDEX ROWID| ADDRESS_CONTROL_CMACTL00       |     1 |    22 |     1   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN          | ADDRESS_CONTROL_CSTMR_NBR_IX   |     1 |       |     1   (0)| 00:00:01 |
|* 12 |           INDEX RANGE SCAN           | ADDRESS_DETAILS_CMADDR00_IX1   |     1 |       |     1   (0)| 00:00:01 |
|  13 |          TABLE ACCESS BY INDEX ROWID | ADDRESS_DETAILS_CMADDR00       |     1 |   100 |     1   (0)| 00:00:01 |
|  14 |         BUFFER SORT                  |                                |     1 |    40 |     1   (0)| 00:00:01 |
|* 15 |          TABLE ACCESS BY INDEX ROWID | ACCOUNT_CMACCT00               |     1 |    40 |     1   (0)| 00:00:01 |
|* 16 |           INDEX RANGE SCAN           | ACCOUNT_CMACCT00_CTMR_NBR_1_IX |     2 |       |     1   (0)| 00:00:01 |
|  17 |        BUFFER SORT                   |                                |     1 |    45 |     1   (0)| 00:00:01 |
|  18 |         TABLE ACCESS BY INDEX ROWID  | CUSTOMER_ID_CMICUS00           |     1 |    45 |     1   (0)| 00:00:01 |
|* 19 |          INDEX RANGE SCAN            | CUSTOMER_ID_CUSTNO_01_IX       |     1 |       |     1   (0)| 00:00:01 |
|* 20 |       INDEX RANGE SCAN               | CUSTOMER_SEGMENT_SAS_ID_NUM_IX |     1 |       |     1   (0)| 00:00:01 |
|* 21 |      TABLE ACCESS BY INDEX ROWID     | CUSTOMER_SEGMENT_SASSIP        |     1 |    22 |     1   (0)| 00:00:01 |
|  22 |     BUFFER SORT                      |                                |     1 |    92 |     1   (0)| 00:00:01 |
|  23 |      TABLE ACCESS BY INDEX ROWID     | CONTACT_DETAILS_CMCONT00       |     1 |    92 |     1   (0)| 00:00:01 |
|* 24 |       INDEX RANGE SCAN               | CUST_NUM_INDX                  |     1 |       |     1   (0)| 00:00:01 |
|  25 |    BUFFER SORT                       |                                |     1 |    28 |     1   (0)| 00:00:01 |
|  26 |     TABLE ACCESS BY INDEX ROWID      | CUSTOMER_DEMOGRAPHIC_SACMNT00  |     1 |    28 |     1   (0)| 00:00:01 |
|* 27 |      INDEX RANGE SCAN                | CUSTOMER_DEMOGRAPHIC_01_IX     |     1 |       |     1   (0)| 00:00:01 |
|  28 |   BUFFER SORT                        |                                |    43M|       | 15752   (2)| 00:03:10 |
|  29 |    INDEX FULL SCAN                   | SERVICE_TYPE_INF_BLINF_RRN_IX  |    43M|       |   949   (1)| 00:00:12 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  11 - access("CMACTL"."CUSTOMER_NUMBER"='180672184')
  12 - access("CMACTL"."ADDRESS_CONTROL_NUMBER"="CMADDR"."ADDRESS_CONTROL_NUMBER")
  15 - filter("ACCT"."ACC_EXPIRY_DATE">SYSDATE@!)
  16 - access("ACCT"."CUSTOMER_NUMBER"='180672184')
  19 - access("CMICUS"."CUSTOMER_NUMBER"='180672184')
  20 - access("CMICUS"."ID_NUMBER"="SASSIP"."ID_NUMBER")
  21 - filter("CMICUS"."ID_TYPE"="SASSIP"."ID_TYPE")
  24 - access("CMCONT"."CUSTOMER_NUMBER"='180672184')
  27 - access("DEMO"."CUSTOMER_NUMBER"='180672184')