sql - Oracle 查询中避免更新字段的最佳方法是什么(如果未更改)？

Question

我只是想知道我可以在 Oracle 查询中使用的最佳方法，以避免更新未更改的字段？

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

在性能方面，如果 xfield1 的现有值为“xxx”，则不应调用此更新的最佳方式是什么。

选项 1:

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

选项 2:

• Invoke update as below:

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

请让我知道以上两种方法中哪一种是最佳和理想的方法，或者是否有其他理想的方法可以使用？

感谢您的帮助

Answer 1

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

过滤谓词 在进行更新之前应用。因此，我会选择选项 2，让 Oracle 为您完成这项工作，而不是手动执行以首先过滤掉行。此外，在两个不同的步骤中执行此操作将是一项开销。行的过滤应该是同一步骤的一部分。

关于性能，我认为索引会发挥重要作用。

你可以测试一下看看:

没有索引

选项 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>

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

有索引

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

Index created.

选项 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>

选项 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>

因此，在所有情况下，在选项 2 中，应用过滤器("SAL"<>9999)。