LOTS OF DATA, how do I delete it?

From: rschick <rschick_at_eagle.wbm.ca>
Date: 1995/11/21
Message-ID: <48tnd7$jca_at_tomcat.sasknet.sk.ca>#1/1

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 Received on Tue Nov 21 1995 - 00:00:00 CET