Wednesday, January 10, 2018

what will happen when you create partition table using CTAS?

I just did some testing on partition tables.

when you create a table from another tables using CTAS (create table as select * from  <table_name> where 1=3),it will create the table structure as same as source table,but when you create the same  for partition table,it wont create partition table,it will just create only NORMAL table.


sql>CREATE TABLE "user1".table1"
   (    "DAY_ID" NUMBER DEFAULT -1,
        "ZIPMASTER_ID" NUMBER DEFAULT -1,
        "CORP_ID" NUMBER DEFAULT -1,
        "ETHNIC_CODE_ID" NUMBER DEFAULT -1,
        "ECOHORT_CODE_ID" NUMBER DEFAULT -1,
        "FIOS_ID" NUMBER DEFAULT -1,
        "UVERSE_ID" NUMBER DEFAULT -1,
        "CC_INBOUND_CALLS" NUMBER DEFAULT -1,
        "DESTINATION_ID" NUMBER DEFAULT -1,
        "HOUSE_STATUS_CODE_ID" NUMBER DEFAULT -1,
        "LOAD_DATE" DATE DEFAULT sysdate,
        "SOURCE_SYSTEM_ID" NUMBER DEFAULT 1 NOT NULL ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "tablespace_data"
  partition by range (day_id)  (PARTITION "calls201401" VALUES LESS THAN (20140201)
  SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255
  COMPRESS FOR OLTP LOGGING
  STORAGE( MAXSIZE UNLIMITED
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "tablespace_data");

table created;

check if this table belongs to partitions:

select table_name  from dba_tab_partitions where table_name='TABLE1';

TABLE1


create table  "user1".table_part" from  "user1".table1 where 1=3;

Table created

check if the newly created table belongs to partition


select table_name  from dba_tab_partitions where table_name='TABLE1';

NO ROWS selectd

select table_name from dba_tables where table_name='TABLE1';

TABLE1

from the above testing ,we can conclude this cloning the structure of the partition table wont  create partition table.


No comments:

Post a Comment