I am involved in a major data conversion effort. Below are the details.
Due to some interface changes and new systems installed, all the item_id values in our database needs to be changed to new values. The item_id column is referenced in almost every table and is in the primary key.
To make things complicated multiple old item_id values can be mapped to a single new item_id value. So an aggregation needs to happen for all the records that have same new item_id. The columns which cannot be aggregated, where we take the attributes of a record which has priority over other items.
Just wanted to know how to approach this conversion.
table(po_details), columns (po_nbr, item_id, req_qty, round, country)
po1 item1 10 Y US
po1 item2 15 N US
po1 item3 20 Y UK
po1 item4 10 N UK
po1 item5 15 Y US
at the end of the conversion.
I should have
po1 itema 45 Y US
po1 itemb 25 N UK
the last 2 column values are picked from po_record's item_id based on the priority.
The stmt to do so is
insert into po_details_new /*+ APPEND */
select item_id, po_nbr, sum(req_qty), min(round), min(country)
select ix.new_item_id item_id,
first_value(po.round) over (partition by ix.new_item_id, po.po_nbr order by ix.priority) round,
first_value(po.county) over (partition by ix.new_item_id, po.po_nbr order by ix.priority) country,
from po_details po,
where po.item_id = ix.old_item_id)
group by item_id, po_nbr
padders - you are correct on the /*+ APPEND */ hint.
I am still in the design phase of this conversion so have not actually run the queries to see how long they will take. Just wanted to put it out and see if there are better methods to do this kind of conversion.
My tables (40 in count) are medium sized. (500,000 to 10 million rows). The xref table will have ~ 400,000 records.
And I plan to alter the new tables for "nologging" during the conversion. Rename the old tables to _bak after conversion and put new tables in its place and enable refrential integrity.