Square DBA – Oracle DBA Talk

I love Oracle’s analytic functions. Today I needed to process call records (CDRs) from a phone switch. I needed to find telephone numbers that had 5 or more 20 minute calls within any 60 minute period of time. I needed to scan a month’s worth of calls, so about 200 million records.

My first inclination was to write a PL/SQL procedure to calculate this for me as I did not think straight SQL would get the job done. I was wrong Oracle’s analytical functions worked nicely and very fast. Here is the code I used.

select count(*) OVER (PARTITION BY called_station_id ORDER BY
call_begin_tmst RANGE 1/24 PRECEDING) as cnt
, called_station_id
    from o1dw.cdr_detail
           where summary_date between to_date('20090701','YYYYMMDD')
 and to_date('20090731','YYYYMMDD')
          and billing_duration_sum > 1200; 

This only looks at calls over 20 minutes (billing_duration_sum > 1200) and then counts how many times a phone number (called_station_id ) was seen in any 60 minute period of time (RANGE 1/24 PRECEDING). I was able to scan 200 million records in about 20 minutes. The process worked perfectly.