Tuesday, September 11, 2012

CTAS for partition tables / create table as select * from partition table

create table as select * from , for partition tables as below.

Here is Table DDL

CREATE TABLE CNS.COMM_CNS_dummy_data
(
  FILE_DT                         NUMBER(6),
  PARENT_HQ_NAME                  VARCHAR2(90 BYTE),
  PARENT_HQ_FIPS_COUNTRY_CODE     VARCHAR2(2 BYTE),
  PARENT_HQ_COUNTRY_CODE          VARCHAR2(3 BYTE),
  PARENT_HQ_STATE                 VARCHAR2(10 BYTE),
  HIERARCHY_CODE                  VARCHAR2(2 BYTE),
  DIAS_CODE                       VARCHAR2(9 BYTE),
  NUMBER_OF_FAMILY_MEMBERS        VARCHAR2(5 BYTE),
  FAMILY_UPDATE_DATE              VARCHAR2(8 BYTE),
  SIC1                            VARCHAR2(32 BYTE),
  SIC2                            VARCHAR2(32 BYTE),
  SIC3                            VARCHAR2(32 BYTE),
  SIC4                            VARCHAR2(32 BYTE),
  SIC5                            VARCHAR2(32 BYTE),
  SIC6                            VARCHAR2(32 BYTE),
  NATIONAL_IDENTIFICATION_NUMBER  VARCHAR2(20 BYTE),
  FILLER10                        VARCHAR2(6 BYTE),
  FILLER11                        VARCHAR2(6 BYTE),
  FIRST_EXECUTIVE_FIRST_NAME      VARCHAR2(13 BYTE),
  CENSUS_GEOCODE                  VARCHAR2(15 BYTE),
  CENSUS_GEOCODE_MATCH_FLAG       VARCHAR2(1 BYTE),
  CENSUS_2003_NECTA_DIV_FIPS      VARCHAR2(5 BYTE),
)
TABLESPACE CNS_DATA
PARTITION BY RANGE (FILE_DT)
(  
  PARTITION CNS_dummy_data_201105 VALUES LESS THAN (201106) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201106 VALUES LESS THAN (201107) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201107 VALUES LESS THAN (201108) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201108 VALUES LESS THAN (201109) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201109 VALUES LESS THAN (201110) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201110 VALUES LESS THAN (201111) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201111 VALUES LESS THAN (201112) TABLESPACE CNS_DATA,  
  PARTITION CNS_dummy_data_201112 VALUES LESS THAN (201201) TABLESPACE CNS_DATA,  
)
;


CTAS for above table will be

CREATE TABLE cns.COMM_cns_dummy_data_BK PARTITION BY RANGE (FILE_DT)
(
  PARTITION cns_dummy_data_201105 VALUES LESS THAN (201106),
  PARTITION cns_dummy_data_201106 VALUES LESS THAN (201107),
  PARTITION cns_dummy_data_201107 VALUES LESS THAN (201108),
  PARTITION cns_dummy_data_201108 VALUES LESS THAN (201109),
  PARTITION cns_dummy_data_201109 VALUES LESS THAN (201110),
  PARTITION cns_dummy_data_201110 VALUES LESS THAN (201111),
  PARTITION cns_dummy_data_201111 VALUES LESS THAN (201112),
  PARTITION cns_dummy_data_201112 VALUES LESS THAN (201201)
  )
  AS SELECT * FROM 
  cns.COMM_cns_dummy_data PARALLEL;

2 comments:

  1. Hi Jignesh, the syntax you gave above is not working. It throws this error:
    Error report:
    SQL Error: ORA-14020: this physical attribute may not be specified for a table partition
    14020. 00000 - "this physical attribute may not be specified for a table partition"
    *Cause: unexpected option was encountered while parsing physical
    attributes of a table partition; valid options for Range or
    Composite Range partitions are INITRANS, MAXTRANS, TABLESPACE,
    STORAGE, PCTFREE, and PCTUSED; only TABLESPACE may be specified
    for Hash partitions
    *Action: remove invalid option(s) from the list of physical attributes
    of a table partition
    *Comment: this error could have resulted from omission of a
    terminating (right) parenthesis following the list of
    partition descriptions

    I am able to use CTAS to create a table with one single partition for all the data and subsequently split it into multiple partitions as required.
    Please let me know how I can make your syntax work.

    ReplyDelete
  2. I think you implemented it wrong, but there is an error when I run it against v 12.1.0.2.0 The PARALLEL keyword should come after the right parenthesis following the partition list and why not specify the degree while you're at it:

    CREATE TABLE cns.COMM_cns_dummy_data_BK PARTITION BY RANGE (FILE_DT)
    (
    PARTITION cns_dummy_data_201105 VALUES LESS THAN (201106),
    PARTITION cns_dummy_data_201106 VALUES LESS THAN (201107),
    PARTITION cns_dummy_data_201107 VALUES LESS THAN (201108),
    PARTITION cns_dummy_data_201108 VALUES LESS THAN (201109),
    PARTITION cns_dummy_data_201109 VALUES LESS THAN (201110),
    PARTITION cns_dummy_data_201110 VALUES LESS THAN (201111),
    PARTITION cns_dummy_data_201111 VALUES LESS THAN (201112),
    PARTITION cns_dummy_data_201112 VALUES LESS THAN (201201)
    )
    parallel (degree 4)
    AS SELECT * FROM
    cns.COMM_cns_dummy_data;

    ReplyDelete