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

--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:
>
> > hmm, sorry my explanation was not clear
> >
> > I have 2 table
> >
> > Contact
> > Contact_archive
> >
> > where i put in contact_archive all the contact that are "archived".
> >
> > Because table contact we do some intensive select order by query and i
> > don't want that the archived contact slow downs the process (because
> > with them the table contact will be very very big)
> >
> > table contact_archive is exactly the same structure as table contact
> >
> > but the probleme you understand is the foreign key :( so i need a way
> > out to make them working.
> >
> > thanks you by advance
> > stephane
>
> how big is very big? I doubt it would be a problem.
> Put them all in one table, Use an archive flag, and make your normal
> selectas WHERE ARCHIVEFLAG IS FALSE
> put an index on the flag field
> then test your selects for speed. you might be surprised.
> Alan
>