Skip to Main Content
  • Questions

  • Issue with the update query in oracle

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, raj.

Asked: May 20, 2017 - 7:20 am UTC

Last updated: May 21, 2017 - 12:46 am UTC

Version: 11g

Viewed 1000+ times

You Asked


0
down vote
favorite
I have an update query which is taking 10 hours to update and still it is in the updating stage itself it has about 135171 records only.

uPDATE hr.raw_address ora
SET (ora.addr_id,
) =
(SELECT oms.addr_id
FROM hr.addresss_oet OMSS,
hr.master_address oms
WHERE NVL (oms.addr_line1, '-1') = NVL (OMSS.addr_line1, '-1')
AND NVL (oms.addr_line2, '-1') = NVL (OMSS.addr_line2, '-1')
AND NVL (oms.city, '-1') = NVL (OMSS.city, '-1')
AND NVL (oms.state, '-1') = NVL (OMSS.state, '-1')
AND NVL (oms.pstl_id, '-1') = NVL (OMSS.pstl_id, '-1')
AND ORA.ADDR_KEY = OMSS.ADDR_KEY
)
WHERE ora.addr_id IS NULL;​
The master table (master_address has about 136000 records) and the join is based on the oet (address_oet) table and the master table are joined based on the address fields . when the address are matched we will take the respective addr_id from the master table and update the addr_id in the raw table based on the addr_key combination on the oet table ant the raw table . But its already 14 hours but still update is running. i guess the table need to scan 18 000 000 000 times can i get the solution for the problem.


Thanku
raj.

and Connor said...

Please give us an execution plan for both the full update, and for the following SQL

SELECT oms.addr_id
FROM hr.addresss_oet OMSS,
     hr.master_address oms
WHERE NVL (oms.addr_line1, '-1') = NVL (OMSS.addr_line1, '-1')
AND NVL (oms.addr_line2, '-1') = NVL (OMSS.addr_line2, '-1')
AND NVL (oms.city, '-1') = NVL (OMSS.city, '-1')
AND NVL (oms.state, '-1') = NVL (OMSS.state, '-1')
AND NVL (oms.pstl_id, '-1') = NVL (OMSS.pstl_id, '-1')
AND :b1 = OMSS.ADDR_KEY


but there's a good chance the SQL statement above will be run for each row in the source table, so if that SQL is slow, then it will be "slow x n" where 'n' is the number of rows that need to be considered.

That inline select suggests you have no true correlation between the two tables, just a concatenation of potentially not-null fields.

If this is a one-off exercise, you could consider putting a hash key on each table as a virtual column, for example

hash_key generated as
ora_hash(addr_line1||':'||addr_line2||... pstl_id)

and then index oms on that hashkey, and omss on ( addr_key, hashkey)

to give good selectivity into each table.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

May I suggest MERGE?

Stew Ashton, May 21, 2017 - 2:01 pm UTC

In the absence of test data this is not tested, but I think it provides the same result with one scan per table.
merge into (
    select addr_id, addr_key from raw_address
    where addr_id is null
) o
using (
    select ma.addr_id, ao.addr_key from master_address ma
    join addresss_oet ao
    on 0 = all(
        decode(ma.addr_line1,ao.addr_line1,0,1),
        decode(ma.addr_line2,ao.addr_line2,0,1),
        decode(ma.city,ao.city,0,1),
        decode(ma.state,ao.state,0,1),
        decode(ma.pstl_id,ao.pstl_id,0,1)
    )
) n
on (o.addr_key = n.addr_key)
when matched then update set addr_id = n.addr_id
    where o.addr_id != n.addr_id;

Correction to MERGE

Stew Ashton, May 21, 2017 - 2:08 pm UTC

Please remove the last line from the MERGE Statement!
merge into (
    select addr_id, addr_key from raw_address
    where addr_id is null
) o
using (
    select ma.addr_id, ao.addr_key from master_address ma
    join addresss_oet ao
    on 0 = all(
        decode(ma.addr_line1,ao.addr_line1,0,1),
        decode(ma.addr_line2,ao.addr_line2,0,1),
        decode(ma.city,ao.city,0,1),
        decode(ma.state,ao.state,0,1),
        decode(ma.pstl_id,ao.pstl_id,0,1)
    )
) n
on (o.addr_key = n.addr_key)
when matched then update set addr_id = n.addr_id;

Sorry about that...

Best regards, Stew
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.