index rebuild

[Guest_imported]

We have table called trade which has a partitioned table based on the trade date.
We have at any given point of time only 5 partitions.We have a bit map index on this trade table and there are no other indexes on this table.Every day we load data for the previous day and sometimes we do get back dated trade data in our Database and since table is partitioned based on the trade data the data moves into their corresponding partitions.Usually the load on this table is 4.5 million records. Out of the 4.5 million records around 15,000 records Will be for the back dated trades. Sometimes we do get around 1 lac records for the back traded data.

My problems lies in this cases only. Suppose that the back traded data is 60,000 rec . Today's date Is 26-mar-2002 .we load data for 25-mar-2002 and the no of records for 25-mar-2002 is 4.5 million rec And for the dates from 20-mar-2002, 21-mar-2002, 22-mar-2002 and 25-mar-2002 combined is 60,000 records. Now if the program fails in-between, we do a rerun and the programs deletes data for 25-mar-2002 And the remaining day's data still remain in the corresponding table partition. So if we restart the Job again what happens is that 60,000 lac is inserted again and we do not have any constraints in the table and the index but since the Index being a bit map index the index usage is increasing whenever the restart takes place .I mean if the index Space is 200 MB then if the job fails the space increases to 290 MB and again the job also fails due to space allocation Problem so the dba has to rebuild index again for the back dated
Partitions (20-mar-2002, 21-mar-2002, 25-mar-2002).

Can you suggest any work around for this problem?

I cannot add any constraints on the table and index .The columns of this index are symbol, trade date and market.

One work around for this problem would be to rebuild the index manually once the loading is complete.What if i change the bit map index to normal index what will be performance issues?

 

[Dagon]

It sounds as if you need some way of identifying records associated with a particular load. I would suggest adding another column to the table e.g. load_number or load_date. This would correspond uniquely to the data file which you are loading. You can then easily identify which records came from which file and back them out.

I'm a bit surprised your bitmap index expands by 90m for only 60000 records. Bitmap indexes are usually quite small. Also, I think it's pretty unusual to have a composite bitmap index. The point of bitmap indexes is to have them on single columns so that they work effectively when you do queries which involve combinations of those columns.

If your index is so large, it is likely that it may be quite selective. In that case, it probably shouldn't be a bitmap index at all. Execute the following query:

select distinct symbol, trade date, market
from <table>

If the number of rows retrieved is more than about 10% of the number of rows in the table, then you should consider switching it to a btree index. Alternatively, create three separate bitmap indexes on each column.

The btree index could take up more space than a bitmap index but would be faster to load.