Introduction

Partitioning Workflow tables can improve the performance of Workflow Engine run-time processing. Partitioning a table creates the subset of table based on the specified column value. Workflow tables are partitioned based on ITEM_TYPE. The wfpart.sql script can be used to migrate the non-partitioned workflow tables into partitioned tables. When there is large volume of data, this will increase the performance as it will have to read through a particular set of data instead of the whole table.

Tables and Indexes

The wfpart.sql script partitions four Workflow tables and recreates the associated indexes as shown in below table.

Table Indexes
WF_ITEM_ACTIVITY_STATUSES

WF_ITEM_ACTIVITY_STATUSES_PK

WF_ITEM_ACTIVITY_STATUSES_N1

WF_ITEM_ACTIVITY_STATUSES_N2
WF_ITEM_ACTIVITY_STATUSES_H

WF_ITEM_ACTIVITY_STATUSES_H_N1

WF_ITEM_ACTIVITY_STATUSES_H_N2
WF_ITEM_ATTRIBUTE_VALUES WF_ITEM_ATTRIBUTE_VALUES_PK
WF_ITEMS

WF_ITEMS_PK

WF_ITEMS_N1

WF_ITEMS_N2

WF_ITEMS_N3
 

Before running the partitioning script, take a back up of these four tables so that you can restore them in case the script fails.To run the script, you must have sufficient free space on the table and index table spaces. During the creation of the partitioned tables, the script requires slightly more disk space than the underlying tables, in the same table space where the underlying tables are located. Similarly, sufficient free space is required for the index table space.

Usage

The wfpart.sql script can be run using below commands.

  1. Run the script using below commands

    sqlplus apps/<apps_passwd> @$FND_TOP/patch/115/sql/wfpart.sql <fnd_user> <fnd_passwd> <apps_user> <apps_passwd> <ult_dir_location>

    <fnd_user> – fnd product username. APPLSYS in this case
    <fnd_passwd> – fnd product password. applsys by default
    <apps_user> – user name for the APPS user
    <apps_passwd>  – password for the APPS user
    <ult_dir_location> – a directory that is included in the UTL_FILE_DIR database initialization parameter.

    For example:

    sqlplus apps/apps @wfpart.sql applsys apps apps apps /usr/tmp

  2. It generates another script wfpart.sql in the utl directory location eg:/usr/tmp

  3. Run the generated wfpart sql script using below command

    sqlplus apps/<apps_passwd> @<ult_dir_location>/wfpart.sql <fnd_user> <fnd_passwd> <apps_user> <apps_passwd>

    sqlplus apps/<apps_passwd> @<ult_dir_location>/wfpart.sql <fnd_user> <fnd_passwd> <apps_user> <apps_passwd>

    For example:

    sqlplus apps/apps @/usr/tmp/wfpart.sql applsys apps apps apps

  4. It gives the below message once the script is completed
    “Post-install script completed, you may ignore error during create
    Please recompile the invalid objects after executing this script”

 

Additional Resources

1. Refer to Partitioning Workflow Tables section in Oracle Workflow Administrator guide