如何优化在具有700M行的Oracle表上运行的更新SQL [英] How to optimize an update SQL that runs on a Oracle table with 700M rows

问题描述

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

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

是否存在任何可以提高性能的SQL提示?还是任何其他加快速度的解决方案?

编辑:该查询将运行一次，然后再也不会运行.

解决方案

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

假定您要经常运行它并希望优化此查询，那么与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:
--------------------------------------------------------------------------------

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

[TABLE] is an Oracle database table with more than 700 million rows. I cancelled the SQL execution after it had been running for 6 hours.

Is there any SQL hint that could improve performance? Or any other solution to speed that up?

EDIT: This query will be run once and then never again.

解决方案

First of all is it a one-time query or is it a recurrent query ? If you only have to do it once you may want to look into running the query in parallel mode. You will have to scan all rows anyway, you could either divide the workload yourself with ranges of ROWID (do-it-yourself parallelism) or use Oracle built-in features.

Assuming you want to run it frequently and want to optimize this query, the number of rows with the field column as NULL will eventually be small compared to the total number of rows. In that case an index could speed things up. Oracle doesn't index rows that have all indexed columns as NULL so an index on field won't get used by your query (since you want to find all rows where field is NULL).

Either:

• create an index on (FIELD, 0), the 0 will act as a non-NULL pseudocolumn and all rows will be indexed on the table.

• create a function-based index on (CASE WHEN field IS NULL THEN 1 END), this will only index the rows that are NULLs (the index would therefore be very compact). In that case you would have to rewrite your query:

  UPDATE [TABLE] SET [FIELD]=0 WHERE (CASE WHEN field IS NULL THEN 1 END)=1

Edit:

Since this is a one-time scenario, you may want to use the PARALLEL hint:

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:
--------------------------------------------------------------------------------

这篇关于如何优化在具有700M行的Oracle表上运行的更新SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！