Active/current (1-5%) and historical records in same table: index or partitioning? I am trying to discover the correct architecture for the following situation. An application stores 'source data' in multiple tables. Each of these 'source data' tables contains actual, current, active records, as well as old, historical records. There will be a maximum of about a 100 historical 'sets' of data (older data will be cleaned up). Data is loaded in the 'source data tables' once per day or less. The data is otherwise never updated (no OLTP), only read.

95% of the time, queries are executed only on the active records. These queries must be executed almost as fast as if they were the only records in the table. It's ok if queries on the old/historical records are a bit slower (say, a cost increased with a factor 5-10 max).

I currently implemented this as follows:


All 'source data' tables contain a column SRC_CURRENT with value 0 or 1. This column contains a bitmap index (but I've tried with a regular index too).
All 'source data' tables contain a column SRC_DATE with the (truncated) date of the set of data. This column contains a regular index.
All queries on all of these 'source data' tables always include either SRC_CURRENT = 1, or SRC_CURRENT = 0 AND SRC_DATE = &lt;date&gt;
The SRC_CURRENT flag is set correctly each time a new set of data is inserted.


But the performance isn't what I expected. What I'm looking for is a way to let the CBO know that the SRC_CURRENT column only contains a value of 1 in about 1-5% of the rows, and that in that case he can limit the query to only this small subset. If a value of 0 is supplied (95-99% of rows), then he can, of course, just ignore this index and rely on the SRC_DATE index instead to select only the appropriate subset.

Can this be solved using only indexes, do I need partitioning, or is what I'm trying to do simply impossible and should I for instance resort to 2 separate source tables of each type (a table containing the active rows, and a table and a _HIS table) and duplicate all queries, with the application selecting the correct query ?

I'm using Oracle 9i, but if there's a solution to this problem available only in later versions, feel free to mention it too, for future reference.

EDIT

I did a test with partitioning one of the tables on the SRC_CURRENT column. This does seem to fix it, both when targeting SRC_CURRENT = 1 as well as when targeting SRC_CURRENT = 0 AND SRC_DATE = &lt;date&gt;. Is this the proper approach ?
Partitioning should work better than index if the number of rows is large. If the query needs to scan only one partition, it is more efficient than finding current rows through index. 

If SRC_CURRENT is updated to 0 when newer rows are loaded, you need to enable row movement for the table. This will allow rows to move from current partition to historical partition.

Alternatively, if current rows are always the most recent rows, you could range-partition the table by date. This would allow you to clear expired rows very fast by running drop partition for the oldest partition. This will also benefit queries against historical data that access a range of dates but not the whole history.