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 (xref_item), columns(old_item_id, new_item_id, priority)
item1 itema 1
item2 itema 2
item3 itema 3
item4 itemb 1
item5 itemb 2
item6 itemb 3
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
Seems reasonable. How big are the two tables? I guess you are asking because you having a performance problem?
I might be tempted to put the SUM in the view as an analytic and then just get the first row from each partition. Not saying for sure that would be a whole lot faster though, just a hunch.
Incidentally you have /*+ APPEND */ hint in the wrong place. It goes immediately after INSERT.
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.
Click Here to Expand Forum to Full Width