Query Tuning Guidelines
For both partitioning and index upgrade, Oracle Clinical has been retuned to use only the primary key index RESPONSES_PK_IDX with the Response ID leading key or the concatenated RESPONSES_RDCM_NFK_IDX with the clinical_study_id and RECEIVED_DCM_ID leading keys. You should review any applications that directly access the Responses table to ensure that they are correctly optimized for the new index structure.
The basic guidelines for re-optimization are:
-
Include clinical_study_id in all access to the Responses table.
There are two reasons for this. Primarily, because Responses are in a partition based on clinical_study_id, the query optimizer can restrict its search to the proper partition if the query contains clinical_study_id. This is called 'Partition Pruning'. In order of preference this reference to clinical_study_id can be a constant, a bind variable in an equi-join, or a join from another table.
Secondly, the concatenated index is prefixed with clinical_study_id to force Responses from different studies in the same partition to be physically grouped together and to optimize certain partition accesses.
-
Redirect all previous queries on DCM_QUESTION_ID or DCM_QUESTION_GROUP_ID to use a join through RECEIVED_DCMs so that they can use the concatenated index.
Since there are no longer indexes with DCM_QUESTION_ID or DCM_QUESTION_GROUP_ID as leading keys, these are no longer efficient access paths. Much of the access involving these keys is already done in the context of a RECEIVED_DCM, so the query tuning is usually minimal. In some cases, it might be necessary to add joins to DCM_QUESTIONS or DCM_QUESTION_GROUPS, then through RECEIVED_DCMs via DCM_ID. For instance, the query shown in Examples Example 9-7 and Example 9-8 selects responses to a particular Question where the patient position is owned locally.
Example 9-7 Query Before Redirection
SELECT response_id, to_char(response_entry_ts, 'DD-MON-YYYY HH24:MI:SS'), received_dcm_id FROM responses r WHERE r.dcm_question_id = :dcm_question_id AND EXISTS (SELECT NULL FROM received_dcms rd, patient_positions papo WHERE rd.patient_position_id = papo.patient_position_id AND papo.owning_location = :current_location AND rd.received_dcm_id = r.received_dcm_id)
Example 9-8 Query After Redirection
SELECT response_id, to_char(response_entry_ts, 'DD-MON-YYYY HH24:MI:SS'), received_dcm_id FROM responses r WHERE r.dcm_question_id = :dcm_question_id AND (clinical_study_id, received_dcm_id, dcm_question_group_id) IN (SELECT rd.clinical_study_id, rd.received_dcm_id, dq.dcm_question_group_id FROM received_dcms rd, patient_positions papo, dcm_questions dq WHERE dq.dcm_question_id = :dcm_question_id AND dq.dcm_que_dcm_subset_sn = 1 AND dq.dcm_que_dcm_layout_sn = 1 AND rd.dcm_id = dq.dcm_id AND rd.patient_position_id = papo.patient_position_id AND papo.owning_location = :current_location)
Parent topic: Partitioning and Indexing