To implement the standard NMS ILM strategy requires the Oracle RDBMS Partitioning option. Oracle Partitioning is a separately licensed module that also requires Oracle RDBMS Enterprise Edition.

NMS provides ILM support for the NMS model management RDBMS tables. NMS model managed tables always include birth and death DATE columns. The death column tracks when a row was taken OUT of the active model. The death column is NULL for active records.

Product support for ILM requires that the NMS model be initially created with ILM (partitioning) turned on. The NMS product includes Oracle RDBMS Data Definition Language (DDL) scripts that are generally used to create the core NMS model managed tables. For example, the $NMS_SQL_FILES/nms_model_ops.sql file contains DDL statements for most core NMS model tables.

Note for model migration: You cannot directly alter an existing (non-partitioned) table into a partitioned table. A table is either partitioned or not when it is created. It should be possible to define a new (empty) partitioned table and "swap" the contents with an existing (non-partitioned) table. Essentially this amounts to copying old table data into a new partitioned table – then dropping the old table and renaming the new one to match the old. The Oracle DBMS_REDEFINITION mechanism may help with this process but the details are left as a project exercise.

If the $NMS_DATA_FILES/[project]_licensed_products.dat file has the "partitioning" option uncommented then, when the NMS model is being initially created (using nms‑setup with the –clean option), NMS model managed tables will be partitioned. The NMS model workbook references the same "partitioning" option and should correctly create proper DDL statements (partitioned or otherwise) for the potentially project specific NMS model managed attribute tables.

The NMS ILM partition scheme uses "PARTITION BY RANGE" with the "INTERVAL" option set to 1 month and the "ENABLE ROW MOVEMENT" option set for each NMS model managed table. This will automatically create a new NMS model managed table partition each time the death column is set to a new non-NULL month date value.

The "PARTITION BY RANGE" option is applied to the virtual ptn_date DATE column that must exist in each NMS model managed table that we need/want an ILM scheme for. The ptn_date column is required to support NMS ILM partitioning but can be safely ignored for non-partitioned NMS models. When the death date is set, generally via the standard NMS incremental model build process, the row is deactivated and moved into the appropriate partition (by month of deactivation).

When NMS ILM is enabled the active NMS model exists entirely in the default mdl_alive partition within each of the various NMS model managed tables. This happens by virtue of the virtual ptn_date column being set to ‘1776-07-01’ when the death column is null (the row is still active). It is necessary to use the virtual ptn_date column for partitioning because partitioning by date requires the partition by date column always be non-null.

As the NMS incremental model build process deactivates rows in the various NMS model managed tables new partitions will accumulate, containing the deactivated NMS model managed table rows. It is up to the project to drop unneeded partitions once they are deemed no longer relevant. To see the partitions within a given schema, the following query may be useful:

To drop a partition from a table, use a SQL statement similar to the following - where SYS_P522 is an Oracle RDBMS generated partition for the ALIAS_MAPPING table: