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

you might consider making a hierarchy of groups of rows in order to cut their number, you would make a join query between an index of the indexes, and the data rows. you could keep the ROWID as a key to the data rows so that you could query by ROWID, the quickest way.

--


In article <4aajvn$408_at_sydney1.world.net>, rallo_at_world.net (Raymond Allo) wrote:



> 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