Partition Maintenance

In this section:

• Prospective Allocation of clinical_study_ids for Partitioning
  
• Strategy for Ongoing Partition Maintenance
  
• Instructions for Partition Maintenance
  
• Rebuild Indexes after Partition Maintenance
  
• Using Read-Only Partitions to Minimize Backup
  

Prospective Allocation of clinical_study_ids for Partitioning

1. Ensure that distributed studies do not intersperse clinical_study_ids arbitrarily.

   If you intend to collapse studies into single partitions or to consolidate dormant studies into read-only partitions, you must ensure that new studies do not get created whose clinical_study_ids lie between existing studies. This can occur in distributed installations of Oracle Clinical when studies are created at multiple locations using database seeds that differ only in their trailing digits. To address this you should either share clinical_study_id sequence across locations by replicating OCL_STUDIES and creating studies centrally or, alternatively, you should ensure that sequences do not overlap by seeding at non-overlapping starting seed values, not just separate trailing digits. For instance, start location 1 at ID 101 and location 2 at ID 1000102 rather than 102.

2. Preallocate planned studies to achieve grouping of small studies.

   To consolidate small studies in single partitions, you should make sure that they have contiguous ranges. For instance, for a new project, create placeholder planned studies for all phase 1 and phase 2 studies so that they have contiguous IDs. This allows them to be grouped into a single partition.

Strategy for Ongoing Partition Maintenance

There are three types of partition maintenance activities:

• Activities that rebuild partitions

• Activities that split or merge partitions

• Activities that change ongoing characteristics of partitions

Activities that rebuild partitions must be performed manually. These include rebuilding the indexes on a partition, moving the data in a partition to a different tablespace, and restructuring the data in a partition to reduce the number of extents or change the PCTFREE storage parameter. See the Oracle documentation for instructions on how to carry out these activities.

Oracle Clinical includes the partition mapping form (see Figure 9-2) and a utility script generator that support SPLIT and MERGE operations and changes to ongoing storage characteristics.

Note that all SQL activities are carried out while connected to the RXC account.

For more information , see:

• Rebuild Partitions
  
• Managing New Studies
  
• Reviewing for Partitions that Need Splitting or Merging
  
• Maintenance of Cost-Based Statistics
  

Rebuild Partitions

Periodically, at weekly or monthly intervals depending on growth rate, review the current partition growth by querying the USER_SEGMENTS view:

SQL> select segment_name, partition_name, extents
SQL> from user_segments 
SQL> where extents > 20 
SQL> and segment_name like 'RESPONSE%';

This shows you any partitions that might need to be rebuilt.

In addition, you might want to initiate a manual process for identifying studies that are about to enter periods of intense reporting. The partitions for these studies could then be rebuilt, both to coalesce index space by rebuilding the indexes and, perhaps, to decrease the PCTFREE storage parameter if data is no longer changing.

See Example 9-6 for instructions on rebuilding partition indexes.

Managing New Studies

Unless there is a study that will grow extremely rapidly, for instance, due to batch loading, there is no need to anticipate the creation of studies and preallocate their partitions. Since new studies are created with incrementally higher clinical_study_ids, they are automatically added to the maximum partition. As long as this partition has a storage clause with large enough space allocations, these studies can start there without significant performance impact. At planned intervals you should review this partition to determine how these studies should be partitioned and use the partition maintenance script (Instructions for Partition Maintenance) to split out the new partitions.

Use the script list_study_resp_cnt_part.sql to obtain a count by study of studies in a particular partition:

list_study_resp_cnt_part.sql  partition_name  spool_file_name

The following example lists the studies in the last partition, that is, the new ones:

SQL>@list_study_resp_cnt_part.sql RESP_CSID_LE_9999999998 new_studies.lis

Reviewing for Partitions that Need Splitting or Merging

As with new studies, existing partitions that contain multiple studies should be periodically reviewed and split as needed.

You may also want to merge partitions for studies that become inactive or that did not grow as planned. In particular, you might want to use the approach of using read-only tablespaces for inactive studies discussed in Using Read-Only Partitions to Minimize Backup.

Maintenance of Cost-Based Statistics

Whenever a partition is merged or split, or when the data volume in a partition has changed significantly, the cost-based statistics for that partition need to be refreshed. The command to refresh the statistics for a partition is:

ANALYZE TABLE Responses PARTITION (partition_name) COMPUTE STATISTICS;

If the partition is large, you can use the statistics estimation with a small sample size. However, since you do not need to analyze the whole table, but just partitions with changes, this may no longer be necessary:

SQL> analyze table responses partition (partition_name) 
SQL>  estimate statistics sample 5 percent;

One approach to maintaining the statistics is to use a table to hold the partition statistics from the previous partition maintenance and then drive the creation of the analyze statements from that table and the current statistics. The code fragment in Example 9-4 illustrates this approach. It triggers the ANALYZE when the data volume in a partition increases by more than 50%.

