--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> > Big like more than 70 000 000 records.
> >
> > but anyway if you have in one table more than 95% of not usefull row
> > (ie archived row) it's can not be good at all, because even if you do
> > in your query WHERE ARCHIVEFLAG IS FALSE, the engine will always need
> > to read all the row and remove from the result the row where
> > ARCHIVEFLAG is true
> >
> > exemple
> >
> > select * from TABLEA join .... where .... and ARCHIVEFLAG IS FALSE
> > order by ....
> >
> > here not really possible to use any index on the ARCHIVEFLAG so the
> > engine will need to filter it manually and that could be time intensive
> > :(
> >
> > the better option is to put all the archived record in a dedicated
> > archived table !
> >
> > no ?
> >
> > stephane
>
> yeh - for 70million I'd use a separate table.
> that's a few small country's worth of people there. SPAM database by any
> chance? :-)
> Alan
>

oh no it's not a contact database, it's was a sample... it's a real estate property database all around the world ( http://www.arkadia.com/usa/ ) the database is not so big, but when we keep the archived record yes it's become very big !

now how to easyly move one record from the main table to the archived table by storedprocedure and handle at the same time all foreign key ?

stephane