| Help with SQL Optimization (merged) [message #363184] |
Fri, 05 December 2008 05:26  |
Creems
Messages: 70
Registered: July 2007
Location: Johannesburg
|
Member |
|
|
Hi People,
I have this SQL query thats been runnning for hours now. I need someone to help me with a better plan for it.
Below are the info:
UPDATE SYSPRD9.PS_PROJ_RESOURCE
SET BI_DISTRIB_STATUS = 'P'
WHERE (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, RESOURCE_ID
EXISTS
(SELECT BUSINESS_UNIT_PC , PROJECT_ID , ACTIVITY_ID , RESOURCE_ID
FROM SYSPRD9.PS_CA_BI_ERR_TA213
WHERE PROCESS_INSTANCE = 1711564
AND CA_ERROR_TYPE EXISTS ('FPC', 'WTH'));
Here is the Plan Output:
Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
UPDATE STATEMENT | 1 | 129 | 4 (25) |
UPDATE | PS_PROJ_RESOURCE | | | |
TABLE ACCESS BY INDEX ROWID | PS_PROJ_RESOURCE
NESTED LOOPS SORT UNIQUE
That is just a part of the execution plan. CAn someone help me optimize it ... make it run faster?
Predicate Information (identified by operation id):
-----------------------------------------------------------------
2 - filter("PROJECT_ID"="PROJECT_ID" AND "ACTIVITY_ID"="ACTIVITY_ID" AND
"RESOURCE_ID"="RESOURCE_ID")
5 - filter("CA_ERROR_TYPE"='FPC' OR "CA_ERROR_TYPE"='WTH')
6 - access("PROCESS_INSTANCE"=1711564)
7 - access("BUSINESS_UNIT"="BUSINESS_UNIT_PC")
The cost (%CPU) is 4 (25%)
Regards.
Texas B
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help with SQL Optimization (merged) [message #363333 is a reply to message #363213] |
Fri, 05 December 2008 19:15  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I agree with @JR.
However if you are updating more than about 10% of the table then it would be faster still to just rebuild the table using CREATE TABLE new_tabe AS SELECT ...
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
