Rem drv: <create type="indexes"/>
Rem
Rem $Header: notification_indexes.sql 29-jun-2005.01:48:30 gsbhatia Exp $
Rem
Rem notification_indexes.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      notification_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    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    dcawley     01/11/05 - Update foreign key on notifyees table 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    dcawley     06/14/04 - Index for MGMT_NOTIFY_JOB_RULE_CONFIGS 
Rem    streddy     07/10/03 - Added notification history table
Rem    dcawley     05/21/03 - Add new indexes
Rem    dcawley     03/12/03 - Add new indexes on MGMT_NOTIFY_RETRY
Rem    dcawley     01/10/03 - Update indexes for MGMT_NOTIFY_SCHEDULES
Rem    dcawley     05/20/02 - Add indexes for MGMT_NOTIFY_DEVICE_ASSOC.
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem


Rem
Rem Indices for MGMT_NOTIFY_EMAIL_GATEWAY table
Rem
ALTER TABLE MGMT_NOTIFY_EMAIL_GATEWAY
   ADD CONSTRAINT MGMT_NOTIFY_EMAIL_GATEWAY_PK
       PRIMARY KEY (mail_host);


Rem
Rem Indices for MGMT_NOTIFY_PROFILES table
Rem
ALTER TABLE MGMT_NOTIFY_PROFILES
   ADD CONSTRAINT MGMT_NOTIFY_PROFILES_PK
       PRIMARY KEY (profile_name);

ALTER TABLE MGMT_NOTIFY_PROFILES
   ADD CONSTRAINT MGMT_NOTIFY_PROFILES_FK
       FOREIGN KEY (escalation_profile_name)
       REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
   ON DELETE CASCADE;


Rem
Rem Indices for MGMT_NOTIFY_DEVICES table
Rem
ALTER TABLE MGMT_NOTIFY_DEVICES
   ADD CONSTRAINT MGMT_NOTIFY_DEVICES_PK
       PRIMARY KEY (device_name, profile_name);

ALTER TABLE MGMT_NOTIFY_DEVICES
   ADD CONSTRAINT MGMT_NOTIFY_DEVICES_FK
       FOREIGN KEY (profile_name)
       REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
   ON DELETE CASCADE;

Rem
Rem Indices for MGMT_NOTIFY_DEVICE_PARAMS table
Rem
ALTER TABLE MGMT_NOTIFY_DEVICE_PARAMS
   ADD CONSTRAINT MGMT_NOTIFY_DEVICE_PARAMS_FKDN
       FOREIGN KEY (device_name,profile_name)
       REFERENCES MGMT_NOTIFY_DEVICES (device_name,profile_name)
   ON DELETE CASCADE;

CREATE INDEX MGMT_NOTIFY_DEVICE_PARAMS_IDX1
  ON MGMT_NOTIFY_DEVICE_PARAMS
     (device_name, profile_name);

Rem
Rem Indices for MGMT_NOTIFY_SCHEDULES table
Rem
ALTER TABLE MGMT_NOTIFY_SCHEDULES
   ADD CONSTRAINT MGMT_NOTIFY_SCHEDULES_PK
   PRIMARY KEY (schedule_owner, schedule_name);

ALTER TABLE MGMT_NOTIFY_SCHEDULES
   ADD CONSTRAINT MGMT_NOTIFY_SCHEDULES_FK
       FOREIGN KEY (schedule_owner)
       REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
   ON DELETE CASCADE;

Rem
Rem Indices for MGMT_NOTIFY_DEV_SCHEDULES table
Rem
ALTER TABLE MGMT_NOTIFY_DEV_SCHEDULES
   ADD CONSTRAINT MGMT_NOTIFY_DEV_SCHEDULES_PK
   PRIMARY KEY (schedule_owner, schedule_name, device_name, device_owner);

ALTER TABLE MGMT_NOTIFY_DEV_SCHEDULES
   ADD CONSTRAINT MGMT_NOTIFY_DEV_SCHEDULES_OFK
       FOREIGN KEY (schedule_owner)
       REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
   ON DELETE CASCADE;

