oracle - History records, missing records, filling in the blanks -

I have a table that has a history of cost by location, these are updated on a monthly basis. For example

  Location 1, $ 500, 01 January -09 for 1, $ 650, 01-Fibi -2009 Area 1, $ 2,000, 01-APR -2009  

If I query for March 1, then I want to return the price for February 1, not present from March 1. I have written a question using an oracle analytic, but it takes a lot of time (it will be fine for a report, but we use it to allow the user to view data from the front and switch the date Are required, for the very long time the table is something like 1 million rows). Therefore, the next thought was that I had to update the table with just the data while remembering. In the above case, I just add a record like 01-FE-2009, except on the 19-March-09 date.

I was thinking that this is the best way of thinking of all of you. Do this. My plan is to create a cursor for only one place, bring the first record, bring it next, and if the next record was not for the next month, then enter a record for the missing month.

A little more information:

  Create Table MAXIMO.FCIHIST_BY_MONTH (LOCATION VARCHAR2 (8 BYTE), Parkla VARCHAR2 (4 BYTE), LO2 VARCHAR2 (6 BYTE), Flo 3 VARCHAR2 (1 BYTE), field VARCHAR2 (4 BYTE), AVG_DEFCOST NUMBER, AVG_CRV NUMBER, FIDIDATE DATE)  

and then I'm using the query will have (the system date and Parklafa ). Table is almost 1 million rows, and, again, when is the right time for a report, it takes way too long for the interactive display

  Choose location, avg_defcost, avg_crv, fciyear fcimonth ,, from (location, avg_defcost, avg_crv, fcimonth, fciyear, fcidate selection fcidate, Max (fcidate) (breakdown service) fcidate more from my_max_date from FCIHIST_BY_MONTH & lt; = '01 December -2008 'and parkalpha = 'Saan') where fcidate = my_max_date;  

It's the best way to a PL / SQL stored procedure that works Is behind the current and runs the queries that fail to return the data. Each month that fails to return data for this unavailable data it inserts a line.

  The process of creating or replacing fill_in_missing_data is cursor has_data_on_date selection locaiton, TRUNC (date_filed) is_date based on the location from the_table group, Trunc (date_field) sequence by sequence 1; date; Day_offress number; N_days_to_insert number; BEGIN a_date: = trunc (sysdate); For R1 fill_in_missing_data in the loop if r1.have_date & lt; Then a_date - the date inserted into a loop n_days_to_insert: = a_date - r1.have_date; - May be away from 1, test is required. 1 day for DAY_OFFSET .. n_days_to_insert loop - enter the_table (location, the_date, amount) values ​​in the missing day (r1.location, a_date-DAY_OFFSET, 0); End loop; end if; A_date: = r1.have_date; - It's a little tricky - I'm going to test it and update it in a few minutes end loop; End;