Rem drv: <create type="indexes" pos="beacon/beacon_indexes.sql+"/>
Rem
Rem $Header: chronos_indexes.sql 23-apr-2007.23:28:35 jsadras Exp $
Rem
Rem chronos_indexes.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_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    adosani     07/16/06 - Backport adosani_bug-5044097 from main 
Rem    jsadras     04/04/07 - Bug:5964374, handle ORA-1450 on 2k block size
Rem    adosani     05/08/06 - bug 5044097 - remove primary key and replace with 
Rem                           nonunique index for pr_mapping and incomp_loads tables 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    eporter     05/04/05 - Bug 4305869: Add MGMT_RT_BOOTSTRAP_TIMES index
Rem    eporter     03/29/05 - Add indexes for bootstrap tables 
Rem    eporter     02/28/05 - Add indexes for new rollup 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    eporter     01/24/05 - Move region_mappping trigger to triggers file 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    eporter     12/13/04 - Remove MGMT_RT_REGION_MEMBERS table 
Rem    eporter     11/30/04 - Bug 3849061 - Indexes for new tables 
Rem    mashukla    02/03/03 - add index on dist tables
Rem    mashukla    12/12/02 - add server timing constraints
Rem    mashukla    07/10/02 - remove cookie constraint
Rem    rpinnama    05/14/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

Rem
Rem Indices for MGMT_RT_METRICS_RAW table
Rem
ALTER TABLE MGMT_RT_METRICS_RAW
   ADD CONSTRAINT MGMT_RT_METRICS_RAW_PK
       PRIMARY KEY (target_guid, collection_timestamp, url_filename,
                      metric_name, submit_action_timestamp, visitor_ip)
   USING INDEX STORAGE
    (FREELISTS 4)
    INITRANS 4;

create index mgmt_rt_metrics_raw_ip_num_idx on mgmt_rt_metrics_raw (visitor_ip_num);

Rem
Rem Indices for MGMT_RT_URLS table
Rem
ALTER TABLE MGMT_RT_URLS
 ADD CONSTRAINT MGMT_RT_URLS_PK
  PRIMARY KEY (url_guid);

ALTER TABLE MGMT_RT_URLS
   ADD CONSTRAINT MGMT_RT_URLS_NAME_UN
       UNIQUE(target_guid, display_name);

ALTER TABLE MGMT_RT_URLS
   ADD CONSTRAINT MGMT_RT_URLS_URL_UN
       UNIQUE(target_guid, url_filename);


Rem
Rem Indices for MGMT_RT_REGIONS table
Rem
ALTER TABLE MGMT_RT_REGIONS
  ADD CONSTRAINT MGMT_RT_REGIONS_PK
  PRIMARY KEY (region_guid);

ALTER TABLE MGMT_RT_REGIONS
    ADD CONSTRAINT MGMT_RT_REGIONS_UN
    UNIQUE(target_guid, region_name);


Rem
Rem Indices for MGMT_RT_REGION_ENTRIES table
Rem
ALTER TABLE MGMT_RT_REGION_ENTRIES
    ADD CONSTRAINT MGMT_RT_REGION_ENTRIES_PK
    PRIMARY KEY (id);
ALTER TABLE MGMT_RT_REGION_ENTRIES
    ADD CONSTRAINT MGMT_RT_REGION_ENTRIES_UN_DOM
    UNIQUE (domain);

create index IDX_REGION_MIN_IP on MGMT_RT_REGION_ENTRIES (MIN_IP);

Rem
Rem Indices for MGMT_RT_REGION_MAPPING table
Rem
ALTER TABLE MGMT_RT_REGION_MAPPING
    ADD CONSTRAINT MGMT_RT_REGION_MAPPING_PK
    PRIMARY KEY (id, region_guid);

create index MGMT_RT_REGION_MAPPING_ID_IDX on MGMT_RT_REGION_MAPPING (id);

ALTER TABLE MGMT_RT_REGION_MAPPING
   ADD CONSTRAINT MGMT_RT_REGION_MAPPING_FK
       FOREIGN KEY (region_guid)
       REFERENCES MGMT_RT_REGIONS (region_guid)
     ON DELETE CASCADE;


Rem
Rem Indices for MGMT_RT_TARGET_PROPERTIES table
Rem
ALTER TABLE MGMT_RT_TARGET_PROPERTIES
   ADD CONSTRAINT RT_TARGET_PROPERTIES_PK
       PRIMARY KEY (target_guid, property_name);



Rem
Rem Indices for MGMT_RT_COOKIE_DATA table
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_COOKIE_DATA
   ADD CONSTRAINT MGMT_RT_COOKIE_DATA_PK
       PRIMARY KEY (raw_index,name,value)
    USING INDEX STORAGE
    (FREELISTS 4)
    INITRANS 4';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/


Rem
Rem Indices for MGMT_RT_URL_1HOUR table
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_URL_1HOUR
   ADD CONSTRAINT url_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    url_filename, url_link)
   USING INDEX PCTFREE 0';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/


Rem
Rem Indices for MGMT_RT_URL_1DAY table
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_URL_1DAY
   ADD CONSTRAINT url_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name, url_filename,url_link)
   USING INDEX PCTFREE 0';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/


Rem
Rem Indices for MGMT_RT_URL_DIST_1HOUR table
Rem
ALTER TABLE MGMT_RT_URL_DIST_1HOUR
   ADD CONSTRAINT url_dist_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    url_filename, num_seconds, dist_value_type)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_URL_DIST_1DAY table
Rem
ALTER TABLE MGMT_RT_URL_DIST_1DAY
   ADD CONSTRAINT url_dist_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    url_filename, num_seconds,dist_value_type)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_DOMAIN_1HOUR table