ALTER TABLE MGMT_NOTIFY_DEV_SCHEDULES
   ADD CONSTRAINT MGMT_NOTIFY_DEV_SCHEDULES_DFK
       FOREIGN KEY (device_name, device_owner)
       REFERENCES MGMT_NOTIFY_DEVICES (device_name, profile_name)
   ON DELETE CASCADE;

Rem
Rem Indices for MGMT_NOTIFY_RULES table
Rem
ALTER TABLE MGMT_NOTIFY_RULES
   ADD CONSTRAINT MGMT_NOTIFY_RULES_PK
       PRIMARY KEY (rule_name, owner);

ALTER TABLE MGMT_NOTIFY_RULES
   ADD CONSTRAINT MGMT_NOTIFY_RULES_FK
       FOREIGN KEY (owner)
       REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
   ON DELETE CASCADE;


Rem
Rem Indices for MGMT_NOTIFY_RULE_CONFIGS table
Rem
ALTER TABLE MGMT_NOTIFY_RULE_CONFIGS
   ADD CONSTRAINT MGMT_NOTIFY_RULE_CONFIGS_FK
       FOREIGN KEY (rule_name, owner)
       REFERENCES MGMT_NOTIFY_RULES (rule_name, owner)
   ON DELETE CASCADE;

CREATE INDEX NOTIFY_RULE_CONFIGS_IDX_01
  ON MGMT_NOTIFY_RULE_CONFIGS
     (rule_name, owner, target_type);

Rem
Rem Indices for MGMT_NOTIFY_JOB_RULE_CONFIGS table
Rem
ALTER TABLE MGMT_NOTIFY_JOB_RULE_CONFIGS
   ADD CONSTRAINT MGMT_NOTIFY_JOB_RULE_CFS_FK
       FOREIGN KEY (rule_name, owner)
       REFERENCES MGMT_NOTIFY_RULES (rule_name, owner)
   ON DELETE CASCADE;
                                                                                
CREATE INDEX NOTIFY_RULE_JOB_CONFIGS_IDX_01
  ON MGMT_NOTIFY_JOB_RULE_CONFIGS
     (rule_name, owner);
                                                                                
Rem
Rem Indices for MGMT_NOTIFY_NOTIFYEES table
Rem
ALTER TABLE MGMT_NOTIFY_NOTIFYEES
   ADD CONSTRAINT MGMT_NOTIFY_NOTIFYEES_FK_RULE
       FOREIGN KEY (rule_name, owner)
       REFERENCES MGMT_NOTIFY_RULES (rule_name, owner)
   ON DELETE CASCADE;

ALTER TABLE MGMT_NOTIFY_NOTIFYEES
   ADD CONSTRAINT MGMT_NOTIFY_NOTIFYEES_FKPROF
       FOREIGN KEY (profile_name)
       REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
   ON DELETE CASCADE;


Rem
Rem Indices for MGMT_NOTIFY_REQUEUE table
Rem
ALTER TABLE MGMT_NOTIFY_REQUEUE
   ADD CONSTRAINT MGMT_NOTIFY_REQUEUE_FK
       FOREIGN KEY (device_name,device_owner)
       REFERENCES MGMT_NOTIFY_DEVICES (device_name,profile_name)
   ON DELETE CASCADE;

CREATE INDEX MGMT_NOTIFY_REQUEUE_IDX_01
  ON MGMT_NOTIFY_REQUEUE
     (last_timestamp, device_name, device_owner);

CREATE INDEX MGMT_NOTIFY_REQUEUE_IDX_02
  ON MGMT_NOTIFY_REQUEUE
     (source_guid, device_name, device_owner);

CREATE INDEX MGMT_NOTIFY_REQUEUE_IDX_03
  ON MGMT_NOTIFY_REQUEUE
     (device_name, device_owner);

Rem
Rem Indices for MGMT_NOTIFICATION_LOG table
Rem Note that we deliberately omitted PK for this table.
Rem
CREATE INDEX MGMT_NOTIFICATION_LOG_IDX_01
  ON MGMT_NOTIFICATION_LOG
    (source_obj_type, source_obj_guid, timestamp);