3

I just wanted to know the best way I can use in Oracle query, to avoid updating a field , if that is unchanged?

Update xtab1 set xfield1='xxx' where xkey='123';

In performance aspect what is best way , with which this update should not be invoked , if the existing value of xfield1 is 'xxx' .

Option1 :

  • step1:Invoke a SELECT to Fetch the value of xfield1
  • step2:If the above value is not 'xxx', then only invoke UPDATE

Option2 :

  • Invoke update as below:

    Update xtab1 set xfield1='xxx' where xkey='123' and xfield1 <> 'xxx'

Please let me know which of the above 2 is best and ideal way, or is there any other ideal approach to be used?

Appreciate your help

Smitha Nair
  • 65
  • 9

4 Answers4

3

Update xtab1 set xfield1='xxx' where xkey='123' and xfield1 <> 'xxx'

The filter predicate is applied before doing the update. So, I would go with option 2 and let Oracle do the job for you rather than doing it manually to first filter out the rows. Also, it would be an overhead to do it in two different steps. The filtering of rows should be a part of the same step.

Regarding the performance, I think indexes would play an important role.

You can test it and see:

Without index

Option 1

SQL> EXPLAIN PLAN FOR
  2  UPDATE t SET sal = 9999 WHERE deptno = 20;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 931696821

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |     5 |    35 |     3   (0)| 00:00:01 |
|   1 |  UPDATE            | T    |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     5 |    35 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=20)

14 rows selected.

SQL>

Option 2

SQL> EXPLAIN PLAN FOR
  2  UPDATE t SET sal = 9999 WHERE deptno = 20 AND sal<>9999;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 931696821

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      |     4 |    28 |     3   (0)| 00:00:01 |
|   1 |  UPDATE            | T    |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     4 |    28 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("DEPTNO"=20 AND "SAL"<>9999)

14 rows selected.

With Index

SQL> CREATE INDEX t_idx ON t(deptno,sal);

Index created.

Option 1

SQL> EXPLAIN PLAN FOR
  2  UPDATE t SET sal = 9999 WHERE deptno = 20;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1175576152

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |       |     5 |    35 |     1   (0)| 00:00:01 |
|   1 |  UPDATE           | T     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| T_IDX |     5 |    35 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("DEPTNO"=20)

14 rows selected.

SQL>

Option 2

SQL> EXPLAIN PLAN FOR
  2  UPDATE t SET sal = 9999 WHERE deptno = 20 AND sal<>9999;

Explained.

SQL>
SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1175576152

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |       |     4 |    28 |     1   (0)| 00:00:01 |
|   1 |  UPDATE           | T     |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| T_IDX |     4 |    28 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("DEPTNO"=20)
       filter("SAL"<>9999)

15 rows selected.

SQL>

So, in option 2 in all the cases, the filter("SAL"<>9999) is applied.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
2

I don't think there will be significant performance difference between the two options, as both will require looking up rows and performing comparison of the values. And I doubt other options such as pre-update triggers will yield better performance than your option 2.

If you really wanted to know how the Oracle optimizer handles your queries, try the EXPLAIN PLAN statement. For example, to see the plan that the Oracle optimizer formulated to execute your second option, try this:

EXPLAIN PLAN FOR
UPDATE xtab1 SET xfield1='xxx' 
WHERE xkey='123' AND xfield1 <> 'xxx'

There is more information about what the different columns of a EXPLAIN PLAN result means in this SO post.

Now, if you are dealing with large number of transactions, I recommend consider other options such as comparing the values at the application level, so as to avoid expensive database I/Os all together where possible :-) or use some form of ETL tools that are optimized to handle large transactions.

Community
  • 1
  • 1
ivan.sim
  • 8,972
  • 8
  • 47
  • 63
1

Where would you fetch the value? In some application?

I don't think there would be much difference between the two for smaller queries. For more complex ones, I would suggest to go with second choice to make Oracle optimize the query for you for best results.

cosmos
  • 2,263
  • 1
  • 17
  • 30
0

Thank you all.

I have chosen to go with the Option 2, even my DBA agrees to that as the better approach.

Smitha Nair
  • 65
  • 9