DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Suggestions needed.

  1. #1
    Join Date
    Feb 2004
    Posts
    77

    Suggestions needed.

    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.

    example:
    --------

    table (xref_item), columns(old_item_id, new_item_id, priority)
    data:
    item1 itema 1
    item2 itema 2
    item3 itema 3
    item4 itemb 1
    item5 itemb 2
    item6 itemb 3

    application table
    table(po_details), columns (po_nbr, item_id, req_qty, round, country)
    data:
    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)
    from (
    select ix.new_item_id item_id,
    po.po_nbr po_nbr,
    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,
    po.req_qty req_qty,
    from po_details po,
    item_xref ix
    where po.item_id = ix.old_item_id)
    group by item_id, po_nbr

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    77
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width