Partition by Date and PK

Question

I am designing a new laboratory database.

My primary data tables will have at least id (PK NUMBER) and created_on (DATE). Also, for any two entries, the entry with a higher id will have a later created_on date.

I plan to partition by created_on to increase performance on recently entered data. Since the columns increase together, the table would also be partitioned by id, implicitly. Oracle wouldn't know about the implied partitioning by id to take advantage of the partitioning of table joins on id.

Two questions:

1. How do I enforce both columns increasing together?

2. How can I take advantage of this implicit partitioning for table joins?

Answer 1

In my opinion, the decision to partition should be based more on the need for table maintenance activities (purging, archiving, etc.) than performance. In your case I'm guessing you'll probably be performing index range scans on the samples for a date range, so make sure the date index is locally (instead of globally) partitioned also . This will also eliminate the need to rebuild the index if you truncate a partition. I'd also guess that the joins on the PK will use seeks by rowid so that will happen after the index range scan and there's no way partitioning can affect this.

[Edit]

With regard to relating the PK and CREATED_ON columns, I work with a couple of systems that construct the numeric key from a sequence that is prefixed with YYYYMMDD and that works pretty well. You'll have to:

• Liberally estimate the number of samples you'll have per day

• Define a sequence that has this as a maximum value and then cycles back to 0

• Have a function that returns YYYYMMDD || {sequence value left-padded with
  zeros to the appropriate fixed length} that is called from a trigger or application code when the key is needed

Some would disagree with embedding meaning in the key, but in practice it is useful to look at a sample ID and have an idea of when it was processed

Answer 2

It's pretty tricky, to be honest. Multicolumn partitioning is one option, whereby you create range-based partitions on more than one column. In 11g you can impliment this either as partitioning on Column A and subpartitioning on Column B, but in 10g you have to partition by range on the two columns together. I think that the tricky part is to know what boundary to partition on because you probably want the two partitioning schemes to "sync".

Answer 3

In this case to speed up performance on joins on "table_id" you should also store corresponding "created_on" in tables that you will mostly join. If you do that you can always join on both "table_id" and "created_on" so your "PARTITION RANGE ALL" turns into "PARTITION RANGE SINGLE". You can measure speed gains and weigh them against additional storage costs.

Edit:

How to keep both fields increasing together:

ALTER TABLE my_table MODIFY created_on DEFAULT SYSDATE;

And fill ID from sequence in all your inserts.

Answer 4

How do I enforce both columns increasing together?

1. Assuming it is a bulk load and id is sequence generated at the time of the bulk-load, you could ALTER SEQUENCE between loads to get more control over the range of sequences used for each partition. If the sequence and created on is assigned prior to the bulk load, you may need a stage in your ETL process to work out what the min/max id for each created date is.

2. Range partition on created_on, Range subpartition on id. Each partition should only have one sub-partition.

3. Assuming that, since this is a new DB, you'll have 11g how about a check constraints on virtual columns. Virtual column date_partition

   CASE WHEN created_on BETWEEN ... AND ... THEN 'PARTITION_1' WHEN created_on BETWEEN ... AND ... THEN 'PARTITION_2' ... END

Similar virtual column on id_partition, though you'd have to query to get the minimum/maximum PK for each partition. Should be quick as, being the primary key, there's an index on it.

Then you add a constraint such that id_partition = date_partition

Answer 5

Hi Steven,

the real important question is: will you ever need to query by range of IDs? It is unlikely you will need to build a query with ID BETWEEN :A AND :B. Therefore, Oracle wouldn't benefit from a correlated partition scheme. For all that matters you could use a GUID for the primary key and you will get better scalability for INSERTS.