| Tune the Update Process [message #211452] |
Thu, 28 December 2006 15:18  |
yerics
Messages: 89
Registered: August 2006
|
Member |
|
|
Hi,
I have table x with 2 Million Rows and table y with 2.1 million rows. Following update process below runs for more than 45 min. Can this be tuned further.
There is unique key constraints for c_sec_complex,c_userid,c_dist_1 and c_dist_2
UPDATE
(SELECT *
FROM sec_userids
WHERE c_sec_complex || c_userid ||c_dist_1||nvl(c_dist_2,'!')
NOT IN
(SELECT c_sec_complex || c_userid ||c_dist_1||nvl(c_dist_2,'!')
FROM vw_temp_sec_userids)
)
SET d_del=sysdate;
Regards,
Prasanna
|
|
|
|
|
|
| Re: Tune the Update Process [message #211471 is a reply to message #211466] |
Thu, 28 December 2006 19:42  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The code below might help a bit.
UPDATE sec_userids
SET d_del=sysdate
WHERE (c_sec_complex, c_userid, c_dist_1, nvl(c_dist_2,'!')
NOT IN (
SELECT b.c_sec_complex, b.c_userid, b.c_dist_1, nvl(b.c_dist_2,'!')
FROM vw_temp_sec_userids b
);
Run it through EXPLAIN PLAN and see if you are getting a HASH JOIN (ANTI) step. If not, then it's probably because vw_temp_sec_userids is a view or one of the join columns is nullable.
Also try:
UPDATE sec_userids a
SET d_del=sysdate
WHERE NOT EXISTS (
SELECT 1
FROM vw_temp_sec_userids b
WHERE b.c_sec_complex = a.c_sec_complex
AND b.c_userid = a.c_userid
AND b.c_dist_1 = a.c_dist_1
AND nvl(b.c_dist_2,'!') = nvl(a.c_dist_2,'!')
)
If that shows UNIQUE or RANGE INDEX SCAN on the objects of vw_temp_sec_userids but not TABLE ACCESS, then it should be a bit more efficient.
Failing this, you could try a MINUS.
UPDATE sec_userids
SET d_del=sysdate
WHERE (c_sec_complex, c_userid, c_dist_1, c_dist_2
IN (
SELECT c_sec_complex, c_userid, c_dist_1, c_dist_2
FROM sec_userids
MINUS
SELECT b.c_sec_complex, b.c_userid, b.c_dist_1, b.c_dist_2
FROM vw_temp_sec_userids b
);
Ross Leishman
|
|
|
|
| Re: Tune the Update Process [message #211573 is a reply to message #211471] |
Fri, 29 December 2006 09:53 |
yerics
Messages: 89
Registered: August 2006
|
Member |
|
|
Thanks a Ton Ross for the wonderful options.
In fact the 2nd query used the following plan..
Execution Plan
---------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'SEC_USERIDS'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SEC_USERIDS'
4 2 INDEX (RANGE SCAN) OF 'CONS_TEMP_SECUID_UNQ' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
208 db block gets
3042 consistent gets
0 physical reads
47328 redo size
361 bytes sent via SQL*Net to client
599 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
199 rows processed
and the last one..with the MINUS.
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'SEC_USERIDS'
2 1 MERGE JOIN
3 2 SORT (JOIN)
4 3 TABLE ACCESS (FULL) OF 'SEC_USERIDS'
5 2 SORT (JOIN)
6 5 VIEW OF 'VW_NSO_1'
7 6 MINUS
8 7 SORT (UNIQUE)
9 8 TABLE ACCESS (FULL) OF 'SEC_USERIDS'
10 7 SORT (UNIQUE)
11 10 TABLE ACCESS (FULL) OF 'TEMP_SEC_USERIDS'
Statistics
----------------------------------------------------------
7 recursive calls
208 db block gets
804 consistent gets
646 physical reads
47328 redo size
363 bytes sent via SQL*Net to client
643 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
199 rows processed
Both of these queries performed fast except for in the 1st case, the physical reads, consistent gets and recursive calls were pretty less.
Thanks Again.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Distribution of data within columns of interest