Update statement resulting time out [message #445806] |
Thu, 04 March 2010 04:04 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
I have written an update statement in a procedure.
UPDATE traininginfo ti
SET ti.onlineuserid =
(SELECT UP.user_id
FROM bu_online_userid_upload UP, candidate c, trainingprog tp,traininginfo ti
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N')
WHERE EXISTS (
SELECT 1
FROM bu_online_userid_upload UP, candidate c, trainingprog tp, traininginfo ti
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N'
);
This is only a part of the procedure. If I comment this part the procedure runs fast.
Please find the explain plan for the above statement :_
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1068K| 2086K| 1782 (3)|
| 1 | UPDATE | TRAININGINFO | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | TRAININGINFO | 1068K| 2086K| 1016 (6)|
| 4 | NESTED LOOPS | | 77 | 2387 | 765 (1)|
| 5 | NESTED LOOPS | | 129 | 3354 | 636 (1)|
| 6 | NESTED LOOPS | | 127 | 2032 | 260 (1)|
|* 7 | VIEW | index$_join$_006 | 127 | 762 | 5 (0)|
|* 8 | HASH JOIN | | | | |
| 9 | BITMAP CONVERSION TO ROWIDS| | 127 | 762 | 1 (0)|
|* 10 | BITMAP INDEX SINGLE VALUE | IDXFLAG | | | |
| 11 | INDEX FAST FULL SCAN | APPLINDX | 127 | 762 | 4 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID | CANDIDATE | 1 | 10 | 2 (0)|
|* 13 | INDEX UNIQUE SCAN | XAK1CANDIDATE | 1 | | 1 (0)|
|* 14 | TABLE ACCESS BY INDEX ROWID | TRAININGINFO | 1 | 10 | 3 (0)|
|* 15 | INDEX RANGE SCAN | XIF106TRAININGINFO1 | 1 | | 2 (0)|
|* 16 | TABLE ACCESS BY INDEX ROWID | TRAININGPROG | 1 | 5 | 1 (0)|
|* 17 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 0 (0)|
| 18 | NESTED LOOPS | | 77 | 2926 | 767 (1)|
| 19 | NESTED LOOPS | | 129 | 4257 | 638 (1)|
| 20 | NESTED LOOPS | | 127 | 2921 | 262 (1)|
| 21 | TABLE ACCESS BY INDEX ROWID | BU_ONLINE_USERID_UPLOAD | 127 | 1651 | 7 (0)|
| 22 | BITMAP CONVERSION TO ROWIDS | | | | |
|* 23 | BITMAP INDEX SINGLE VALUE | IDXFLAG | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | CANDIDATE | 1 | 10 | 2 (0)|
|* 25 | INDEX UNIQUE SCAN | XAK1CANDIDATE | 1 | | 1 (0)|
|* 26 | TABLE ACCESS BY INDEX ROWID | TRAININGINFO | 1 | 10 | 3 (0)|
|* 27 | INDEX RANGE SCAN | XIF106TRAININGINFO1 | 1 | | 2 (0)|
|* 28 | TABLE ACCESS BY INDEX ROWID | TRAININGPROG | 1 | 5 | 1 (0)|
|* 29 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 0 (0)|
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "TRAININGINFO" "TI","TRAININGPROG" "TP","CANDIDATE"
"C", (SELECT "UP"."APPLNO" "APPLNO","UP"."FLAG" "FLAG",ROWID "ROWID" FROM
"BU_ONLINE_USERID_UPLOAD" "indexjoin$_alias$_002","BU_ONLINE_USERID_UPLOAD"
"indexjoin$_alias$_001" WHERE "UP"."FLAG"='F' AND ROWID=ROWID) "UP" WHERE "UP"."FLAG"='F'
AND "UP"."APPLNO"="C"."APPLNO" AND "TI"."TRGPROGID"="TP"."TRGPROGID" AND
"TP"."CANCELFLAG"='N' AND "C"."INTERMEDIARYID"="TI"."INTERMEDIARYID" AND
"TI"."SWITCHFLAG"='N'))
7 - filter("UP"."FLAG"='F')
8 - access(ROWID=ROWID)
10 - access("UP"."FLAG"='F')
13 - access("UP"."APPLNO"="C"."APPLNO")
14 - filter("TI"."SWITCHFLAG"='N')
15 - access("C"."INTERMEDIARYID"="TI"."INTERMEDIARYID")
16 - filter("TP"."CANCELFLAG"='N')
17 - access("TI"."TRGPROGID"="TP"."TRGPROGID")
23 - access("UP"."FLAG"='F')
25 - access("UP"."APPLNO"="C"."APPLNO")
26 - filter("TI"."SWITCHFLAG"='N')
27 - access("C"."INTERMEDIARYID"="TI"."INTERMEDIARYID")
28 - filter("TP"."CANCELFLAG"='N')
29 - access("TI"."TRGPROGID"="TP"."TRGPROGID")
64 rows selected.
Elapsed: 00:00:00.40
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)|
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | |
----------------------------------------------------------------------------------
The tables candidate, trainingprog,traininginfo have around 2-3 lakh records in each
Please advice as what can be done to the query to run it faster.
Regards,
Mahi
[Updated on: Thu, 04 March 2010 04:16] Report message to a moderator
|
|
|
|
Re: Update statement resulting time out [message #445815 is a reply to message #445811] |
Thu, 04 March 2010 04:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Thanks cookiemonster,
I edited the post for that change.
Please find the indexes on the tables used in update statement:-
Below is the list of indexes on the tables used :-
(I) BU_ONLINE_USERID_UPLOAD:-
1). create index APPLINDX ON BU_ONLINE_USERID_UPLOAD(APPLNO)
2). create INDEX UIDINDX ON BU_ONLINE_USERID_UPLOAD(UID_CREATION_DATE)
3). create INDEX UINDX ON BU_ONLINE_USERID_UPLOAD(USER_ID)
4). CREATE INDEX TCOMPINDX1 ON BU_ONLINE_USERID_UPLOAD(TRAININGORGID, APPLNO)
5). CREATE BITMAP INDEX IDXFLAG ON BU_ONLINE_USERID_UPLOAD(FLAG)
(II)TRAININGINFO :-
CREATE INDEX XIF313TRAININGINFO1 ON TRAININGINFO(PARAMETERCD)
CREATE INDEX XIF21TRAININGINFO1 ON TRAININGINFO(TRGPROGID)
CREATE INDEX XIF106TRAININGINFO1 ON TRAININGINFO(INTERMEDIARYID)
CREATE BITMAP INDEX IDXSWITCHFLAG ON TRAININGINFO(SWITCHFLAG)
CREATE unique index on traininginfo(intermediaryid, trgprogid)
(III)TRAININGPROG :-
CREATE UNIQUE INDEX XAK1TRAININGPROG1 ON TRAININGPROG(TRGPROGSECID)
CREATE INDEX NUQ_TPROGSTARTDATE1 ON TRAININGPROG(STARTDATE)
CREATE INDEX NUQ_TPROGENDDATE1 ON TRAININGPROG(ENDDATE)
CREATE BITMAP INDEX CANCELFLAG ON TRAININGPROG(CANCELFLAG)
CREATE INDEX BATCHTYPEINDX ON TRAININGPROG(BATCHTYPE)
CREATE UNIQUE INDEX TEXTCONST1 ON TRAININGPROG(TRGPROGID)
(IV)CANDIDATE :-
CREATE INDEX INDEX_APPLNDATE ON CANDIDATE(INTERMEDIARYID, DOAPPRECEIPT)
CREATE INDEX INDEX_APPLNODATE ON CANDIDATE(INTERMEDIARYID, APPLNO, DOAPPRECEIPT)
CREATE INDEX INDEX_PREDECISION ON CANDIDATE(INTERMEDIARYID, PREAPPDECISIONCENTRAL, APPDECISIONCENTRAL)
CREATE INDEX INDEX_PREDECISIONTOTAL ON CANDIDATE(INTERMEDIARYID, DOAPPRECEIPT, PREAPPDECISIONCENTRAL, APPDECISIONCENTRAL)
CREATE INDEX INDEX_APPLNDEC ON CANDIDATE(APPLNO, APPDECISIONCENTRAL)
CREATE UNIQUE INDEX XAK1CANDIDATE ON CANDIDATE(APPLNO)
CREATE INDEX XIF273CANDIDATE ON CANDIDATE(PRIORAGENCYEXPCD)
CREATE INDEX XIF274CANDIDATE ON CANDIDATE(AREAOFWORKEXPCD)
Please give me some input on this as the client is very annoyed with the slow query.
All the 4 tables involved have around 2-3 lakh records.
The number of record which should be updated hardly comes to 10.
Regards,
Mahi
[Updated on: Thu, 04 March 2010 04:38] Report message to a moderator
|
|
|
Re: Update statement resulting time out [message #445820 is a reply to message #445806] |
Thu, 04 March 2010 04:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You really should just copy and paste your queries, it avoids confusions like that.
The main problem appears to be the full table scan on traininginfo.
So how many rows are in that table?
How many rows should be updated?
What indexes do you have on that table?
|
|
|
Re: Update statement resulting time out [message #445822 is a reply to message #445815] |
Thu, 04 March 2010 04:39 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Given that you're accessing every row of the TrainingInfo table, you shuold be able to knock a big chunk off the processing time by rewriting the query like this:UPDATE traininginfo ti
SET ti.onlineuserid = NVL(
(SELECT UP.user_id
FROM bu_online_userid_upload UP, candidate c, trainingprog tp,traininginfo ti
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N'),ti.onlineuserid);
Actually, looking at your query more carefully, you've got the TrainingInfo table inside the sub-query too.
What happens if you rewrite the query like this:UPDATE traininginfo ti
SET ti.onlineuserid = NVL(
(SELECT UP.user_id
FROM bu_online_userid_upload UP, candidate c, trainingprog tp
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N'),ti.onlineuserid);
|
|
|
Re: Update statement resulting time out [message #445825 is a reply to message #445820] |
Thu, 04 March 2010 04:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi Jrow,
Thanks for pointing that out. Actually while testing I just put the table inside to test the result of inner query.
UPDATE traininginfo ti
SET ti.onlineuserid =
(SELECT UP.user_id
FROM bu_online_userid_upload UP, candidate c, trainingprog tp
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N')
WHERE EXISTS (
SELECT 1
FROM bu_online_userid_upload UP, candidate c, trainingprog tp
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N');
And the plan table :-
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 12 | 6384K (1)|
| 1 | UPDATE | TRAININGINFO | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS FULL | TRAININGINFO | 1068K| 12M| 1023 (6)|
|* 4 | FILTER | | | | |
| 5 | NESTED LOOPS | | 1 | 21 | 6 (0)|
| 6 | NESTED LOOPS | | 1 | 15 | 4 (0)|
|* 7 | TABLE ACCESS BY INDEX ROWID| TRAININGPROG | 1 | 5 | 2 (0)|
|* 8 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 1 (0)|
|* 9 | INDEX RANGE SCAN | INDEX_APPLNODATE | 1 | 10 | 2 (0)|
|* 10 | TABLE ACCESS BY INDEX ROWID | BU_ONLINE_USERID_UPLOAD | 1 | 6 | 2 (0)|
|* 11 | INDEX RANGE SCAN | APPLINDX | 1 | | 1 (0)|
|* 12 | FILTER | | | | |
| 13 | NESTED LOOPS | | 1 | 28 | 6 (0)|
| 14 | NESTED LOOPS | | 1 | 15 | 4 (0)|
|* 15 | TABLE ACCESS BY INDEX ROWID | TRAININGPROG | 1 | 5 | 2 (0)|
|* 16 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 1 (0)|
|* 17 | INDEX RANGE SCAN | INDEX_APPLNODATE | 1 | 10 | 2 (0)|
|* 18 | TABLE ACCESS BY INDEX ROWID | BU_ONLINE_USERID_UPLOAD | 1 | 13 | 2 (0)|
|* 19 | INDEX RANGE SCAN | APPLINDX | 1 | | 1 (0)|
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( EXISTS (SELECT 0 FROM "TRAININGPROG" "TP","CANDIDATE"
"C","BU_ONLINE_USERID_UPLOAD" "UP" WHERE :B1='N' AND "UP"."APPLNO"="C"."APPLNO" AND
"UP"."FLAG"='F' AND "C"."INTERMEDIARYID"=:B2 AND "TP"."TRGPROGID"=:B3 AND
"TP"."CANCELFLAG"='N'))
4 - filter(:B1='N')
7 - filter("TP"."CANCELFLAG"='N')
8 - access("TP"."TRGPROGID"=:B1)
9 - access("C"."INTERMEDIARYID"=:B1)
10 - filter("UP"."FLAG"='F')
11 - access("UP"."APPLNO"="C"."APPLNO")
12 - filter(:B1='N')
15 - filter("TP"."CANCELFLAG"='N')
16 - access("TP"."TRGPROGID"=:B1)
17 - access("C"."INTERMEDIARYID"=:B1)
18 - filter("UP"."FLAG"='F')
19 - access("UP"."APPLNO"="C"."APPLNO")
Note
-----
- 'PLAN_TABLE' is old version
49 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)|
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | |
----------------------------------------------------------------------------------
Please tell me a way to remove the full table scan.
Regards,
Mahi
|
|
|
Re: Update statement resulting time out [message #445826 is a reply to message #445822] |
Thu, 04 March 2010 04:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Jrow,
I used your query and below is the plan table output :-
UPDATE traininginfo ti
SET ti.onlineuserid = NVL(
(SELECT UP.user_id
FROM bu_online_userid_upload UP, candidate c, trainingprog tp
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N'),ti.onlineuserid);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1068K| 12M| 1019 (6)|
| 1 | UPDATE | TRAININGINFO | | | |
| 2 | TABLE ACCESS FULL | TRAININGINFO | 1068K| 12M| 1019 (6)|
|* 3 | FILTER | | | | |
| 4 | NESTED LOOPS | | 1 | 28 | 6 (0)|
| 5 | NESTED LOOPS | | 1 | 15 | 4 (0)|
|* 6 | TABLE ACCESS BY INDEX ROWID| TRAININGPROG | 1 | 5 | 2 (0)|
|* 7 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 1 (0)|
|* 8 | INDEX RANGE SCAN | INDEX_APPLNODATE | 1 | 10 | 2 (0)|
|* 9 | TABLE ACCESS BY INDEX ROWID | BU_ONLINE_USERID_UPLOAD | 1 | 13 | 2 (0)|
|* 10 | INDEX RANGE SCAN | APPLINDX | 1 | | 1 (0)|
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:B1='N')
6 - filter("TP"."CANCELFLAG"='N')
7 - access("TP"."TRGPROGID"=:B1)
8 - access("C"."INTERMEDIARYID"=:B1)
9 - filter("UP"."FLAG"='F')
10 - access("UP"."APPLNO"="C"."APPLNO")
Note
-----
- 'PLAN_TABLE' is old version
30 rows selected.
Elapsed: 00:00:00.15
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)|
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | |
----------------------------------------------------------------------------------
The full table scan still coming.
|
|
|
Re: Update statement resulting time out [message #445831 is a reply to message #445826] |
Thu, 04 March 2010 05:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
Please tell me a way to get rid of the FULL TABLE SCAN.
I have given the list of indexes in the second post. Please let me know if I need to create any index.
Please find attached the screenshot of explan plan from Toad which gives the value of cardinality with a clearer picture.
Please advice,
Mahi
[Updated on: Thu, 04 March 2010 05:21] Report message to a moderator
|
|
|
Re: Update statement resulting time out [message #445833 is a reply to message #445806] |
Thu, 04 March 2010 05:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Since you're referencing the unique key on traininginfo in the where clause I very much doubt an extra index is going to help. It'd use that one if it thought it could.
Try a merge, something like this:
MERGE INTO trainingingo t1
USING (SELECT up.user_id new_id, ti.intermediaryid, ti.trgprogid
FROM bu_online_userid_upload UP, candidate c, trainingprog tp, trainingingo ti
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N') NEW
ON (t1.intermediaryid = new.intermediaryid
AND t1.trgprogid = new.trgprogid)
WHEN MATCHED THEN
UPDATE SET t1.onlineuserid = new.new_id;
And next time we ask you for additional information can you please put it in a new post rather than update an existing one. It makes the thread a lot easier to follow if you do.
|
|
|
Re: Update statement resulting time out [message #445837 is a reply to message #445833] |
Thu, 04 March 2010 05:35 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi cookiemonster,
I ran the query given by you.
Below is the plan table for it:-
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 77 | 1463 | 917 (1)|
| 1 | MERGE | TRAININGINFO | | | |
| 2 | VIEW | | | | |
| 3 | NESTED LOOPS | | 77 | 5082 | 917 (1)|
| 4 | NESTED LOOPS | | 77 | 2926 | 763 (1)|
| 5 | NESTED LOOPS | | 129 | 4257 | 634 (1)|
| 6 | NESTED LOOPS | | 127 | 2921 | 257 (0)|
|* 7 | TABLE ACCESS FULL | BU_ONLINE_USERID_UPLOAD | 127 | 1651 | 3 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| CANDIDATE | 1 | 10 | 2 (0)|
|* 9 | INDEX UNIQUE SCAN | XAK1CANDIDATE | 1 | | 1 (0)|
|* 10 | TABLE ACCESS BY INDEX ROWID | TRAININGINFO | 1 | 10 | 3 (0)|
|* 11 | INDEX RANGE SCAN | XIF106TRAININGINFO1 | 1 | | 2 (0)|
|* 12 | TABLE ACCESS BY INDEX ROWID | TRAININGPROG | 1 | 5 | 1 (0)|
|* 13 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 0 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID | TRAININGINFO | 1 | 28 | 2 (0)|
|* 15 | INDEX UNIQUE SCAN | TEXTCONST2 | 1 | | 1 (0)|
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("UP"."FLAG"='F')
9 - access("UP"."APPLNO"="C"."APPLNO")
10 - filter("TI"."SWITCHFLAG"='N')
11 - access("C"."INTERMEDIARYID"="TI"."INTERMEDIARYID")
12 - filter("TP"."CANCELFLAG"='N')
13 - access("TI"."TRGPROGID"="TP"."TRGPROGID")
15 - access("T1"."INTERMEDIARYID"="TI"."INTERMEDIARYID" AND
"T1"."TRGPROGID"="TI"."TRGPROGID")
Note
-----
- 'PLAN_TABLE' is old version
37 rows selected.
Elapsed: 00:00:00.26
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)|
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | |
----------------------------------------------------------------------------------
I will take care while posting for new post.
Mahi
|
|
|
Re: Update statement resulting time out [message #445839 is a reply to message #445837] |
Thu, 04 March 2010 05:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
I made a small change in the query by putting one condition in the where clause :-
SQL> explain plan for UPDATE traininginfo ti
2 SET ti.onlineuserid = NVL(
3 (SELECT UP.user_id
4 FROM bu_online_userid_upload UP, candidate c, trainingprog tp
5 WHERE UP.applno = c.applno
6 AND UP.flag = 'F'
7 AND c.intermediaryid = ti.intermediaryid
8 -- AND ti.switchflag = 'N'
9 AND ti.trgprogid = tp.trgprogid
10 AND tp.cancelflag = 'N'),ti.onlineuserid)
11 where ti.switchflag = 'N' ;
Explained.
Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 534K| 6260K| 1057 (9)|
| 1 | UPDATE | TRAININGINFO | | | |
|* 2 | TABLE ACCESS FULL | TRAININGINFO | 534K| 6260K| 1057 (9)|
| 3 | NESTED LOOPS | | 1 | 28 | 6 (0)|
| 4 | NESTED LOOPS | | 1 | 15 | 4 (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID| TRAININGPROG | 1 | 5 | 2 (0)|
|* 6 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 1 (0)|
|* 7 | INDEX RANGE SCAN | INDEX_APPLNODATE | 1 | 10 | 2 (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID | BU_ONLINE_USERID_UPLOAD | 1 | 13 | 2 (0)|
|* 9 | INDEX RANGE SCAN | APPLINDX | 1 | | 1 (0)|
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TI"."SWITCHFLAG"='N')
5 - filter("TP"."CANCELFLAG"='N')
6 - access("TP"."TRGPROGID"=:B1)
7 - access("C"."INTERMEDIARYID"=:B1)
8 - filter("UP"."FLAG"='F')
9 - access("UP"."APPLNO"="C"."APPLNO")
Note
-----
- 'PLAN_TABLE' is old version
29 rows selected.
Elapsed: 00:00:00.17
Is this query better than others because the only difference I see in the first three lines in plan_output for number of records. But the cost of CPU increases.
Regards,
Mahi
|
|
|
|
Re: Update statement resulting time out [message #445842 is a reply to message #445840] |
Thu, 04 March 2010 05:46 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hey cookiemonster,
Please advice on the post just before yours. I am looking into the index for BU_ONLINE_USERID_UPLOAD (applno, flag) .
Is the query with one condition in where clause better ?
I added the index for for BU_ONLINE_USERID_UPLOAD (applno, flag)
but the full table scan is still there for BU_ONLINE_USERID_UPLOAD .
Regards,
Mahi
[Updated on: Thu, 04 March 2010 05:50] Report message to a moderator
|
|
|
|
Re: Update statement resulting time out [message #445845 is a reply to message #445843] |
Thu, 04 March 2010 06:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
The changed query that seems working better than the first one is :-
SQL> explain plan for UPDATE traininginfo ti
2 SET ti.onlineuserid = NVL(
3 (SELECT UP.user_id
4 FROM bu_online_userid_upload UP, candidate c, trainingprog tp
5 WHERE UP.applno = c.applno
6 AND UP.flag = 'F'
7 AND c.intermediaryid = ti.intermediaryid
8 -- AND ti.switchflag = 'N'
9 AND ti.trgprogid = tp.trgprogid
10 AND tp.cancelflag = 'N'),ti.onlineuserid)
11 where ti.switchflag = 'N' ;
Explained.
Elapsed: 00:00:00.06
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 534K| 6260K| 1057 (9)|
| 1 | UPDATE | TRAININGINFO | | | |
|* 2 | TABLE ACCESS FULL | TRAININGINFO | 534K| 6260K| 1057 (9)|
| 3 | NESTED LOOPS | | 1 | 28 | 6 (0)|
| 4 | NESTED LOOPS | | 1 | 15 | 4 (0)|
|* 5 | TABLE ACCESS BY INDEX ROWID| TRAININGPROG | 1 | 5 | 2 (0)|
|* 6 | INDEX UNIQUE SCAN | TEXTCONST1 | 1 | | 1 (0)|
|* 7 | INDEX RANGE SCAN | INDEX_APPLNODATE | 1 | 10 | 2 (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID | BU_ONLINE_USERID_UPLOAD | 1 | 13 | 2 (0)|
|* 9 | INDEX RANGE SCAN | APPLINDX | 1 | | 1 (0)|
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TI"."SWITCHFLAG"='N')
5 - filter("TP"."CANCELFLAG"='N')
6 - access("TP"."TRGPROGID"=:B1)
7 - access("C"."INTERMEDIARYID"=:B1)
8 - filter("UP"."FLAG"='F')
9 - access("UP"."APPLNO"="C"."APPLNO")
Please tell me if there is a way I can remove the full table scan.
Regards,
Mahi
|
|
|
Re: Update statement resulting time out [message #445846 is a reply to message #445806] |
Thu, 04 March 2010 06:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
There's no way to remove the full table scan with the update in that form. It relies on the full table scan.
That's why I suggested the merge - which isn't doing a full table scan on traininginfo but unfortunately is on bu_online_userid_upload - hence my index suggestion.
Run the update and the merge, see how long they actually take.
Try adding the index I suggested and see what difference it makes.
|
|
|
Re: Update statement resulting time out [message #445848 is a reply to message #445846] |
Thu, 04 March 2010 06:23 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi cookiemonster,
I tried adding the index for the merge statement, but the full table scan didn't go for the table bu_online_userid_upload.
Please tell me as what is difference between the value for the column "Cost (CPU%) " --
"1023 (6) and 5320K (1)" to "1057 (9) and 1057 (9)"
Which is better...
|
|
|
Re: Update statement resulting time out [message #445855 is a reply to message #445806] |
Thu, 04 March 2010 06:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well it would have helped if you said which of the numerous explain plans you were referening to, but not much.
Cost is just oracles estimate of how much work it's going to do. It's not an absolute figure. This asktom thread may help.
You really should actually run the new statements and see how long they actually take.
|
|
|
Re: Update statement resulting time out [message #446012 is a reply to message #445833] |
Fri, 05 March 2010 02:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
MERGE INTO trainingingo t1
USING (SELECT up.user_id new_id, ti.intermediaryid, ti.trgprogid
FROM bu_online_userid_upload UP, candidate c, trainingprog tp, trainingingo ti
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N') NEW
ON (t1.intermediaryid = new.intermediaryid
AND t1.trgprogid = new.trgprogid)
WHEN MATCHED THEN
UPDATE SET t1.onlineuserid = new.new_id;
When I am trying to run the Merge query its giving error :-
ORA-00905: missing keyword
Please advice as how to get rid of this error. All the columns and tablename are correct. What could be the possible reson for it.
Regards,
Mahi
[Updated on: Fri, 05 March 2010 03:03] Report message to a moderator
|
|
|
|
Re: Update statement resulting time out [message #446033 is a reply to message #446031] |
Fri, 05 March 2010 04:36 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
This is oracle version 9.2. Yesterday I was testing the samething on 10G and it was working. But on client site they are using 9i. Will I won't be able to use this? Please give alternate solution.
|
|
|
Re: Update statement resulting time out [message #446034 is a reply to message #445806] |
Fri, 05 March 2010 05:00 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That's why we tell you to post the oracle version when creating a thread, which you did but used the wrong one. You should always give the version of the production database (and if there's more than one - the lowest version).
You shouldn't be developing and testing in 10g if the client is running on 9i. Especially if you are doing performance tuning. 9i and 10g can give very different plans for the same sql statement.
So - all these explain plans - are they from your 10g DB or the 9i one?
If they are from the 10g then you will need to re-run the explain plans on the 9i DB and post those.
And no that merge will not work in 9i as the insert statement clause is mandatory in that version.
Also your client really needs to upgrade to 10g - oracle no longer supports 9i.
|
|
|
Re: Update statement resulting time out [message #446037 is a reply to message #446034] |
Fri, 05 March 2010 05:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi cookiemonster ,
Please find the query and the explain plan :-
UPDATE TRAININGINFO TI
SET TI.ONLINEUSERID = ( SELECT UP.USER_ID
FROM BU_ONLINE_USERID_UPLOAD UP,
CANDIDATE C, TRAININGPROG TP
WHERE UP.APPLNO = C.APPLNO
AND UP.FLAG='F'
AND C.INTERMEDIARYID = TI.INTERMEDIARYID
AND TI.SWITCHFLAG = 'N'
AND TI.TRGPROGID = TP.TRGPROGID
AND TP.CANCELFLAG = 'N' )
WHERE EXISTS (SELECT 1
FROM BU_ONLINE_USERID_UPLOAD UP,
CANDIDATE C,
TRAININGPROG TP
WHERE UP.APPLNO = C.APPLNO
AND C.INTERMEDIARYID = TI.INTERMEDIARYID
AND TI.SWITCHFLAG = 'N'
AND TI.TRGPROGID = TP.TRGPROGID
AND TP.CANCELFLAG = 'N');
The condition UP.FLAG='F' is missing in "where exists" condition because the updation has to be done for those rows only where this condition is true.
Row# PLAN_TABLE_OUTPUT
1
2 ---------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost |
4 ---------------------------------------------------------------------------------------------
5 | 0 | UPDATE STATEMENT | | 19799 | 831K| 99292 |
6 | 1 | UPDATE | TRAININGINFO | | | |
7 |* 2 | FILTER | | | | |
8 | 3 | TABLE ACCESS FULL | TRAININGINFO | 19799 | 831K| 297 |
9 |* 4 | FILTER | | | | |
10 | 5 | NESTED LOOPS | | 1 | 39 | 5 |
11 | 6 | NESTED LOOPS | | 1 | 26 | 4 |
12 |* 7 | TABLE ACCESS BY INDEX ROWID| TRAININGPROG | 1 | 16 | 2 |
13 |* 8 | INDEX UNIQUE SCAN | TEXTCONST1 | 9784 | | 1 |
14 | 9 | TABLE ACCESS BY INDEX ROWID| CANDIDATE | 1 | 10 | 2 |
15 |* 10 | INDEX UNIQUE SCAN | SYS_C006684 | 1061K| | 1 |
16 |* 11 | INDEX RANGE SCAN | APPLINDX | 1 | 13 | 1 |
17 |* 12 | FILTER | | | | |
18 | 13 | NESTED LOOPS | | 1 | 59 | 6 |
19 | 14 | NESTED LOOPS | | 1 | 26 | 4 |
20 |* 15 | TABLE ACCESS BY INDEX ROWID | TRAININGPROG | 1 | 16 | 2 |
21 |* 16 | INDEX UNIQUE SCAN | TEXTCONST1 | 9784 | | 1 |
22 | 17 | TABLE ACCESS BY INDEX ROWID | CANDIDATE | 1 | 10 | 2 |
23 |* 18 | INDEX UNIQUE SCAN | SYS_C006684 | 1061K| | 1 |
24 |* 19 | TABLE ACCESS BY INDEX ROWID | BU_ONLINE_USERID_UPLOAD | 1 | 33 | 2 |
25 |* 20 | INDEX RANGE SCAN | APPLINDX | 96 | | 1 |
26 ---------------------------------------------------------------------------------------------
27
28 Predicate Information (identified by operation id):
29 ---------------------------------------------------
30
31 2 - filter( EXISTS (SELECT /*+ */ 0 FROM "TRAININGPROG" "TP","CANDIDATE" "C","BU_ONLINE
32 _USERID_UPLOAD" "UP" WHERE :B1='N' AND "UP"."APPLNO"="C"."APPLNO" AND "C"."I
33 NTERMEDIARYID"=:B2 AND "TP"."TRGPROGID"=:B3 AND "TP"."CANCELFLAG"='N'))
34 4 - filter(:B1='N')
35 7 - filter("TP"."CANCELFLAG"='N')
36 8 - access("TP"."TRGPROGID"=:B1)
37 10 - access("C"."INTERMEDIARYID"=:B1)
38 11 - access("UP"."APPLNO"="C"."APPLNO")
39 12 - filter(:B1='N')
40 15 - filter("TP"."CANCELFLAG"='N')
41 16 - access("TP"."TRGPROGID"=:B1)
42 18 - access("C"."INTERMEDIARYID"=:B1)
43 19 - filter("UP"."FLAG"='F')
44 20 - access("UP"."APPLNO"="C"."APPLNO")
Please suggest something.
Thanks for looking into this,
Mahi
CM: Fixed formatting of query to make it more readable. Please do so yourself next time.
[Updated on: Fri, 05 March 2010 05:36] by Moderator Report message to a moderator
|
|
|
|
Re: Update statement resulting time out [message #446048 is a reply to message #446037] |
Fri, 05 March 2010 05:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
virmahi wrote on Fri, 05 March 2010 11:18
UPDATE TRAININGINFO TI
SET TI.ONLINEUSERID = ( SELECT UP.USER_ID
FROM BU_ONLINE_USERID_UPLOAD UP,
CANDIDATE C, TRAININGPROG TP
WHERE UP.APPLNO = C.APPLNO
AND UP.FLAG='F'
AND C.INTERMEDIARYID = TI.INTERMEDIARYID
AND TI.SWITCHFLAG = 'N'
AND TI.TRGPROGID = TP.TRGPROGID
AND TP.CANCELFLAG = 'N' )
WHERE EXISTS (SELECT 1
FROM BU_ONLINE_USERID_UPLOAD UP,
CANDIDATE C,
TRAININGPROG TP
WHERE UP.APPLNO = C.APPLNO
AND C.INTERMEDIARYID = TI.INTERMEDIARYID
AND TI.SWITCHFLAG = 'N'
AND TI.TRGPROGID = TP.TRGPROGID
AND TP.CANCELFLAG = 'N');
The condition UP.FLAG='F' is missing in "where exists" condition because the updation has to be done for those rows only where this condition is true.
Not sure what you mean by that but what it's going to do is set ONLINEUSERID to null for any row that matches the exist but has
UP.FLAG set to anything other than 'F'. Are you sure that's what you want?
Also I assume this new explain plan comes from the 9i DB, correct? Please state these things, there's been enough confusion on this thread already.
|
|
|
Re: Update statement resulting time out [message #446049 is a reply to message #445806] |
Fri, 05 March 2010 05:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Try this:
UPDATE (SELECT TI.ONLINEUSERID old_id, UP.USER_ID new_id
FROM BU_ONLINE_USERID_UPLOAD UP,
CANDIDATE C, TRAININGPROG TP
WHERE UP.APPLNO = C.APPLNO
AND UP.FLAG='F'
AND C.INTERMEDIARYID = TI.INTERMEDIARYID
AND TI.SWITCHFLAG = 'N'
AND TI.TRGPROGID = TP.TRGPROGID
AND TP.CANCELFLAG = 'N')
SET new_id = old_id;
|
|
|
|
Re: Update statement resulting time out [message #446054 is a reply to message #446049] |
Fri, 05 March 2010 06:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
I added table TRAININGINFO TI into the given query :-
UPDATE (SELECT TI.ONLINEUSERID old_id, UP.USER_ID new_id
FROM BU_ONLINE_USERID_UPLOAD UP,
CANDIDATE C, TRAININGPROG TP,TRAININGINFO TI
WHERE UP.APPLNO = C.APPLNO
AND UP.FLAG='F'
AND C.INTERMEDIARYID = TI.INTERMEDIARYID
AND TI.SWITCHFLAG = 'N'
AND TI.TRGPROGID = TP.TRGPROGID
AND TP.CANCELFLAG = 'N')
SET new_id = old_id;
And got the error :- ORA-01779: cannot modify a column which maps to a non key-preserved table
|
|
|
|
Re: Update statement resulting time out [message #446079 is a reply to message #446071] |
Fri, 05 March 2010 06:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Thanks cookiemonster,
I am looking into the link. I cannot use the query given by JRowBottom because that is using NVL function to remove the where exist clause. But since I have to use the where exists clause..... can't use that query.
|
|
|
Re: Update statement resulting time out [message #446088 is a reply to message #446079] |
Fri, 05 March 2010 07:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
I tried below query :-
merge into traininginfo t1
using ( SELECT up.user_id new_id, ti.intermediaryid, ti.trgprogid
FROM bu_online_userid_upload UP, candidate c, trainingprog tp, traininginfo ti
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N' ) new
on ( t1.intermediaryid = new.intermediaryid
AND t1.trgprogid = new.trgprogid )
when matched then UPDATE SET t1.onlineuserid = new.new_id
when not matched then insert (INTERMEDIARYID) values ( null );
explain plan :-
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
-----------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2179T| 77P| | 600G|
| 1 | MERGE | TRAININGINFO | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 44948 | 2940K| 2200K| 2265 |
|* 4 | HASH JOIN | | 44948 | 1667K| | 1261 |
|* 5 | TABLE ACCESS FULL | TRAININGPROG | 42573 | 207K| | 67 |
|* 6 | HASH JOIN | | 72378 | 2332K| | 1152 |
|* 7 | TABLE ACCESS FULL | BU_ONLINE_USERID_UPLOAD | 539 | 7007 | | 2 |
|* 8 | HASH JOIN | | 1104K| 21M| 11M| 1141 |
|* 9 | TABLE ACCESS FULL | TRAININGINFO | 539K| 5267K| | 297 |
| 10 | INDEX FAST FULL SCAN| INDEX_APPLNODATE | 1089K| 10M| | 299 |
| 11 | TABLE ACCESS FULL | TRAININGINFO | 1078K| 29M| | 297 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."TRGPROGID"(+)="TI"."TRGPROGID" AND "T1"."INTERMEDIARYID"(+)="TI"."INTERM
EDIARYID")
4 - access("TI"."TRGPROGID"="TP"."TRGPROGID")
5 - filter("TP"."CANCELFLAG"='N')
6 - access("UP"."APPLNO"="C"."APPLNO")
7 - filter("UP"."FLAG"='F')
8 - access("C"."INTERMEDIARYID"="TI"."INTERMEDIARYID")
9 - filter("TI"."SWITCHFLAG"='N')
This has taken little less time than others. Please advice if this query won't have any other side effects ( I mean it would work logically well)
|
|
|
|
|
Re: Update statement resulting time out [message #446100 is a reply to message #445806] |
Fri, 05 March 2010 07:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The whole point of the nvl is to ensure that any records that don't match the sub-query get updated to the value they were before. So they won't get set to null they'll be left unchanged.
That said, if you've got any update triggers on the table that might cause problems if you're updating rows you don't need to, even if you're settting the values to exactly what they were before.
|
|
|
Re: Update statement resulting time out [message #446459 is a reply to message #446100] |
Mon, 08 March 2010 09:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Thinking about it, if you don't mind a two step process, and the total number of rows to be updated is fairly small, this might work:
STEP 1:CREATE TABLE temp_update AS
SELECT UP.user_id1
,ti.intermediaryid
,ti.trgprogid
FROM bu_online_userid_upload UP
,candidate c
,trainingprog tp
,traininginfo ti
WHERE UP.applno = c.applno
AND UP.flag = 'F'
AND c.intermediaryid = ti.intermediaryid
AND ti.switchflag = 'N'
AND ti.trgprogid = tp.trgprogid
AND tp.cancelflag = 'N'
CREATE UNIQUE INDEX temp_update_idx on temp_update (intermediaryid ,trgprogid,user_id1);
Step 2:
UPDATE traininginfo ti
SET onlineuserid = (SELECT tu.user_id1
FROM temp_update tu
WHERE tu.intermediaryid = ti.intermediaryid
AND ti.trgprogid = tu.trgprogid)
WHERE (ti.intermediaryid,ti.trgprogid) IN (
SELECT ti.intermediaryid
,ti.trgprogid
FROM temp_update)
This should (hopefuly) use indexed access to the driving table for the update.
|
|
|
Re: Update statement resulting time out [message #446465 is a reply to message #445806] |
Mon, 08 March 2010 10:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'd like to hope that the unique index can be:
CREATE UNIQUE INDEX temp_update_idx on temp_update (intermediaryid ,trgprogid);
If you need user_id for uniqueness then the subsequent update is going to fail with a "single row sub-query returned too many rows" error.
Assuming the key is what I suggest then step 2 can be re-written as an update join view which should be faster still:
UPDATE (SELECT ti.onlineuserid old_val, tu.user_id new_val
FROM traininginfo ti, temp_update tu
WHERE tu.intermediaryid = ti.intermediaryid
AND tu.trgprogid = ti.trgprogid)
SET old_val = new_val;
Of course with this solution there is small possibility of hitting read consistency issues if the data in the tables changes between creating the temp_update table and running the update.
|
|
|
Re: Update statement resulting time out [message #446737 is a reply to message #446465] |
Wed, 10 March 2010 03:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I added the user_id into the index so it could do an index-only access to the table.
The read-consistency is a potential problem - possibly it's worth locking the affected rows at the start of the process.
|
|
|
|