Rem
ALTER TABLE MGMT_RT_DOMAIN_1HOUR
   ADD CONSTRAINT domain_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_DOMAIN_1DAY table
Rem
ALTER TABLE MGMT_RT_DOMAIN_1DAY
   ADD CONSTRAINT domain_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_DOMAIN_DIST_1HOUR table
Rem
ALTER TABLE MGMT_RT_DOMAIN_DIST_1HOUR
   ADD CONSTRAINT domain_dist_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet, 
                    num_seconds, dist_value_type)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_DOMAIN_DIST_1DAY table
Rem
ALTER TABLE MGMT_RT_DOMAIN_DIST_1DAY
   ADD CONSTRAINT domain_dist_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet, 
                    num_seconds, dist_value_type)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_IP_1HOUR table
Rem
ALTER TABLE MGMT_RT_IP_1HOUR
   ADD CONSTRAINT ip_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name, visitor_node)
   USING INDEX
  PCTFREE 0;


Rem
Rem Indices for MGMT_RT_IP_1DAY table
Rem
ALTER TABLE MGMT_RT_IP_1DAY
   ADD CONSTRAINT ip_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name, visitor_node)
  USING INDEX
  PCTFREE 0;


Rem
Rem Indices for MGMT_RT_IP_DIST_1HOUR table
Rem
ALTER TABLE MGMT_RT_IP_DIST_1HOUR
   ADD CONSTRAINT ip_dist_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_node, num_seconds, dist_value_type)
   USING INDEX
  PCTFREE 0;


Rem
Rem Indices for MGMT_RT_IP_DIST_1DAY table
Rem
ALTER TABLE MGMT_RT_IP_DIST_1DAY
   ADD CONSTRAINT ip_dist_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_node, num_seconds, dist_value_type)
   USING INDEX
  PCTFREE 0;


Rem
Rem Indices for MGMT_RT_BOOTSTRAP_TIMES table
Rem
ALTER TABLE MGMT_RT_BOOTSTRAP_TIMES
   ADD CONSTRAINT mgmt_rt_bootstrap_times_pk
       PRIMARY KEY (target_guid)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_URL_BOOTSTRAP table
Rem

BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_URL_BOOTSTRAP
   ADD CONSTRAINT url_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    url_filename, url_link)
   USING INDEX PCTFREE 0';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indices for MGMT_RT_URL_DIST_BOOTSTRAP table
Rem
ALTER TABLE MGMT_RT_URL_DIST_BOOTSTRAP
   ADD CONSTRAINT url_dist_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    url_filename, num_seconds, dist_value_type)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_DOMAIN_BOOTSTRAP table
Rem
ALTER TABLE MGMT_RT_DOMAIN_BOOTSTRAP
   ADD CONSTRAINT domain_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_DOMAIN_DIST_BOOTSTRAP table
Rem
ALTER TABLE MGMT_RT_DOMAIN_DIST_BOOTSTRAP
   ADD CONSTRAINT domain_dist_bs_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_domain, visitor_subnet, 
                    num_seconds, dist_value_type)
   USING INDEX PCTFREE 0;


Rem
Rem Indices for MGMT_RT_IP_BOOTSTRAP table
Rem
ALTER TABLE MGMT_RT_IP_BOOTSTRAP
   ADD CONSTRAINT ip_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name, visitor_node)
   USING INDEX
  PCTFREE 0;


Rem
Rem Indices for MGMT_RT_IP_DIST_BOOTSTRAP table
Rem
ALTER TABLE MGMT_RT_IP_DIST_BOOTSTRAP
   ADD CONSTRAINT ip_dist_bootstrap_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, metric_name,
                    visitor_node, num_seconds, dist_value_type)
   USING INDEX
  PCTFREE 0;


Rem
Rem Indices for MGMT_RT_INCOMPLETE_LOADS table
Rem
CREATE INDEX inc_loads_idx ON MGMT_RT_INCOMPLETE_LOADS
   (target_guid, aggregate_hour_timestamp, page_url);


Rem
Rem Indices for MGMT_RT_INCOMPLETE_LOADS_1HOUR table
Rem
ALTER TABLE MGMT_RT_INCOMPLETE_LOADS_1HOUR
   ADD CONSTRAINT inc_loads_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url)
   USING INDEX
  PCTFREE 0;


Rem
Rem Indices for MGMT_RT_INCOMPLETE_LOADS_1DAY table
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_INCOMPLETE_LOADS_1DAY
   ADD CONSTRAINT inc_loads_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url)
   USING INDEX
  PCTFREE 0';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/


Rem
Rem Indices for MGMT_RT_PR_MAPPING table
Rem
BEGIN
EXECUTE IMMEDIATE
   'CREATE INDEX pr_mapping_idx ON MGMT_RT_PR_MAPPING
   (target_guid, aggregate_hour_timestamp, page_url, request_url)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/


Rem
Rem Indices for MGMT_RT_PR_MAPPING_1HOUR table
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_PR_MAPPING_1HOUR
   ADD CONSTRAINT pr_mapping_1hour_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url, request_url)
   USING INDEX
  PCTFREE 0';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/

Rem
Rem Indices for MGMT_RT_PR_MAPPING_1DAY table
Rem
BEGIN
EXECUTE IMMEDIATE
  'ALTER TABLE MGMT_RT_PR_MAPPING_1DAY
   ADD CONSTRAINT pr_mapping_1day_primary_key
       PRIMARY KEY (target_guid, rollup_timestamp, page_url, request_url)
   USING INDEX
  PCTFREE 0';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1450 THEN RAISE ; END IF ;
END ;
/