Oracle Auto-Partitioning Table

Oracle Auto-Partitioning Table

Oracle Partitioning tables are fantastic to manage and  run a query data from them. The best practices around large table is to partitions them in a sensible way so that your business can benefit it. There are various ways you can partitioned a non-partitioned table. Oracle Range Partition table is pretty common when you need to partition the table by daily/monthly/quarterly/yearly.

Before the introduction of Interval Partition, Range Partition was the only to partition based on date range. The cons of Range Partition is to create a partition manually ( who has time to create manually daily or monthly?). This is going to be a nightmare  when you are required to maintain a database for top data companies. With  Oracle 11g, Oracle enhanced the range partition with Interval Partitioning which automatically creates new partition based on the data you insert.

Its Lab Time Now!

--Creating Auto Partition table

create table 

pos_data ( 

   start_date        DATE,

   store_id          NUMBER,

   inventory_id      NUMBER(6),

   qty_sold          NUMBER(3),

) 

PARTITION BY RANGE (start_date) 

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 

(  

   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2015', 'DD-MM-YYYY')),

   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-214', 'DD-MM-YYYY')) 

)

-- will create a partition for 2015.

insert into pos_data (start_date, store_id, inventory_id, qty_sold) 

values ( '15-AUG-15', 1, 1, 1);

commit;

--Verify the new partitions

SELECT * FROM dba_tab_partitions WHERE table_name = 'POS_DATA';

Recommendation:

Oracle Advanced Compression along with Partition table is the best recipe for high query performance and less storage utilization. This combo will make your client & boss both happy.