1

I'm trying to figure the best possible way to determine the partition strategy in Oracle 12c (12.2.0.1.0) This post is almost identical to my requirements. However, I want to know the best possible way to implement in Oracle 12c (12.2.0.1.0) version.

Here is my question: We have four (4) distinct programs for which the bills are submitted in our system.

The approx volume of bills submitted per year is as follows:
Program_1 ~ 3M per year
Program_2 ~ 1M per year
Program_3 ~ 500K per year
Program_4 ~ 100K per year

My initial thought process is to create PARTITION BY LIST (PROGRAM) AND SUBPARTITION BY RANGE (BILL_SUBMISSION_DATE).

I would like to use oracle interval feature for SUBPARTITION, would like to know if there are any limitations with this approach.

AJORA
  • 27
  • 5

1 Answers1

0

Your approach of partitioning by PROGRAM and sub-partitioning by BILL_SUBMISSTION_DATE sounds good.

I have not tested performance differences (I imagine they would be negligible), but for coding the INTERVAL option makes querying and maintenance easier in my opinion.

For the following example the table partition clause I used was:

partition by range (INVOICE_MONTH) interval (numtoyminterval(1, 'MONTH'))

Example query, using old style partition names, query a partition for an invoice for April 2012, assuming I created a partition named INV201204 for those invoices for that month:

select * from MARK_INV_HDR
    partition ('INV201204');

And the same query, using INTERVAL automatically generated partitions:

select * from MARK_INV_HDR 
    where invoice_month = to_date('2012-04', 'yyyy-mm');

The advantage of the later query is I don't have to know the naming convention for the partitions.

To drop the oldest partition, one query and one DDL:

select to_char(min(invoice_month), 'dd-Mon-yyyy') as min_inv_dt from MARK_INV_HDR;

MIN_INV_DT
-----------
01-Apr-2012

alter table mark_inv_hdr 
    drop partition for (TO_DATE('01-Apr-2012', 'dd-Mon-yyyy'))
    update global indexes;

EDIT:

Update: I forgot that you cannot use the INTERVAL clause on a sub-partition; thanks to user12283435 for the reminder. In looking more closely at the question, it appears that there is probably no need to partition on PROGRAM, so just a single partition by range on BILL_SUBMISSION_DATE with the INTERVAL clause should work fine.

When you have a small set of values like you do for PROGRAM, no obvious reason to partition on it. The typical example of partitioning by list given in Oracle documentation is list of regions, for a global call center, so that you can do batch reports and maintenance on certain regions after business hours, etc. You can have a global bit-mapped index on PROGRAM, if you don't do many updates, if you query criteria frequently includes just one PROGRAM. (Updating a column with a bit-mapped index will briefly lock the table.)

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32
  • 1
    Thanks Mark. Looks like Oracle does not allow interval at `subpartition` level. Does this mean we've to manually create the `partitions` and `subpartitions`? – AJORA Nov 06 '19 at 02:42
  • Oh, yeah; forgot about that.... thinking about it a bit more, the `PROGRAM` column is probably not really necessary to partition by, so you could just partition on `BILL_SUBMISSION_DATE` using the `INTERVAL` clause, and no sub-partition needed. I'll try and update my answer in my spare time with a bit more reasoning. – Mark Stewart Nov 07 '19 at 16:29