Partitioning is simple terms means to divide a big table/index into smaller parts .
Why do we need partitioning?
Ofcourse the question comes to mind that why do we need partitioning or what is the use of partitioning.
Well partitioning has 3 main advantages->
1 Performance improvement by using partitioning pruning and partition wise joins
2 Mangeability- operations van be performed on per partition basis rather than on whole table
3 Availability- A single partition can be dropped/truncated, different partitions can be stored in different tablespaces, etc
Type of partitioning
1 Range partitions
2 List partitions
3 Hash partitions
4 Composite partitions
What is Interval partitioning ?
Interval partitioning basically extends range partitions. Traditionally in range partitions, the partitions for future had to be added manually using
alter table
add partition command
otherwise oracle would give following error in insert operation
ORA-14400: inserted partition key does not map to any partition
In Interval partitioning, all the future partitions would be automatically added by oracle on demand which means that when we fire an insert statement for lets say a date Aug2010 and when oracle finds that this partition is not available in the table, then it would automatically add this particular partition in the table and the insert statement would execute succesfully!'
create table tab_int_part ( start_date DATE, store_id NUMBER )
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION tab_int_part_p1 VALUES LESS THAN (TO_DATE('1-7-2010', 'DD-MM-YYYY')) );
However, there are some restrictions also of interval partitioning-
1 It can only have single partitioning key which can be either number or date datatype
2 It does not support IOT
3 Domain index are not supported these tables.
4 Also, we can create only single level interval partitioned tables and composite partitioned tables can be only of -interval range, interval list and interval hash partitions.
How to convert range partitioned table to Interval partitioning
ALTER TABLE
SET INTERVAL (interval value);
for monthly partitions-
ALTER TABLE table_month_part SET INTERVAL(NUMTOYMINTERVAL(1,'MONTH'));
for daily partitions-
ALTER TABLE table_day_part SET INTERVAL (numtodsinterval(1,'day'));
How to convert Interval partition to Range partition
Use "SET INTERVAL ()" to convert interval partitioning back to range partitioning.
ALTER TABLE table_day_part SET INTERVAL ();
Also, remember that in oracle 11g, there can be a maximum of 1024k-1=1048575 partitions or sub partitions in a table or an index. Also, when using interval partitioning, oracle counts not only the physical partitions, but it actually counts the partitions from the lowest value created to the highest value created because it assumes that in between partitions might be created anytime!!