Solved

Update taking too long

Posted on 2008-06-16
21
1,063 Views
Last Modified: 2013-12-19
Trying to update table of 30,000 rows from a table of about 1500 rows.

update table-a set row_status = '2'
where
         data_src_pk1 = '422'
  and course_id like '2073%'
  and table-a.primary_key = update_table.primary_key;

It's been running seven hours.  Any notions what is wrong?



0
Comment
Question by:xoxomos
  • 12
  • 3
  • 3
  • +1
21 Comments
 
LVL 8

Expert Comment

by:shanikawm
ID: 21799239
Can you give description of two tables?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 21799451
what is this "update_table.primary_key" ? in your update.

Does your table have any indexes
0
 

Author Comment

by:xoxomos
ID: 21799610
update_table is just one colum containing the value of the primary key.  Table-a is the table to be updated.  Table-a.primary_key is the column on table-a that that is to be matched.
I'm looking at Enterprise Manager now and seeing:

SQL statements were found waiting for row lock waits.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 21799620
is that update_table a normal sql table or pl/sql table/collection ( array ) or what is that ?

is this update part of some pl/sql block  of code or a stand alone one ?
0
 

Author Comment

by:xoxomos
ID: 21799634
EM says:
Significant row contention was detected in the TABLE "BB_BB60.COURSE_MAIN" with object id 6950. Trace the cause of row contention in the application logic using the given blocked SQL.


How do I trace the cause of row contention?
0
 

Author Comment

by:xoxomos
ID: 21800085
First i tried creating an external table and updating from that.  Then I created update_table as select * from external_table so it is now just a table created from SQL.
0
 

Author Comment

by:xoxomos
ID: 21800126
Sorry, I got that wrong on top.  It should be:
update table-a set row_status = '2'
where
         data_src_pk1 = '422'
  and course_id like '2073%'
  and table-a.primary_key = (select  update_table.primary_key from update_table);
That's the entire query.


0
 
LVL 8

Expert Comment

by:shanikawm
ID: 21800176
You have only 1 row in update_table?
0
 

Author Comment

by:xoxomos
ID: 21800192
no there are about 1590
0
 
LVL 8

Assisted Solution

by:shanikawm
shanikawm earned 75 total points
ID: 21800218
Then correct query should be,

update table-a set row_status = '2'
where   data_src_pk1 = '422'  and course_id like '2073%'
and table-a.primary_key IN (select  update_table.primary_key from update_table);


And what is the data type of row_status and data_src_pk1?
0
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 75 total points
ID: 21800264
what indexes do you have on the both the tables involved ?

if you could let us know this, then we can come up with a query
to reduce time.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21800663
Deliver fresh statistics to the optimizer.
There are enough postings here how to do this.
As said above analyze the indexes.
Normally they cause the problem.
Also row chaining and migrations can speed down the update.
Tables and indexes should be placed on different disk devices.
0
 

Author Comment

by:xoxomos
ID: 21804449
Thanks, i'll try all these.  I'll run statistics, but Oracle has it's own job where it runs statistics that shows a last analyzed date of Jun 15, 2008.

 BB_BB60.COURSE_MAIN Table NO NO Jun 15, 2008 2:23:52 AM
0
 

Author Comment

by:xoxomos
ID: 21804826
I'm attaching a zip of the Enterprise Manager page that shows the table, constraints and indexes.
swapna.txt
0
 

Author Comment

by:xoxomos
ID: 21804906
The table used to update is just one column, primary_key of type number.      


[No] [No]       PRIMARY_KEY      NUMBER      
      
            
0
 

Author Comment

by:xoxomos
ID: 21806803
The same thing in a MERGE works ???????

Merge into course_main A

using course_main_updates B

on (A.pk1 = B.primary_key)

when matched then update

set A.row_status ='0'

where  A.data_src_pk1 ='422' and A.course_id like '2073%'

0
 
LVL 47

Accepted Solution

by:
schwertner earned 100 total points
ID: 21810606
There are 11 indexes on the table!
It is tooooo much!
0
 

Author Comment

by:xoxomos
ID: 21814331
My sentiments exactly :-)
0
 

Author Comment

by:xoxomos
ID: 21814506
Well it finally did complete using the subquery method.  I had stopped looking so i don't know how long it took but the merge/update method was MUCH MUCH quicker????????????
0
 

Author Comment

by:xoxomos
ID: 21817442
Still no idea why the update using the subquery took more than a day to complete, but the same update on the same tables took about a minute using the MERGE???????????????
0
 
LVL 47

Expert Comment

by:schwertner
ID: 21820344
in your original guery I do not see any subquery.
If you investigate the full text possibly you will see
that you use 'correlated' subquery.
Correlated subqueries executes many times - once time for every row of the main query.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
RDBMS and No sql database 4 58
Shredding xml into an oracle 11g Database 2 44
Oracle Insert not working 10 27
SQL2016 to ORACLE11G linked-server 6 12
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question