need to tune this sql query to improve performance

Question

I have the below query. which is slowing down the performance because of the subquery in it. I tried a lot to add Join instead of Subquery. but in vain. Can anybody tell me how can I rewrite this query using JOIN?

update Table_1
set status = 'Status_2' 
where status ='status_1' and (col_1, col_2, col_3, nvl(col_4,0), col_5) in ( 
               select col_1, col_2, col_3, nvl(col_4,0), col_5 from Table_2 where status ='Status_0');

Please see the SELECT * FROM table(DBMS_XPLAN.Display); below

Plan hash value: 1290346170
------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT              |                      |     1 |   376 |   456   (3)| 00:00:06 |
|   1 |  UPDATE                       | Table_1              |       |       |            |          |
|   2 |   NESTED LOOPS                |                      |       |       |            |          |
|   3 |    NESTED LOOPS               |                      |     1 |   376 |   456   (3)| 00:00:06 |
|   4 |     SORT UNIQUE               |                      |     1 |   316 |   454   (3)| 00:00:06 |
|*  5 |      TABLE ACCESS FULL        | Table_2              |     1 |   316 |   454   (3)| 00:00:06 |
|*  6 |     INDEX RANGE SCAN          | Table1_INDEX         |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| Table_1              |     1 |    60 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Please run `EXPLAIN PLAN FOR your_query` then `SELECT * FROM table(DBMS_XPLAN.Display)`, then copy it's output and paste it to the question. — krokodilko, Jun 30 '15 at 06:04
@Msn The estimated time is just 6 seconds, which is quite fast. What is the actual time taken by the query? — Lalit Kumar B, Jun 30 '15 at 06:15
The query is looking for only 1 row in table2 and updates only 1 row in table1, What do you want to improve ? This plan is optimal. BTW you have not showed us a full explain plan, there is a predicate information which is coming after this table — krokodilko, Jun 30 '15 at 06:20
I think you can use [`EXISTS` instead of `IN`](http://stackoverflow.com/q/12896007/4519059) ;). — shA.t, Jun 30 '15 at 06:21
@LalitKumarB, kordirko, now I fired this query in the dev environment where the number of records in the table are less (below 5000). but the query is taking time in Production where table contain more than 6 Million records). — Shabeeralimsn, Jun 30 '15 at 06:43
can anbody suggest how to include JOIN in this query instead of Subquery ? — Shabeeralimsn, Jun 30 '15 at 06:44
@Msn How can you compare the explain plan of dev database with that of performance in production? Either post the explain plan of production or tell the actual execution time of dev.... two different databases, two different servers, two different settings/parameters/hardwares.......... — Lalit Kumar B, Jun 30 '15 at 06:48
@LalitKumarB, but if I could use JOIN instead of subquery I think I can imporove performance out there.... — Shabeeralimsn, Jun 30 '15 at 06:55

score 2 · Accepted Answer · answered Jun 30 '15 at 07:17

2

Does it work better when you do it like this?

update 
   (select Table_1.status
   from Table_1
      join Table_2 on 
             Table_1.col_1 = Table_2.col_1
         and Table_1.col_2 = Table_2.col_2
         and Table_1.col_3 = Table_2.col_3
         and nvl(Table_1.col_4, 0) = nvl(Table_2.col_4, 0)
         and Table_1.col_5 = Table_2.col_5
   where Table_1.status = 'status_1'
      and Table_2.status = 'Status_0')
set status = 'Status_2' ;

answered Jun 30 '15 at 07:17

Wernfried Domscheit

54,457
9
76
110

1

This is the right answer, but @Msn must note: if (col_1, col_2, col_3, col_5) does not contains the primary key of Table_1 and Table_2 then Oracle will throw an ORA-01779 error (key-preserved tables). – acesargl Jun 30 '15 at 07:36
@Wernfried, getting the error " QL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table". should I make composite key all these coumns? – Shabeeralimsn Jun 30 '15 at 08:28

score 0 · Answer 2 · answered Jun 30 '15 at 06:59

0

With a with statement and the Materialized hint you can preload the data of table_2 in a global temporary table. This might improve the performance of your query.

answered Jun 30 '15 at 06:59

hsstan

29
4

I can try. I'm on cellphone. With ASD as(select /*+materialized*/ col_1, col_2, col_3, nvl(col_4,0), col_5 from Table_2 where status ='Status_0') update Table_1 set status = 'Status_2' where status ='status_1' and (col_1, col_2, col_3, nvl(col_4,0), col_5) in ( select * from ASD) – hsstan Jun 30 '15 at 07:24
Using the undocumented materialize hint doesn't make sense unless the common table expression is used more than once. – Jon Heller Jul 01 '15 at 06:02

need to tune this sql query to improve performance

2 Answers2