Views: How bad are they on performance?

The subject says it all...

We have 16 tables in our database. 8 of which are base tables and 8 of which are incremental tables. We have views for each of the 8 base tables that link the incremental table to it. Basically a SELECT * from 'base' and SELECT * from 'incremental'. Both the base & incremental layouts are exactly the same.

Our Oracle database is 100Gb large. Every 3 months we do a 'purge' of data older than 18 months on our database. This is quite time-consuming.

I want to make the purge process much quicker. My answer (so far) is to break down each of the 8 tables into 6 3-month pieces with an additional 3-month 'incremental' piece.

We will simply truncate the oldest 'piece' of the table and rebuild it larger or smaller if needed. We'll then ex/import the incremental data into the now trucated base piece, and then truncate the incremental.

My questions (about time) are, 1) Will extending the view from 2 (base+incremental) tables to 7 (6 base+1 incremental) slow performance significantly? and 2) Anyone have any better ideas than this one to shorten the history purge?

Note that the database is updated twice a week and only hit on batch production runs 2-3 times a week. It's not an overly-active database, as in an on-line system would experience.

Thanks much!

-Bruce Bristol
bbristol_at_ix.netcom.com Received on Mon Jan 27 1997 - 00:00:00 CST