Search

Create table for Exchange With clause on a Partitioned Table in Oracle Database 12c Release 2 (12.2)


With the advent of 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.


CREATE TABLE EMP_PART ( 
   EMPNO    NUMBER      NOT NULL
  ,ENAME    VARCHAR2(11) NOT NULL
  ,JOB      VARCHAR2(10) NOT NULL
  ,MGR      NUMBER
  ,HIREDATE DATE              NOT NULL
  ,SAL      NUMBER
  ,COMM     NUMBER
  ,DEPTNO   NUMBER       NOT NULL
  ,CONSTRAINT chk_empno CHECK (empno between 1 and 208)
) 
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 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 employee_id ,Last_name ,job_id, manager_id,HIRE_DATE, Salary, commission_pct, department_id
 from employees WHERE department_id != 20;
 
 COMMIT;



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 TABLE emp_part_exchange
  FOR EXCHANGE WITH TABLE emp_part;

-- ==============================================================================================================================================INSERT INTO emp_part_exchange(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT employee_id ,Last_name ,job_id, manager_id,HIRE_DATE, Salary, commission_pct, department_id
 from employees WHERE department_id = 20;
 
COMMIT;


Exchange Partition




ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 
-----------------------------------------------------------------------
We would get a check constraint mismatch to avoid this some manual work is required
-----------------------------------------------------------------------
  
   ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT chk_empno_ep 
   CHECK (empno between 1 and 208);
-----------------------------------------------------------------------

ALTER TABLE emp_part_exchange 
   ADD CONSTRAINT emp_part_pk_ep
   PRIMARY KEY (empno);
   
---------------------------------------------------------------------



Retry it now, it should work.


  ALTER TABLE emp_part 
   EXCHANGE PARTITION p_research 
   WITH TABLE emp_part_exchange; 
-----------------------------------------------------------------------
 Check the output
-----------------------------------------------------------------------
  select * from emp_part;
 

Online conversion of non partitioned table to non-partitioned table


ORACLE 12.2 allows the conversion of a non partitioned table to a partitioned table using the ALTER TABLE command. The whole conversion can be done “online” meaning, that during the conversion DML operations against the table are allowed.




CREATE TABLE emp_dept
AS
SELECT e.employee_id ,e.last_name ,e.job_id ,e.manager_id, e.department_id
      ,e.hire_date ,e.salary  ,d.department_name
 FROM employees e
  JOIN departments d  ON (e.department_id = d.department_id);

  ---------------------------------------------------------------------
  

ALTER TABLE MODIFY




ALTER TABLE emp_dept MODIFY
   PARTITION BY LIST (department_id) (
      PARTITION P_DEPT10 VALUES (10)
      ,PARTITION P_DEPT20 VALUES (20)
      ,PARTITION P_DEPT30 VALUES (30)
      ,PARTITION P_DEPT40 VALUES (40)
      ,PARTITION P_DEFAULT VALUES (DEFAULT)
   ) 






source: https://oracle-base.com/articles/12c/create-table-for-exchange-with-table-12cr2#the-problem



4 views
 
  • LinkedIn
  • Facebook