SQL Question - Need a query
I have a SQL question (Using Oracle Release 9.2)
In our application we have item_cost_history table which stores cost of an item on a particular date.
Item_Cost_History: Item_Id, Item_Cost, Cost_Date.
Item_Cost is downloaded everyday and we maintain an archive of item_cost by cost_date so as to fetch the cost as of a particular date.
The query (Q1) used to get cost is
where item_id = given_item_id
and cost_date = given_date;
This query is present throughout the code.
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.
Item_Cost_Archive : Item_Id, Item_Cost, Last_Cost_Modify_Date.
Item1 3.00 17-JAN-2004
Item1 3.20 31-JAN-2004
Item1 3.15 17-FEB-2004
Is there a way to create a view (Item_Cost_History) so that Q1 can be executed as it is and fetch results.
Click Here to Expand Forum to Full Width