Rem drv: <create type="indexes" pos="baselines/baselines_indexes.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/basic/basic_indexes.sql /st_emcore_10.2.0.4.2db11.2/1 2009/03/05 20:18:58 jsadras Exp $
Rem
Rem basic_indexes.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      basic_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    jsadras     02/27/09 - compress mgmt_metrics indexes
Rem    shnavane    09/10/07 - Fix bug #6403430
Rem    shnavane    09/11/07 - Backport shnavane_bug-6403430 from main
Rem    jsadras     04/23/07 - Backport jsadras_bug-5934433 from main
Rem    jsadras     04/03/07 - Bug:5934433: Capture ORA-1450
Rem    neearora    02/27/07 - bug 5714026
Rem    neearora    03/13/07 - Backport neearora_bug-5714026 from
Rem                           st_emcore_10.2.0.1.0
Rem    gsbhatia    07/13/05 - Fix repmgr header 
Rem    dcawley     06/29/05 - Remove PK on annotations 
Rem    jsadras     06/17/05 - index on mgmt_current_metrics.metric_guid 
Rem                           Bug:4289902 
Rem    rpinnama    06/06/05 - Modify targets idx02 to include target type 
Rem    rkpandey    05/17/05 - Bug-4353778: Creating new index on 
Rem                           mgmt_target_agent_assoc 
Rem    dkjain      04/15/05 - add index on mgmt_category_map 
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    scgrover    02/01/05 - compress index, 
Rem    rpinnama    01/07/05 - Add PK for mgmt_metric_versions 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    asawant     11/10/04 - Adding PK for MGMT_TARGET_TYPE_VERSIONS 
Rem    asawant     06/09/04 - Adding PK to MGMT_ALL_TARGET_PROPS 
Rem    rpinnama    07/06/04 - Change mgmt_classes to mgmt_category_classes 
Rem    rpinnama    06/21/04 - Change the index of mgmt_category map
Rem    rpinnama    06/17/04 - Add indexes for MGMT_CLASSES and 
Rem                           MGMT_CATEGORIES 
Rem    rpinnama    10/31/03 - Add index for mgmt_metadata_load
Rem    rpinnama    07/18/03 - Add agent_guid, coll_name to the PK of metric_errors
Rem    skini       07/11/03 - Add type_meta_ver to type_prop_defs table
Rem    streddy     07/10/03 - Added mgmt_annotation table
Rem    xxu         01/14/03 - index for MGMT_TARGET_TYPES and MGMT_TARGET_PROP_DEFS
Rem    vnukal      12/30/02 - Add coll_name to mgmt_metric_errors and mgmt_current_metric_errors
Rem    rpinnama    02/06/03 - Add type_meta_ver to the metrics primary key
Rem    edemembe    12/05/02 - Removing obsolete secondary indexes on high throughput tables
Rem    rpinnama    11/22/02 - Add primary key for change_agent_url
Rem    rpinnama    11/01/02 - Add support for detecting duplicate targets
Rem    njagathe    11/13/02 - Adding additional key columns
Rem    rpinnama    10/03/02 - Remove composite_key from composite key index 
Rem                           to make the index more usable
Rem    njagathe    09/25/02 - Update UNIQUE indices for MGMT_METRICS indices
Rem    xxu         09/17/02 - add primary key
Rem    edemembe    08/21/02 - Adding IOTs
Rem    edemembe    08/09/02 - Changing current metric indexes
Rem    rpinnama    07/18/02 - Change the primary key for composite_keys
Rem    njagathe    07/11/02 - Adding target_guid to composite_keys table
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

Rem
Rem Indices for MGMT_METRICS table
Rem

ALTER TABLE MGMT_METRICS
   ADD CONSTRAINT MGMT_METRICS_PK
       PRIMARY KEY (target_type, metric_name, metric_column, type_meta_ver,
                    CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3,
                    CATEGORY_PROP_4, CATEGORY_PROP_5)
       USING INDEX COMPRESS 3 ;

CREATE UNIQUE INDEX MGMT_METRICS_IDX_01
  ON MGMT_METRICS
     (metric_guid, type_meta_ver,
      CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3,
      CATEGORY_PROP_4, CATEGORY_PROP_5) ;

CREATE UNIQUE INDEX MGMT_METRICS_IDX_03
  ON MGMT_METRICS
     (target_type, metric_guid, metric_type, is_for_summary, type_meta_ver,
      CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3,
      CATEGORY_PROP_4, CATEGORY_PROP_5)
  COMPRESS 3  ;

Rem
Rem Indices for MGMT_METRIC_VERSIONS table
Rem
ALTER TABLE mgmt_metric_versions
  ADD CONSTRAINT mgmt_metric_versions_pk
    PRIMARY KEY(target_type, metric_name);


