Choosing the index partition type for OLTP application

Question

The ORDER_ITEMS table holds the data related to the orders.

Use case: OLTP. The rows will be added and the historical data(more than 3 years old) will be deleted periodically. Partition/Index maintenance activity should be nil or minimum.

The table is huge and needs to be partitioned using the key - ORDER_DATE. Interval partitioning is chosen because the partitions should be automatically created when a record with a new ORDER_DATE is added, for which there is no existing partition. The partitions should be deleted automatically when there are no rows for the partition.

CREATE TABLE ORDER_ITEMS("ID" VARCHAR2(255 CHAR) NOT NULL PRIMARY KEY, 
    "ORDER_ID" VARCHAR2(255 CHAR), 
    "BRANCH" VARCHAR2(255 CHAR), 
    "ORDER_DATE" DATE, 
    "SHIPPING_TYPE" VARCHAR2(255 CHAR))
PARTITION BY RANGE (ORDER_DATE)  
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION "PARTITION_ORDER_ITEMS_DEC_2020" VALUES LESS THAN (TO_DATE('01-JAN-2020', 'DD-MON-YYYY')))

Also, an index is required on the the columns "ORDER_ID", "BRANCH" and "ORDER_DATE" to speed up the search queries and this index needs to be partitioned to avoid maintenance when the data is removed(as per When to Partition an Index).

Going through this post - Oracle Global Index vs. Local Index, it appears that Global index should be used for OLTP and Local index should be used for OLAP. Also, the disadvantage with GLOBAL index seems to be that it requires maintenance when partitions are dropped.

Given that I need to partition the table based on "ORDER_DATE" and also need to have an index for the columns - "ORDER_ID", "BRANCH" and "ORDER_DATE", what is the right type of index that needs to be created keeping in mind that the use case is OLTP with nil or minimum partition/index maintenance? The table will be queried for reporting purpose as:

SELECT * FROM ORDER_ITEMS 
WHERE ORDER_ID IN ('1','2','3',...) AND BRANCH = 'XYZ' 
AND ORDER_DATE >= 'DD-MON-RR' AND ORDER_DATE <= 'DD-MON-RR'

The range for ORDER_DATE can vary from a day to 3 years.

Answer 1

Something else you'll need to keep in mind when considering global index vs local.

If the partition key is NOT in the index, and the index is UNIQUE: it MUST be global. If it's not unique, you're ok with local (or global). This makes logical sense if you take a second to think about it ;) but it's worth mentioning for clarity.

In your case above: Partitioned on ORDER_DATE

What's your Primary Key? I'm assuming that it's just ORDER_ID ? This means the PK will have to be Global, as it's unique, and doesn't have ORDER_DATE in it. (and doesn't make sense to have it in there).

For your other index: ORDER_ID + BRANCH + ORDER_DATE This one can definitely be LOCAL. Now, "should it be?"

That's going to depend on how your OLTP accesses this table. Is it only via ORDER_ID? or does it have the ORDER_DATE in there as well?

ie: select * from order_items where order_date > :dt and ; ??

If you use the order_date in the where clause, Oracle can "partition prune", meaning it can immediately ignore all the partitions that do not apply because of the order_date clause. This is a good case for using LOCAL index. If you have a GLOBAL index, oracle has only 2 options:

1. Prune to the specific partition, and FULL scan it. or
2. don't prune to specific partition, and use global index scan.

Either might work or might not work (depending on many factors), but with a local index, AND usage of the partition key, Oracle can do both: Go straight to only the partition(s) that matter, and use the local index on those partitions.

However, when it comes down to deciding which to use (ie global vs local), it really depends on how your queries are accessing the table.

See this thread on asktom where he explains this better than I ever could :) https://asktom.oracle.com/pls/apex/asktom.search?tag=partitioning-non-unique-indexes

Answer 2

First of all, there are three types of indexes. Sometimes the names are mixed a bit and it is not clear which one is actually used.

