Partitioning tables
If we have an existing table that need to be partitioned here are steps to follow
Consider a non partitioned table testx which has around 500000 rows
We can partition table based on requirements
Say we have a column called date which is in this format 20160401000000
Format is yyyymmddhhmmss
I will create a new table testx_partition with the following syntax. While creating table that will include partition name with a maxvalue clause. Here i chose partition name as TESTX_MAX
CREATE TABLE SVADAPALLI.TESTX_PARTITION
(
RDR_KEY NUMBER,
VEHICLE_KEY NUMBER,
DATE NUMBER,
VEHICLE_ID NUMBER,
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 80K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
PARTITION BY RANGE (DATE)
(PARTITION TESTX_MAX VALUES LESS THAN (MAXVALUE));
Once the empty table is created, The next step is to move the data from original table to partition table
This is called partition exchange
ALTER TABLE testx_partition
EXCHANGE PARTITION testx_max
WITH TABLE testx
WITHOUT VALIDATION;
Once the above is done all values from original table will be moved to the new table.
Now drop the original table and rename the new table.
Drop table testx cascade;
rename testx_partition to testx;
Once this is done your new table will now have all your data in TESTX_MAX partition.
Now that you have your data, you can easily split partition and create new partitions depending on your requirements. In my case i will do it monthly here.
ALTER TABLE TESTX
SPLIT PARTITION TESTX_MAX AT
('20160201000000')
INTO (PARTITION TESTX_JAN
TABLESPACE USERS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
),
PARTITION TESTX_MAX)
UPDATE GLOBAL INDEXES;