Rem
Rem Indices for MGMT_CATEGORY_CLASSES table
Rem
ALTER TABLE mgmt_category_classes
  ADD CONSTRAINT mgmt_category_classes_pk
    PRIMARY KEY(class_name);


Rem
Rem Indices for MGMT_CATEGORIES table
Rem
ALTER TABLE mgmt_categories
  ADD CONSTRAINT mgmt_categories_pk
    PRIMARY KEY(class_name, category_name);

Rem
Rem Indices for MGMT_CATEGORY_MAP table
Rem
ALTER TABLE mgmt_category_map
  ADD CONSTRAINT mgmt_category_map_pk
    PRIMARY KEY(target_type, type_meta_ver, object_guid, class_name);

CREATE INDEX mgmt_category_map_idx_01
ON mgmt_category_map(category_name, object_type, object_guid);

Rem
Rem Indices for MGMT_TARGET_TYPES table
Rem

ALTER TABLE MGMT_TARGET_TYPES
   ADD CONSTRAINT MGMT_TARGET_TYPES_PK
       PRIMARY KEY (target_type)
       USING INDEX;

Rem
Rem Indices for MGMT_TARGET_TYPE_VERSIONS table
Rem

ALTER TABLE MGMT_TARGET_TYPE_VERSIONS
   ADD CONSTRAINT MGMT_TARGET_TYPE_VERSIONS_PK
       PRIMARY KEY (target_type, type_meta_ver)
       USING INDEX;

Rem
Rem Indices for MGMT_TARGETS table
Rem

ALTER TABLE MGMT_TARGETS
   ADD CONSTRAINT MGMT_TARGETS_PK
       PRIMARY KEY (target_type, target_name)
       USING INDEX INITRANS 4;

ALTER TABLE MGMT_TARGETS
   ADD CONSTRAINT MGMT_TARGETS_IDX_01
       UNIQUE (target_guid)
       USING INDEX INITRANS 4;

CREATE INDEX MGMT_TARGETS_IDX_02
  ON MGMT_TARGETS
     (emd_url, target_type)
  INITRANS 4;

Rem
Rem Indices for MGMT_TYPE_PROPERTIES table
Rem

ALTER TABLE MGMT_TYPE_PROPERTIES
   ADD CONSTRAINT MGMT_TYPE_PROPERTIES_PK
       PRIMARY KEY (TARGET_TYPE, PROPERTY_NAME)
       USING INDEX;

CREATE INDEX MGMT_TYPE_PROPERTIES_IDX_01 ON
  MGMT_TYPE_PROPERTIES(PROPERTY_NAME);

Rem
Rem Indices for MGMT_TARGET_PROP_DEFS table
Rem

ALTER TABLE MGMT_TARGET_PROP_DEFS
   ADD CONSTRAINT MGMT_TARGET_PROP_DEFS_PK
       PRIMARY KEY (target_type, type_meta_ver, property_name, property_type)
       USING INDEX;

Rem
Rem Indices for MGMT_ALL_TARGET_PROPS table
Rem

ALTER TABLE MGMT_ALL_TARGET_PROPS
   ADD CONSTRAINT MGMT_ALL_TARGET_PROPS_PK
       PRIMARY KEY (property_name, property_type)
       USING INDEX;

Rem
Rem Indices for MGMT_TARGET_PROPERTIES table
Rem

ALTER TABLE MGMT_TARGET_PROPERTIES
   ADD CONSTRAINT MGMT_TARGET_PROPERTIES_PK
       PRIMARY KEY (target_guid, property_name, property_type)
       USING INDEX (CREATE UNIQUE INDEX MGMT_TARGET_PROPERTIES_PK 
                    ON MGMT_TARGET_PROPERTIES (target_guid, property_name, property_type)
                    COMPRESS 1);

Rem
Rem Indices for MGMT_TARGET_AGENT_ASSOC table
Rem
ALTER TABLE MGMT_TARGET_AGENT_ASSOC
   ADD CONSTRAINT MGMT_TARGET_AGENT_ASSOC_PK
       PRIMARY KEY (target_guid, agent_guid)
       USING INDEX;

Rem
Rem Indices for MGMT_TARGET_AGENT_ASSOC table
Rem

CREATE UNIQUE INDEX MGMT_TARGET_AGENT_ASSOC_UK ON MGMT_TARGET_AGENT_ASSOC (agent_guid, target_guid);

Rem
Rem Indices for MGMT_CURRENT_METRICS table
Rem

CREATE INDEX MGMT_CURRENT_METRICS_IDX_01 
ON MGMT_CURRENT_METRICS(metric_guid) ;

Rem
Rem Indices for MGMT_STRING_METRIC_HISTORY table
Rem

