时间戳

首页 > 解决方案 > 用 rownum 更新列的最快方法

sql - 用 rownum 更新列的最快方法

问题描述

我有一个表包含 3 个月的数据，每天包含大约 100,000,000 条记录。我想每天用一列（作为序列）中的 rownum 更新表。我尝试了两种最简单的方法，数据库序列（花了很长时间）和一个简单的更新语句：

update /*+ append parallel */ my_table
set my_row_num = rownum
WHERE  my_date = DATE'2019-11-11';

大约花了112分钟。

我想要更新表格的最快方法。

ps：该表按日期列划分，但与我用来更新的表不同，在我用来更新 的日期列上有一个索引。

标签： sqloracleoracle12c

解决方案

确保您的更新语句同时使用并行读取和并行写入 - 将append提示替换为enable_parallel_dml.

示例表和数据：

--drop table my_table;
create table my_table(my_row_num number, my_date date);
insert into my_table select 1, date '2019-11-11' from dual connect by level <= 100000;
begin
    dbms_stats.gather_table_stats(user, 'my_table');
end;
/
commit;

该append提示仅用于插入。虽然parallel提示适用于整个语句，但不一定适用于 DML 部分。除非您已经运行过alter session enable parallel dml;，否则您的更新不会完全并行化。在下面的执行计划中，并不是说第一个PX操作要等到两个操作之后才会发生UPDATE。

explain plan for
update /*+ append parallel */ my_table
set my_row_num = rownum
WHERE  my_date = DATE'2019-11-11';


select * from table(dbms_xplan.display);


Plan hash value: 1791278450
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT       |          |   100K|  1074K|    39   (3)| 00:00:01 |        |      |            |
|   1 |  UPDATE                | MY_TABLE |       |       |            |          |        |      |            |
|   2 |   COUNT                |          |       |       |            |          |        |      |            |
|   3 |    PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   4 |     PX SEND QC (RANDOM)| :TQ10000 |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
|   5 |      PX BLOCK ITERATOR |          |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| MY_TABLE |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,00 | PCWP |            |
----------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - filter("MY_DATE"=TO_DATE(' 2019-11-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
         U -  parallel
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - PDML is disabled in current session

但是使用新的 12c 提示enable_parallel_dml，第二个UPDATE是PX操作的子项。这意味着写入现在也将并行完成。

explain plan for
update /*+ enable_parallel_dml parallel */ my_table
set my_row_num = rownum
WHERE  my_date = DATE'2019-11-11';

select * from table(dbms_xplan.display);


Plan hash value: 3185872929
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                 |          |   100K|  1074K|    39   (3)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                  |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)            | :TQ10002 |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|   3 |    UPDATE                        | MY_TABLE |       |       |            |          |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                   |          |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10001 |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,01 | S->P | HASH (BLOCK|
|   6 |       BUFFER SORT                |          |   100K|  1074K|            |          |  Q1,01 | SCWP |            |
|   7 |        COUNT                     |          |       |       |            |          |  Q1,01 | SCWP |            |
|   8 |         PX RECEIVE               |          |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,01 | SCWP |            |
|   9 |          PX SEND 1 SLAVE         | :TQ10000 |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,00 | P->S | 1 SLAVE    |
|  10 |           PX BLOCK ITERATOR      |          |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,00 | PCWC |            |
|* 11 |            TABLE ACCESS FULL     | MY_TABLE |   100K|  1074K|    39   (3)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
  11 - filter("MY_DATE"=TO_DATE(' 2019-11-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
 
   0 -  STATEMENT
         U -  parallel
 
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2

但是完全调整并行语句是很困难的。您可能希望使用select dbms_sqltune.report_sql_monitor(sql_id => 'your SQL_ID') from dual来确保您的语句请求足够的并行会话（并行提示和配置是否足够好？），系统为其提供了足够的并行会话（是否有足够的并行会话可用？），以及语句能够使用并行会话（是否有一个序列化点，也许具有rownum阻止该语句完全使用并行性的功能？）。

推荐阅读