| slow update on large table [message #251062] |
Thu, 12 July 2007 06:09  |
subusona
Messages: 70
Registered: March 2005
Location: delhi
|
Member |
|
|
UPDATE case_information a
SET queue = 'XYZ',
queue_type = 'CL' ,
queue_date = SYSDATE ,
makedate = SYSDATE
WHERE exists (select 1 from case_tab b where a.case_id = b.case_id)
case_tab table has a single column case_id which is been populated from batch.
case_tab table can contain 50,000 records at times
and case_information table has more than 1 million records having 400 columns
please suggest a solution..
subu
|
|
|
|
|
|
| Re: slow update on large table [message #251086 is a reply to message #251065] |
Thu, 12 July 2007 06:41  |
subusona
Messages: 70
Registered: March 2005
Location: delhi
|
Member |
|
|
Additional information::
10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
EXPLAIN PLAN
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
UPDATE STATEMENT Hint=ALL_ROWS 156 K 28161
UPDATE CS_CASE_INFO
HASH JOIN RIGHT SEMI 156 K 9 M 28161
TABLE ACCESS FULL CASE_TAB 156 K 1 M 75
TABLE ACCESS FULL CS_CASE_INFO 732 K 36 M 25634
|
|
|
|
|
|
| Re: slow update on large table [message #251320 is a reply to message #251115] |
Thu, 12 July 2007 21:38 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
How long does it take?
How many rows does it update?
Now run this query:
SELECT *
FROM (
SELECT *
FROM case_information a
WHERE exists (
select 1 from case_tab b where a.case_id = b.case_id)
)
WHERE rownum > 1
How long does it take?
This query will tell you how long it takes to FIND the data. The difference between it and the update statement will give you the amount of time it takes to writeback the updates.
eg. If the update takes 10 mins, and the select above takes 1 min, then there is no point tuning the row-finding part because you will never get it below 9 minutes.
When you know the problem (the find or the writeback) post your findings here.
Ross Leishman
|
|
|
|
|
|
| Re: slow update on large table [message #251525 is a reply to message #251475] |
Sat, 14 July 2007 02:29 |
subusona
Messages: 70
Registered: March 2005
Location: delhi
|
Member |
|
|
Yes case id is uniqe identifier and both are indexed.
This update takes almost 45 mts. It will updates all the rows in case_info table pesent in case_tab table. Case_tab table contains 40,000 records.
This is strange, I am not sure why it is taking so long time, I have tried analyzed the table.
also just before this update there is a insert on case_tab as
insert /*+ append */ case_id into case_tab
select case_id from case_info
where queue = 'QUE1'
and deliquent_flg = 'Y'
and status_flg = 'A'
fin_id = 'SOME_STR'
All the columns in where condition are having low cardinality records. Hence there is no index on them. Only fin_id is a part of unique key.
queue columns has atmost 90 distinct values.
deliquent_flg columns has atmost 2 distinct values.
status_flg columns has atmost 2 distinct values.
This is also taking lot of time.
Please help.
subusona
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
