If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Since the cost of an item does not change very frequntly, we are storing the same repeated cost over and over again with different dates. As a result the table size is huge because we keep atleast 30 day archive for 500,000 items.

To reduce the amount of data we are storing, we have decided to change our approach in storing item_cost and record changes to item_cost only We are going to create a new table Item_Cost_Archive and record only cost changes and create a view Item_Cost_History so that the schema change is transparent to the application.

It'd be a pretty expensive query to run. You'd join from the archive table to a table of dates, and it'd be a lot of LIO's to do so.

You might consider storing the data in an index-organized structure -- that'd probably cut your total storage requirement in half straight away, reduce LIO's in retrieving costs, and the PK index would be a good candidate for compression ...

Note that I made date lead the time in the PK definition -- this would give the index a rightwards growing characteristic that would eliminate any need for maintenance in the future, and also would decrease the overhead on inserting new records. You'd have to think about whether this will work for all your queries though. You could supplement the PK index with a compressed alternative on item_id also.

-- As far as my knowledge goes Oracle offers a way to create a View which will return different records according to session environment variables (or context value). Combining that feature with the above view definition you can get the exact answer to your problem. If you get how to do it then please paste the result in the forum and also mail me.

Have you compared performance between this solution and using a compressed IOT-based solution, so that you know what you are sacrificing in terms of CPU usage, LIO's etc to save on so-many Mb of disk space?