With ORACLE 12.2 the create table statement has been enriched by the new “for exchange with” clause. This enables us to create a exchange table that is a clone of the partitioned table which is the target of a later exchange table command
The new clause takes care of datatypes, data scale and precision, NOT NULL constraints, hidden column, virtual columns, unused columns, etc.
Indexes, constraints (other than NOT NULL) are not cloned.
Preparation:
-- =============================================================================
-- Create Partitioned Table
-- =============================================================================
CREATE TABLE EMP_PART (
EMPNO NUMBER(4,0) NOT NULL
,ENAME VARCHAR2(10 CHAR) NOT NULL
,JOB VARCHAR2(9 CHAR) NOT NULL
,MGR NUMBER(4,0)
,HIREDATE DATE NOT NULL
,SAL NUMBER(7,2)
,COMM NUMBER(7,2)
,DEPTNO NUMBER(2,0) NOT NULL
,CONSTRAINT chk_sal_comm_less_1000 CHECK (NVL(sal,0) + NVL(comm,0) < 10000)
,CONSTRAINT chk_empno CHECK (empno between 1000 and 9999)
)
PARTITION BY LIST (deptno) (
PARTITION p_accounting VALUES (10)
,PARTITION p_research VALUES (20)
,PARTITION p_sales VALUES (30)
,PARTITION p_default VALUES (DEFAULT)
);
-- =============================================================================
-- Create Local Index
-- =============================================================================
CREATE INDEX emp_part_idx ON EMP_PART (hiredate)
LOCAL (
PARTITION p_accounting_hiredate_idx
,PARTITION p_research_hiredate_idx
,PARTITION p_sales_hiredate_idx
,PARTITION p_default_hiredate_idx
);
-- =============================================================================
-- Add a Primary Key
-- =============================================================================
ALTER TABLE emp_part
ADD CONSTRAINT emp_part_pk
PRIMARY KEY (empno);
-- =============================================================================
-- Fill Data
-- =============================================================================
INSERT INTO emp_part (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE deptno != 20;
COMMIT;
-- =============================================================================
-- add a virtual column
-- =============================================================================
ALTER TABLE emp_part
ADD total_income GENERATED ALWAYS AS (NVL(SAL,0) + NVL(COMM,0));
-- =============================================================================
-- set a column to be hidden
-- =============================================================================
ALTER TABLE emp_part MODIFY deptno INVISIBLE;
-- =============================================================================
-- set a column unused
-- =============================================================================
ALTER TABLE emp_part SET UNUSED COLUMN job;
If we wanted to load the data of department 20 using an exchange partition command with an existing table holding all those rows we somehow had to make sure that the table used for the exchange has the exact same structure, indexes, constraints, etc. as the partitioned table. The new clause in the create table command help us solving this (at least some of the issues).
Create exchange table:
-- =============================================================================
-- Create exchange table
-- =============================================================================
CREATE TABLE emp_part_exchange
FOR EXCHANGE WITH TABLE emp_part;
-- =============================================================================
-- Fill exchange table w/o job as it is unused
-- =============================================================================
INSERT INTO emp_part_exchange(EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM emp
WHERE deptno = 20;
-- =============================================================================
-- Check the table
-- =============================================================================
SELECT column_name
,data_type ||
CASE data_type
WHEN 'VARCHAR2' THEN '(' || data_length || ')'
WHEN 'NUMBER' THEN NULLIF('(' || data_precision || ',' || data_scale || ')','(,)')
WHEN 'DATE' THEN NULL
END AS data_type
,nullable
,hidden_column
,virtual_column
FROM user_tab_cols
WHERE table_name = 'EMP_PART_EXCHANGE'
ORDER BY segment_column_id;
COLUMN_NAME DATA_TYPE NULL HIDDEN VIRTUAL
------------------------------ --------------- ---- ------- --------
EMPNO NUMBER(4,0) N NO NO
ENAME VARCHAR2(10) N NO NO
SYS_C00003_16123011:26:29$ VARCHAR2(9) Y YES NO
MGR NUMBER(4,0) Y NO NO
HIREDATE DATE N NO NO
SAL NUMBER (7,2) Y NO NO
COMM NUMBER (7,2) Y NO NO
DEPTNO NUMBER (2,0) N YES NO
TOTAL_INCOME NUMBER (,) Y NO YES
SELECT constraint_name, constraint_type, search_condition_vc
FROM user_constraints
WHERE table_name = 'EMP_PART_EXCHANGE';
CONSTRAINT_NAME TYPE SEARCH_CONDITION_VC
------------------------------ ---- --------------------------------------------------
SYS_C008877 C "DEPTNO" IS NOT NULL
SYS_C008878 C "EMPNO" IS NOT NULL
SYS_C008879 C "ENAME" IS NOT NULL
SYS_C008880 C "HIREDATE" IS NOT NULL
SELECT index_name, index_type, partitioned
FROM user_indexes
WHERE table_name = 'EMP_PART_EXCHANGE';
no rows selected
Exchange Partition?
So if we did an exchange partition at this point we would fail if the exchange table is not empty…
-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part
EXCHANGE PARTITION p_research
WITH TABLE emp_part_exchange;
Error starting at line : 1 in command -
ALTER TABLE emp_part
EXCHANGE PARTITION p_research
WITH TABLE emp_part_exchange
Error report -
SQL Error: ORA-14118: CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION
14118. 00000 - "CHECK constraint mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause: The corresponding columns in the tables specified in the
ALTER TABLE EXCHANGE PARTITION statement have CHECK constraint
defined on them.
*Action: Ensure that the two tables do not have CHECK constraint
defined on any column
Additional work to be done:
We still need to create constraints on the exchange table manually…
-- =============================================================================
-- Check constraint are not created but needed when exchanging the partition
-- =============================================================================
ALTER TABLE emp_part_exchange
ADD CONSTRAINT chk_sal_comm_less_1000_ep
CHECK (NVL(sal,0) + NVL(comm,0) < 10000);
ALTER TABLE emp_part_exchange
ADD CONSTRAINT chk_empno_ep
CHECK (empno between 1000 and 9999);
-- =============================================================================
-- Add primary key constraint as it is not created
-- =============================================================================
ALTER TABLE emp_part_exchange
ADD CONSTRAINT emp_part_pk_ep
PRIMARY KEY (empno);
After this is done the exchange partition is working.
-- =============================================================================
-- Exchange Partition
-- =============================================================================
ALTER TABLE emp_part
EXCHANGE PARTITION p_research
WITH TABLE emp_part_exchange;
Table EMP_PART altered.
Conclusion:
The create table for exchange clause is nice but if would be even better if constraints would also be created on the clone. Furthermore the possibility to do a CTAS in combination with the for exchange clause would be on my wishlist.
