Rem drv: <create type="indexes"/>
Rem
Rem $Header: ecm_indexes.sql 11-jul-2007.12:00:28 rrawat Exp $
Rem
Rem ecm_indexes.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ecm_indexes.sql 
Rem
Rem    DESCRIPTION
Rem      contains indexes for ecm tables (see ecm_tables.sql)
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    rrawat      07/11/07 - Backport rrawat_bug-5478872 from main
Rem    rrawat      01/03/07 - Bug-5478872
Rem    nitijain    09/09/05 - Creating new indexes on ecm tables 
Rem    mningomb    07/22/05 - fix for bug: 4506651 
Rem    rmadampa    07/13/05 - add index on MGMT_ECM_GEN_SNAPSHOT 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    agor        06/20/05 - remove few unwanted indexes for perf reasons 
Rem    abhalla     06/14/05 - bug fix 4260363 
Rem    asaraswa    05/17/05 - adding indexes for ECM fwk tables 
Rem    ajdsouza    05/17/05 - add inet/mac address index to mgmt_ecm_hw_nic
Rem    agor        04/28/05 - add index on mgmt_inv_summary 
Rem    groyal      02/16/05 - Comment out obsolete policy indexes 
Rem    vkhizder    02/15/05 - add ecm_snapshot and csa indexes 
Rem    agor        02/16/05 - fix 4189156 
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    ktlaw       01/11/05 - add repmgr header 
Rem    vkhizder    01/02/05 - changing os components index 
Rem    smalathe    11/26/04 - Bug#3903438: Add index on MGMT_DELTA_IDS 
Rem    asaraswa    11/09/04 - adding index on id column in mgmt_ecm_csa_failed 
Rem    ranmath     09/10/04 - Invoke Host Patching indexes script 
Rem                           (ecm_hostpatch_indexes.sql). 
Rem    agor        07/30/04 - history table for group 
Rem    agor        07/27/04 - add available searches table 
Rem    vkhizder    07/27/04 - adding mgmt_inv_file index 
Rem    vkhizder    07/14/04 - gen_snapshot indecies, csa extensions (failed table)
Rem    shuberma    11/11/03 - Adding index on the delta_time field of the 
Rem                           mgmt_delta_entries table 
Rem    shuberma    10/09/03 - history view index 
Rem    shuberma    09/25/03 - Adding indexes for MGMT_POLICY_VIOLATIONS 
Rem    shuberma    09/16/03 - renamed patch_guid to ap_guid in some tables 
Rem    shuberma    09/11/03 - Some new indexes 
Rem    vkhizder    07/28/03 - adding index on MGMT_ECM_CSA_GENERAL_INFO for faster locating of 
Rem                           snapshots with the same identifying information for their targets
Rem    shuberma    08/05/03 - Remove unique constraint on index 
Rem    shuberma    07/28/03 - Adding index on Policy Violations, rule_id
Rem    shuberma    07/23/03 - Adding some indexes to support the fks of advisory tables
Rem    shuberma    07/17/03 - Adding some indexes for the critical patch queries
Rem    shuberma    07/08/03 - No index > 2000 bytes
Rem    vkhizder    06/26/03 - create CSA indecies
Rem    rmenon      06/14/03 - indexes changed because I changed name of table
Rem                mgmt_comp_id_to_job_id_map to
Rem                mgmt_comp_result_to_job_map and also replaced
Rem                column job_guid withg execution_id
Rem    rmenon      06/10/03 - adding index for mgmt_delta_comp_properties -
Rem                           since I had to drop the primary key constraint
Rem                           on it( on delta_comp_guid, name). also adding index for the new table
Rem                           mgmt_comp_id_to_job_id_map
Rem    shuberma    06/10/03 - Adding some indexes to support FK cascade
Rem    shuberma    04/23/03 - 
Rem    shuberma    04/23/03 - Correcting some inventory indexes
Rem    shuberma    04/15/03 - Removing some unused stuff
Rem    shuberma    01/20/03 - add indexes to support recent changes in delta_snap table
Rem    shuberma    12/11/02 - Adding indexes on Comparison tables
Rem    shuberma    10/25/02 - Adding recommended indexes to improve comparison performance
Rem    agor        09/06/02 - sql stuff
Rem    shuberma    08/23/02 - Changing the name of snap_guid to delta_guid for delta stuff
Rem    agor        08/30/02 - added index for loaded files table
Rem    rmenon      06/17/02 - added indexes for new tables (for NIC info and FS mount info)
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem


