| Tuning Update Statements [message #155502] |
Mon, 16 January 2006 10:03  |
csatish
Messages: 5
Registered: January 2006
|
Junior Member |
|
|
Hi,
I need some help on tuning an update statement . This Statement takes around 240 mins to update 27 million records.
The Sql is given below
UPDATE TMP_REP_SUM rs1
SET rs1.DET_CD = 'REPEAT'
WHERE EXISTS
(
SELECT 1
FROM TMP_REP_SUM rs2
WHERE rs1.p_id = rs2.p_id
AND (rs1.sc_id <> rs2.sc_id)
AND (((rs1.sc_end_dt >= rs2.sc_strt_dt)
AND (rs1.sc_end_dt <= rs2.sc_end_dt))
OR ((rs2.scn_end_dt >= rs1.sc_strt_dt)
AND (rs2.sc_end_dt <= rs1.sc_end_dt)))
);
The table tmp_rep_sum has indexes on the following columns
Index1 on p_id,sc_id,sc_strt_dt,sc_end_Dt
Index2 on det_cd
The explain plan is attached
-
Attachment: EXP_PLAN.txt
(Size: 0.39KB, Downloaded 1644 times)
|
|
|
|
| Re: Tuning Update Statements [message #155529 is a reply to message #155502] |
Tue, 17 January 2006 00:33  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
How many rows is it updating? If it's more than 100,000 then 4 hours is not too bad.
You might be able to avoid a full table scan by reading the index twice instead of once. And parallel query might speed it up as well.
UPDATE TMP_REP_SUM rs
SET DET_CD = 'REPEAT'
WHERE rowid in (
(
SELECT /*+ parallel(rs2) parallel(rs1)*/ rs2.rowid
FROM TMP_REP_SUM rs1 TMP_REP_SUM rs2
WHERE rs1.p_id = rs2.p_id
AND (rs1.sc_id <> rs2.sc_id)
AND (((rs1.sc_end_dt >= rs2.sc_strt_dt)
AND (rs1.sc_end_dt <= rs2.sc_end_dt))
OR ((rs2.scn_end_dt >= rs1.sc_strt_dt)
AND (rs2.sc_end_dt <= rs1.sc_end_dt)))
);
Ideally, it would something like:
UPDATE TMP_REP_SUM
FILTER
MERGE JOIN
INDEX ACCESS (FAST FULL SCAN) Index2
INDEX ACCESS (FAST FULL SCAN) Index2
Plus a few line for Parallel Query stuff.
_____________
Ross Leishman
|
|
|
|
| Re: Tuning Update Statements [message #155587 is a reply to message #155502] |
Tue, 17 January 2006 08:17 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm not understanding something, how this update makes any sense, your rs1 and rs2 are the same table?
TMP_REP_SUM rs1
TMP_REP_SUM rs2
Also, since you are changing a column that is indexed, and that column is not involved in the criteria for which rows to update, I would want to at least test the speed of doing the update with the index disabled and then rebuilt at the end to avoid the row by row index maintenance overhead.
|
|
|
|
| Re: Tuning Update Statements [message #155682 is a reply to message #155529] |
Wed, 18 January 2006 00:11 |
csatish
Messages: 5
Registered: January 2006
|
Junior Member |
|
|
Thanks Ross,
But I forgot to mention that the table is a global temporary table and the parallel option won't work. Since the table is a self join and has to update around 27 million rows (likely to increase to another 15 million) this would take huge amount of time. So could you please let me know any other way around
Cheers,
Satish
|
|
|
|
| Re: Tuning Update Statements [message #155684 is a reply to message #155587] |
Wed, 18 January 2006 00:16 |
csatish
Messages: 5
Registered: January 2006
|
Junior Member |
|
|
Thanks martin.
The problem began when i dropped the index and reran the job.Previously the same job used to take 2 hrs to complete to update 27 million records i wanted to tune a bit faster so i dropped the index on the column being updated and then ran the job. It took around 10 hrs to complete. So i recreated the index and then ran the job. Now it is taking around 4 hours.
I am running the dbms_stat gather for this table before this update so the statistics are refreshed correctly.Also this table is a Global temporary table
Any other solution please let me know
Thanks in Advance
Satish
|
|
|
|
|
|
| Re: Tuning Update Statements [message #155713 is a reply to message #155696] |
Wed, 18 January 2006 02:28 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I think you've got your indexes mixed up. "Index2" has to be the 4 column index - there is no way that SQL could use an index on DET_CD.
Assuming this is right, then Index1 must be the one on DET_CD. I would drop it like smartin says. I think you dropped the wrong one before when it made the update slower.
When you've dropped the DET_CD index, try my SQL above.
The reason why this SQL should be fast is because indexes are sorted. A MERGE join on a leading subset of the index should not require a sort. In your plan, the FILTER step performs a separate index lookup for every row - thats 27mill scans. That's RANGE scans too, not unique scans. The MERGE will full scan the leaf nodes twice only.
_____________
Ross Leishman
|
|
|
|
| Re: Tuning Update Statements [message #155745 is a reply to message #155713] |
Wed, 18 January 2006 05:33 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Hi,
Can u pl. post the table structure of TMP_REP_SUM ? In your query is there a typing error in the third line?
(((rs1.sc_end_dt >= rs2.sc_strt_dt)
AND (rs1.sc_end_dt <= rs2.sc_end_dt))
OR ((rs2.scn_end_dt >= rs1.sc_strt_dt)--is this rs2_sc_end_dt?
AND (rs2.sc_end_dt <= rs1.sc_end_dt)))
|
|
|
|
| Re: Tuning Update Statements [message #155872 is a reply to message #155745] |
Thu, 19 January 2006 01:23 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
An easier way to search for crossovers is:
WHERE rs1.p_id = rs2.p_id
AND rs1.sc_id <> rs2.sc_id
AND rs1.sc_end_dt >= rs2.sc_strt_dt
AND rs2.sc_end_dt >= rs1.sc_strt_dt
_____________
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
