sql - 如何优化在具有700M行的Oracle表上运行的更新SQL

Question

UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL

[TABLE]是具有超过7亿行的Oracle数据库表。运行6个小时后，我取消了SQL执行。

是否有任何SQL提示可以提高性能？或任何其他解决方案来加快速度？

编辑:此查询将运行一次，然后不再运行。

Answer 1

首先，它是一次性查询还是循环查询？如果只需要执行一次，则可能要考虑以并行模式运行查询。无论如何，您都将不得不扫描所有行，您可以使用ROWID(自己动手做并行性)范围自己划分工作负载，也可以使用Oracle内置功能。

假设您要经常运行它并希望优化此查询，则field列为NULL的行数最终将比总行数少。在这种情况下，索引可以加快处理速度。 Oracle不会将所有索引列都为NULL的行编入索引，因此查询不会使用field上的索引(因为您要查找field为NULL的所有行)。

任何一个:

在(FIELD, 0)上创建索引，0将充当非NULL伪列，并且所有行都将在表上建立索引。

在(CASE WHEN field IS NULL THEN 1 END)上创建基于函数的索引，这只会索引NULL的行(因此索引将非常紧凑)。在这种情况下，您将不得不重写查询:
UPDATE [TABLE] SET [FIELD]=0 WHERE (CASE WHEN field IS NULL THEN 1 END)=1

编辑:

由于这是一次性方案，因此您可能需要使用 PARALLEL提示:

SQL> EXPLAIN PLAN FOR
  2  UPDATE /*+ PARALLEL(test_table 4)*/ test_table
  3     SET field=0
  4   WHERE field IS NULL;

Explained

SQL> select * from table( dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4026746538
--------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |            | 22793 |   289K|    12   (9)| 00:00:
|   1 |  UPDATE               | TEST_TABLE |       |       |            |
|   2 |   PX COORDINATOR      |            |       |       |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000   | 22793 |   289K|    12   (9)| 00:00:
|   4 |     PX BLOCK ITERATOR |            | 22793 |   289K|    12   (9)| 00:00:
|*  5 |      TABLE ACCESS FULL| TEST_TABLE | 22793 |   289K|    12   (9)| 00:00:
--------------------------------------------------------------------------------