An Oracle Database 11g New Feature to share...

So, I'm just back from a quick vacation... Spent a few days on a cruise with my wife and then a couple of days at the Mouse (Disney).... had a great time!! I'll talk about the trip in a bit more detail in another blog entry. It's late now, and I wanted to get something in about Oracle Database 11g, because I promised I would.

Oracle 11g has introduced a new kind of partitioning called interval partitioning. If you have done range partitioning in the past, then you know that there is a maintenance cost. Often you find yourself having to add partitions as time goes on. Wouldn't it be nice if you could just tell Oracle you wanted to partition every month and it would create the partitions for you? That is exactly what interval partitioning does. Here is an example:

create table selling_stuff_daily
( prod_id number not null, cust_id number not null
, sale_dt date not null, qty_sold number(3) not null
, unit_sale_pr number(10,2) not null
, total_sale_pr number(10,2) not null
, total_disc number(10,2) not null)
partition by range (sale_dt)
interval (numtoyminterval(1,'MONTH'))
( partition p_before_1_jan_2007 values
less than (to_date('01-01-2007','dd-mm-yyyy')));

Note the interval keyword. This defines the interval that you want each partition to represent. In this case, Oracle will create the next partition for dates less than 02-01-2007 when the first record that belongs in that partition is created. Other than the interval keyword, this statement is just like a regular create table statement....

Cool stuff. Note that the partition names are system generated, which I don't personally like, but you can rename them if you like. I've not yet found a way to create some form of template that Oracle will use for partition naming (would be nice, maybe another release).

There is also an optional store in clause that provides the ability to define the tablespace to store the partition in, much like with hash partitioned tables.

So, there is a new Oracle Database 11g feature for you. More to come, and much more in my Oracle Press book Oracle Database 11g New Features, which should be out soon after Oracle Database 11g is released. It's available on Amazon for pre-orders now, and I hope you will check it out!