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