OraFAQ Forum: Performance Tuning » Update statement resulting time out

Home » RDBMS Server » Performance Tuning » Update statement resulting time out (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Update statement resulting time out [message #445806]

Thu, 04 March 2010 04:04

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 #445811 is a reply to message #445806]

Thu, 04 March 2010 04:10

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Before we worry about performance are you sure that update is correct? I would have thought both sub-queries should have the same where clause but the 2nd one doesn't have this line:

AND UP.flag = 'F'

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

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

cookiemonster
Messages: 13958
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?

Report message to a moderator

Re: Update statement resulting time out [message #445822 is a reply to message #445815]

Thu, 04 March 2010 04:39

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);

Report message to a moderator

Re: Update statement resulting time out [message #445825 is a reply to message #445820]

Thu, 04 March 2010 04:47

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

Report message to a moderator

Re: Update statement resulting time out [message #445826 is a reply to message #445822]

Thu, 04 March 2010 04:52

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.

Report message to a moderator

Re: Update statement resulting time out [message #445831 is a reply to message #445826]

Thu, 04 March 2010 05:14

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

Attachment: explain_plan_toad.jpg
(Size: 48.78KB, Downloaded 1030 times)

[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

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Update statement resulting time out [message #445837 is a reply to message #445833]

Thu, 04 March 2010 05:35

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

Report message to a moderator

Re: Update statement resulting time out [message #445839 is a reply to message #445837]

Thu, 04 March 2010 05:43

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

Report message to a moderator

Re: Update statement resulting time out [message #445840 is a reply to message #445806]

Thu, 04 March 2010 05:44

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Well that looks a lot better than the original, how long does it take now?

You could also try adding an index on BU_ONLINE_USERID_UPLOAD (applno, flag)

Report message to a moderator

Re: Update statement resulting time out [message #445842 is a reply to message #445840]

Thu, 04 March 2010 05:46

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 #445843 is a reply to message #445806]

Thu, 04 March 2010 05:49

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Difficult to tell. Try running both and see what the real times are.

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

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

Report message to a moderator

Re: Update statement resulting time out [message #445846 is a reply to message #445806]

Thu, 04 March 2010 06:14

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Update statement resulting time out [message #445848 is a reply to message #445846]

Thu, 04 March 2010 06:23

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...

Report message to a moderator

Re: Update statement resulting time out [message #445855 is a reply to message #445806]

Thu, 04 March 2010 06:38

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Update statement resulting time out [message #446012 is a reply to message #445833]

Fri, 05 March 2010 02:47

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 #446031 is a reply to message #445806]

Fri, 05 March 2010 04:16

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

What oracle version are you using?

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

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.

Report message to a moderator

Re: Update statement resulting time out [message #446034 is a reply to message #445806]

Fri, 05 March 2010 05:00

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Update statement resulting time out [message #446037 is a reply to message #446034]

Fri, 05 March 2010 05:18

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 #446038 is a reply to message #446037]

Fri, 05 March 2010 05:28

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

Please find the screenshot of the explain with cardinalities from Toad.

Attachment: explain_plan.jpg
(Size: 76.69KB, Downloaded 1012 times)

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

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Update statement resulting time out [message #446049 is a reply to message #445806]

Fri, 05 March 2010 05:47

cookiemonster
Messages: 13958
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;

Report message to a moderator

Re: Update statement resulting time out [message #446050 is a reply to message #445806]

Fri, 05 March 2010 05:49

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Oh and the TOAD explain was pointless.
Card in TOAD plan = Rows in sqlplus plan.
So we already had that information.
Just use sqlplus, it's easier for us to read.

Report message to a moderator

Re: Update statement resulting time out [message #446054 is a reply to message #446049]

Fri, 05 March 2010 06:06

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

Report message to a moderator

Re: Update statement resulting time out [message #446071 is a reply to message #445806]

Fri, 05 March 2010 06:28

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

I was afraid that might happen.
Have a read of this thread:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:273215737113

It contains lots of details on that error and how you might get around it along with some stuff on using merge in 9i that might be helpful.

If none of that works then JRowbottoms query up near the top of the thread might be the best you can get unless your client upgrades to 10g.

Report message to a moderator

Re: Update statement resulting time out [message #446079 is a reply to message #446071]

Fri, 05 March 2010 06:41

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.

Report message to a moderator

Re: Update statement resulting time out [message #446088 is a reply to message #446079]

Fri, 05 March 2010 07:12

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)

Report message to a moderator

Re: Update statement resulting time out [message #446090 is a reply to message #445806]

Fri, 05 March 2010 07:14

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why do you need the where exists?

Report message to a moderator

Re: Update statement resulting time out [message #446092 is a reply to message #446090]

Fri, 05 March 2010 07:24

virmahi
Messages: 266
Registered: April 2008
Location: India

Senior Member

To update only those records which matches the conditions and all records do not get updated with NULL value.

Report message to a moderator

Re: Update statement resulting time out [message #446100 is a reply to message #445806]

Fri, 05 March 2010 07:54

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Update statement resulting time out [message #446459 is a reply to message #446100]

Mon, 08 March 2010 09:57

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.

Report message to a moderator

Re: Update statement resulting time out [message #446465 is a reply to message #445806]

Mon, 08 March 2010 10:43

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Update statement resulting time out [message #446737 is a reply to message #446465]

Wed, 10 March 2010 03:54

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.

Report message to a moderator

Re: Update statement resulting time out [message #446744 is a reply to message #445806]

Wed, 10 March 2010 04:24

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

That makes sense.

Since the OP only wants to update a handfull of rows I suspect the performance difference between yours and mine will be negligable.

Report message to a moderator

Previous Topic:	Explicit cursor data
Next Topic:	Lot of Trace files under udump,bdump with error: Ioctl ASYNC_CONFIG error, errno = 1

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 14:16:10 CST 2024