ORDER_ITEMS table holds the data related to the orders.
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 -
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')))
index is required on the the columns
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
Local index should be used for
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_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.