Hi,
I have a really huge table (CALLS_DATA) which has 584 million records. The table has a non-unique b-tree index on (district,site).
This is what the table looks like:
district, site,call_ designation,calling_number,called_number, call_charge, call_duration
I have to find the top 30 called numbers (based on revenue) for each district,site combination and create a table along with a few other details.
The query should be on these lines:
SELECT /*+ all_ROWS */
call.site call_sites,
call.district call_district,
call.call_designation designation,
call.called_number called_number,
round((sum(call.call_charge)/100000),5) revenue,
round(sum(call.call_duration)/1000,3) duration,
count(call.ccba_id) no_of_calls
fROM calls_data call
GROUP BY
call.site,
call.district,
call.called_number,
call.call_designation
ORDER BY
call.site,
call.district,
revenue desc
I am having problems with figuring out the top 30 numbers for each district-site combination. I have another table called business_sites which is a lookup table for all the sites, and has a unique key on (district,site)
Initially I thought of this:
cursor c1 is select district,site from business_sites;
.
.
cursor c2 (v_district varchar2,v_site varchar2) is
select * from (select call.site call_sites,
call.district call_district,
call.call_designation call_designation,
call.called_number called_number,
round((sum(call.call_charge)/100000),5) revenue,
round(sum(call.call_duration)/1000,3) duration,
count(call.ccba_id) no_of_calls
fROM calls_data call
where district=v_district
and site=v_site
GROUP BY
call.site,
call.district,
call.called_number,
call.call_designation
ORDER BY
call.site,
call.district,
revenue desc)
where rownum < 30;
Then, opening c2 within the c1 cursor loop, I thought I would insert the records into another table. But i realised that there are 4.5 million district-site combinations, and if i do it this way, this table is going to take a very long time to create.
What else can i do, that will make this table creation faster?
-Anukta
I have a really huge table (CALLS_DATA) which has 584 million records. The table has a non-unique b-tree index on (district,site).
This is what the table looks like:
district, site,call_ designation,calling_number,called_number, call_charge, call_duration
I have to find the top 30 called numbers (based on revenue) for each district,site combination and create a table along with a few other details.
The query should be on these lines:
SELECT /*+ all_ROWS */
call.site call_sites,
call.district call_district,
call.call_designation designation,
call.called_number called_number,
round((sum(call.call_charge)/100000),5) revenue,
round(sum(call.call_duration)/1000,3) duration,
count(call.ccba_id) no_of_calls
fROM calls_data call
GROUP BY
call.site,
call.district,
call.called_number,
call.call_designation
ORDER BY
call.site,
call.district,
revenue desc
I am having problems with figuring out the top 30 numbers for each district-site combination. I have another table called business_sites which is a lookup table for all the sites, and has a unique key on (district,site)
Initially I thought of this:
cursor c1 is select district,site from business_sites;
.
.
cursor c2 (v_district varchar2,v_site varchar2) is
select * from (select call.site call_sites,
call.district call_district,
call.call_designation call_designation,
call.called_number called_number,
round((sum(call.call_charge)/100000),5) revenue,
round(sum(call.call_duration)/1000,3) duration,
count(call.ccba_id) no_of_calls
fROM calls_data call
where district=v_district
and site=v_site
GROUP BY
call.site,
call.district,
call.called_number,
call.call_designation
ORDER BY
call.site,
call.district,
revenue desc)
where rownum < 30;
Then, opening c2 within the c1 cursor loop, I thought I would insert the records into another table. But i realised that there are 4.5 million district-site combinations, and if i do it this way, this table is going to take a very long time to create.
What else can i do, that will make this table creation faster?
-Anukta