Hi All,
I have FRD_TBL table where i am truncating and loading data daily. I am getting probably 100k rows in this table daily. I have another table FRD_TBL_H which has 80 million rows and where i want to historize the data from FRD_TBL table by keeping old rows using logic Valid_From and Valid_TO column.
I am trying to perform below query logic for historization but not sure if its performance efficient. First from Merge query i am trying to close the old versions for beld that comes from FRD_TBL table and then from Insert query insert new versions of old beld and new beld.
First Merge Query:
MERGE INTO FRD_TBL_H e
USING FRD_TBL h
ON (e.beld = h.beld)
WHEN MATCHED THEN
UPDATE SET e.valid_to = sysdate
where e.valid_to = 01.01.1999
Then Insert query
insert into FRD_TBL_H e
select h.*,
sysdate as valid_from,
01.01.1999 as valid_to
from FRD_TBL h;
As i am quiet new to use Partition logic for the table...Will the Merge query is performance efficient if i use parition by month on valid_to column or do i need to have partition on valid_from as well ?