Rem drv: <create type="indexes"/>
Rem
Rem $Header: severity_indexes.sql 11-jul-2007.12:15:49 rrawat Exp $
Rem
Rem severity_indexes.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_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    rrawat      06/15/07 - Bug-6122316
Rem    rrawat      07/11/07 - Backport rrawat_bug-6122316 from main
Rem    dgiaimo     04/30/07 - Changing 10.2.4.0 to 10.2.0.4
Rem    smudumba    03/06/07 - Fixing bug 5758101
Rem    bram        07/12/06 - Backport bram_fix_5105621 from main 
Rem    bram        04/17/06 - Add Index for mgmt_current_violation
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    dkjain      04/15/05 - add another index on mgmt_violations 
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    rpinnama    01/31/05 - Fix bug 4109302. Add index for violation type
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    rpinnama    09/13/04 - Remove mgmt_severity indexes 
Rem    rpinnama    06/17/04 - Add indexes for violation tables 
Rem    dcawley     07/14/03 - Remove notification_status
Rem    tjaiswal    07/09/02 - Obsolete mgmt annotations table
Rem    rpinnama    08/15/02 - Add severity code to primary key
Rem    rpinnama    05/16/02 - Fix the script.
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem


Rem
Rem Indices for MGMT_VIOLATIONS table
Rem
ALTER TABLE mgmt_violations
  ADD  CONSTRAINT mgmt_violations_pk
    PRIMARY KEY(target_guid, policy_guid, key_value, collection_timestamp, violation_level) 
      USING INDEX STORAGE (FREELISTS 4) INITRANS 4;

CREATE INDEX mgmt_violations_idx_02
  ON mgmt_violations (violation_level desc, collection_timestamp desc)
    STORAGE (FREELISTS 4) INITRANS 4;

CREATE INDEX mgmt_violations_idx_03
  ON mgmt_violations(policy_guid, key_value)
    STORAGE (FREELISTS 4) INITRANS 4;

CREATE INDEX mgmt_violations_idx_04
  ON mgmt_violations(load_timestamp)
    STORAGE (FREELISTS 4) INITRANS 4;

CREATE UNIQUE INDEX mgmt_violations_idx_05
  ON mgmt_violations (violation_guid)
    STORAGE (FREELISTS 4) INITRANS 4;

CREATE INDEX mgmt_violations_idx_06
  ON mgmt_violations (policy_guid, violation_type, collection_timestamp)
    STORAGE (FREELISTS 4) INITRANS 4;

Rem
Rem Indices for MGMT_CURRENT_VIOLATION table
Rem
ALTER TABLE mgmt_current_violation
  ADD  CONSTRAINT mgmt_current_violation_pk
    PRIMARY KEY(target_guid, policy_guid, key_value)
      USING INDEX STORAGE (FREELISTS 4) INITRANS 4;

CREATE UNIQUE INDEX mgmt_current_violation_idx_01
  ON mgmt_current_violation (violation_guid)
    STORAGE (FREELISTS 4) INITRANS 4;

CREATE INDEX mgmt_current_violation_idx_03
  ON mgmt_current_violation(policy_guid, key_value, collection_timestamp)
    STORAGE (FREELISTS 4) INITRANS 4;

CREATE INDEX mgmt_current_violation_idx_04
  ON mgmt_current_violation(violation_type, target_guid, policy_guid)
    STORAGE (FREELISTS 4) INITRANS 4;

@&EM_SQL_ROOT/core/10.2.0.4/severity/severity_indexes.sql

Rem
Rem Indices for MGMT_VIOLATION_CONTEXT table
Rem
ALTER TABLE mgmt_violation_context
  ADD  CONSTRAINT mgmt_violation_context_pk
    PRIMARY KEY(target_guid, policy_guid, key_value, collection_timestamp, column_name);

Rem
Rem Indices for MGMT_MGMT_LAST_VIOLATION table
Rem
CREATE INDEX mgmt_last_violation_idx01
  ON MGMT_LAST_VIOLATION  (policy_guid)
    STORAGE (FREELISTS 4) INITRANS 4;