Legacy platform

Oracle table partitioning

You can use Oracle partitioning to aid the maintainability of large tables. You should not view partitioning as a performance tool to achieve higher throughput.

Under certain circumstances it may increase throughput, but these circumstances are rare in Sterling™ Order Management System Software. However, as a tool to improve the maintainability of the largest tables, partitioning can be valuable. Before implementing any partitions in a production environment, it is essential that you test the changes with the expected production workflows. Sterling has tested and developed the following points with regard to Oracle partitioning and the Sterling Order Management System Software application:

  • With the careful selection of tables based on workflow analysis, and using Global Indexes, table partitioning did not cause an appreciable degradation of throughput compared to non-partitioned tables.
  • Converting the Global Indexes to Local (non-prefixed) Indexes showed a minimal I/O increase. Application throughput dropped minimally. These tables were accessed only by the purge agents and at low access volumes. General industry consensus is to use Global Indexes for high query volumes, though local indexes on low access volumes may maintain acceptable performance.
  • Table partition compression can save up to 85% of the disk space used. We recommend this only on low volume access tables such as the history tables.

Table partitions should ideally be set up on the initial installation. Tables may be partitioned once loaded. We have used and recommend testing Oracle's "dbms.redefinition" package. This package is well documented by Oracle in the Oracle® Database PL/SQL Packages and Types Reference and the Oracle® Database Administrator's Guide.

Oracle does not support LONG columns in table partitions. Any LONG columns need to be converted to CLOB before attempting to partition a table. The LONG to CLOB conversion can be performed by the "dbms.redefinition" package at the same time as the table partitioning.