Rem
Rem Indices for MGMT_ECM_SNAPSHOT table
Rem
CREATE UNIQUE INDEX MGMT_ECM_SNAP_GUID_IDX
    ON MGMT_ECM_SNAPSHOT( SNAPSHOT_GUID,
                          IS_CURRENT,
                          TARGET_NAME);

CREATE INDEX MGMT_ECM_SNAP_IDX
    ON MGMT_ECM_SNAPSHOT(SNAPSHOT_TYPE, TARGET_NAME,
    TARGET_TYPE) COMPRESS 1 ;

CREATE INDEX MGMT_ECM_SNAP_IDX2
    ON MGMT_ECM_SNAPSHOT(IS_CURRENT, TARGET_TYPE,  
    SNAPSHOT_TYPE, TARGET_NAME) COMPRESS 1;

Rem
Rem Indices for MGMT_INV_SUMMARY table
Rem
CREATE INDEX MGMT_INV_SUMMARY_IDX
    ON MGMT_INV_SUMMARY(SNAPSHOT_GUID, MAP_TARGET_TYPE) COMPRESS 2;

Rem
Rem Indices for MGMT_ECM_LOADED_FILES table
Rem
CREATE INDEX MGMT_ECM_LADED_FILES_IDX
    ON MGMT_ECM_LOADED_FILES(SESSION_ID);


Rem
Rem Indices for MGMT_INV_COMPONENT table
Rem
CREATE UNIQUE INDEX MGMT_INV_COM_CONT_IDX
    ON MGMT_INV_COMPONENT(CONTAINER_GUID, NAME, VERSION)
    COMPRESS 1;

CREATE INDEX MGMT_INV_COMPONENT_IDX_1 
    ON MGMT_INV_COMPONENT  
(
  CONTAINER_GUID, 
  COMPONENT_GUID,
  NAME,
  VERSION
) COMPRESS 1;

Rem
Rem Indexes for MGMT_INV_COMPONENT_PATCH table
Rem
Rem This first one is to facilitate Cascade deletes when the cascade
Rem comes from the Patch table.
Rem
CREATE INDEX MGMT_INV_COMP_PATCH_PIDX 
    ON MGMT_INV_COMPONENT_PATCH  (PATCH_GUID) ;
    
Rem
Rem Indexes for MGMT_INV_PATCH_FIXED_BUG table
Rem
Rem This first one is to facilitate Cascade deletes when the cascade
Rem comes from the Patch table.
Rem
CREATE INDEX MGMT_INV_PATCH_FIXED_BUG_CIDX
    ON MGMT_INV_PATCH_FIXED_BUG  (COMPONENT_GUID, BUG_NUMBER) ;

Rem
Rem Indexes for MGMT_INV_VERSIONED_PATCH table
Rem
Rem This first one is to facilitate Cascade deletes when the cascade
Rem comes from the Patch table.
Rem
CREATE INDEX MGMT_INV_VERSIONED_PATCH_PIDX
    ON MGMT_INV_VERSIONED_PATCH  (PATCHSET_GUID) 
    COMPRESS 1;

Rem
Rem Indexes for MGMT_INV_FILE table
Rem
Rem This first one is to facilitate Cascade deletes when the cascade
Rem comes from the Patch table.
Rem
CREATE INDEX MGMT_INV_FILE_LAST_PATCH_IDX 
    ON MGMT_INV_FILE (LAST_PATCH_GUID);

Rem
Rem Indices for MGMT_INV_DEPENDENCY_RULE table
Rem
CREATE INDEX MGMT_INV_DEP_ER_IDX
    ON MGMT_INV_DEPENDENCY_RULE(REFERENCER_GUID, DEPENDEE_GUID)
    COMPRESS 1;

