Database Partitioning and Order Purging

For the relational database such as Oracle or PostgreSQL, partitioning has been supported. Previously, there were non-partitioned database, where all the order related information was stored in non-partition tables.

The disadvantage of non-partition tables is that the table size keeps on growing, and there is no way to delete specific date range orders.

Now, the tables are managed through weekly partitions. You can delete the partitions for the orders. The partitions can be deleted automatically through the partition deletion scripts.

Oracle

By running the $OM_HOME/db/dbscripts/oracle/order/upgrade_5.1.0hf5_to_5.1.0hf6_dbsetup.sh script, the order specific non-partitioned tables are converted to partitioned tables.

Note: For Oracle, before you run the $OM_HOME/db/dbscripts/oracle/order/upgrade_5.1.0hf5_to_5.1.0hf6_dbsetup.sh upgrade script, connect to Oracle with the SYSDBA privilege and provide the following permissions to the user:
ALTER SESSION SET CONTAINER = pdb1; <Database-Name>
grant resource, connect, create session to OM_ORDER_US5;
grant alter session to OM_ORDER_US5;
grant execute on dbms_redefinition to OM_ORDER_US5;
grant create any sequence to OM_ORDER_US5;
grant create any table to OM_ORDER_US5;
grant alter any table to OM_ORDER_US5;
grant drop any table to OM_ORDER_US5;
grant lock any table to OM_ORDER_US5;
grant select any table to OM_ORDER_US5;
grant create any index to OM_ORDER_US5;
grant create any trigger to OM_ORDER_US5;
grant create any job to OM_ORDER_US5;

To drop the partition, you can run the $OM_HOME/db/dbscripts/oracle/order/drop_partition_by_name.sql script.

Similarly, to purge orders, run $OM_HOME/db/dbscripts/oracle/order/purge_orders.sql script.

Note: For more details, see the drop_partition_by_name_Readme.txt and purge_orders_ReadMe.txt files from the $OM_HOME/db/dbscripts/oracle/order directory.

PostgreSQL

By running the $OM_HOME/db/dbscripts/postgreSQL/order/bin/upgrade_5.1.0hf5_to_5.1.0hf6_db-setup.sh script, the order specific non-partitioned tables are converted to partitioned tables.

To drop the partition, you can run the $OM_HOME/db/dbscripts/postgreSQL/order/scripts/drop_partition_by_name.sql script.

Similarly, to purge orders, run $OM_HOME/db/dbscripts/postgreSQL/order/scripts/purge_orders.sql script.

Note: For more details, see the drop_partition_by_name_Readme.txt and purge_orders_ReadMe.txt files from the $OM_HOME/db/dbscripts/postgreSQL/order/bin directory.