Rem drv: <create type="indexes" pos="system_dashboard/system_dashboard_indexes.sql+"/>
Rem
Rem $Header: beacon_indexes.sql 23-apr-2007.23:28:34 jsadras Exp $
Rem $Header: beacon_indexes.sql 23-apr-2007.23:28:34 jsadras Exp $
Rem
Rem beacon_indexes.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      beacon_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     04/23/07 - Backport jsadras_bug-5934433 from main
Rem    jsadras     04/04/07 - Bug:5964374, handle ORA_1450 on 2k Block size
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    rmarripa    03/10/05 - remove obsolete tables 
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    vjraghav    11/03/04 - Removing the PING table 
Rem    vjraghav    06/25/04 - New tables in R2 
Rem    snakai      10/13/03 - add e2e indexes for timestamp-based queries
Rem    snakai      09/25/03 - add tgt guid to primary key of e2e conn and stmt 
Rem    jriel       09/09/03 - add nodetype to e2e tables 
Rem    asawant     05/14/03 - Adding E2E_JDBC related indexes
Rem    snakai      01/02/03 - Add prim keys/indexes for e2e tables
Rem    snakai      07/16/02 - log availability changes
Rem    snakai      06/05/02 - delete beacon target
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem


Rem
Rem Indices for MGMT_BCN_TARGET table
Rem
ALTER TABLE MGMT_BCN_TARGET
   ADD CONSTRAINT bcn_tgt_primary_key
       PRIMARY KEY (target_guid, beacon_target_guid)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_AVAIL_DEF table
Rem
ALTER TABLE MGMT_BCN_AVAIL_DEF
   ADD CONSTRAINT bcn_avail_primary_key
       PRIMARY KEY (target_guid)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_AVAIL_JOB table
Rem
ALTER TABLE MGMT_BCN_AVAIL_JOB
   ADD CONSTRAINT bcn_avail_job_primary_key
       PRIMARY KEY (target_guid)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;

CREATE INDEX MGMT_BCN_AVAIL_JOB_IDX_02
  ON MGMT_BCN_AVAIL_JOB
     (insert_time);



Rem
Rem Indices for MGMT_BCN_TXN_DEFN table
Rem
ALTER TABLE MGMT_BCN_TXN_DEFN
   ADD CONSTRAINT bcn_txn_primary_key
       PRIMARY KEY (target_guid, txn_guid, txn_type, name)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;

ALTER TABLE MGMT_BCN_TXN_DEFN
   ADD CONSTRAINT bcn_txn_name_unique_key
       UNIQUE (target_guid, name)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;

Rem
Rem Indices for MGMT_BCN_STEP_DEFN table
Rem
ALTER TABLE MGMT_BCN_STEP_DEFN
   ADD CONSTRAINT bcn_txn_step_defn_primary_key
       PRIMARY KEY (target_guid, txn_guid, step_guid, step_type, name)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_STEPGROUP_DEFN table
Rem
ALTER TABLE MGMT_BCN_STEPGROUP_DEFN
   ADD CONSTRAINT bcn_group_defn_primary_key
       PRIMARY KEY (target_guid, txn_guid, stepgroup_guid, stepgroup_type, name)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_STEPGROUP_STEPS table
Rem
ALTER TABLE MGMT_BCN_STEPGROUP_STEPS
   ADD CONSTRAINT bcn_group_steps_primary_key
       PRIMARY KEY (target_guid, txn_guid, stepgroup_guid, step_guid)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_TXN_AUDIT table
Rem
ALTER TABLE MGMT_BCN_TXN_AUDIT
   ADD CONSTRAINT bcn_txn_audit_primary_key
       PRIMARY KEY (target_guid, txn_guid, audit_timestamp)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_TXN_PROPS table