Rem
Rem Indices for MGMT_INV_PATCHSET table
Rem
CREATE INDEX MGMT_INV_PATCHSET_CONT
    ON MGMT_INV_PATCHSET(CONTAINER_GUID);


Rem
Rem Indices for MGMT_INV_PATCH table
Rem
CREATE INDEX MGMT_INV_PATCH_CONT_IDX
    ON MGMT_INV_PATCH(CONTAINER_GUID);



Rem
Rem Indices for MGMT_ARU_FAMILY_PRODUCT_MAP table
Rem
CREATE INDEX MGMT_ARU_FAM_PRD_PRD_IDX
    ON MGMT_ARU_FAMILY_PRODUCT_MAP(PRODUCT_ID);


Rem
Rem Indices for MGMT_ARU_PRODUCT_RELEASE_MAP table
Rem
CREATE INDEX MGMT_ARU_PRD_RLS_RLS_IDX
    ON MGMT_ARU_PRODUCT_RELEASE_MAP(RELEASE_ID);


Rem
Rem Indices for MGMT_BUG_PATCH_FIXES_BUG table
Rem
CREATE INDEX MGMT_BUG_PATCH_FIXES_BUG_IDX
    ON MGMT_BUG_PATCH_FIXES_BUG(BUG_NUMBER);


Rem
Rem Indices for MGMT_BUG_FIX_APPLICABLE_COMP_LIST table
Rem
CREATE INDEX MGMT_BUG_FIX_APPLIC_CL_IDX
    ON MGMT_BUG_FIX_APPLIC_COMP_LIST(AP_GUID, BUG_NUMBER);


Rem
Rem Indices for MGMT_BUG_ADV_HOME_PATCH table
Rem
CREATE INDEX MGMT_BUG_ADV_HOME_PATCH_IDX
    ON MGMT_BUG_ADV_HOME_PATCH(TARGET_GUID);


Rem
Rem Indices for MGMT_CPF_METRIC_SOURCE table
Rem
CREATE INDEX MGMT_CPF_METRIC_SOURCE_IDX
    ON MGMT_CPF_METRIC_SOURCE(TARGET_GUID);


Rem
Rem Indices for MGMT_BUG_ADV_HOME_PATCH table
Rem
CREATE INDEX MGMT_BUG_ADVISORY_BUG_IDX
    ON MGMT_BUG_ADVISORY_BUG(ADVISORY_NAME);


Rem
Rem Indices for MGMT_DELTA_IDS table
Rem
CREATE UNIQUE INDEX MGMT_DELTA_IDS_IDX
    ON MGMT_DELTA_IDS(COLLECTION_TYPE, KEY_STRING )
    COMPRESS 1;
CREATE INDEX MGMT_DELTA_IDS_TIME_IDX
    ON MGMT_DELTA_IDS(LAST_UPDATED_TIME);

Rem
Rem Indices for MGMT_DELTA_ENTRY table
Rem

Rem Removing following index as is causing storage issues: see bug 4338052
Rem CREATE UNIQUE INDEX MGMT_DELTA_PREV_DE
Rem    ON MGMT_DELTA_ENTRY(PREV_DELTA_ENTRY_GUID);

CREATE INDEX MGMT_DELTA_ENTRY_ROWGUID_IDX
    ON MGMT_DELTA_ENTRY(ROW_GUID)
    COMPRESS 1;
CREATE INDEX MGMT_DELTA_ENTRY_SHOULD_BE_UK
    ON MGMT_DELTA_ENTRY(DELTA_GUID, ROW_GUID, DELTA_TIME)
    COMPRESS 1;
CREATE INDEX MGMT_DELTA_ENTRY_TIME_IDX
    ON MGMT_DELTA_ENTRY(DELTA_TIME)
    COMPRESS 1;


Rem
Rem Indices for MGMT_DELTA_ENTRY_VALUES table
Rem

Rem Removing following index as is causing storage issues: see bug 4338052
Rem CREATE INDEX MGMT_DELTA_VALUES_IDX
Rem     ON MGMT_DELTA_ENTRY_VALUES( NAME );


Rem
Rem Indices for MGMT_DELTA_ID_VALUES table
Rem