ALTER TABLE MGMT_STRING_METRIC_HISTORY
   ADD CONSTRAINT MGMT_STRING_METRIC_HISTORY_PK
       PRIMARY KEY (target_guid, metric_guid, key_value, collection_timestamp)
       USING INDEX (CREATE UNIQUE INDEX MGMT_STRING_METRIC_HISTORY_PK 
                    ON MGMT_STRING_METRIC_HISTORY (target_guid, metric_guid, key_value, collection_timestamp)
                    COMPRESS 3);

Rem
Rem Indices for MGMT_LONG_TEXT table
Rem

ALTER TABLE MGMT_LONG_TEXT
  ADD CONSTRAINT MGMT_LONG_TEXT_PK
      PRIMARY KEY (digest);

Rem
Rem Indices for MGMT_METRICS_COMPOSITE_KEYS table
Rem

ALTER TABLE MGMT_METRICS_COMPOSITE_KEYS
  ADD CONSTRAINT MGMT_METRICS_COMP_KEYS_PK
      PRIMARY KEY (target_guid, composite_key) USING INDEX 
      (CREATE UNIQUE INDEX MGMT_METRICS_COMP_KEYS_PK ON
      MGMT_METRICS_COMPOSITE_KEYS(target_guid, composite_key) COMPRESS 1);

CREATE INDEX MGMT_METRICS_COMP_KEYS_IDX_02
  ON MGMT_METRICS_COMPOSITE_KEYS
     (target_guid, key_part1_value,
      key_part2_value, key_part3_value,
      key_part4_value, key_part5_value);

Rem
Rem Indices for MGMT_TARGETS_DELETE table
Rem
ALTER TABLE MGMT_TARGETS_DELETE
   ADD CONSTRAINT MGMT_TARGETS_DELETE
       PRIMARY KEY (target_guid)
       USING INDEX;

Rem
Rem Indices for MGMT_DUPLICATE_TARGETS table
Rem
ALTER TABLE MGMT_DUPLICATE_TARGETS
   ADD CONSTRAINT MGMT_DUPLICATE_TARGETS_PK
       PRIMARY KEY (target_guid, duplicate_emd_url)
       USING INDEX;

Rem
Rem Indices for MGMT_CHANGE_AGENT_URL table
Rem
BEGIN
EXECUTE IMMEDIATE 
'ALTER TABLE MGMT_CHANGE_AGENT_URL
   ADD CONSTRAINT MGMT_CHANGE_AGENT_URL_PK
       PRIMARY KEY (last_emd_url, emd_url)
       USING INDEX' ;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indices for MGMT_TARGET_ROLLUP_TIMES table
Rem

ALTER TABLE MGMT_TARGET_ROLLUP_TIMES
   ADD CONSTRAINT MGMT_TARGET_ROLLUP_TIMES_PK
       PRIMARY KEY (target_guid, rollup_table_name) USING INDEX
       (CREATE UNIQUE INDEX MGMT_TARGET_ROLLUP_TIMES_PK ON MGMT_TARGET_ROLLUP_TIMES
         (target_guid, rollup_table_name) COMPRESS 1);

Rem
Rem Indices for MGMT_METRIC_ERRORS table
Rem

ALTER TABLE MGMT_METRIC_ERRORS
   ADD CONSTRAINT MGMT_METRIC_ERRORS_PK
       PRIMARY KEY (target_guid, metric_guid, coll_name, agent_guid, collection_timestamp)
       USING INDEX (CREATE UNIQUE INDEX MGMT_METRIC_ERRORS_PK ON MGMT_METRIC_ERRORS
                     (target_guid, metric_guid, coll_name, agent_guid, collection_timestamp)
                     COMPRESS 4);

Rem
Rem Indices for MGMT_CURRENT_METRIC_ERRORS table
Rem
ALTER TABLE MGMT_CURRENT_METRIC_ERRORS
   ADD CONSTRAINT MGMT_CURRENT_METRIC_ERRORS_PK
       PRIMARY KEY (target_guid, metric_guid, coll_name, agent_guid )
       USING INDEX INITRANS 4;

CREATE INDEX MGMT_CUR_ERRORS_IDX_02
  ON MGMT_CURRENT_METRIC_ERRORS
     (metric_guid)
  INITRANS 4;
  

Rem
Rem Index for annotation table
Rem
CREATE INDEX MGMT_ANNOTATION_IDX_01
  ON MGMT_ANNOTATION
     (source_obj_type, source_obj_guid, timestamp)
  INITRANS 4;

Rem
Rem Primary key for metadata sets
Rem
ALTER TABLE MGMT_METADATA_SETS 
   ADD CONSTRAINT MGMT_METADATA_SETS_PK
       PRIMARY KEY (target_type, type_meta_ver, table_name)
   USING INDEX STORAGE (FREELISTS 4) INITRANS 4;