Example 9-4 Using a Table to Compute Statistics

CREATE TABLE resp_part AS 
  SELECT partition_name, bytes 
    FROM user_segments 
    WHERE segment_name = 'RESPONSES';

spool analyze_responses.sql

SELECT 'ANALYZE TABLE RESPONSES PARTITION ('|| 
                            u.partition_name||') COMPUTE STATISTICS'
  FROM resp_part r, user_segments u 
  WHERE u.segment_name = 'RESPONSES' 
    AND u.partition_name = r.partition_name 
    AND u.bytes/1.5 > r.bytes 
  UNION ALL 
SELECT 'ANALYZE TABLE RESPONSES PARTITION ('|| 
u.partition_name||') COMPUTE STATISTICS' 
FROM user_segments u 
WHERE u.segment_name = 'RESPONSES' 
AND not exists (select null from resp_part r 
                 where r.partition_name = u.partition_name) 

Instructions for Partition Maintenance

When you determine that you need to merge or split partitions, first use the Maintain Partition Mapping form (see Figure 9-2). Insert records for partitions that are to be split or delete records for partitions that are to be merged. You can also alter the storage clauses for partitions, although this affects only new storage allocations — it does not rebuild the existing partition space.

You then use the utility script gen_alter_partition.sql to generate the SQL partition maintenance commands.

gen_alter_partition output_file.sql

For example:

SQL> @gen_alter_partition.sql alter.sql

The generation script compares the partition definition in the Oracle Clinical Partition Mapping table with the actual partition structure in the database. It then generates the SQL DDL statements to merge or split the partitions and to alter the storage clauses (see Example 9-5).

You should review the generated script and make any necessary modifications before you run it.

Example 9-5 Sample Output from the Partition Maintenance Script

REM Split case 4: Split RESP_CSID_LE_10002201 into RESP_CSID_LE_10001901 at 10001902
ALTER TABLE responses SPLIT PARTITION RESP_CSID_LE_10002201 AT (10001902) INTO
(PARTITION RESP_CSID_LE_10001901 , PARTITION RESP_CSID_LE_10002201
STORAGE (INITIAL 4M NEXT 4M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0)
);
ALTER INDEX RESPONSE_PK_IDX MODIFY PARTITION RESP_CSID_LE_10001901
STORAGE (NEXT 2M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0)
;
ALTER INDEX RESPONSE_RDCM_NFK_IDX MODIFY PARTITION RESP_CSID_LE_10001901
STORAGE (NEXT 4M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) 
; 
REM Merge case 2: Merge RESP_CSID_LE_10001102 into RESP_CSID_LE_10001202 
ALTER TABLE responses MERGE PARTITIONS RESP_CSID_LE_10001102, 
RESP_CSID_LE_10001202 INTO PARTITION
RESP_CSID_LE_10001202; 
REM Merge case 2: Merge RESP_CSID_LE_10001101 into RESP_CSID_LE_10001202 
ALTER TABLE responses MERGE PARTITIONS RESP_CSID_LE_10001101, 
RESP_CSID_LE_10001202 INTO PARTITION  
RESP_CSID_LE_10001202;

Rebuild Indexes after Partition Maintenance

After you run the partition maintenance script (see Example 9-5), some of the associated index partitions may be unusable. Use the SQL code shown in Example 9-6 to detect the affected partitions. It generates a script (rebuild.sql) that you run to rebuild the index partitions.

Example 9-6 SQL Code for Rebuilding Index Partitions

set pagesize 1000 
set verify off 
set feedback off 
set heading off 
spool rebuild.sql 
select distinct 'ALTER TABLE RESPONSES MODIFY PARTITION '|| partition_name||' 
REBUILD UNUSABLE LOCAL INDEXES 
/' 
from all_ind_partitions 
where index_name in ('RESPONSE_PK_IDX', 'RESPONSE_RDCM_NFK_IDX') 
and status = 'UNUSABLE' 
/ 
spool off 
set verify on 
set feedback on 
set heading on 

Using Read-Only Partitions to Minimize Backup

Oracle enables you to designate tablespaces as read-only. If a tablespace is designated read-only, any attempt to modify data in that tablespace causes an Oracle error. Since the tablespace can not be modified, it can be created using data files on a separate physical device that only needs to be backed-up after maintenance activity during which the tablespace is modifiable. Over time, a significant portion of the Responses table data volume could be migrated to such read-only tablespaces, dramatically reducing the data volume that needs to be backed up by nightly backups.

To implement this strategy, you would set up the tablespace using data files on a separate physical device. A manual process of identifying studies that will no longer have modifications must be implemented. At periodic intervals, quarterly or semi-annually, you would perform partition maintenance to rebuild the partitions for those studies. During the maintenance, you alter the tablespace to make it writable. You move the data by exporting the partition, dropping the partition, and recreating the partition using the new tablespace. Once all studies have been moved, you alter the tablespace to be read-only once again and perform a backup of the device.