Tables Cleanup and Index Maintenance

The SQL Loader runs in parallel mode in order to maximize performance. Therefore the target tables are not emptied prior to the data insertion and must be done explicitly, by running corresponding batch jobs.

Indexes are disabled during the data upload and then need to be re-built afterwards. The product provides batch processes that support index disabling and re-building indexes and gathering table statistics.

Most of the larger volume tables in the application are partitioned. The partitioned indexes cannot be disabled implicitly by the data upload process and should be disabled implicitly before running the upload.

If your are migrating an existing on-premise Oracle Enterprise Utilities customer to the cloud, the migration requirements may include the data in the custom tables. On cloud, these tables reside in the Customer Modification schema. The product provides the dedicated batch controls to operate on the Customer Modification schema.

  • Use batch control K1-SCLTB to truncate tables in Staging area and batch control K1-CLNTB to truncate tables in Production. Use batch control K1-CLNCM to truncate tables in Customer Modification schema.

  • Use batch control K1-SDSIN to disable indexes in Staging area and batch control   K1-DRPIN to disable indexes in Production. Use batch control K1-DRICM to disable indexes on tables in the Customer Modification schema.

  • Use batch control K1–RIUSS to rebuild indexes and/or update table statictics in Staging and K1–RIUSP to perform the same in Production. Use batch control K1-RUSCM rebuild indexes and/or update statictics on tables Customer Modification schema.

  • Use batch control K1-CLNKY to truncate Key Reference tables in Staging area

  • Use batch control K1-CLNRT to truncate XML Resolution tables in Staging Area

  • Use batch process K1–SDSTG to disable Triggers in Staging and batch control K1–PDSTG to disable triggers in Production

  • Use batch process K1–SENTG to enable Triggers in Staging and batch control K1–PENTG to enable triggers in Production

Supported scenarios include:

  • Truncate Specific Table or Maintenance Object: In order to truncate the specific table, submit the corresponding batch job, specifying the table parameter. In order to truncate the data stored in a specific Maintenance Object, submit the corresponding batch job, specifying the maintenanceObject parameter. The process will also disable indexes in the truncated tables.

    Note:
    If the truncated table’s metadata defines a Key table, the Key table gets truncated too.

  • Truncate all Converted Tables: In order to truncate all converted tables run the corresponding batch job without specifying either the table or maintenanceObject parameters. The process will disable indexes and truncate all tables marked for conversion in the metadata.

  • Truncate Key Reference Tables or XML Resolution Tables for Specific Table or Maintenance Object: In order to truncate Key Reference table linked to a staging table, submit the corresponding batch job, specifying the table parameter. In order to truncate Key Reference tables for a specific Maintenance Object, submit the corresponding batch job, specifying the maintenanceObject parameter.

  • Truncate all Key Reference Tables or XML Resolution Tables: Submit the corresponding batch job without specifying either the table or maintenanceObject parameters to truncate all Key Reference tables.

  • Disable Indexes for Specific Table or Maintenance Object: In order to disable indexes for the specific table, submit the corresponding batch job, specifying the table parameter. In order to disable indexes for the specific Maintenance Object’s tables, submit the corresponding batch job, specifying the maintenanceObject parameter.

  • Disable Indexes for all converted Tables: In order to disable indexes on all converted tables run the corresponding batch job without specifying either the table or maintenanceObject parameters. The process will disable indexes and truncate all tables marked for conversion in the metadata.

Note:
You should not use conversion batch processes to truncate and/or alter indexes on the batch and batch job stream-related tables. Attempt to cleanup the batch job history or reset sequences may render the system un-operational.