In the project database design process, because of the large amount of data in the single table, we need to partition the table. Because the data in the table is a historical transaction, it is divided by month to improve the query and management.
Because of the lack of understanding of the table partition before, in order to achieve the above function to check a lot of data, the first direction is through the crontab call Shell script automatically create partitions on a monthly basis, or use the Oracle job call stored procedures to automatically create partitions. In the study of the above two sets of scenarios, there is no intention to find oracle11g partition function, for use range partition can be year, month, days automatically generate partitions. The syntax is as follows:
creat TABLE TABLE1
(
table_id Number (8),
sub_date DATE,
VALUE number (8)
)
PARTITION by RANGE ( sub_date)
INTERVAL (numtoyminterval (1, ' MONTH '))
(PARTITION
P1 VALUES less THAN (' 2014-05-01 ', ' Yyyy-mm-dd '))
;
Data prior to May 1, 2014 is placed in the P1 partition, and the data for the month after May 1 will automatically create a partition whenever there is data.