Rem
ALTER TABLE MGMT_BCN_TXN_PROPS
   ADD CONSTRAINT bcn_txn_props_primary_key
       PRIMARY KEY (target_guid, txn_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_STEP_PROPS table
Rem
ALTER TABLE MGMT_BCN_STEP_PROPS
   ADD CONSTRAINT bcn_step_props_primary_key
       PRIMARY KEY (target_guid, step_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_BCNTXN_PROPS table
Rem
ALTER TABLE MGMT_BCN_BCNTXN_PROPS
   ADD CONSTRAINT bcn_bcntxn_props_primary_key
       PRIMARY KEY (target_guid, txn_guid, bcn_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_BCN_BCNSTEP_PROPS table
Rem
ALTER TABLE MGMT_BCN_BCNSTEP_PROPS
   ADD CONSTRAINT bcn_bcnstep_props_primary_key
       PRIMARY KEY (target_guid, step_guid, bcn_guid, name, string_part)
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;


Rem
Rem Indices for MGMT_ADMIN_METRIC_THRESHOLDS table
Rem
ALTER TABLE MGMT_ADMIN_METRIC_THRESHOLDS
   ADD CONSTRAINT ADMIN_METRIC_THRES_PRIM_KEY
       PRIMARY KEY (target_guid, metric_guid, coll_name, key_value);

CREATE INDEX MGMT_ADMIN_METRIC_THRES_IDX_02
  ON MGMT_ADMIN_METRIC_THRESHOLDS
     (metric_guid, key_value);


Rem
Rem Indices for MGMT_BCN_AVAIL_LOG table
Rem
CREATE INDEX MGMT_BCN_AVAIL_LOG_IDX
  ON MGMT_BCN_AVAIL_LOG
     (target_guid, severity_guid);


Rem
Rem Indexes for e2e tables
Rem

Rem
Rem Indexes for MGMT_E2E_SUMMARY
Rem
BEGIN
EXECUTE IMMEDIATE 
   'ALTER TABLE MGMT_E2E_SUMMARY
   ADD CONSTRAINT e2e_smry_primary_key
       PRIMARY KEY ( target_guid, collection_timestamp, uri, vhost )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_SUMMARY_1HOUR
Rem
BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_SUMMARY_1HOUR
   ADD CONSTRAINT e2e_smry_1hr_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, vhost )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_SUMMARY_1DAY
Rem

BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_E2E_SUMMARY_1DAY
   ADD CONSTRAINT e2e_smry_1dy_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, vhost )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_DETAILS
Rem
BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_DETAILS
   ADD CONSTRAINT e2e_deta_primary_key
       PRIMARY KEY ( target_guid, collection_timestamp, uri, node_type, 
                     node_id, key_guid, parent_key_guid, app_id, vhost )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_DETAILS_1HOUR
Rem

BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_DETAILS_1HOUR
   ADD CONSTRAINT e2e_deta_1hr_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, node_type, 
                     node_id, key_guid, parent_key_guid, app_id, vhost )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_DETAILS_1DAY
Rem

BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_DETAILS_1DAY
   ADD CONSTRAINT e2e_deta_1dy_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, node_type, 
                     node_id, key_guid, parent_key_guid, app_id, vhost )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_SQL
Rem
BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_SQL
   ADD CONSTRAINT e2e_sql_primary_key
       PRIMARY KEY ( target_guid, collection_timestamp, uri, key_guid, vhost, app_id )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_SQL_1HOUR
Rem

BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_SQL_1HOUR
   ADD CONSTRAINT e2e_sql_1hr_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, key_guid, vhost, app_id )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_SQL_1DAY
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_E2E_SQL_1DAY
   ADD CONSTRAINT e2e_sql_1_dy_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, key_guid, vhost, app_id )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_JDBC
Rem
BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_JDBC
   ADD CONSTRAINT e2e_jdbc_primary_key
       PRIMARY KEY ( target_guid, collection_timestamp, uri, key_guid, vhost, app_id )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_JDBC_1HOUR
Rem
BEGIN
EXECUTE IMMEDIATE
   'ALTER TABLE MGMT_E2E_JDBC_1HOUR
   ADD CONSTRAINT e2e_jdbc_1hr_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, key_guid, vhost, app_id )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_JDBC_1DAY
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_E2E_JDBC_1DAY
   ADD CONSTRAINT e2e_jdbc_1_dy_primary_key
       PRIMARY KEY ( target_guid, rollup_timestamp, uri, key_guid, vhost, app_id )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indexes for MGMT_E2E_SQL_STMT
Rem
ALTER TABLE MGMT_E2E_SQL_STMT
   ADD CONSTRAINT e2e_sql_stmt_primary_key
       PRIMARY KEY ( stmt_guid, part_no, target_guid )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;

CREATE INDEX MGMT_E2E_SQL_STMT_IDX01
  ON MGMT_E2E_SQL_STMT
     ( target_guid, ttl_ref )
  INITRANS 4;

Rem
Rem Indexes for MGMT_E2E_SQL_CONN
Rem
ALTER TABLE MGMT_E2E_SQL_CONN
   ADD CONSTRAINT e2e_sql_conn_primary_key
       PRIMARY KEY ( conn_guid, target_guid )
       USING INDEX STORAGE
                (FREELISTS 4)
                INITRANS 4;

CREATE INDEX MGMT_E2E_SQL_CONN_IDX01
  ON MGMT_E2E_SQL_CONN
     ( target_guid, ttl_ref )
  INITRANS 4;