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

>
> LOTS OF DATA, how do I delete it?
>
> rschick_at_eagle.wbm.ca (rschick)
> 21 Nov 1995 23:29:43 GMT
> Personal
>
> Newsgroups:
> comp.databases.oracle
>
> 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

If you want to speed this up, you really need to generate a unique numeric key for each of your compound primary keys. I know it's more overhead, redundant, makes the table larger, etc. . . .

BUT, comparing two numeric values to each other for the join is going to be a lot faster than comparing two sets of TWO Varchar fields.

By all means, put an index in this numeric key! This will speed up your queries and joins (provided you join by the numeric key and not the concatenated string). And be sure to index your week/date field. This should speed up the deletes, if you're deleting where week = or week < or something similar.

Hope this helps!

    _   _  _ _  __    : Paul Roberts                 
   |_  |_|  |  |      : Programmer / Analyst        
    _| | | _|_ |__    : proberts_at_isc901.jsc.nasa.gov 

Science Applications International Corporation

   (An employee-owned company)

  The opinions expressed are my own, and not necessarily   the views of SAIC, NASA, or the U.S. government. Received on Tue Nov 28 1995 - 00:00:00 CET