Upgrading Indexes

Oracle Clinical was retuned to require only two indexes on the Responses table:

• RESPONSE_PK_IDX – primary key index on RESPONSE_ID and RESPONSE_ENTRY_TS

• RESPONSE_RDCM_NFK_IDX – an expanded, concatenated index on RECEIVED_DCM_ID

The following indexes are no longer required:

• RESPONSE_CS_NFK_IDX – on CLINICAL_STUDY_ID

• RESPONSE_DCMQG_NFK_IDX – on DCM_QUESTION_GROUP_ID

• RESPONSE_UK_IDX – on DCM_QUESTION_ID

This, combined with the use of Oracle database leading key compression, results in up to 50% savings in space allocated to the Responses table and its indexes, and a significant reduction in runtime overhead to maintain the indexes. These savings occur whether you partition or not.

For more information , see:

• Index Upgrade Process
  
• Response Index Upgrade
  
• ResponsesT (Test Database) Index Upgrade
  
• RECEIVED_DCI and RECEIVED_DCM Index Upgrades
  

Index Upgrade Process

To upgrade the indexes you must modify and execute the script rebuild_resp_index_non_part.sql in the install directory while connected to the RXC account. You should modify the script to provide storage parameters appropriate for the size of your Responses table. The foreign key index is approximately 70% the size of the Responses table when created and the primary key index is approximately 40%. In addition, if you are dropping the obsolete indexes, you should un-comment the drop index statements and move them to before the index creation statements to ensure that their space is freed before the new indexes are created.

SQL> @rebuild_resp_index_non_part.sql 

Response Index Upgrade

If you have other applications that directly access the Responses table, these may need to be retuned before you perform the index upgrade (due to the new leading CLINICAL_STUDY_ID key on the RESPONSE_RDCM_NFK_IDX) or before you drop the optional indexes (see Query Tuning Guidelines). If you are not partitioning you can retain one or more of these indexes until your other applications are retuned.

Note:

If you are partitioning, the new index structure is automatically created as part of the partitioning upgrade, so you should not do the upgrade described in this section.

If you are partitioning and want to retain one or more of the old indexes, you have to manually recreate them as non-partitioned global indexes after the partitioning upgrade.

Before upgrading the indexes, you must analyze your disk space requirements if you are preserving the existing indexes. Due to leading key compression the new, concatenated RESPONSE_RDCM_NFK_IDX index is significantly smaller than the version 3.1 concatenated RESPONSE_DCMQG_NFK_IDX it functionally replaces. However, if you are preserving all pre-existing indexes, there is a small (20-30%) net increase in the size of the pre-existing RESPONSE_RDCM_NFK_IDX. Dropping any of the pre-existing indexes, including the largely superfluous RESPONSE_CS_NFK_IDX, releases at least that amount of space.

Even if you are not preserving existing indexes, since you will be dropping and recreating all of the indexes, the index upgrade is a good time to replan the use of physical storage for the Response table indexes. Placing the indexes on a tablespace on a separate physical device from the Responses table is a good practice.

For more information , see:

• Regenerate Procedures and Data Extract Views
  

Regenerate Procedures and Data Extract Views

Once the Response table indexes are rebuilt, you must regenerate all procedures and data extract views for active studies. If you have disabled the generation of Version 4-style procedure and view optimization (see Enforcing Pre-Version 4.0 Optimization), you must enable it before regenerating procedures and views.

To regenerate procedures and views, follow the instructions for using the procedure and view regeneration utilities documented in Utilities For procedure regeneration you should select the parameters FULL, GENERATE and ALL.

ResponsesT (Test Database) Index Upgrade

A script, rebuild_respt_index.sql in the install directory, rebuilds the indexes on the Responsest, or test database Responses table. This script should be run whether or not you are partitioning the Responses table. The upgrade for Responsest should be done at the same time as either partitioning or the Responses table index upgrade.

RECEIVED_DCI and RECEIVED_DCM Index Upgrades

To take advantage of the Oracle index compression feature, you can optionally rebuild selected indexes on the RECEIVED_DCMs and RECEIVED_DCIs tables. Since these can be large tables, this script is not automatically run as part of the upgrade to Oracle Clinical V4. The upgrade of the RECEIVED_DCMs and RECEIVED_DCIs indexes can be done at any time that is convenient, independently of either partitioning or Responses table index upgrade. This change reduces the size of these indexes and, therefore, improves performance due to fewer disk accesses.

To rebuild the indexes on the RECEIVED_DCMs and RECEIVED_DCIs tables, execute the script oclupg32to40opt5.sql in the install directory:

SQL> @oclupg32to40opt5.sql