Re: LOTS OF DATA, how do I delete it?

rschick_at_eagle.wbm.ca (rschick) wrote:

>Got a couple of questions for all of you:
 

>1) How to best delete 1 million rows from a table.
>2) How to best SELECT data from 24 million rows.
 

>Overview
>=======
>I have built an Oracle 7.1.3 database which houses a 15 gig database.
>It's purpose is to do cellular rate plan analysis (based on a customer's cellular phone
>calling history). We get a weekly data load of 250 meg which is all the calls and summary data
>for the week. The front end is VB 3.0 and the data is loaded via SQL Loader (DIRECT).
 

>Problem
>======
>Portions of the data in the calls table(s), say it's called T_CALLS, must be removed/replaced
>with the new calls data. Each of our proposed calls table holds 4 sets of calls (one for each
>billing period). The OLD billing cycle (say Sept 7/95) will have to be replaced with the NEW
>billing cycle (say Sept 7/96). We cannot DROP the table since there are 4 billing periods in
>each month (days 7/15/22/28). Currently we have 6 call tables (one for each month). One option
>is to DELETE the OLD cycle then SQL Load the new one. The DELETE will be from 1 to 1.5
>million rows a week. We estimated the DELETEwill take 150 minutes (which is too slow).
 

>The only other options we have is to make a table PER billing cycle (ie T_SEPT_7 table name)
>then just truncate the table and SQL Load the new load. The problem I forsee with this is that I
>will have to be joining all of these tables when doing an SQL call. The maximum join would involve
>24 tables (4 cycles/mo > 4 tables X 6 months = 24 tables). Each of these billing cycle tables will
>hold 1 to 1.5 million rows each.
 

>The primary key for all the calls tables is the cellular phone number (two char fields : char(3)
>and char(4) for the phone number 123-4567). When querying the calls tables, I always know
>the phone number (primary key).
 

>What ever the solution, the responce time is NOT critical. However, a user can only wait for so long
>before shutting off the PC.
 

>Any ideas would be tried and tested on my lovely Unix/Win95 platforms.
 

>Thanks a million (pun pun pun),
>Rob Schick
>SHL Systemhouse
>rschick_at_shl.com

I have another weird suggestion for you. But it would be best to do this in version 7.1.6 or higher or 7.2 or 7.3

I did this in 7.2.
rename table x to y
create table x as select * from y where records you want to keep; create indexes again
drop table
grant .. to role(s)

This is what we use for datawarehousing since we want to keep our indexes optimal aswell. In 7.2 with the fast loads etc it works faster then deleting 30% of your table.

The other suggestion with stored procedure and a loop wiht a commit every 500 orso is not to bad either. It at least solves your rollback segment blow out.

Raymond Received on Sat Dec 09 1995 - 00:00:00 CET