Oracle: Interval partitioning and dependant tables

oraclepartitioning

I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to another 80M records between them. This database is basically a log of data so the data going in is written once and only read after that.

The admims want to partition the data per month because they want to quickly remove old data after two year and avoid having to rebuild all the indexes. So their intent to to remove old partitions.

After reading a lot, my idea was to do interval partitioning as per many examples I found on the net, however I then found that the other two tables cannot then employ partition by reference. Why oracle does not allow this, no-one here knows.

So my question is what, in your experience, would you do to partition the data? given it's is spread across the 3 tables. And is this the best technique for handing a rolling removal of old data like this?

Best Answer

You can't use interval partitioning with reference partitioning, however, It's possible to mix range partitioning and reference partitioning, as shown in the example of reference partitioning in the 11g doc:

The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.

The child table doesn't need to contain the partition key (here the log date). It inherits the partition key from the parent table.

Reference partitioning schema

Range partitioning involves a bit more maintenance because you have to create the partitions yourself. However, once the partitions are created, range and interval work similarly.

Related Solutions

Rename interval partitioning system generated partition names

There's a trick you can use, at least for date-range partitions: the HIGH_VALUE field in user_tab_partitions is a valid date expression. If you "eval" that, you'll get a date object for the cutoff point for that partition. You can then use that to build your partition name from.

Sample PL/SQL to try out (generates a string from the high_value column):

declare
  hv varchar2(9);
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO' and partition_name not like 'PART_%')
  loop
    execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from dual' into hv;
    dbms_output.put_line('alter table foo rename partition '||x.partition_name
                       ||' to PART_'||hv);
  end loop;
end;
/

(Replace the put_line with execute immediate once you've verified that it actually does what you want. The -1 in the date select is to make it more "human friendly", i.e. partition name to match the date it contains.)

As for dropping old partitions, you can use the same trick. Same type of loop to get a date object for each partition, and generate an alter table ... drop partition ... statement for the partitions you don't want to keep.

Simplistic example where you would drop partitions older than 10 days (be vary wary of off-by-one errors, there probably is one below):

declare
  dt date;
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO')
  loop
    execute immediate 'select '||x.high_value||' from dual' into dt;
    if dt < sysdate - 10 then
      dbms_output.put_line('to drop: '||x.partition_name);
    end if;
  end loop;
end;
/

You're then left with taking care of index maintenance if necessary.

Sql-server – Partitioning on a single filegroup

There are two advantages to partitioning a table in the same filegroup:

Allowing portions of a large index to be rebuilt incrementally, allowing for more efficient maintenance. Review the ALTER INDEX [foo] REBUILD PARTITION=n for more details.
Leveraging partition elimination and (possibly) partition level locking to improve query maintenance. I discuss this on my blog.

There are several things to keep in mind if you're partitioning.

If your table has a clustered index (and it really should), your partitioning key must be part of the clustered index.
To avoid performance issues, you should align your partitions. This means all your indexes should include your partition key, whether as an include or as part of the index itself.
Index rebuilds for partitions are offline in current versions of SQL Server (2005-2012). If your partitions are to large and your rebuilding by partition, this could lead to blocking issues.

I recommend doing some thorough research on partitioning before implementing it. Kendra Little has an excellent list of resources where you can get started.

Best Answer

Related Solutions

Rename interval partitioning system generated partition names

Sql-server – Partitioning on a single filegroup

Related Question