Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

need to tune this sql query to improve performance

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 |
------------------------------------------------------------------------------------------------------
like image 369
Shabeeralimsn Avatar asked Jun 30 '15 05:06

Shabeeralimsn


1 Answers

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' ;
like image 75
Wernfried Domscheit Avatar answered Nov 15 '22 00:11

Wernfried Domscheit