0

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 9 hours but still update is running.

APC
  • 144,005
  • 19
  • 170
  • 281
Rak kundra
  • 168
  • 11
  • Just to make sure, have you confirmed that there is no blocking lock that your update could be waiting on? Thanks – alexgibbs May 20 '17 at 03:52
  • There are no blocking sessions on the table .I tried to take only one record and update the table and it updated within seconds.So that i can assume that there are no blocking sessions on the table . – Rak kundra May 20 '17 at 05:16
  • so for each records in the raw table you have to update you have to scan the all the 136000 records of the master table. If you have to update 1000 records then you have to scan 136 000 000 master records. If you have to update all the 135171 records than you have to scan 135171*13600 ~ 18 000 000 000 records. – miracle173 May 20 '17 at 06:59
  • yeah !! can i get any alternate option for that ? do i need to create index's on the table ? – Rak kundra May 20 '17 at 07:03
  • how much time it will take @miracle173 its running from the past 15 hours. – Rak kundra May 20 '17 at 07:14
  • How many records in RAW_ADDRESS where ADDR_ID is null? What is ADDR_KEY? How selective is it? – APC May 20 '17 at 09:11
  • @APC I have about 136767 records where addr id is null in the raw address table – Rak kundra May 20 '17 at 11:58
  • You haven't answered my other question. Tuning is about the details. We can't help you if you won't provide **full details**. – APC May 20 '17 at 13:12

1 Answers1

0

the reason your update is taking a lot of time is the column based function you are using in the where condition (NVL for each column)

there's few tricks you can use and it should help

replace the null values in your base tables with actual values like 'NA'

use pl/sql cursor with rownum counter and for loop to update ( return 100 records update them and flag a dummy column with a value to exclude them from the next 100 and commit) that is great because if you stop for one reason you can continue where you left off

Ibrahim M. Nabil
  • 16
  • 1