exchange partition global index

jnrjian

已于 2023-08-30 20:41:22 修改

阅读量239

点赞数

文章标签： oracle

于 2023-08-10 21:09:54 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/132218675

版权

EXCHANGE PARTITION with a Table having a UNIQUE INDEX and PK Constraint. (Doc ID 1620636.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

Running the following EXCHANGE PARTITION results in an ora-14098:

alter table gps exchange partition GPS_DATA_2013_11_24
with table gps_partition_temp
INCLUDING INDEXES WITHOUT VALIDATION; SQL> 2 3
with table gps_partition_temp
*
ERROR at line 2:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

CHANGES

CAUSE

Below is the Failing testcase :

The test Environment was Oracle RDBMS Database 11.2.0.3

ALTER TABLE GPS
DROP PRIMARY KEY CASCADE;

DROP TABLE GPS CASCADE CONSTRAINTS;

CREATE TABLE GPS
(
GPS_GUID            RAW(16)                   DEFAULT (sys_guid())          NOT NULL,
DVC_ID              NUMBER(12)                NOT NULL,
USR_ID              NUMBER(12)                NOT NULL,
ENTRY_UTC           TIMESTAMP(6)              DEFAULT (SYS_EXTRACT_UTC(SYSTIMESTAMP)) NOT NULL,
COVERAGE_STAT_ID    NUMBER(12)                NOT NULL,
SPEED               FLOAT(126)                DEFAULT (0),
DIRECTION           FLOAT(126)                DEFAULT (0),
ESTIMATED_ACCURACY NUMBER(12),
UPDATE_UTC          TIMESTAMP(6)              DEFAULT (SYS_EXTRACT_UTC(SYSTIMESTAMP)) NOT NULL,
LATITUDE            FLOAT(126),
LONGITUDE           FLOAT(126),
TS_GUID             RAW(16),
CLUSTER_ID          VARCHAR2(50 BYTE),
GEOCODE_UTC         TIMESTAMP(6),
STREET              NVARCHAR2(100),
SUITE               VARCHAR2(50 BYTE),
CITY                NVARCHAR2(100),
STATE_PROVINCE      NVARCHAR2(100),
POSTAL_CODE         NVARCHAR2(20),
COUNTRY             NVARCHAR2(100)
)
TABLESPACE USERS
PARTITION BY RANGE (ENTRY_UTC)
( PARTITION GPS_DATA_2013_11_10 VALUES LESS THAN (TIMESTAMP' 2013-11-10 00:00:00')
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS,
PARTITION GPS_DATA_2013_11_17 VALUES LESS THAN (TIMESTAMP' 2013-11-17 00:00:00')
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS,
PARTITION GPS_DATA_2013_11_24 VALUES LESS THAN (TIMESTAMP' 2013-11-24 00:00:00')
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS
)
ENABLE ROW MOVEMENT;

CREATE UNIQUE INDEX CX_GPS ON GPS
(USR_ID, ENTRY_UTC)
TABLESPACE USERS
LOCAL (
PARTITION GPS_DATA_2013_11_10
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS,
PARTITION GPS_DATA_2013_11_17
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS,
PARTITION GPS_DATA_2013_11_24
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS)
NOPARALLEL;

CREATE UNIQUE INDEX PK_GPS ON GPS
(GPS_GUID)
LOGGING
TABLESPACE USERS
NOPARALLEL;

ALTER TABLE GPS ADD (
CONSTRAINT PK_GPS
PRIMARY KEY
(GPS_GUID)
USING INDEX PK_GPS);

Testcase to reproduce the issue, with 1 row.

insert 1 row

Insert into GPS
(GPS_GUID, DVC_ID, USR_ID, ENTRY_UTC, COVERAGE_STAT_ID, DIRECTION, ESTIMATED_ACCURACY, UPDATE_UTC, LATITUDE, LONGITUDE)
Values
('8A1694614246012B0142635BDB1576E0', 8087975, 8321547, TO_TIMESTAMP('11/17/2013 12:00:25.471000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 1,
-1, 0, TO_TIMESTAMP('11/17/2013 12:00:33.557980 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 0, 0);
COMMIT;

DROP TABLE gps_partition_temp;

CREATE TABLE gps_partition_temp TABLESPACE users PCTFREE 0
INITRANS 10 MAXTRANS 255 STORAGE( INITIAL 1 m Next 1 m MINEXTENTS 1
MAXEXTENTS UNLIMITED ) AS SELECT * FROM gps WHERE 1=2;

INSERT /*+ APPEND */ INTO gps_partition_temp
select a.* from gps partition(GPS_DATA_2013_11_24) a where rownum < 100;

COMMIT;

CREATE UNIQUE INDEX gps_partemp_gpsguid ON gps_partition_temp (gps_guid) COMPUTE STATISTICS;

ALTER TABLE gps_partition_temp ADD CONSTRAINT pk_gps_partition_temp PRIMARY KEY
(gps_guid) DISABLE VALIDATE;

alter table gps exchange partition GPS_DATA_2013_11_24
with table gps_partition_temp
INCLUDING INDEXES WITHOUT VALIDATION;

Result:
========

alter table gps exchange partition GPS_DATA_2013_11_24
with table gps_partition_temp
INCLUDING INDEXES WITHOUT VALIDATION; SQL> 2 3
with table gps_partition_temp
*
ERROR at line 2:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

CONCLUSION
-----------
This issue arises due to the UNIQUE INDEX defined for the PK CONSTRAINT.

The issue is documented in the DataWarehousing Guide.

Chapter 16 Maintaining the Data Warehouse
"
If the partitioned table has a primary or unique key that is enforced with a global index structure,
ensure that the constraint on sales_pk_jan01 is validated without the creation of an index structure, as in
the following:

ALTER TABLE sales_01_2001 ADD CONSTRAINT sales_pk_jan01
PRIMARY KEY (sales_transaction_id) DISABLE VALIDATE;

The creation of the constraint with ENABLE clause would cause the creation of a unique index, which does not
match a local index structure of the partitioned table. You must not have any index structure built on the
nonpartitioned table to be exchanged for existing global indexes of the partitioned table. The exchange command
would fail.
"

SOLUTION

ALTER TABLE GPS
DROP PRIMARY KEY CASCADE;

DROP TABLE GPS CASCADE CONSTRAINTS;

CREATE INDEX PK_GPS ON GPS                      --   The UNIQUE option can be removed here.
(GPS_GUID)                                       --   The PK defined below will provide the uniqueness
LOGGING
TABLESPACE USERS
NOPARALLEL;

ALTER TABLE GPS ADD (
CONSTRAINT PK_GPS
PRIMARY KEY
(GPS_GUID)
USING INDEX PK_GPS);

CREATE UNIQUE INDEX CX_GPS ON GPS
(USR_ID, ENTRY_UTC)
TABLESPACE USERS
LOCAL (
PARTITION GPS_DATA_2013_11_10
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS,
PARTITION GPS_DATA_2013_11_17
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS,
PARTITION GPS_DATA_2013_11_24
   LOGGING
   NOCOMPRESS
   TABLESPACE USERS)
NOPARALLEL;

Testcase to resolve the issue, with 1 row.

DROP TABLE gps_partition_temp;

CREATE TABLE gps_partition_temp TABLESPACE users AS SELECT * FROM gps WHERE 1=2;

INSERT /*+ APPEND */ INTO gps_partition_temp
select a.* from gps partition(GPS_DATA_2013_11_24) a where rownum < 100;

COMMIT;

ALTER TABLE gps_partition_temp ADD CONSTRAINT pk_gps_partition_temp PRIMARY KEY
(gps_guid) DISABLE VALIDATE;

CREATE UNIQUE INDEX CX_GPS_partition_temp ON GPS_partition_temp
(USR_ID, ENTRY_UTC)
TABLESPACE USERS NOPARALLEL;

alter table gps exchange partition GPS_DATA_2013_11_24
with table gps_partition_temp
INCLUDING INDEXES WITHOUT VALIDATION;

( note DISABLE VALIDATE and WITHOUT VALIDATION; are required)

THIS WORKS

disable validate 不能插数据有啥用

SQL>
SQL> INSERT /*+ APPEND */ INTO gps
2 select a.* from gps partition(GPS_DATA_2013_11_24) a where rownum < 100;
INSERT /*+ APPEND */ INTO gps
select a.* from gps partition(GPS_DATA_2013_11_24) a where rownum < 100

ORA-26026: 唯一的索引 SYSTEM.PK_GPS 最初处于无法使用的状态

SQL>