Rem Removing following index as is causing storage issues: see bug 4338052
Rem CREATE INDEX MGMT_DELTA_ID_VALUES_IDX
Rem    ON MGMT_DELTA_ID_VALUES( NAME );

Rem
Rem Indices for MGMT_DELTA_SNAP table
Rem
CREATE UNIQUE INDEX MGMT_DELTA_SNAP_IDX
    ON MGMT_DELTA_SNAP(TRANSACTION_ID);

CREATE INDEX MGMT_DELTA_SNAP_NEW_TIME_IDX
    ON MGMT_DELTA_SNAP( NEW_LEFT_TIMESTAMP, NEW_LEFT_TARGET_NAME, TARGET_TYPE );

CREATE INDEX MGMT_DELTA_SNAP_IDX2
    ON MGMT_DELTA_SNAP( SNAPSHOT_TYPE, TARGET_TYPE, NEW_LEFT_TARGET_NAME )
    COMPRESS 3;

CREATE INDEX MGMT_DELTA_SNAP_IDX3 
    ON MGMT_DELTA_SNAP( TARGET_TYPE, SNAPSHOT_TYPE, DELTA_TYPE, DELTA_GUID );


Rem
Rem Indices for mgmt_hc_cpu_details table
Rem
create index mgmt_hc_cpu_details_idx
    on mgmt_hc_cpu_details(snapshot_guid)
    COMPRESS 1;


Rem
Rem Indices for mgmt_hc_iocard_details table
Rem
create index mgmt_hc_iocard_details_idx
    on mgmt_hc_iocard_details(snapshot_guid)
    COMPRESS 1;

Rem
Rem Indices for mgmt_hc_nic_details table
Rem
create index mgmt_hc_nic_details_idx
    on mgmt_hc_nic_details(snapshot_guid)
    COMPRESS 1;

Rem
Rem Indices for mgmt_hc_os_components table
Rem
create index mgmt_hc_os_components_idx1
    on mgmt_hc_os_components(snapshot_guid, type, name)
    COMPRESS 1;

Rem
Rem Indices for mgmt_hc_fs_mount_details table
Rem
create index mgmt_hc_fs_mount_details_idx1
    on mgmt_hc_fs_mount_details(snapshot_guid);

Rem
Rem Indices for mgmt_hc_vendor_sw_summary table
Rem
create index mgmt_hc_vendor_sw_summary_idx
    on mgmt_hc_vendor_sw_summary(snapshot_guid)
    COMPRESS 1;


Rem
Rem Indices for mgmt_hc_vendor_sw_components table
Rem
create index mgmt_hc_vendor_sw_comps_idx
    on mgmt_hc_vendor_sw_components(vendor_software_guid)
    COMPRESS 1;


rem
rem Index for MGMT_DELTA_COMPARISON_DELTAS.  Need to access deltas in a comparison 
rem by the collection type and status.
rem
CREATE INDEX MGMT_DELTA_COMP_DELTA_IDX
  ON MGMT_DELTA_COMPARISON_DELTAS( DELTA_COMP_GUID, COLLECTION_TYPE, STATUS );

rem
rem Index for MGMT_DELTA_COMPARISON_DELTAS.  Need to access deltas in a comparison 
rem by the collection type and a subset of key columns.  This will enable fast 
rem join to get the set of unique key_guids.  Good for drilling down from parent
rem row to get child rows.
rem
rem NOTE: VALUE was removed from this because of 2K block size database
rem limit on index size.
CREATE INDEX MGMT_DELTA_COMP_KEY_IDX
  ON MGMT_DELTA_COMP_KEY_COLS( DELTA_COMP_GUID, COLLECTION_TYPE, NAME );
  
rem
rem Index for MGMT_DELTA_COMP_PROPERTIES.  Its primary key has been dropped
rem since the original combination is no longer unique. Hence creating index
rem on the same column
rem
CREATE INDEX MGMT_DELTA_COMP_PROP_IDX
  ON MGMT_DELTA_COMP_PROPERTIES( DELTA_COMP_GUID, NAME );
  
rem
rem Index for MGMT_COMP_RESULT_TO_JOB_MAP.  Its select involve 
rem execution_id and delta_comp_guid
rem

