Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: Delta load (adds and deletes

    Hi Guys,
    I am trying to build a database which needs to be updated once in a month. I get same source every month(with additions and deletions from previous load) and i need to find newly added records and the missing records from the previous load(Delta). i need to check besed on a key field (SSN).
    what is the most efficient way to calculate delta since i am working with appx 80 million records

    any help would be appreciated
    thanks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Delta load (adds and deletes

    Originally posted by babums
    Hi Guys,
    I am trying to build a database which needs to be updated once in a month. I get same source every month(with additions and deletions from previous load) and i need to find newly added records and the missing records from the previous load(Delta). i need to check besed on a key field (SSN).
    what is the most efficient way to calculate delta since i am working with appx 80 million records

    any help would be appreciated
    thanks in advance
    select ssn from old_table
    minus
    select ssn from new_table;
    -- results set are the deleted SSNs

    select ssn from new_table
    minus
    select ssn from old_table;
    --results set are the new SSNs

  3. #3
    Join Date
    Aug 2003
    Posts
    7

    Re: Delta load (adds and deletes

    Is this the most efficiant way? I thought of this long time back but i am looking for a better way of doing it. i have two problems. i have to process 60 million records in a single load and old_table has ssn stored in number format and new table has SSN Stored in varchar type(SQL Loader extract from flat file).
    any suggestions are welcome


    Originally posted by anacedent
    select ssn from old_table
    minus
    select ssn from new_table;
    -- results set are the deleted SSNs

    select ssn from new_table
    minus
    select ssn from old_table;
    --results set are the new SSNs

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Delta load (adds and deletes

    Originally posted by babums
    Is this the most efficiant way? I thought of this long time back but i am looking for a better way of doing it. i have two problems. i have to process 60 million records in a single load and old_table has ssn stored in number format and new table has SSN Stored in varchar type(SQL Loader extract from flat file).
    any suggestions are welcome
    You can have it
    good, fast, cheap;
    pick any two.

    If you can include into the "flat file",
    two additional columns
    (1 ADD/CHANGE/DELETE[A|C|D] flag
    2 MODIFY date
    ) then you could "selectively" change the existing table data.

    Either you "know beforehand" what data has changed (how & when)
    or you need to do a brute force compare.

    You can't have both "dumb data" and efficient processing at the same time. Either input data contain enough details to efficiently "merge" the
    changes into the existing table, or pay the price of determining what has changed via brute force compares.

Posting Permissions

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