Database Partitioning with an Oracle Database
The Oracle database is partitioned using reference partitioning. A hierarchy tree of tables is created for existing TIBCO Fulfillment Order Management tables using a foreign key relationship between tables. The table hierarchy is maintained using the ORDERS table as a primary (or a parent) table for all tables.
A column PARTITIONDATE in the ORDERS table is used for partitioning. The column PARTITIONDATE maintains the same date as SUBMITTEDDATE, and this column is populated when a new order is submitted in the system. The ORDERS table is partitioned weekly based on the PARTITIONDATE column.
- Data is logically separated
With partitioning in place, the database administrator has the option to keep each partition in its own tablespace resulting in better space management and improving the total cost of ownership.
- Purge performance is improved
You can now truncate a partition instead of manually deleting entries from the ORDERS and other related tables for purging. Since the partition truncate operation is performed at a data dictionary level, this operation is very quick compared to manually deleting the rows.
TIBCO Fulfillment Order Management comes with the ORDERS table in a weekly partitioned format based on the PARTITIONDATE column. The PARTITIONDATE column maintains a date when the order was submitted to the system. Each month is divided into five partitions for each week in the month.
The following is an example of weekly partitioning:Partition Number Orders Submitted Between (Date and Time) Partition 1 FROM: 28th of the last month - 1 a.m. TO: 1st of current month - 12:59 a.m.
Partition 2 FROM: 1st of current month - 1 a.m. TO: 7th of current month - 12:59 a.m.
Partition 3 FROM: 7th of current month - 1 a.m. TO: 14th of current month - 12:59 a.m.
Partition 4 FROM: 14th of current month - 1 a.m. TO: 21st of current month - 12:59 a.m.
Partition 5 FROM: 21st of current month - 1 a.m. TO: 28th of current month - 12:59 a.m.
You can partition database on a monthly basis as well. Example scripts for partitioning monthly for the ORDERS table and related create future partitions can be found under the $AF_HOME/samples/db_scripts/oracle folder.Note: The $AF_HOME/db/oracle/oms/OMS_DDL.sql file has default partition entries until the date 12-28-2017:01:00:00 AM. If the default dates are about to be passed within a month or have already passed, you must manually change the date entries to the dates that you want to create and map the partitions.