Rem drv: <create type="indexes" pos="emx/emx_indexes.sql+"/>
Rem
Rem $Header: gensvc_indexes.sql 19-aug-2005.16:47:40 yxie Exp $
Rem
Rem gensvc_indexes.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      gensvc_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    yxie        08/12/05 - add new job details table index 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    andyao      05/12/05 - add indices to beacon_target_guid
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    ktlaw       01/11/05 - add repmgr header 
Rem    snakai      11/11/04 - test availability 
Rem    snakai      10/04/04 - modify indexes for table changes 
Rem    snakai      09/24/04 - snakai_svc_template_1
Rem    snakai      09/21/04 - Created
Rem

----------------------------------------------------------------
-- Test Collection Update Job
----------------------------------------------------------------

ALTER TABLE MGMT_GENSVC_UPDBCN_JOB
  ADD CONSTRAINT MGMT_GENSVC_UBJOB_PK
      PRIMARY KEY ( target_guid, operation_guid, beacon_target_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

ALTER TABLE MGMT_GENSVC_UPDBCN_JOB_TESTS
  ADD CONSTRAINT MGMT_GENSVC_UBJOB_TESTS_PK
      PRIMARY KEY ( target_guid, operation_guid, test_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

----------------------------------------------------------------
-- Pending/Past Jobs Details
----------------------------------------------------------------
ALTER TABLE MGMT_GENSVC_JOBS_DETAILS
  ADD CONSTRAINT MGMT_GENSVC_JOBS_DETAILS_PK
      PRIMARY KEY ( target_guid, operation_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

----------------------------------------------------------------
-- Monitoring Templates
----------------------------------------------------------------

ALTER TABLE MGMT_GENSVC_TMPL_VARS
  ADD CONSTRAINT MGMT_GENSVC_TMPL_VARS_PK
      PRIMARY KEY (template_guid, name)
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

----------------------------------------------------------------
-- Service and Test Availabilty
----------------------------------------------------------------

ALTER TABLE MGMT_GENSVC_TEST_AVAIL
  ADD CONSTRAINT MGMT_GENSVC_TEST_AV_PK
      PRIMARY KEY ( target_guid, test_guid, 
                    start_collection_timestamp, current_status )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

ALTER TABLE MGMT_GENSVC_TEST_CUR_AVAIL
  ADD CONSTRAINT MGMT_GENSVC_TEST_CUR_AV_PK
      PRIMARY KEY ( target_guid, test_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

ALTER TABLE MGMT_GENSVC_TEST_AVAIL_MARKER
  ADD CONSTRAINT MGMT_GENSVC_TEST_AV_MRKR_PK
      PRIMARY KEY ( target_guid, test_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

ALTER TABLE MGMT_GENSVC_AVAIL_CONFIG
  ADD CONSTRAINT MGMT_GENSVC_AV_CFG_PK
      PRIMARY KEY ( target_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

ALTER TABLE MGMT_GENSVC_AVAIL_TESTS
  ADD CONSTRAINT MGMT_GENSVC_AV_TESTS_PK
      PRIMARY KEY ( target_guid, test_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

ALTER TABLE MGMT_GENSVC_AVAIL_BEACONS
  ADD CONSTRAINT MGMT_GENSVC_AV_BCNS_PK
      PRIMARY KEY ( target_guid, beacon_target_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

ALTER TABLE MGMT_GENSVC_AVAIL_JOB
  ADD CONSTRAINT MGMT_GENSVC_AV_JOB_PK
      PRIMARY KEY ( target_guid, test_guid )
      USING INDEX STORAGE
            (FREELISTS 4)
            INITRANS 4;

CREATE INDEX MGMT_GENSVC_AV_EVNTS_IDX_01
  ON MGMT_GENSVC_AVAIL_EVENTS
       ( target_guid, event_timestamp, test_guid, beacon_target_guid ) 
  INITRANS 4;

CREATE INDEX MGMT_GENSVC_AV_BCNS_IDX_01
  ON MGMT_GENSVC_AVAIL_BEACONS
       ( beacon_target_guid )
  INITRANS 4;

CREATE INDEX MGMT_GENSVC_UDB_JOB_IDX_01
  ON MGMT_GENSVC_UPDBCN_JOB
       ( beacon_target_guid )
  INITRANS 4;

CREATE INDEX MGMT_GENSVC_AV_EVNTS_IDX_02
  ON MGMT_GENSVC_AVAIL_EVENTS
       ( beacon_target_guid )
  INITRANS 4;