Does the number of columns updated affect the performance of update query in Oracle

Question

I have an Oracle update query like below

Query1 : UPDATE Table1 set col1=value1 where tablekey=123

Query2 : UPDATE Table1 set col1=value1,col2=value2 where tablekey=123

Will there be a performance difference between these two?

Answer 1

put below query on top of your update query,it will gives you time required for execution of queries and SQL Server parse and compile time.

SET STATISTICS TIME ON

Answer 2

Nothing much, except the number of bytes.

You could check the EXPLAIN PLAN:

SQL> EXPLAIN PLAN FOR UPDATE EMP SET ENAME='A' WHERE EMPNO=7369;

Explained.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1494045816

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

Predicate Information (identified by operation id):

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

   2 - filter("EMPNO"=7369)

14 rows selected.

SQL>
SQL> EXPLAIN PLAN FOR UPDATE EMP SET ENAME='A', DEPTNO=30 WHERE EMPNO=7369;

Explained.

SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1494045816

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

Predicate Information (identified by operation id):

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

   2 - filter("EMPNO"=7369)

14 rows selected.

For more detailed information, you could set autotrace on:

SQL> set autotrace on
SQL> UPDATE EMP SET ENAME='A' WHERE EMPNO=7369;

1 row updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1494045816

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMPNO"=7369)


Statistics
----------------------------------------------------------
          4  recursive calls
          1  db block gets
          3  consistent gets
          0  physical reads
        352  redo size
        857  bytes sent via SQL*Net to client
        842  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> UPDATE EMP SET ENAME='A', DEPTNO=30 WHERE EMPNO=7369;

1 row updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 1494045816

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("EMPNO"=7369)


Statistics
----------------------------------------------------------
          4  recursive calls
          1  db block gets
          3  consistent gets
          0  physical reads
        316  redo size
        858  bytes sent via SQL*Net to client
        853  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

So, you see a very slight difference for the bytes sent via SQL*Net to client and bytes received via SQL*Net from client. For more columns, the difference would increase slightly more.

Answer 3

In this examples, tables TT1 and TT2 both have same data in first update I'm updating only 1 column and in other update I'm updating 3 columns, (both tables don't have any triggers). The timings as you can see pretty much the same.

*The table contains just mock data.

SQL> UPDATE TT1 SET EMPNO=1234 WHERE EMPNO=7839 ;

65536 rows updated.

Elapsed: 00:00:06.65

SQL> UPDATE TT2 SET EMPNO=1234,SAL=1000,DEPTNO=15 WHERE EMPNO=7839 ;

65536 rows updated.

Elapsed: 00:00:05.76
SQL>