Oracle Database Discussions

Hi All,

I am working on a project to calculate calls, inbound and outbound at a telecom company.

We get 12 million call records everyday i.e. Calling_number_From , Call_start_time,Calling_number_to and call_end_time.
We then split these records into 4 records using UNION ALL which means we have 48 Million records to process and then we do order by call_time.
This order by takes hours to run, Please advice on ideas to improve performance.
Table has Parallel_degree 10
We are on Oracle 10G






We spilt the call into four records i.e.
Each call will have incoming number and outgoing number.

Incoming call

Main_number Column_Call time Count_calls
999 Call_start_time +1
999 Call_end_time -1


Outgoing Call

Main_number Column_Call time Count_calls
888 Call_start_time +1
888 Call_end_time -1

Then we sort the Column_call_time by asc order and check for maximum simultaneous Incoming,outgoing and maximum calls active for each Main_number in one hour.That is the reason we need the sort

Do you guys know Any other alogoritm to do the same?
Any way to sort 48 million rows faster.


Below is the query.

SELECT did_qry.PART_TS,
did_qry.P_NUMBER ,
TO_CHAR(did_qry.call_time,'HH24')
||':00-'
||TO_CHAR(DID_QRY.CALL_TIME,'HH24')
||':59' HOUR_RANGE,
FLAG,
HOUR_CHANGE,
DECODE(HOUR_CHANGE,'HC',DID_QRY.ACTIVE_CALLS+1,DECODE(DID_QRY.ACTIVE_CALLS,0,1,DID_QRY.ACTIVE_CALLS)) ACTIVE_CALLS,
DECODE(HOUR_CHANGE,'HC',DID_QRY.IO_CALLS_CNT+1,DECODE(DID_QRY.IO_CALLS_CNT,0,1,DID_QRY.IO_CALLS_CNT)) io_calls
FROM
(SELECT PART_TS,
P_NUMBER,
did,
call_time,
flag ,
hour_change,
SUM(act) over ( partition BY P_NUMBER order by rownum ) active_calls ,
SUM(io_calls) over ( partition BY P_NUMBER,flag order by rownum ) io_calls_cnt
FROM
(
select TRUNC(H.PART_TS) PART_TS,
TPILOT.P_NUM P_NUMBER,
h.orig_num did,
h.Call_start_ts call_time,
'IN' flag,
1 act,
1 io_calls,
'NA' hour_change
from CALL_REC H,
DISCONN_CD DCODE,
P_DID TPILOT
where ( (H.PART_TS >=to_date('17-02-2011 23:59:59','DD-MM-YYYY HH24:MI:SS')
and H.PART_TS <to_date('14-02-2011 23:59:59','DD-MM-YYYY HH24:MI:SS'))
)
AND DCODE.EFF_START_DT <= H.PART_TS
AND DCODE.EFF_END_DT > H.PART_TS
AND dcode.CDR_C_CDE =h.A_I_ID
AND dcode.CDR_B_CDE =h.R_C_ID
AND dcode.AB_DIS_IND ='N'
AND RECORD_TYP_ID ='00000000'
AND tpilot.EFF_START_DT <= h.PART_TS
AND tpilot.EFF_END_DT > h.PART_TS
and TPILOT.D_NUM =H.TERM_NUM

UNION ALL

select TRUNC(H.PART_TS) PART_TS,
tpilot.P_NUM P_NUMBER,
h.term_num did,
h.PART_TS call_time,
'IN' flag,
-1 act,
-1 io_calls,
DECODE(greatest(TO_CHAR(h.Call_start_ts,'HH12'),TO_CHAR(h.PART_TS,'HH12')), least(TO_CHAR(h.Call_start_ts,'HH12'),TO_CHAR(h.PART_TS,'HH12')),'NC',DECODE(greatest(TO_CHAR(h.Call_start_ts,'HH12'),TO_CHAR(h.PART_TS,'HH12')), TO_CHAR(h.PART_TS,'HH12'),'HC','NC')) hour_change
from CALL_REC H,
DISCONN_CD DCODE,
P_DID tpilot
where H.PART_TS >=to_date('17-02-2011 23:59:59','DD-MM-YYYY HH24:MI:SS')
and H.PART_TS <to_date('19-02-2011 00:00:00','DD-MM-YYYY HH24:MI:SS')
AND DCODE.EFF_START_DT <= h.PART_TS
AND DCODE.EFF_END_DT > h.PART_TS
AND dcode.CDR_C_CDE =h.A_I_ID
AND dcode.CDR_B_CDE =h.R_C_ID
AND dcode.AB_DIS_IND ='N'
AND RECORD_TYP_ID ='00000000'
and TPILOT.EFF_START_DT <= H.PART_TS
and TPILOT.EFF_END_DT > H.PART_TS
and TPILOT.D_NUM =H.TERM_NUM

UNION ALL

SELECT TRUNC(H.PART_TS) PART_TS,
pilot.P_NUM P_NUMBER,
h.orig_num did,
h.Call_start_ts call_time,
'OUT' flag,
1 act,
1 io_calls,
'NA' hour_change
FROM CALL_REC H,
DISCONN_CD DCODE,
P_DID PILOT
where H.PART_TS >=to_date('17-02-2011 23:59:59','DD-MM-YYYY HH24:MI:SS')
and H.PART_TS <to_date('19-02-2011 00:00:00','DD-MM-YYYY HH24:MI:SS')
AND DCODE.EFF_START_DT <= H.PART_TS
AND DCODE.EFF_END_DT > H.PART_TS
AND dcode.CDR_C_CDE =h.A_I_ID
AND dcode.CDR_B_CDE =h.R_C_ID
AND dcode.AB_DIS_IND ='N'
AND RECORD_TYP_ID ='00000000'
AND pilot.EFF_START_DT <= h.PART_TS
and PILOT.EFF_END_DT > H.PART_TS
and PILOT.D_NUM =H.ORIG_NUM
UNION ALL

SELECT TRUNC(h.PART_TS) PART_TS,
pilot.P_NUM P_NUMBER,
h.term_num did,
h.PART_TS call_time,
'OUT' flag,
-1 act,
-1 io_calls,
DECODE(greatest(TO_CHAR(h.Call_start_ts,'HH12'),TO_CHAR(h.PART_TS,'HH12')), least(TO_CHAR(h.Call_start_ts,'HH12'),TO_CHAR(h.PART_TS,'HH12')),'NC',DECODE(greatest(TO_CHAR(h.Call_start_ts,'HH12'),TO_CHAR(h.PART_TS,'HH12')), TO_CHAR(h.PART_TS,'HH12'),'HC','NC')) hour_change
FROM CALL_REC H,
DISCONN_CD DCODE,
P_DID pilot
WHERE H.PART_TS >=to_date('17-02-2011 23:59:59','DD-MM-YYYY HH24:MI:SS')
and H.PART_TS <to_date('19-02-2011 00:00:00','DD-MM-YYYY HH24:MI:SS')
AND DCODE.EFF_START_DT <= h.PART_TS
AND DCODE.EFF_END_DT > h.PART_TS
AND dcode.CDR_C_CDE =h.A_I_ID
AND dcode.CDR_B_CDE =h.R_C_ID
AND dcode.AB_DIS_IND ='N'
AND RECORD_TYP_ID ='00000000'
AND pilot.EFF_START_DT <= h.PART_TS
AND pilot.EFF_END_DT > h.PART_TS
AND pilot.D_NUM =h.orig_num
ORDER BY 2,4,6 ASC
)
) DID_QRY
)