WL#5630: Allow WITHOUT VALIDATION on EXCHANGE PARTITION
Status: Complete
In WL#4445, EXCHANGE PARTITION WITH TABLE, the possibility to skip the row by row validation step (to validate that all rows matched the partition constrains) was allowed through the IGNORE option. This lead to bugs like BUG#55944 and IGNORE was not a good word since it violates WL#4103, see BUG#57708 where it should be removed. Therefore to allow the DBA to take responsibility that all rows would fit into the partition, this worklog is about adding [{WITH|WITHOUT} VALIDATION] as an option to skip the row-by-row validation step. This also means that the partitioning handler must: * be able to repair a partition that have rows which does not match the partition * avoid calling delete_row() calls to handlers that don't have the row in them (i.e. verify every read on the partition that the row is within the partition.)
FR1: Allow the added optional syntax WITH[OUT] VALIDATION so the full syntax should be: ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE t [{WITH|WITHOUT} VALIDATION] FR2: By using: ALTER TABLE partitioned_table EXCHANGE PARTITION part WITH TABLE non_partitioned_table WITHOUT VALIDATION no validation of the rows will be done (i.e. only a file level + meta data operation). And it will be possible to EXCHANGE a table into a partition which includes rows that does not match the partition criteria (still must match the row structure). FR3: By using: ALTER TABLE partitioned_table EXCHANGE PARTITION part WITH TABLE non_partitioned_table WITH VALIDATION or simply: ALTER TABLE partitioned_table EXCHANGE PARTITION part WITH TABLE non_partitioned_table Each row will be checked that it is allowed into the partition (same behavior as before this wl). FR4: Behavior for management/maintenance command should be the same as before: ALTER TABLE t ANALYZE PARTITION (done within each partition) should not be affected by misplaced rows (i.e. succeed if a row is placed in the wrong partition) ALTER TABLE t OPTIMIZE PARTITION should work as Analyze, i.e. not affected by misplaced rows. ALTER TABLE t CHECK PARTITION (first done on the partition by the underlying engine if supported, then a scan is done on the partition for searching for misplaced rows.) I.e. ALTER TABLE t CHECK PARTITIONshould find and report misplaced partitions ALTER TABLE t REPAIR PARTITION (same as check, but will move misplaced rows to the correct partition) ALTER TABLE t REBUILD PARTITION should fail (fixed in this WLs updated patch!) if there are misplaced rows in the rebuilt partitions that does not match any of the other rebuilt partitions. ALTER TABLE t REORGANIZE PARTITION should work as rebuild (i.e. fail if there is a misplaced row in the set of partitions that are reorganized and the misplaced rows does not match the new partitions).
Add reserved works: WITHOUT and VALIDATION Allow optionally {WITH|WITHOUT} VALIDATION after ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE t If WITHOUT VALIDATION is given, then skip the validation step in EXCHANGE PARTITION.
See RB#5467.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.