Partition Exchange PK UK 导致分区交换慢
Applies to:
Oracle Database - Enterprise Edition - Version 19.6.0.0.0 and later
Information in this document applies to any platform.
Symptoms
Partition Exchange WITHOUT VALIDATION is running for a long time and not completing. There are no global indexes on the partitioned table.
Changes
Cause
When you exchange partitions, logging attributes are preserved. You can optionally specify if local indexes are also to be exchanged with the INCLUDING INDEXES clause, and if rows are to be validated for proper mapping with the WITH VALIDATION clause.
Note:
When you specify WITHOUT VALIDATION for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation is performed as if WITH VALIDATION were specified to maintain the integrity of the constraints.
To avoid the overhead of this validation activity, issue the following statement for each constraint before performing the exchange partition operation:
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name KEEP INDEX
Enable the constraints after the exchange.
If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.
Solution
To avoid the overhead of this validation activity, issue the following statement for each constraint before performing the exchange partition operation:
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name KEEP INDEX
Enable the constraints after the exchange.
If you specify WITHOUT VALIDATION, then you must ensure that the data to be exchanged belongs in the partition you exchange.
Example:
The solution is to disable any constraints on both the tables and then re-enable it .
SQL> alter table TEST_EXCH modify constraint TEST_EXCH_PK disable;
Table altered.
Elapsed: 00:00:00.03
SQL> alter table TEST_DETAILS1_HIST modify constraint TEST_DETAILS1_HIST_PK disable;
Table altered.
Elapsed: 00:00:00.01
SQL> ALTER TABLE TEST_DETAILS1_HIST
2 EXCHANGE PARTITION P5237260000000005882 WITH TABLE TEST_EXCH
3 INCLUDING INDEXES
4 WITHOUT VALIDATION;
Table altered.
Elapsed: 00:00:00.17
SQL> alter table TEST_DETAILS1_HIST modify constraint TEST_DETAILS1_HIST_PK enable novalidate;
Table altered.
Elapsed: 00:00:00.01
SQL> alter table TEST_DETAILS1_HIST modify constraint TEST_DETAILS1_HIST_PK rely;
SQL> select index_name,PARTITION_NAME,status from dba_ind_partitions where index_owner='CORE' and partition_name='P5237260000000005882' and INDEX_NAME in (select distinct index_name from dba_part_indexes where table_name='TEST_DETAILS1_HIST');
INDEX_NAME PARTITION_NAME STATUS
-------------------------------- ----------------------------- --------
TEST_DETAILS1_HIST_PK P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_04 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_05 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_10 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_08 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_14 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_06 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_03 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_07 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_09 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_13 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_01 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_12 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_02 P5237260000000005882 USABLE
TEST_DETAILS1_HIST_S_IDX_11 P5237260000000005882 USABLE
----- PK
Purpose
This document explains why a full table scan is performed on the non-partitioned table during a partition exchange.
Details
Diagnostics Using 10046 Trace
----------------------------------
ALTER TABLE TPART EXCHANGE PARTITION P1 WITH TABLE NOPART;
PARSING IN CURSOR #2 len=95 dep=1 uid=0 oct=3 lid=0 tim=9791815726807 hv=483205816 ad='8d47aa68'
select 1 from "TEST"."NOPART" where TBL$OR$IDX$PART$NUM("TEST"."TPART",0,0,65535,"EMPNO") != :1
END OF STMT
PARSE #2:c=0,e=1497,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=0,tim=9791815726796
BINDS #2:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=1 size=24 offset=0
bfp=ffffffff7cc68078 bln=22 avl=02 flg=05
value=1
EXEC #2:c=0,e=742,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=9791815727824
FETCH #2:c=10000,e=14578,p=1,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=9791815742470
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=33551 op='TABLE ACCESS FULL NOPART '
Impact
--------
When data is exchanged from the partition to the non-partitioned exchange table (NOPART is empty), the exchange can be very fast.
However, when the data is being exchanged from the non-partitioned table into the partitioned table (NOPART contains data), a full table scan can significantly impact the performance when NOPART contains a lot of rows.
Answer
----------
As seen in the 10046 tracefile,we use TBL$OR$IDX$PART$NUM function to find which partition a particular row would belong to. When we are doing exchange of a partition with a table, we need to be sure that all the rows in the table would qualify for the partition with which the table is being exchanged. Hence we need to do a FTS of the table, using this function against each and every table row and confirming the rows eligibility to be a apart of the partition. Actual exchange partition is still a simple data dictionary operation involving no data movement. Initial exchange partition was faster because table had no rows.If you are sure that all table rows would fit into the partition, you can use 'WITHOUT VALIDATION' clause. this should avoid a FTS on the table.
But, if there is a primary key or unique key constraint on partitioned table, full scan will still be done (no matter we use "WITHOUT VALIDATION") in order to guarantee such constraint is not violated.
Conclusion
-----------------
Though exchange partition is just a data dictionary operation without actual data movement, the time taken for this operation depends upon the size of non-partitioned table.
When WITHOUT VALIDATION is specified for the exchange partition operation, this is normally a fast operation because it involves only data dictionary updates. However, if the table or partitioned table involved in the exchange operation has a primary key or unique constraint enabled, then the exchange operation will be performed as if WITH VALIDATION were specified in order to maintain the integrity of the constraints.
WITH VALIDATION 不是只看分区键对不对吗，怎么还看PK UK了.
原文地址：https://blog.csdn.net/jnrjian/article/details/143024806
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！