Rem drv: <create type="indexes" pos="user_model/user_model_indexes.sql+"/>
Rem
Rem $Header: rca_indexes.sql 29-jun-2005.01:48:41 gsbhatia Exp $
Rem
Rem rca_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      rca_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    gsbhatia    06/26/05 - New repmgr header impl 
Rem    jriel       05/12/05 - no event result duplication 
Rem    jriel       04/21/05 - add recovery table 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    jriel       11/12/04 - add event action 
Rem    jriel       09/29/04 - add net event tables 
Rem    jriel       08/30/04 - add on demand flags 
Rem    jriel       08/17/04 - remove exception table 
Rem    jriel       07/01/04 - jriel_rca1
Rem    jriel       06/14/04 - Created
Rem

rem   MGMT_RCA_EVENT

ALTER TABLE MGMT_RCA_EVENT
  ADD CONSTRAINT MGMT_RCA_EVENT_PK
      PRIMARY KEY (event_guid);
      
CREATE INDEX MGMT_RCA_EVENT_IDX_01
  ON MGMT_RCA_EVENT (event_guid, source_guid);
  
CREATE INDEX MGMT_RCA_EVENT_IDX_02
  ON MGMT_RCA_EVENT (event_guid, target_guid);
  
CREATE INDEX MGMT_RCA_EVENT_IDX_03
  ON MGMT_RCA_EVENT (target_guid);
  
CREATE INDEX MGMT_RCA_EVENT_IDX_04
  ON MGMT_RCA_EVENT (source_guid);
  
rem   MGMT_RCA_SUMMARY

ALTER TABLE MGMT_RCA_SUMMARY 
  ADD CONSTRAINT MGMT_RCA_SUMMARY_PK
      PRIMARY KEY (event_guid);

ALTER TABLE MGMT_RCA_SUMMARY
  ADD CONSTRAINT MGMT_RCA_SUMMARY_FK
      FOREIGN KEY (event_guid)
      REFERENCES MGMT_RCA_EVENT (event_guid)
  ON DELETE CASCADE;
  
CREATE INDEX MGMT_RCA_SUMMARY_IDX_01
  ON MGMT_RCA_SUMMARY (severity_guid);
  
CREATE INDEX MGMT_RCA_SUMMARY_IDX_02
  ON MGMT_RCA_SUMMARY (target_guid);
  
rem   MGMT_RCA_RUN

ALTER TABLE MGMT_RCA_RUN 
  ADD CONSTRAINT MGMT_RCA_RUN_PK
      PRIMARY KEY (event_guid, update_id);

ALTER TABLE MGMT_RCA_RUN
  ADD CONSTRAINT MGMT_RCA_RUN_FK
      FOREIGN KEY (event_guid)
      REFERENCES MGMT_RCA_EVENT (event_guid)
  ON DELETE CASCADE;

rem   MGMT_RCA_EVENT_ASSOC
      
ALTER TABLE MGMT_RCA_EVENT_ASSOC
  ADD CONSTRAINT MGMT_RCA_EVENT_ASSOC_PK
      PRIMARY KEY (event_guid, update_id, symptom_event_guid, cause_event_guid);

ALTER TABLE MGMT_RCA_EVENT_ASSOC
  ADD CONSTRAINT MGMT_RCA_EVENT_ASSOC_FK
      FOREIGN KEY (event_guid, update_id)
      REFERENCES MGMT_RCA_RUN (event_guid, update_id)
  ON DELETE CASCADE;

ALTER TABLE MGMT_RCA_EVENT_ASSOC
  ADD CONSTRAINT MGMT_RCA_EVENT_ASSOC_FK2
      FOREIGN KEY (symptom_event_guid)
      REFERENCES MGMT_RCA_EVENT (event_guid)
  ON DELETE CASCADE;

ALTER TABLE MGMT_RCA_EVENT_ASSOC
  ADD CONSTRAINT MGMT_RCA_EVENT_ASSOC_FK3
      FOREIGN KEY (cause_event_guid)
      REFERENCES MGMT_RCA_EVENT (event_guid)
  ON DELETE CASCADE;
  