CREATE INDEX MGMT_COMP_RESULT_TO_JOB_IDX
  ON MGMT_COMP_RESULT_TO_JOB_MAP( EXECUTION_ID, DELTA_COMP_GUID);

rem
rem Index for MGMT_COMP_RESULT_TO_JOB_MAP.  This is because 
rem delta_comp_guid column is a foreign key and al foreign keys 
rem MUST be indexed to avoid potential deadlocks during 
rem delete/updates of parent table.
rem

CREATE INDEX MGMT_COMP_RESULT_TO_JOB_IDX1
  ON MGMT_COMP_RESULT_TO_JOB_MAP( DELTA_COMP_GUID);


rem -------------------------------------------------------------------------------
rem   The following indexes are obsolete with GC Rel 2.  They had been used to
rem   support the Version 1 policy framework; that is, the framework prior to
rem   the metric/policy unification effort.
rem

/*
Rem
Rem Indices for MGMT_POLICY_VIOLATIONS table
Rem
CREATE INDEX MGMT_POLICY_VIOLATIONS_IDX1
    ON MGMT_POLICY_VIOLATIONS(RULE_ID, POLICY_ID, TARGET_GUID )
    COMPRESS 2;

CREATE INDEX MGMT_POLICY_VIOLATIONS_IDX2
    ON MGMT_POLICY_VIOLATIONS(TARGET_GUID, RULE_ID )
    COMPRESS 1;

*/
  

rem ********************** CSA INDECIES (BEGIN) **************************

CREATE INDEX MGMT_ECM_CSA_COOKIES_IDX
  ON MGMT_ECM_CSA_COOKIES( ECM_SNAPSHOT_ID );

CREATE INDEX MGMT_ECM_CSA_GENERAL_INFO_IDX 
  ON MGMT_ECM_CSA_GENERAL_INFO( TARGET_ID_METHOD, OS_USER_NAME, 
                                BOOT_DISK_VOLUME_SERIAL_NUM,
                                HOSTNAME, DOMAIN ); 

CREATE INDEX MGMT_ECM_CSA_GENERAL_INFO_IDX2
  ON MGMT_ECM_CSA_GENERAL_INFO( APPID );

-- should this one be on all columns due to the presentation view with COUNT(*)
-- what about host config index then?
CREATE INDEX MGMT_ECM_HW_CPU_IDX
  ON MGMT_ECM_HW_CPU( ECM_SNAPSHOT_ID );

-- should this one be on all columns due to the presentation view with COUNT(*)
CREATE INDEX MGMT_ECM_HW_IOCARD_IDX
  ON MGMT_ECM_HW_IOCARD( ECM_SNAPSHOT_ID );

-- should this one be on all columns due to the presentation view with COUNT(*)
CREATE INDEX MGMT_ECM_HW_NIC_IDX
  ON MGMT_ECM_HW_NIC( ECM_SNAPSHOT_ID );

-- the inet and mac address index on the hw_nic table will speed storage post processing
-- look up of this table
CREATE INDEX MGMT_ECM_HW_NIC_IDX2
  ON MGMT_ECM_HW_NIC( INET_ADDRESS );

CREATE INDEX MGMT_ECM_HW_NIC_IDX3
  ON MGMT_ECM_HW_NIC( MAC_ADDRESS );

CREATE INDEX MGMT_ECM_OS_COMPONENT_IDX
  ON MGMT_ECM_OS_COMPONENT( ECM_SNAPSHOT_ID, NAME, TYPE );

CREATE INDEX MGMT_ECM_OS_FILESYSTEM_IDX
  ON MGMT_ECM_OS_FILESYSTEM( ECM_SNAPSHOT_ID, RESOURCE_NAME, 
                             MOUNT_LOCATION, TYPE );

CREATE INDEX MGMT_ECM_OS_REG_SW_IDX
  ON MGMT_ECM_OS_REGISTERED_SW( ECM_SNAPSHOT_ID, NAME, VENDOR_NAME, 
                                VERSION, INSTALLED_LOCATION );

CREATE INDEX MGMT_ECM_OS_REG_SW_COMP_IDX
  ON MGMT_ECM_OS_REGISTERED_SW_COMP( ECM_SNAPSHOT_ID, ID );

