Update taking too long

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?



xoxomosAsked:
Who is Participating?
 
schwertnerConnect With a Mentor Commented:
There are 11 indexes on the table!
It is tooooo much!
0
 
shanikawmCommented:
Can you give description of two tables?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
what is this "update_table.primary_key" ? in your update.

Does your table have any indexes
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
xoxomosAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
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
 
shanikawmCommented:
You have only 1 row in update_table?
0
 
xoxomosAuthor Commented:
no there are about 1590
0
 
shanikawmConnect With a Mentor Commented:
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
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
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
 
schwertnerCommented:
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
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
I'm attaching a zip of the Enterprise Manager page that shows the table, constraints and indexes.
swapna.txt
0
 
xoxomosAuthor Commented:
The table used to update is just one column, primary_key of type number.      


[No] [No]       PRIMARY_KEY      NUMBER      
      
            
0
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
My sentiments exactly :-)
0
 
xoxomosAuthor Commented:
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
 
xoxomosAuthor Commented:
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
 
schwertnerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.