CREATE INDEX MGMT_RCA_EVENT_ASSOC_IDX_01
  ON MGMT_RCA_EVENT_ASSOC (event_guid);
  
CREATE INDEX MGMT_RCA_EVENT_ASSOC_IDX_02
  ON MGMT_RCA_EVENT_ASSOC (cause_event_guid);

CREATE INDEX MGMT_RCA_EVENT_ASSOC_IDX_03
  ON MGMT_RCA_EVENT_ASSOC (symptom_event_guid);
  
rem   MGMT_RCA_EXCEPTION

--ALTER TABLE MGMT_RCA_EXCEPTION
--  ADD CONSTRAINT MGMT_RCA_EXCEPTION_PK
--      PRIMARY KEY (event_guid, update_id, exception_id);
      
--ALTER TABLE MGMT_RCA_EXCEPTION
--  ADD CONSTRAINT MGMT_RCA_EXCEPTION_FK
--      FOREIGN KEY (event_guid, update_id)
--      REFERENCES MGMT_RCA_RUN (event_guid, update_id)
---  ON DELETE CASCADE;

rem   MGMT_RCA_TRACE

ALTER TABLE MGMT_RCA_TRACE
  ADD CONSTRAINT MGMT_RCA_TRACE_PK
      PRIMARY KEY (event_guid, update_id);
      
ALTER TABLE MGMT_RCA_TRACE
  ADD CONSTRAINT MGMT_RCA_TRACE_FK
      FOREIGN KEY (event_guid, update_id)
      REFERENCES MGMT_RCA_RUN (event_guid, update_id)
  ON DELETE CASCADE;

rem   MGMT_RCA_METRIC_TEST

ALTER TABLE MGMT_RCA_METRIC_TEST
  ADD CONSTRAINT MGMT_RCA_METRIC_TEST_PK
      PRIMARY KEY (metric_test_guid);
      
CREATE INDEX MGMT_RCA_METRIC_TEST_IDX_01
  ON MGMT_RCA_METRIC_TEST (target_guid, metric_guid);
      
CREATE INDEX MGMT_RCA_METRIC_TEST_IDX_02
  ON MGMT_RCA_METRIC_TEST (scope_guid);
  
rem   MGMT_RCA_TEST_RESULT

ALTER TABLE MGMT_RCA_TEST_RESULT
  ADD CONSTRAINT MGMT_RCA_TEST_RESULT_PK
      PRIMARY KEY (result_guid);
      
rem -- can't have this constraint as a result record may exist
rem -- for a status test in which case the test_guid is the
rem -- metric_guid for the status metric
rem --
rem ALTER TABLE MGMT_RCA_TEST_RESULT
rem   ADD CONSTRAINT MGMT_RCA_TEST_RESULT_FK
rem       FOREIGN KEY (metric_test_guid)
rem       REFERENCES MGMT_RCA_METRIC_TEST (metric_test_guid)
rem   ON DELETE CASCADE;

CREATE INDEX MGMT_RCA_TEST_RESULT_IDX_01
  ON MGMT_RCA_TEST_RESULT (result_guid, collection_timestamp);

CREATE INDEX MGMT_RCA_TEST_RESULT_IDX_02
  ON MGMT_RCA_TEST_RESULT (target_guid);
  
CREATE INDEX MGMT_RCA_TEST_RESULT_IDX_03
  ON MGMT_RCA_TEST_RESULT (metric_test_guid);
  
  
CREATE INDEX MGMT_RCA_TARGET_PROPS_IDX_01
  ON MGMT_RCA_TARGET_PROPS (target_guid);
  
CREATE INDEX MGMT_RCA_METRIC_PROPS_IDX_01
  ON MGMT_RCA_METRIC_PROPS (target_guid, metric_guid);  
  
ALTER TABLE MGMT_NET_EVENTS
  ADD CONSTRAINT MGMT_NET_EVENTS_PK
      PRIMARY KEY (net_event_guid);
      
CREATE INDEX MGMT_NET_EVENTS_IDX_01
  ON MGMT_NET_EVENTS (net_event_guid, ip_address, collection_time);
  
CREATE INDEX MGMT_RCA_RECOV_IDX_01
  ON MGMT_RCA_RECOVERY (source_guid);