Rem drv: <create type="indexes"/>
Rem
Rem $Header: eval_indexes.sql 28-jun-2005.11:06:08 chyu Exp $
Rem
Rem eval_indexes.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      eval_indexes.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    chyu        06/28/05 - New repmgr header impl 
Rem    skini       07/01/03 - blocksize changes
Rem    vchao       08/21/02 - Change mgmt_sql_evaluation, mgmt_sqlproblem_factors prim key
Rem    vchao       08/12/02 - replace sql_statistics_primary_key with sql_reuse_primary_key
Rem    vchao       08/11/02 - Add index for mgmt_sql_reuse table
Rem    yfeng       08/09/02 - Change MGMT_BAD_SQL_EVALUTION to MGMT_SQL_EVALUATION
Rem    yfeng       08/06/02 - Removing digest
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

Rem
Rem Indices for MGMT_SQL_EVALUATION table
Rem
ALTER TABLE MGMT_SQL_EVALUATION
  ADD CONSTRAINT sql_evaluation_pk
      PRIMARY KEY (target_guid, eval_timestamp,
                   reason_code, address, hash_value, plan_hash_value);

Rem
Rem Indices for MGMT_SQL_BIND_VARS table
Rem
ALTER TABLE MGMT_SQL_BIND_VARS
  ADD CONSTRAINT sql_statistics_primary_key
      PRIMARY KEY (target_guid, collection_timestamp, metric_name, digest);

CREATE INDEX MGMT_SQL_BIND_VARS_IDX_01
  ON MGMT_SQL_BIND_VARS
     (target_guid, normalized_digest);


Rem
Rem Indices for MGMT_SQLPROBLEM_FACTORS table
Rem
ALTER TABLE MGMT_SQLPROBLEM_FACTORS
  ADD CONSTRAINT sql_probfac_pk
      PRIMARY KEY (target_guid, eval_timestamp,
                   address, hash_value, plan_hash_value, reason_code, attribute);

ALTER TABLE MGMT_SQLPROBLEM_FACTORS
  ADD CONSTRAINT sql_probfac_eval_fk
      FOREIGN KEY (target_guid, eval_timestamp,
                   address, hash_value, plan_hash_value, reason_code)
      REFERENCES MGMT_SQL_EVALUATION (target_guid, eval_timestamp,
                                      address, hash_value, plan_hash_value, reason_code)
  ON DELETE CASCADE;


Rem
Rem Indices for MGMT_PLANPROBLEM_FACTORS table
Rem
ALTER TABLE MGMT_PLANPROBLEM_FACTORS
  ADD CONSTRAINT sql_plan_probfac_pk
      PRIMARY KEY (target_guid, eval_timestamp,
                   address, hash_value, plan_hash_value, id, reason_code, attribute);

ALTER TABLE MGMT_PLANPROBLEM_FACTORS
  ADD CONSTRAINT sql_plan_probfac_eval_fk
      FOREIGN KEY (target_guid, eval_timestamp,
                   address, hash_value, plan_hash_value, reason_code)
      REFERENCES MGMT_SQL_EVALUATION (target_guid, eval_timestamp,
                                      address, hash_value, plan_hash_value, reason_code)
  ON DELETE CASCADE;


Rem
Rem Indices for MGMT_SQL_REUSE table
Rem
 ALTER TABLE MGMT_SQL_REUSE
    ADD CONSTRAINT sql_reuse_primary_key
        PRIMARY KEY (target_guid, collection_timestamp);