In this article, Oralce horizontal table sharding finds that tables are stored in each partition table by year based on the above horizontal table sharding. Since the data volume is still large by year after the business promotion. So we can consider whether we can split it in the year table.
The following describes the splitting procedure.
Original table structure (only for partitions ):
From the figure above, we can see that the WLKP_FP_DATA_2012 partition is stored in the tablespace WLKP_FP_DATA_2012.
So after half the time, we had statistical data for half a year and found that WLKP_FP_DATA_2012 had a lot of partition table data.
We consider splitting WLKP_FP_DATA_2012 by quarter.
- Alter TableWLKP_FP_KJ split partition WLKP_FP_DATA_2012At(To_date ('2017-04-01','Yyyy-mm-dd'))Into(Partition WLKP_FP_DATA_2012_1, partition WLKP_FP_DATA_2012 );
- Alter TableWLKP_FP_KJ split partition WLKP_FP_DATA_2012At(To_date ('2017-07-01','Yyyy-mm-dd'))Into(Partition WLKP_FP_DATA_2012_2, partition WLKP_FP_DATA_2012 );
- Alter TableWLKP_FP_KJ split partition WLKP_FP_DATA_2012At(To_date ('2017-10-01','Yyyy-mm-dd'))Into(Partition WLKP_FP_DATA_2012_3, partition WLKP_FP_DATA_2012 );
- Alter TableWLKP_FP_KJ split partition WLKP_FP_DATA_2012At(To_date ('2017-12-31','Yyyy-mm-dd'))Into(Partition WLKP_FP_DATA_2012_4, partition WLKP_FP_DATA_2012 );
Run the preceding statement to split WLKP_FP_DATA_2012 into four sub-partitions: WLKP_FP_DATA_2012_1, WLKP_FP_DATA_2012_2, WLKP_FP_DATA_2012_3, and WLKP_FP_DATA_2012_4
Note: Why is the last partition time? Because the end time for splitting WLKP_FP_DATA_2012 is.
If the time is, an error will be reported.
The split partition is as follows:
Compared with the above, we can see that we split the WLKP_FP_DATA_2012 sub-partition table by quarter. Note that the above is still split under the current WLKP_FP_DATA_2012 tablespace.
We can see that the partition table is only extended to. What should we do in?