• Global Nonpartitioned Indexes: You have one index which spans over all partitions of the table. If you alter a table partition then such index gets UNUSABLE and you have to rebuild it, unless you use the UPDATE GLOBAL INDEXES clause.
• Local Partitioned Indexes: This index is partitioned in the same way as the partitions of the base table. Each index partition belongs solely to one table partition. This index is created with LOCAL clause.
• Global Partitioned Indexes: This index is partitioned but the partition key is different to partition key of base table which can be also a nonpartitioned table. For example, you could have the table not partitioned or partitioned by RANGE but the index is partitioned by LIST of a different column.

Most common are Global nonpartitioned and Local partitioned indexes. Actually I cannot imagine any use case where a Global partitioned Index would make sense, they might be useful only in very special circumstances. When you have a requirement for Global Partitioned Indexes, then using sub-partitions would be the better option in most cases.

My suggestion is simpler and should be sufficient for 90% of all use cases:

By default, you should prefer LOCAL Partitioned indexes. Global nonpartitioned indexes are only used when you have a unique index/primary key and the partition key (in your case ORDER_DATE) is not part of the index.

When your table is really big, i.e. thousands of partitions, then you may consider global nonpartitioned indexes also for non-unique indexes. As drawback, the index rebuilds may take very long when you drop/truncate a partition.

Answer 3

Oracle documentation provides the guidelines here - Deciding on the Type of Partitioned Index to Use

From the documentation:

The type of partitioned index to use should be chosen after reviewing various factors.

When deciding what kind of partitioned index to use, you should consider the following guidelines in this order:

1. If the table partitioning column is a subset of the index keys, then use a local index. If this is the case, then you are finished. If this is not the case, then continue to guideline 2.
2. If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
3. If your priority is manageability, then consider a local index. If this is the case, then you are finished. If this is not the case, continue to guideline 4.
4. If the application is an OLTP type and users need quick response times, then use a global index. If the application is a DSS type and users are more interested in throughput, then use a local index.

See Also:

Using Partitioning in a Data Warehouse Environment for information about partitioned indexes and how to decide which type to use in data warehouse environment

Using Partitioning in an Online Transaction Processing Environment for information about partitioned indexes and how to decide which type to use in an online transaction processing environment

Answer 4

If you will be dropping partitions and want minimal maintenance overhead, you must keep your indexes all LOCAL. That unfortunately will require adding the partitioning key (ORDER_DATE) to the end of your unique indexes, or declaring them all non-unique.

The only situation in which a GLOBAL index is appropriate here is if your application queries by ORDER_ID at a very high frequency without pruning (without also specifying ORDER_DATE). A low frequency of such lookups is not an issue. What's a few added milliseconds here and there? A human pulling up an order is low frequency. But if your app is querying by ORDER_ID thousands or millions of times per hour (a program looping and doing row-by-row work) making the index LOCAL is going to hurt you. Every partition of the index has to be accessed because Oracle doesn't know in which one your ORDER_ID value will be found. If you have 1000 partitions, that's 1000x more work than a global index.

If you do have very high frequency lookups on ORDER_ID, go global and eat the cost of maintenance at partition-drop time, hopefully in a regularly scheduled minimal activity window.

A compromise option is to redefine your partition interval to be a greater amount. If you do one partition per year rather than one per month, that's 12x fewer partitions, so those non-pruned local index lookups will be 12x faster.

Another compromise option is to add a ORDER_DATE predicate (at least a range predicate if not equality) to all your queries against ORDER_ID. Then you will prune partitions and the locality of the index won't be an issue. This is of course not always possible given your business logic. But in my experience often you can at least define a window (e.g. > a year old ) and at least bypass accessing those other 20 years of ancient data that you can't possibly find your interesting ORDER_ID in. This can reduce index access time by 20x in this imaginary scenario.

As for other indexes on less unique columns (like BRANCH), definitely go LOCAL. The larger number of results per query will overshadow the impact of scanning multiple index partitions. You will likely not see a frequency of such queries high enough that the locality is an issue.

As for indexing ORDER_DATE, this is usually not recommended for a date that is also the partitioning key. That's because any predicate against this column will prune partitions anyway. It's a lot faster to get January 2023 rows by doing a full table scan of the January partition than it is to access each row by ROWID from an index. The only situation in which an index on this column would make sense is if you have frequent queries asking for a very specific date (like just today, or a specific hour). Anything larger than that and you're better off without the index. If you do end up with such an index, of course it should be LOCAL, since any access of it will be pruning partitions by definition.