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:

1. 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.

2. 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)