Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Updating from external table

    I have an external table that contains data that I need to use to update an Oracle table. It's taking quite a while (40 minutes to update 25,000 recs), and I was hoping for some pointers. Here's the basic update statement now:

    update oracle_table ot
    set field = (select field from external_table et where ot.pk = et.pk)

    I'm pretty sure the load will go faster if I insert the contents of the external table into an oracle table and index the pk field. Is this the best way to go?

    Thanks,
    Chuck

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Chuck,

    Since you cannot index an external table (reference) I would suggest you insert the entire external table into a temporary use table, index the temp table by the PK column and then do your UPDATE query. Problem with your query taking so long is that EVERY TIME you update one column in your ORACLE_TABLE, the query requests that the EXTERNAL_TABLE be scanned for a match... without an index, your are issuing 25,000 full table scans ... that it takes ONLY 40 minutes is actually pretty good.
    JoeB
    save disk space, use smaller fonts

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Another way would be to do a cursor loop through the external table and update the indexex master table. This would scan the external table only once and would allow you to not use a GTT.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    We inserted the data into an indexed table, and then ran the update statement, reducing the total runtime to about 30 seconds.
    -cf

Posting Permissions

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