Planning for Partitioning
Before partitioning the Responses table you must decide how you want to partition it, both initially and on an ongoing basis. To do this, you need to understand how the partitioning is implemented and how this impacts your partition strategy.
Oracle recommends that you place all but the smallest active studies in separate partitions. Small studies that happen to have contiguous clinical_study_ids can share a single partition without impacting performance significantly. Inactive studies with contiguous clinical_study_ids can be merged into a single partition (see Using Read-Only Partitions to Minimize Backup).
Other partitioning schemes are possible and give differing degrees of performance and problem isolation benefits. One alternate approach is to segment the table into larger partitions that loosely correspond to time-slices based upon sequential clinical_study_id allocation. This approach minimizes partition maintenance and gives some problem isolation benefit, but is not likely to give much performance benefit because active studies will tend to share the same partitions.
To assist in the planning process, Oracle Clinical includes several listings and utilities to create the initial partitioning structure. A forms-based user interface is used to perform the actual partition definition prior to generating the partitioned Response table creation script.
For more information , see:
- Decide Partitions Needed
- Define Partition Structure
- Implement Partition Structure
- Generating Table and Index Creation SQL
Parent topic: Partitioning and Indexing
Decide Partitions Needed
The following SQL statement provides a listing of all studies with data, the number of responses per study, the most recent response creation in the study, and the flag that indicates whether the study is frozen:
SELECT /*+ ordered */ cs.study, cs.clinical_study_id, css.frozen_flag,
a.resp_count, a.max_date
FROM
(SELECT count(*) resp_count, clinical_study_id,
to_char(max(response_entry_ts), 'DD-MON-YYYY') max_date
FROM responses
GROUP BY clinical_study_id ORDER BY clinical_study_id) a,
clinical_studies cs,
clinical_study_states css
WHERE a.clinical_study_id = cs.clinical_study_id
AND cs.clinical_study_id = css.clinical_study_id
AND css.current_flag = 'Y'
ORDER BY clinical_study_id
Analyze the resulting list to determine which ranges of clinical_study_id can be combined into single partitions and which should be in their own partition. Among the issues to consider are:
-
Identify studies that will never have changes to their data and that are unlikely to have intensive reporting requirements in the future (see Using Read-Only Partitions to Minimize Backup).
Action: Consolidate as much as possible and locate in read-only tablespace (see Using Read-Only Partitions to Minimize Backup).
-
Identify studies that are complete, but may have continued reporting requirements.
Action: Keep in separate partitions, as appropriate by size, but allocate minimal free space. Consider placing tablespace on fast storage devices.
-
Identify contiguous ranges of small studies that will not grow to be large studies.
Action: Consider consolidating into single partitions. This is especially relevant if there are studies that were created but will never contain data.
Parent topic: Planning for Partitioning
Define Partition Structure
Once you are prepared to define the initial partition structure, you use the following script to populate the actual table:
SQL> @populate_part_map_table.sql log_file_name.log
This script populates the mapping table with one row per study with storage clauses based upon the number of responses. The storage clauses are designed to result in a range of 1 to 15 extents for a given table size.
Table 9-1 Default Storage for Oracle Clinical Responses Partitions
| Partition Size | Max Responses | Table Extent Size | Index Extent Size |
|---|---|---|---|
|
Very Small |
<10K |
256K |
128K |
|
Small |
<50K |
512K |
256K |
|
Medium |
<100K |
1M |
512K |
|
Large |
<500K |
4M |
2M |
|
Very Large |
500K+ |
16M |
8M |
Parent topic: Planning for Partitioning
Implement Partition Structure
Once you have executed the script, you can use the Maintain Partition Mapping Tables form, shown in Figure 9-2 (navigate to Admin, then Partition Admin), to implement the partitioning scheme determined above.
Note:
You always specify the maximum clinical_study_id included in a partition. The minimum is implicitly one more than the maximum of the previous partition.
You can delete rows to merge partitions. You can edit the storage clauses to reflect consolidation or to adjust the storage because you are aware of factors that affect the size, such as planned rapid growth of a partition. Bear in mind that you can always rebuild a partition later, independent of the upgrade process.
Figure 9-2 Maintain Partition Mapping Tables Window
Description of "Figure 9-2 Maintain Partition Mapping Tables Window"
Parent topic: Planning for Partitioning
Generating Table and Index Creation SQL
Once you have completed modifying the partition mapping, you use the script:
SQL> @gen_create_part_table.sql part_table_ddl.sql
to generate the partitioned table creation script and:
SQL> @gen_create_part_index.sql part_index_ddl.sql
to generate a creation script for both partitioned indexes (see Example 9-2 and Example 9-3). Note that the CREATE statement for RESPONSE_RDCM_NFK_ID uses the index tablespace but the table storage clause (see Example 9-3), because this large concatenated index is approximately 70-80% as large as the data segment while RESPONSE_PK_IDX is 40-50% as large.
Example 9-1 Sample Responses Table Creation Statement
CREATE TABLE RESPONSES ( RESPONSE_ID NUMBER(10,0) NOT NULL , RESPONSE_ENTRY_TS DATE NOT NULL , ENTERED_BY VARCHAR2(30) NOT NULL , RECEIVED_DCM_ID NUMBER(10,0) NOT NULL , DCM_QUESTION_ID NUMBER(10,0) NOT NULL , DCM_QUESTION_GROUP_ID NUMBER(10,0) NOT NULL , CLINICAL_STUDY_ID NUMBER(10,0) NOT NULL , REPEAT_SN NUMBER(3,0) NOT NULL , END_TS DATE NOT NULL , VALIDATION_STATUS VARCHAR2(3) DEFAULT 'NNN' NOT NULL , SECOND_PASS_INDICATOR VARCHAR2(1) NULL , VALUE_TEXT VARCHAR2(200) NULL , DISCREPANCY_INDICATOR VARCHAR2(1) NULL , DATA_CHANGE_REASON_TYPE_CODE VARCHAR2(15) NULL , DATA_COMMENT_TEXT VARCHAR2(200) NULL , AUDIT_COMMENT_TEXT VARCHAR2(200) NULL , EXCEPTION_VALUE_TEXT VARCHAR2(200) NULL) TABLESPACE RXC_RESP_TSPA STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) PARTITION BY RANGE (CLINICAL_STUDY_ID) ( PARTITION RESP_CSID_LE_1 VALUES LESS THAN (2) STORAGE (INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_101 VALUES LESS THAN (102) STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_1001 VALUES LESS THAN (1002) STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_1201 VALUES LESS THAN (1202) STORAGE (INITIAL 4M NEXT 4M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_10000802 VALUES LESS THAN (10000803) STORAGE (INITIAL 16M NEXT 16M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_10001202 VALUES LESS THAN (10001203) …. …. , PARTITION RESP_CSID_LE_10242201 VALUES LESS THAN (10242202) STORAGE (INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_9999999998 VALUES LESS THAN (9999999999) STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) ) ENABLE ROW MOVEMENT;
Example 9-2 Sample Responses Primary Key Index Creation Script
CREATE UNIQUE INDEX RESPONSE_PK_IDX ON RESPONSES ( RESPONSE_ID, RESPONSE_ENTRY_TS,CLINICAL_STUDY_ID) TABLESPACE RXC_RESP_IDX_TSPA COMPUTE STATISTICS NOLOGGING STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) LOCAL ( PARTITION RESP_CSID_LE_1 STORAGE (INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_101 STORAGE (INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_1001 STORAGE (INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_1201 STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) … … , PARTITION RESP_CSID_LE_10242201 STORAGE (INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_9999999998 STORAGE (INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) ) ;
Once these scripts are generated you should review them and manually make any changes that can not be made through the Maintain Partition Mapping Table form. For instance, to modify the percent free default, for the table as a whole or for particular partitions, you must currently do this by editing the generated creation script. The script generation currently ignores the partition-specific tablespace specification, so you must manually amend the scripts to add this after the partition name and before the storage clause to place a partition in a tablespace other than the one specified for the table or index as a whole.
In addition, you should amend the index creation scripts to add the clauses COMPUTE STATISTICS and NOLOGGING at the position indicated in bold in Examples Example 9-2 and Example 9-3. COMPUTE STATISTICS allows the cost-based statistics to be computed as the new indexes are created and avoids the time-consuming extra step of separately computing the statistics. NOLOGGING avoids the overhead of writing to the Oracle redo log file, which is unnecessary since the database will be backed up after the upgrade (see Step 6. Create Indexes on Partitioned Responses Table and Maintenance of Cost-Based Statistics).
Example 9-3 Sample Responses Foreign Key Index Creation Statement
CREATE INDEX RESPONSE_RDCM_NFK_IDX ON RESPONSES ( CLINICAL_STUDY_ID, RECEIVED_DCM_ID, DCM_QUESTION_GROUP_ID, DCM_QUESTION_ID, END_TS, RESPONSE_ENTRY_TS, REPEAT_SN, RESPONSE_ID, VALUE_TEXT, VALIDATION_STATUS, EXCEPTION_VALUE_TEXT, DATA_COMMENT_TEXT) TABLESPACE RXC_RESP_IDX_TSPA COMPUTE STATISTICS NOLOGGING STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) COMPRESS 6 LOCAL ( PARTITION RESP_CSID_LE_1 STORAGE (INITIAL 512K NEXT 512K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_101 STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_1001 STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_1201 STORAGE (INITIAL 4M NEXT 4M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_10000802 STORAGE (INITIAL 16M NEXT 16M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) … … , PARTITION RESP_CSID_LE_10242201 STORAGE (INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) , PARTITION RESP_CSID_LE_9999999998 STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 99 PCTINCREASE 0) ) ;
Parent topic: Planning for Partitioning