I'm creating a brand new laboratory database.

Initially my data tables may have a minimum of id (PK NUMBER) and created_on (DATE). Also, for just about any two records, the entry having a greater id may have a later created_on date.

I intend to partition by created_on to improve performance on lately joined data. Because the posts increase together, the table would be also partitioned by id, unconditionally. Oracle wouldn't know of the implied partitioning by id to make use of the partitioning of table joins on id.

Two questions:

  1. How do you enforce both posts growing together?

  2. How do i employ this implicit partitioning for table joins?

For me, the choice to partition ought to be based more about the requirement for table maintenance activities (getting rid of, archiving, etc.) than performance. Inside your situation I am speculating you'll most likely be carrying out index range scans around the samples for any time frame, so make certain the date index is in your area (rather than globally) partitioned also . This can also eliminate the necessity to rebuild the index should you truncate a partition. I'd also reckon that the joins around the PK uses seeks by rowid to ensure that may happen following the index range scan and there is not a way partitioning can impact this.

[Edit]

Regarding relevant the PK and Produced_ON posts, I make use of a handful of systems that construct the number key from the sequence that's prefixed with YYYYMMDD which works pretty much. You will need to:

  • Liberally estimate the amount of samples you will have daily

  • Define a sequence which has this like a maximum value after which cycles to

  • Possess a function that returns YYYYMMDD towards the appropriate fixed length that's known as from the trigger or application code when the bottom line is needed

Some would disagree with embedding meaning within the key, but used it's helpful to check out an example ID and know of if this was processed

the actual real question is: are you going to ever have to query by selection of IDs? It's unlikely you will have to develop a query with ID BETWEEN :A AND :B. Therefore, Oracle wouldn't take advantage of a correlated partition plan. For the only goal you could utilize a GUID for that primary key and you'll improve scalability for INSERTS.

It's pretty tricky, to tell the truth. Multicolumn partitioning is a option, whereby you create range-based partitions on several column. In 11g you are able to impliment this either as partitioning on Column A and subpartitioning on Column B, however in 10g you need to partition by range around the two posts together. I believe the tricky part is to be aware what boundary to partition on since you most likely want the 2 partitioning schemes to "sync".

Within this situation to accelerate performance on joins on "table_id" it's also wise to store corresponding "produced_on" in tables that you'll mostly join. Should you choose that you could always join on "table_id" and "produced_on" so that your "PARTITION RANGE ALL" becomes "PARTITION RANGE SINGLE". You are able to measure speed gains and weigh them against additional storage costs.

Edit:

How you can keep both fields growing together:

ALTER TABLE my_table MODIFY created_on DEFAULT SYSDATE;

And fill ID from sequence in most your card inserts.