Rem DO NOT PUT ANY HEADERS IN THIS FILE
Rem
Rem $Header: severity_indexes.sql 06-mar-2007.04:04:17 smudumba Exp $
Rem
Rem severity_indexes.sql
Rem
Rem Copyright (c) 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    smudumba    03/06/07 - Fixing bug 5758101
Rem    smudumba    03/06/07 - Created
Rem

DECLARE
    l_count  NUMBER DEFAULT 0;
BEGIN
    SELECT count(*)
    INTO l_count
    FROM all_indexes
    WHERE LOWER(index_name) = 'mgmt_current_violation_idx_05';
    
    IF (l_count > 0)
    THEN
        -- drop the index 
        EXECUTE IMMEDIATE 'DROP INDEX mgmt_current_violation_idx_05';
   END IF;

   EXECUTE IMMEDIATE
         'CREATE INDEX mgmt_current_violation_idx_05
          ON MGMT_CURRENT_VIOLATION (TARGET_GUID, VIOLATION_TYPE, VIOLATION_LEVEL)
          STORAGE (FREELISTS 4) INITRANS 4';
END;
/