Home » RDBMS Server » Performance Tuning » Please help for tuning .sql query (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Please help for tuning .sql query [message #644241] |
Sun, 01 November 2015 08:26  |
 |
msol25
Messages: 396
Registered: June 2011
|
Senior Member |
|
|
Hi Expert,
Please help for tuning the view query:
select *
from(
SELECT /*+ noPARALLEL(a) */
DISTINCT x.dir, x.a_b_number, x.caller_name, x.alias_name,
x.call_date, x.call_time, x.call_type, x.duration_in_sec,
x.service_type, x.swich, asterminating_cell_site_id,
x.originating_cell_site_id, x.imie, x.imsi,
x.mobile_number, x.call_start_time, x.start_cell_id,
x.end_cell_id, x.start_lac_id, x.end_lac_id, lkp.latitude,
lkp.longitude
FROM (SELECT /*+ ordered use_nl(a b)*/
call_start_time, 'In' dir,
a.dest_phone_nbr a_b_number,
NVL
((SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN SUBSTR (a.phone_nbr, -9)
ELSE '1'
END
)),
(SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN LPAD
(SUBSTR (a.phone_nbr,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS caller_name,
NVL
((SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN SUBSTR (a.phone_nbr, -9)
ELSE '1'
END
)),
(SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN LPAD
(SUBSTR (a.phone_nbr,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS alias_name,
TO_CHAR (a.call_start_time,
'mm/dd/yyyy') call_date,
TO_CHAR (a.call_start_time,
'hh24:mi:ss') call_time,
(CASE
WHEN INSTR (loading_from, '_msc', 1, 1) <> 0
THEN CASE
WHEN TRIM (a.call_type) = '3V'
THEN 'Video Call'
ELSE 'Voice Call'
END
WHEN INSTR (loading_from, '_gprs', 1, 1) <> 0
THEN CASE
WHEN TRIM (a.call_type) = '3T'
THEN 'TV Watch'
WHEN TRIM (a.call_type) = '3I'
THEN '3G internet'
ELSE 'GPRS'
END
WHEN INSTR (loading_from, '_mms', 1, 1) <> 0
THEN 'MMS'
WHEN INSTR (loading_from, '_ota', 1, 1) <> 0
THEN 'Abwab'
WHEN INSTR (loading_from, '_lbs', 1, 1) <> 0
THEN 'LBS'
WHEN INSTR (loading_from, '_sms', 1, 1) <> 0
THEN CASE
WHEN TRIM (a.call_type) = 'SB'
THEN 'bulck SMS'
WHEN (TRIM (a.call_type) = 'S1')
OR (TRIM (a.call_type) = 'S3')
OR ( TRIM (a.call_type) = 'S5'
OR (TRIM (a.call_type) = 'S7')
)
THEN 'SMS'
WHEN (TRIM (a.call_type) = 'S2')
OR (TRIM (a.call_type) = 'S4')
OR ( TRIM (a.call_type) = 'S6'
OR (TRIM (a.call_type) = 'S8')
)
THEN 'Confirmation SMS'
WHEN TRIM (a.call_type) = 'SM'
THEN 'Interactive SMS'
WHEN TRIM (a.call_type) = 'R2'
THEN 'Confirmation SMS - Saudi Roamer'
WHEN TRIM (a.call_type) = 'D1'
THEN 'SMS from 905'
ELSE 'SMS'
END
ELSE a.call_type
END
) AS call_type,
((talk_minutes * 60) + talk_seconds
) duration_in_sec,
(CASE
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'I'
THEN 'International'
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'G'
THEN 'ALJAWAL'
WHEN a.call_type = 'PL' OR a.call_type = 'PG'
THEN 'ALJAWAL'
WHEN TRIM (a.call_type) IN
('S1', 'S2', 'S3', 'S4', 'S5', 'S6',
'S7', 'S8')
THEN 'ALJAWAL'
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'Q'
THEN 'MOBILY'
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'L'
THEN 'Land Line'
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'M'
THEN 'Call to Operator'
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'F'
THEN 'Call to 800'
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'R'
THEN 'JAWALNET'
ELSE a.call_type
END
) service_type,
a.hdr_file_seq_nbr_part1
|| a.hdr_file_seq_nbr_part2
|| a.hdr_file_seq_nbr_part3 swich,
a.last_calling_location asterminating_cell_site_id,
a.first_calling_location originating_cell_site_id,
a.calling_imei imie, a.calling_imsi imsi,
phone_nbr mobile_number, a.start_cell_id,
a.end_cell_id, a.start_lac_id, a.end_lac_id
/*Added as part of 6.7 CR Alshamel system*/
FROM
-----------------------------------------------------------------------------------------------------------------
(SELECT /*+ noPARALLEL(cdr) noPARALLEL_INDEX(cdr POSTMED_CDR_DEST_PHONE_NBR_IX) noPARALLEL_INDEX(cdr POSTMED_CDR_PHONE_NBR_IX)*/
hdr_rec_type, hdr_create_date,
hdr_file_seq_nbr_part1,
hdr_file_seq_nbr_part2,
hdr_file_seq_nbr_part3, cdr_record_type,
phone_nbr, dest_phone_nbr, bill_svc_code,
bill_phone_nbr, call_start_time,
talk_minutes, talk_seconds, call_type,
calling_imsi, calling_imei, dissconn_party,
time_stop_charge, interruption_time,
time_register_to_charge, charged_party,
exchange_identity, msc_id, outgoing_route,
incomming_route, data_volume,
originating_loc_num,
first_calling_location,
last_calling_location, fault_code,
switch_id, translated_num, msc_address,
cdr_type, msisdn, cell_id,
cause_for_record_closing,
message_submission, call_reference,
message_length, originating_msc,
call_amount1, call_amount2,
jurisdiction_of_usage, repriced_ind,
original_called_num, first_called_location,
last_called_location, destination_address,
sms_result, chargable_duration,
loading_date, loading_from, start_cell_id,
end_cell_id, start_lac_id, end_lac_id
FROM dm_exa_dwh.postmed_cdr cdr) a
UNION ALL
-----------------------------------------------------------------------------------------------------------
SELECT call_start_time, 'Out' dir, a.phone_nbr a_b_number,
NVL
((SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN SUBSTR
(a.dest_phone_nbr,
-9
)
ELSE '1'
END
)),
(SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN LPAD
(SUBSTR
(a.dest_phone_nbr,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS caller_name,
NVL
((SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN SUBSTR
(a.dest_phone_nbr,
-9
)
ELSE '1'
END
)),
(SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN LPAD
(SUBSTR
(a.dest_phone_nbr,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS alias_name,
TO_CHAR (a.call_start_time,
'mm/dd/yyyy') call_date,
TO_CHAR (a.call_start_time,
'hh24:mi:ss') call_time,
(CASE
WHEN INSTR (loading_from, '_msc', 1, 1) <> 0
THEN CASE
WHEN TRIM (a.call_type) = '3V'
THEN 'Video Call'
ELSE 'Voice Call'
END
WHEN INSTR (loading_from, '_gprs', 1, 1) <> 0
THEN CASE
WHEN TRIM (a.call_type) = '3T'
THEN 'TV Watch'
WHEN TRIM (a.call_type) = '3I'
THEN '3G internet'
ELSE 'GPRS'
END
WHEN INSTR (loading_from, '_mms', 1, 1) <> 0
THEN 'MMS'
WHEN INSTR (loading_from, '_ota', 1, 1) <> 0
THEN 'Abwab'
WHEN INSTR (loading_from, '_lbs', 1, 1) <> 0
THEN 'LBS'
WHEN INSTR (loading_from, '_sms', 1, 1) <> 0
THEN CASE
WHEN TRIM (a.call_type) = 'SB'
THEN 'bulck SMS'
WHEN (TRIM (a.call_type) = 'S1')
OR (TRIM (a.call_type) = 'S3')
OR ( TRIM (a.call_type) = 'S5'
OR (TRIM (a.call_type) = 'S7')
)
THEN 'SMS'
WHEN (TRIM (a.call_type) = 'S2')
OR (TRIM (a.call_type) = 'S4')
OR ( TRIM (a.call_type) = 'S6'
OR (TRIM (a.call_type) = 'S8')
)
THEN 'Confirmation SMS'
WHEN TRIM (a.call_type) = 'SM'
THEN 'Interactive SMS'
WHEN TRIM (a.call_type) = 'R2'
THEN 'Confirmation SMS - Saudi Roamer'
WHEN TRIM (a.call_type) = 'D1'
THEN 'SMS from 905'
ELSE 'SMS'
END
ELSE a.call_type
END
) AS call_type,
((talk_minutes * 60) + talk_seconds
) duration_in_sec,
(CASE
WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'I'
THEN 'International'
WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'G'
THEN 'ALJAWAL'
WHEN a.call_type = 'PL' OR a.call_type = 'PG'
THEN 'ALJAWAL'
WHEN TRIM (a.call_type) IN
('S1', 'S2', 'S3', 'S4', 'S5', 'S6')
THEN 'ALJAWAL'
WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'Q'
THEN 'MOBILY'
WHEN SUBSTR (TRIM (a.call_type), 2, 1) = 'Q'
THEN 'ALJAWAL'
WHEN SUBSTR (TRIM (a.call_type), 1, 1) = 'L'
THEN 'Land Line'
WHEN TRIM (a.call_type) IN ('MG', 'MI')
THEN 'Mobile Internet Services'
WHEN TRIM (a.call_type) = 'MA'
THEN 'Mobile Application'
ELSE a.call_type
END
) service_type,
a.hdr_file_seq_nbr_part1
|| a.hdr_file_seq_nbr_part2
|| a.hdr_file_seq_nbr_part3 swich,
a.last_calling_location asterminating_cell_site_id,
a.first_calling_location originating_cell_site_id,
a.calling_imei imie, a.calling_imsi imsi,
dest_phone_nbr mobile_number, a.start_cell_id,
a.end_cell_id, a.start_lac_id, a.end_lac_id
/*added as part of 6.7 CR alshamel system*/
FROM (SELECT /*+ noPARALLEL(cdr) noPARALLEL_INDEX(cdr POSTMED_CDR_DEST_PHONE_NBR_IX) noPARALLEL_INDEX(cdr POSTMED_CDR_PHONE_NBR_IX)*/
hdr_rec_type, hdr_create_date,
hdr_file_seq_nbr_part1,
hdr_file_seq_nbr_part2,
hdr_file_seq_nbr_part3, cdr_record_type,
phone_nbr, dest_phone_nbr, bill_svc_code,
bill_phone_nbr, call_start_time,
talk_minutes, talk_seconds, call_type,
calling_imsi, calling_imei, dissconn_party,
time_stop_charge, interruption_time,
time_register_to_charge, charged_party,
exchange_identity, msc_id, outgoing_route,
incomming_route, data_volume,
originating_loc_num,
first_calling_location,
last_calling_location, fault_code,
switch_id, translated_num, msc_address,
cdr_type, msisdn, cell_id,
cause_for_record_closing,
message_submission, call_reference,
message_length, originating_msc,
call_amount1, call_amount2,
jurisdiction_of_usage, repriced_ind,
original_called_num, first_called_location,
last_called_location, destination_address,
sms_result, chargable_duration,
loading_date, loading_from, start_cell_id,
end_cell_id, start_lac_id, end_lac_id
FROM dm_exa_dwh.postmed_cdr cdr) a
UNION ALL
-------------------------------------------------------------------------------------------------------
SELECT /*+ noPARALLEL(a) ordered use_nl(a b c) noPARALLEL_INDEX(a PST_CDR_LL_B_NUM_IX) */
call_start_time, 'Out' dir, a.a_number a_b_number,
NVL
((SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (b_number),
1,
2
) <> '00'
THEN SUBSTR (a.b_number, -9)
ELSE '1'
END
)),
(SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (b_number),
1,
2
) <> '00'
THEN LPAD
(SUBSTR (a.b_number,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS caller_name,
NVL
((SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (b_number),
1,
2
) <> '00'
THEN SUBSTR (a.b_number, -9)
ELSE '1'
END
)),
(SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (b_number),
1,
2
) <> '00'
THEN LPAD
(SUBSTR (a.b_number,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS alias_name,
TO_CHAR (a.call_start_time,
'mm/dd/yyyy') call_date,
TO_CHAR (a.call_start_time,
'hh24:mi:ss') call_time,
NULL AS call_type,
( SUBSTR (TRIM (chargeable_duration), 1, 2)
* 60
* 60
)
+ (SUBSTR (TRIM (chargeable_duration), 4, 2) * 60)
+ SUBSTR (TRIM (chargeable_duration), 7, 2)
duration_in_sec,
NULL service_type, NULL swich,
NULL asterminating_cell_site_id,
NULL originating_cell_site_id, NULL imie,
NULL imsi, b_number mobile_number,
NULL start_cell_id, NULL end_cell_id,
NULL start_lac_id, NULL end_lac_id
FROM dm_exa_dwh.pst_ll_cdr a
UNION ALL
-------------------------------------------------------------------------------------------------------
SELECT /*+ noPARALLEL(a) ordered use_nl(a b c) noPARALLEL_INDEX(a PST_CDR_LL_B_NUM_IX) */
call_start_time, 'In' dir, a.b_number a_b_number,
NVL
((SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (a_number),
1,
2
) <> '00'
THEN SUBSTR (a.a_number, -9)
ELSE '1'
END
)),
(SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (a_number),
1,
2
) <> '00'
THEN LPAD
(SUBSTR (a.a_number,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS caller_name,
NVL
((SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (a_number),
1,
2
) <> '00'
THEN SUBSTR (a.a_number, -9)
ELSE '1'
END
)),
(SELECT alias_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR (TRIM (a_number),
1,
2
) <> '00'
THEN LPAD
(SUBSTR (a.a_number,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS alias_name,
TO_CHAR (a.call_start_time,
'mm/dd/yyyy') call_date,
TO_CHAR (a.call_start_time,
'hh24:mi:ss') call_time,
NULL AS call_type,
( SUBSTR (TRIM (chargeable_duration), 1, 2)
* 60
* 60
)
+ (SUBSTR (TRIM (chargeable_duration), 4, 2) * 60)
+ SUBSTR (TRIM (chargeable_duration), 7, 2)
duration_in_sec,
NULL service_type, NULL swich,
NULL asterminating_cell_site_id,
NULL originating_cell_site_id, NULL imie,
NULL imsi, a_number mobile_number,
NULL start_cell_id, NULL end_cell_id,
NULL start_lac_id, NULL end_lac_id
FROM dm_exa_dwh.pst_ll_cdr a) x,
abinitio_reports.postmed_coordinate_lookup lkp
-- dm_exa_dwh.postmed_cdr cdr
WHERE a_b_number NOT LIKE '%559692565'
AND mobile_number NOT LIKE '%559692565'
AND a_b_number NOT LIKE '%014404011'
AND mobile_number NOT LIKE '%014404011'
AND x.start_cell_id = lkp.cell_id(+)
AND x.start_lac_id = lkp.lac_id(+)
)
where ----mobile_number = '503415880'
caLL_START_TIME BETWEEN TO_DATE('2015-09-29','yyyy-mm-dd')
AND TO_DATE('2015-10-31','yyyy-mm-dd')+1 AND a_b_number IN('504000000','0504000000')
Explain Plan:
PLAN_TABLE_OUTPUT
Plan hash value: 858624757
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 14806 | 6 (17)| 00:00:01 | | |
| 1 | VIEW | DWH_SHAMEL_CDR_VW | 11 | 14806 | 6 (17)| 00:00:01 | | |
| 2 | HASH UNIQUE | | 11 | 13783 | 6 (17)| 00:00:01 | | |
| 3 | NESTED LOOPS OUTER | | 11 | 13783 | 5 (0)| 00:00:01 | | |
| 4 | VIEW | | 11 | 13442 | 4 (0)| 00:00:01 | | |
| 5 | UNION-ALL | | | | | | | |
| 6 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 7 | UNION-ALL | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|* 9 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 10 | NESTED LOOPS | | | | | | | |
| 11 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 16 | NESTED LOOPS | | | | | | | |
| 17 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|* 19 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|* 20 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|* 21 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 22 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 23 | UNION-ALL | | | | | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|* 25 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 26 | NESTED LOOPS | | | | | | | |
| 27 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|* 28 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 29 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|* 30 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 32 | NESTED LOOPS | | | | | | | |
| 33 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|* 34 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|* 35 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 38 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 39 | UNION-ALL | | | | | | | |
| 40 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|* 41 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 42 | NESTED LOOPS | | | | | | | |
| 43 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|* 44 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 45 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|* 46 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 47 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 48 | NESTED LOOPS | | | | | | | |
| 49 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|* 50 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|* 51 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|* 52 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|* 53 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 54 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 55 | UNION-ALL | | | | | | | |
| 56 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|* 57 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 58 | NESTED LOOPS | | | | | | | |
| 59 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|* 60 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 61 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|* 62 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 63 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 64 | NESTED LOOPS | | | | | | | |
| 65 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|* 66 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|* 67 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|* 68 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|* 69 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 70 | REMOTE | POSTMED_CDR | 1 | 338 | 1 (0)| 00:00:01 | DM_EX~ | R->S |
| 71 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 72 | UNION-ALL | | | | | | | |
| 73 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|* 74 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 75 | NESTED LOOPS | | | | | | | |
| 76 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|* 77 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 78 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|* 79 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 80 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 81 | NESTED LOOPS | | | | | | | |
| 82 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|* 83 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|* 84 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|* 85 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|* 86 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 87 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 88 | UNION-ALL | | | | | | | |
| 89 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|* 90 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 91 | NESTED LOOPS | | | | | | | |
| 92 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|* 93 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|* 94 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|* 95 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|* 96 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 97 | NESTED LOOPS | | | | | | | |
| 98 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|* 99 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*100 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*101 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*102 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 103 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 104 | UNION-ALL | | | | | | | |
| 105 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*106 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 107 | NESTED LOOPS | | | | | | | |
| 108 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*109 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*110 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*111 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*112 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 113 | NESTED LOOPS | | | | | | | |
| 114 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*115 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*116 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*117 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*118 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 119 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 120 | UNION-ALL | | | | | | | |
| 121 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*122 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 123 | NESTED LOOPS | | | | | | | |
| 124 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*125 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*126 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*127 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*128 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 129 | NESTED LOOPS | | | | | | | |
| 130 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*131 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*132 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*133 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*134 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 135 | REMOTE | POSTMED_CDR | 1 | 338 | 1 (0)| 00:00:01 | DM_EX~ | R->S |
| 136 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 137 | UNION-ALL | | | | | | | |
| 138 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*139 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 140 | NESTED LOOPS | | | | | | | |
| 141 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*142 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*143 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*144 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*145 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 146 | NESTED LOOPS | | | | | | | |
| 147 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*148 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*149 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*150 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*151 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 152 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 153 | UNION-ALL | | | | | | | |
| 154 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*155 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 156 | NESTED LOOPS | | | | | | | |
| 157 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*158 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*159 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*160 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*161 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 162 | NESTED LOOPS | | | | | | | |
| 163 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*164 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*165 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*166 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*167 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 168 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 169 | UNION-ALL | | | | | | | |
| 170 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*171 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 172 | NESTED LOOPS | | | | | | | |
| 173 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*174 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*175 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*176 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*177 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 178 | NESTED LOOPS | | | | | | | |
| 179 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*180 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*181 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*182 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*183 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 184 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 185 | UNION-ALL | | | | | | | |
| 186 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*187 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 188 | NESTED LOOPS | | | | | | | |
| 189 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*190 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*191 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*192 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*193 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 194 | NESTED LOOPS | | | | | | | |
| 195 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*196 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*197 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*198 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*199 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 200 | REMOTE | PST_LL_CDR | 5 | 455 | 1 (0)| 00:00:01 | DM_EX~ | R->S |
| 201 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 202 | UNION-ALL | | | | | | | |
| 203 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*204 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 205 | NESTED LOOPS | | | | | | | |
| 206 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*207 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*208 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*209 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*210 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 211 | NESTED LOOPS | | | | | | | |
| 212 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*213 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*214 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*215 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*216 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 217 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 218 | UNION-ALL | | | | | | | |
| 219 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*220 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 221 | NESTED LOOPS | | | | | | | |
| 222 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*223 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*224 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*225 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*226 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 227 | NESTED LOOPS | | | | | | | |
| 228 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*229 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*230 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*231 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*232 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 233 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 234 | UNION-ALL | | | | | | | |
| 235 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*236 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 237 | NESTED LOOPS | | | | | | | |
| 238 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*239 | TABLE ACCESS BY INDEX ROWID | S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*240 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*241 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*242 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 243 | NESTED LOOPS | | | | | | | |
| 244 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*245 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*246 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*247 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*248 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 249 | VIEW | SHAMEL_CDR_NAME_VW | 2 | 980 | 4 (0)| 00:00:01 | | |
| 250 | UNION-ALL | | | | | | | |
| 251 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 48 | 1 (0)| 00:00:01 | | |
|*252 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
| 253 | NESTED LOOPS | | | | | | | |
| 254 | NESTED LOOPS | | 1 | 90 | 2 (0)| 00:00:01 | | |
|*255 | TABLE ACCESS BY INDEX ROWID| S_ASSET_EDI | 1 | 54 | 1 (0)| 00:00:01 | | |
|*256 | INDEX RANGE SCAN | SERIAL_NUM_INDEX1 | 6 | | 1 (0)| 00:00:01 | | |
|*257 | INDEX UNIQUE SCAN | S_ORG_EXT_PK | 1 | | 1 (0)| 00:00:01 | | |
|*258 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1 | 36 | 1 (0)| 00:00:01 | | |
| 259 | NESTED LOOPS | | | | | | | |
| 260 | NESTED LOOPS | | 1 | 161 | 2 (0)| 00:00:01 | | |
|*261 | TABLE ACCESS BY INDEX ROWID| CUSTOMER_SERVICES_LL_SUBXTL | 1 | 39 | 1 (0)| 00:00:01 | | |
|*262 | INDEX RANGE SCAN | CSTMR_SRVCS_LL_ACCSS_NBR_02_IX | 2 | | 1 (0)| 00:00:01 | | |
|*263 | INDEX RANGE SCAN | CUSTOMER_CMMAST_01_CUST_NO_IX | 1 | | 1 (0)| 00:00:01 | | |
|*264 | TABLE ACCESS BY INDEX ROWID | CUSTOMER_CMMAST00 | 1 | 122 | 1 (0)| 00:00:01 | | |
| 265 | REMOTE | PST_LL_CDR | 4 | 364 | 1 (0)| 00:00:01 | DM_EX~ | R->S |
| 266 | TABLE ACCESS BY INDEX ROWID | POSTMED_COORDINATE_LOOKUP | 1 | 31 | 1 (0)| 00:00:01 | | |
|*267 | INDEX RANGE SCAN | POSTMED_COORDINATE_CELL_LAC_IX | 1 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("X"."ROW_ID"=:B1)
12 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
13 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
14 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
15 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
18 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
19 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
20 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
21 - filter("C"."EXPIRY_DATE">SYSDATE@!)
25 - access("X"."ROW_ID"=:B1)
28 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
29 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
30 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
31 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
34 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
35 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
36 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
37 - filter("C"."EXPIRY_DATE">SYSDATE@!)
41 - access("X"."ROW_ID"=:B1)
44 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
45 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
46 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
47 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
50 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
51 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
52 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
53 - filter("C"."EXPIRY_DATE">SYSDATE@!)
57 - access("X"."ROW_ID"=:B1)
60 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
61 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
62 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
63 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
66 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
67 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
68 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
69 - filter("C"."EXPIRY_DATE">SYSDATE@!)
74 - access("X"."ROW_ID"=:B1)
77 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
78 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
79 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
80 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
83 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
84 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
85 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
86 - filter("C"."EXPIRY_DATE">SYSDATE@!)
90 - access("X"."ROW_ID"=:B1)
93 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
94 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
95 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
96 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
99 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
100 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
101 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
102 - filter("C"."EXPIRY_DATE">SYSDATE@!)
106 - access("X"."ROW_ID"=:B1)
109 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
110 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
111 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
112 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
115 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
116 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
SUBSTR(:B4,(-9)) ELSE '1' END )
117 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
118 - filter("C"."EXPIRY_DATE">SYSDATE@!)
122 - access("X"."ROW_ID"=:B1)
125 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
126 - access("A"."SERIAL_NUM"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
127 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
128 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
131 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
132 - access("B"."ACCESS_NUMBER"=CASE WHEN (((SUBSTR(TRIM(:B1),2,1)<>'I') AND (TRIM(:B2)<>'GR')) OR (:B3 IS NULL)) THEN
LPAD(SUBSTR(:B4,(-9)),20,'0') ELSE '1' END )
133 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
134 - filter("C"."EXPIRY_DATE">SYSDATE@!)
139 - access("X"."ROW_ID"=:B1)
142 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
143 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
144 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
145 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
148 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
149 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
150 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
151 - filter("C"."EXPIRY_DATE">SYSDATE@!)
155 - access("X"."ROW_ID"=:B1)
158 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
159 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
160 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
161 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
164 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
165 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
166 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
167 - filter("C"."EXPIRY_DATE">SYSDATE@!)
171 - access("X"."ROW_ID"=:B1)
174 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
175 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
176 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
177 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
180 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
181 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
182 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
183 - filter("C"."EXPIRY_DATE">SYSDATE@!)
187 - access("X"."ROW_ID"=:B1)
190 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
191 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
192 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
193 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
196 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
197 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
198 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
199 - filter("C"."EXPIRY_DATE">SYSDATE@!)
204 - access("X"."ROW_ID"=:B1)
207 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
208 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
209 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
210 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
213 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
214 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
215 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
216 - filter("C"."EXPIRY_DATE">SYSDATE@!)
220 - access("X"."ROW_ID"=:B1)
223 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
224 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
225 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
226 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
229 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
230 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
231 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
232 - filter("C"."EXPIRY_DATE">SYSDATE@!)
236 - access("X"."ROW_ID"=:B1)
239 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
240 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
241 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
242 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
245 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
246 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN SUBSTR(:B2,(-9)) ELSE '1' END )
247 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
248 - filter("C"."EXPIRY_DATE">SYSDATE@!)
252 - access("X"."ROW_ID"=:B1)
255 - filter("A"."END_DT" IS NULL AND "A"."ROOT_ASSET_ID"="A"."ROW_ID" AND "A"."STATUS_CD"<>'Inactive')
256 - access("A"."SERIAL_NUM"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
257 - access("A"."BILL_ACCNT_ID"="B"."ROW_ID")
258 - filter("B"."ACCNT_TYPE_CD"='Billing' OR "B"."ACCNT_TYPE_CD"='Prepaid Billing')
261 - filter("B"."DISCONNECTION_DATE">SYSDATE@!)
262 - access("B"."ACCESS_NUMBER"=CASE WHEN (SUBSTR(TRIM(:B1),1,2)<>'00') THEN LPAD(SUBSTR(:B2,(-9)),20,'0') ELSE '1' END )
263 - access("B"."CUSTOMER_NUMBER"="C"."CUSTOMER_NUMBER")
264 - filter("C"."EXPIRY_DATE">SYSDATE@!)
267 - access("X"."START_CELL_ID"="LKP"."CELL_ID"(+) AND "X"."START_LAC_ID"="LKP"."LAC_ID"(+))
Remote SQL Information (identified by operation id):
----------------------------------------------------
70 - SELECT /*+ NOPARALLEL ("POSTMED_CDR") NO_PARALLEL_INDEX ("POSTMED_CDR" "POSTMED_CDR_DEST_PHONE_NBR_IX") NO_PARALLEL_INDEX
("POSTMED_CDR" "POSTMED_CDR_PHONE_NBR_IX") */ "HDR_FILE_SEQ_NBR_PART1","HDR_FILE_SEQ_NBR_PART2","HDR_FILE_SEQ_NBR_PART3","PHONE_NBR
","DEST_PHONE_NBR","CALL_START_TIME","TALK_MINUTES","TALK_SECONDS","CALL_TYPE","CALLING_IMSI","CALLING_IMEI","FIRST_CALLING_LOCATIO
N","LAST_CALLING_LOCATION","LOADING_FROM","START_CELL_ID","END_CELL_ID","START_LAC_ID","END_LAC_ID" FROM "PST_CDR"."POSTMED_CDR"
"POSTMED_CDR" WHERE "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
("DEST_PHONE_NBR"='0504000000' OR "DEST_PHONE_NBR"='504000000') AND "CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DEST_PHONE_NBR" NOT LIKE '%559692565' AND "PHONE_NBR" NOT LIKE '%559692565' AND "DEST_PHONE_NBR"
NOT LIKE '%014404011' AND "PHONE_NBR" NOT LIKE '%014404011' AND "DEST_PHONE_NBR" IS NOT NULL AND "PHONE_NBR" IS NOT NULL AND
"DEST_PHONE_NBR" IS NOT NULL AND "PHONE_NBR" IS NOT NULL (accessing 'DM_EXA_DWH_ODSDWH' )
135 - SELECT /*+ NOPARALLEL ("POSTMED_CDR") NO_PARALLEL_INDEX ("POSTMED_CDR" "POSTMED_CDR_DEST_PHONE_NBR_IX") NO_PARALLEL_INDEX
("POSTMED_CDR" "POSTMED_CDR_PHONE_NBR_IX") */ "HDR_FILE_SEQ_NBR_PART1","HDR_FILE_SEQ_NBR_PART2","HDR_FILE_SEQ_NBR_PART3","PHONE_NBR
","DEST_PHONE_NBR","CALL_START_TIME","TALK_MINUTES","TALK_SECONDS","CALL_TYPE","CALLING_IMSI","CALLING_IMEI","FIRST_CALLING_LOCATIO
N","LAST_CALLING_LOCATION","LOADING_FROM","START_CELL_ID","END_CELL_ID","START_LAC_ID","END_LAC_ID" FROM "PST_CDR"."POSTMED_CDR"
"POSTMED_CDR" WHERE "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("PHONE_NBR"='0504000000' OR
"PHONE_NBR"='504000000') AND "CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PHONE_NBR" NOT LIKE
'%559692565' AND "DEST_PHONE_NBR" NOT LIKE '%559692565' AND "PHONE_NBR" NOT LIKE '%014404011' AND "DEST_PHONE_NBR" NOT LIKE
'%014404011' AND "PHONE_NBR" IS NOT NULL AND "DEST_PHONE_NBR" IS NOT NULL AND "PHONE_NBR" IS NOT NULL AND "DEST_PHONE_NBR" IS NOT
NULL (accessing 'DM_EXA_DWH_ODSDWH' )
200 - SELECT /*+ NOPARALLEL ("PST_LL_CDR") USE_NL ("PST_LL_CDR") NO_PARALLEL_INDEX ("PST_LL_CDR" "PST_CDR_LL_B_NUM_IX") */
"A_NUMBER","B_NUMBER","CALL_START_TIME","CHARGEABLE_DURATION" FROM "PST_CDR_LL"."PST_LL_CDR" "PST_LL_CDR" WHERE
"CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("A_NUMBER"='0504000000' OR
"A_NUMBER"='504000000') AND "A_NUMBER" NOT LIKE '%559692565' AND "B_NUMBER" NOT LIKE '%559692565' AND "A_NUMBER" NOT LIKE
'%014404011' AND "B_NUMBER" NOT LIKE '%014404011' AND "A_NUMBER" IS NOT NULL AND "B_NUMBER" IS NOT NULL AND "A_NUMBER" IS NOT NULL
AND "B_NUMBER" IS NOT NULL AND "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing
'DM_EXA_DWH_ODSDWH' )
265 - SELECT /*+ NOPARALLEL ("PST_LL_CDR") USE_NL ("PST_LL_CDR") NO_PARALLEL_INDEX ("PST_LL_CDR" "PST_CDR_LL_B_NUM_IX") */
"A_NUMBER","B_NUMBER","CALL_START_TIME","CHARGEABLE_DURATION" FROM "PST_CDR_LL"."PST_LL_CDR" "PST_LL_CDR" WHERE
"CALL_START_TIME">=TO_DATE(' 2015-09-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND ("B_NUMBER"='0504000000' OR
"B_NUMBER"='504000000') AND "B_NUMBER" NOT LIKE '%559692565' AND "A_NUMBER" NOT LIKE '%559692565' AND "B_NUMBER" NOT LIKE
'%014404011' AND "A_NUMBER" NOT LIKE '%014404011' AND "B_NUMBER" IS NOT NULL AND "A_NUMBER" IS NOT NULL AND "B_NUMBER" IS NOT NULL
AND "A_NUMBER" IS NOT NULL AND "CALL_START_TIME"<=TO_DATE(' 2015-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing
'DM_EXA_DWH_ODSDWH' )
View query is getting hanged up.Please can you help me to resolve the issue
|
|
|
|
|
|
| Re: Please help for tuning .sql query [message #644260 is a reply to message #644242] |
Mon, 02 November 2015 03:10  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
There are a lot of replicated column calculations that are replicated. You can avoid that by moving the calculation up a level.
So for example:
NVL
((SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN SUBSTR (a.phone_nbr, -9)
ELSE '1'
END
)),
(SELECT full_name
FROM edi_views.shamel_cdr_name_vw b
WHERE b.access_number =
(CASE
WHEN SUBSTR
(TRIM (a.call_type),
2,
1
) <> 'I'
AND TRIM (a.call_type) <>
'GR'
OR a.call_type IS NULL
THEN LPAD
(SUBSTR (a.phone_nbr,
-9
),
20,
'0'
)
ELSE '1'
END
))
) AS caller_name
Can be moved up into the 2nd select - select distinct .... from (<unioned queries>).
Same for alias_name, call_type and service_type
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 13:40:17 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
