Hello Stephane,

> 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 ?

I'm wondering if you don't have things backwards --

Should a column in your table point to CONTACT or should a CONTACT record
point to a column in your table?

If so, you can easily archive a CONTACT by moving the record to the
CONTACT_ARCHIVE table.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com

>
> --- 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
>>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>