CREATE UNIQUE INDEX MGMT_ECM_CSA_APPID_TARGET_IDX
  ON MGMT_ECM_CSA_APPID_TARGET_MAP(TARGET_GUID, APPID);

CREATE INDEX MGMT_ECM_CSA_FAILED_TS_IDX
  ON MGMT_ECM_CSA_FAILED( SAVED_TIMESTAMP );

CREATE INDEX MGMT_ECM_CSA_FAILED_IDX2
  ON MGMT_ECM_CSA_FAILED( APPID );

CREATE INDEX MGMT_ECM_CSA_FAILED_IDX3
  ON MGMT_ECM_CSA_FAILED( ID );

rem ********************** CSA INDECIES (END) **************************

CREATE INDEX MGMT_ECM_GEN_SNAP_IDX
  ON MGMT_ECM_GEN_SNAPSHOT( TARGET_TYPE, SNAPSHOT_TYPE, 
                            IS_CURRENT,  TARGET_NAME );

CREATE INDEX MGMT_ECM_GEN_SNAP_IDX2
  ON MGMT_ECM_GEN_SNAPSHOT( IS_CURRENT, TARGET_GUID, SNAPSHOT_TYPE );

  --Although idx MGMT_ECM_GEN_SNAP_IDX2(IS_CURRENT, TARGET_GUID,
  --SNAPSHOT_TYPE) existed, did not want to modify it by swapping
  --TARGET_GUID and SNAPSHOT_TYPE as it would affect queries using
  --only IS_CURRENT and TARGET_GUID columns. Hence creating a new
  --index on IS_CURRENT and SNAPSHOT_TYPE columns.
CREATE INDEX MGMT_ECM_GEN_SNAP_IDX3
  ON MGMT_ECM_GEN_SNAPSHOT( IS_CURRENT, SNAPSHOT_TYPE );

CREATE INDEX MGMT_ECM_SAVEDHC_IDX
  ON MGMT_ECM_SAVEDHOSTCONFIG( TARGET_GUID );



rem ********************** ECM FRAMEWORK INDICES (BEGIN) **************************

CREATE INDEX SMHT_FK
  ON MGMT_ECM_MD_HIST_TBLS (METADATA_ID, PARENT_TABLE_NAME);

CREATE INDEX SMHT_IDX1
  ON MGMT_ECM_MD_HIST_TBLS(NUM_HIST_UI_KEYS, TARGET_TYPE, SNAPSHOT_TYPE, NAME);


CREATE INDEX SMT_FK
  ON MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, PARENT_TABLE_NAME);

CREATE INDEX SMC_IDX2
  ON MGMT_ECM_SNAPSHOT_MD_COLUMNS (IS_KEY, METADATA_ID, TABLE_NAME, HISTORY_ON, COMPARE_ON, UI_ON);

CREATE INDEX MDAT_IDX1
  ON MGMT_ECM_MD_ALL_TBL_COLUMNS (HISTORY_UI_ON, IS_KEY, HISTORY_ON);

CREATE INDEX MDAT_IDX2
  ON MGMT_ECM_MD_ALL_TBL_COLUMNS (TARGET_TYPE, SNAPSHOT_TYPE, TABLE_NAME, NAME, HISTORY_UI_ON);

rem ************************* ECM FWK INDICES (END)

rem ************************* ECM INVENTORY INDICES (BEGIN) **********************

CREATE INDEX MGMT_ECM_IDX_TOP_LEVEL
 ON MGMT_INV_COMPONENT ( IS_TOP_LEVEL );

CREATE INDEX MGMT_ECM_IDX_PATCHSET_VERSION
 ON MGMT_INV_VERSIONED_PATCH ( VERSION );

CREATE INDEX MGMT_ECM_IDX_PLAT_NAME
 ON MGMT_ARU_PLATFORMS( PLATFORM_NAME );


rem ************************* ECM INVENTORY INDICES (END) **********************

rem Create Host Patching indexes.
@&EM_SQL_ROOT/core/latest/ecm/ecm_hostpatch_indexes.sql