I would try to do it on this way:

1. In main table create field IDTABLE INTEGER, and make it a primary key.
2. Create generator GEN_IDTABLE for that field.
3. Create 2nd table DELETIONS( FOR_DATE DATE, FIRST_ID INTEGER, LAST_ID
INTEGER)
4. Create trigger OnAfterInsert For MainTable somethin like:
if not exists in deletions mydate insert(MainTable.mydate,
MainTable.idtable, MainTable.idtable)
else update deletions set last_id=MainTable.idtable where for_date =
MainTable.mydate
5. create procedure DELETE1000(for_del_date date)
select first_id, last_id from deletions where for_date=for_del_date into
iFirst, iMax;
if iMax > iFirst+1000 then begin
delete from MainTable where idtable>=:ifirst and idtable<=:iMax
delete from deletions where for_date=:for_Del_date
end
else begin
delete from MainTable where idtable>=:ifirst and
idtable<=:ifirst+1000
update deletions set idfirst=idfirst+1000 where
for_date=:for_Del_date
end
6. Every nitght i wil call procedure delete1000( DATE-7 ) as long as there
is record in deletions table with that date

With this i will cinsider two things: 1. make a small deletions (1000 recs)
in a time (to reduce garbage)
and 2. do it with primary key

What do you think?

Just my 2cents

Dominik Murk

----- Original Message -----
From: "Nando Dessena" <nandod@...>
To: <ib-support@yahoogroups.com>
Sent: Thursday, July 04, 2002 8:55 AM
Subject: Re: [ib-support] large deletions


> Raul,
>
> > > an atomic index on
> > > a two-value column in a multi-million record table does not look
exactly
> > > a speed enhancer to me.
> >
> > It depends how the values of that column are distributed: if the two
values
> > are distributed aprox. equally the index it's just a waste of space, but
> > more dispropotion between the two values is, more spped you'll get
selecting
> > the minoritar ones
>
> that's right. You gain some speed vs not having an index at all.
> Although, in IB, I would feel somehow safer if that index had the
> primary key stuck after the flag to improve selectivity.
>
> > In fact, thats why some RDBMS's offer a feature named
> > "partitioned tables", which is an enhaced version of what I've just told
> > you).
>
> Before partitioned tables we should implement clustered indexes.
> A long way to go, I guess...
>
> > (They still sell FoxPro, I've heard !)
>
> Even worse, people buy it! :-)
> Ciao
> --
> ____
> _/\/ando
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>