我在合并某些数据时遇到了问题。
我有两个表：
CREATE TABLE tmp_table
(
TROWID ROWID NOT NULL
, NEW_FK1 NUMBER(10)
, NEW_FK2 NUMBER(10)
, CONSTRAINT TMP_TABLE_PK_1 PRIMARY KEY
(
TROWID
)
ENABLE
)
CREATE UNIQUE INDEX TMP_TABLE_PK_1 ON tmp_table (TROWID ASC)
CREATE TABLE my_table
(
M_ID NUMBER(10) NOT NULL
, M_FK1 NUMBER(10)
, M_FK2 NUMBER(10)
, M_START_DATE DATE NOT NULL
, M_END_DATE DATE
, M_DELETED NUMBER(1) NOT NULL
, M_CHECK1 NUMBER(1) NOT NULL
, M_CHECK2 NUMBER(1) NOT NULL
, M_CHECK3 NUMBER(1)
, M_CREATION_DATE DATE
, M_CREATION_USER NUMBER(10)
, M_UPDATE_DATE DATE
, M_UPDATE_USER NUMBER(10)
, CONSTRAINT MY_TABLE_PK_1 PRIMARY KEY
(
M_ID
)
ENABLE
)
CREATE UNIQUE INDEX TMP_TABLE_PK_1 ON my_table (M_ID ASC)
CREATE INDEX TMP_TABLE_IX_1 ON my_table (M_UPDATE_DATE ASC, M_FK2 ASC)
CREATE INDEX TMP_TABLE_IX_2 ON my_table (M_FK1 ASC, M_FK2 ASC)
tmp_table是一个临时表，我只存储将在my_table更新的记录和信息。这意味着tmp_table。TROWID 是应合并my_table行的 rowid。
合并记录总数应为：94M，总my_table为540M。
查询：
MERGE /*+parallel*/ INTO my_table m
USING (SELECT /*+parallel*/ * FROM tmp_table) t
ON (m.rowid = t.TROWID)
WHEN MATCHED THEN
UPDATE SET m.M_FK1 = t.M_FK1 , m.M_FK2 = t.M_FK2 , m.M_UPDATE_DATE = trunc(sysdate)
, m.M_UPDATE_USER = 0 , m.M_CREATION_USER = 0
执行计划为：
Operation | Table | Estimated Rows |
MERGE STATEMENT | | |
- MERGE | my_table | |
-- PX CORDINATOR | | |
--- PX SENDER | | |
---- PX SEND QC (RANDOM) | | 95M |
----- VIEW | | |
------ HASH JOIN BUFFERED | | 95M |
------- PX RECEIVE | | 95M |
-------- PX SEND HASH | | 95M |
--------- PX BLOCK ITERATOR | | 95M |
---------- TABLE ACCESS FULL | tmp_table | 95M |
------- PX RECEIVE | | 540M |
-------- PX SEND HASH | | 540M |
--------- PX BLOCK ITERATOR | | 540M |
---------- TABLE ACCESS FULL | my_table | 540M |
在上面的计划中，最昂贵的操作是哈希连接缓冲。 对于我看到的两次完整扫描，不需要超过 5/6 分钟，而是在 2 小时后进行哈希连接，达到执行的 1%。
我不知道怎么需要那么多时间; 有什么建议吗？
编辑
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 94M| 9719M| | 3027K (2)| 10:05:29 |
| 1 | MERGE | my_table | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN | | 94M| 7109M| 3059M| 3027K (2)| 10:05:29 |
| 4 | TABLE ACCESS FULL| tmp_table | 94M| 1979M| | 100K (2)| 00:20:08 |
| 5 | TABLE ACCESS FULL| my_table | 630M| 33G| | 708K (3)| 02:21:48 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("tmp_table"."TROWID"="m".ROWID)
你可以做很多事情。请检查它们是否对您的情况有益，因为里程会有所不同。
1( 仅使用您触摸的目标表的列(通过选择或更新(：
MERGE
INTO (SELECT m_fk1, m_fk2, m_update_date, m_update_user, m_creation_user
FROM my_table) m
2( 仅使用所需的源表的列。在您的情况下，这是所有列，因此不会有任何好处：
MERGE
INTO (...) m
USING (SELECT trowid, new_fk1, new_fk2 FROM tmp_table) t
1( 和 2( 都将减小哈希联接所需的存储大小，并使优化程序能够对所有列(如果可用(使用索引。
3(在ROWIDs的特殊情况下，对源表进行排序似乎非常有益(至少在我的测试中(。如果对 rowid 进行排序，则可能会一起更新同一物理块中的行，这可能会提高性能：
MERGE
INTO (...) m
USING (SELECT ... FROM tmp_table ORDER BY trowid) t
4(由于您的源表非常大，我想它的表空间分布在几个数据文件中。您可以通过查询进行检查
SELECT f, count(*) FROM (
SELECT dbms_rowid.rowid_relative_fno(trowid) as f from tmp_table
) GROUP BY f ORDER BY f;
如果目标表使用多个数据文件，则可以尝试按数据文件对临时表进行分区：
CREATE TABLE tmp_table (
TROWID ROWID NOT NULL
, NEW_FK1 NUMBER(10)
, NEW_FK2 NUMBER(10)
, FNO NUMBER
) PARTITION BY RANGE(FNO) INTERVAL (1) (
PARTITION p0 VALUES LESS THAN (0)
);
您可以使用以下语句填充列FNO：
dbms_rowid.rowid_relative_fno(rowid)
现在，您可以逐个数据文件更新数据文件，从而减少哈希连接所需的内存。获取文件编号列表
SELECT DISTINCT fno FROM tmp_table;
14
15
16
17
并逐个文件运行更新文件：
MERGE
INTO (SELECT ... FROM my_table) m
USING (SELECT ... FROM tmp_table PARTITION FOR (14) ORDER BY trowid) t
和下一个PARTITION FOR (15)等。系统上的文件编号显然会有所不同。
5(最后，尝试使用嵌套循环而不是哈希连接。通常优化器会选择更好的连接计划，但我忍不住尝试一下：
MERGE /*+ USE_NL (m t) */
INTO (SELECT ... FROM my_table) m
USING (SELECT ... FROM tmp_table partition for (14) ORDER BY trowid) t
ON (m.rowid = t.TROWID)