Rem drv: <create type="indexes"/>
Rem
Rem $Header: jobs_indexes.sql 09-oct-2006.14:51:41 rdabbott Exp $
Rem
Rem jobs_indexes.sql
Rem
Rem Copyright (c) 2002, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_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    rdabbott    09/06/06 - fix bug 5478937: add index
Rem    rdabbott    10/09/06 - Backport rdabbott_bug-5478937 from main
Rem    kmanicka    08/01/06 - Backport kmanicka_bug-5223528 from main
Rem    kmanicka    07/20/06 - add initrans 4 to MGMT_JOB_QUEUE_IDX01
Rem    kmanicka    07/20/06 - Backport kmanicka_bug-4967469 from main
Rem    kmanicka    07/13/06 - add index MGMT_JOB_EXEC_SUMM_IDX06 & MGMT_JOB_EXT_TARGET_IDX02
Rem    skini       09/08/05 - Add index on target_guid for exec_cred_info 
Rem                           table 
Rem    skini       08/30/05 - Add index on MGMT_JOB_STATECHANGES table 
Rem    skini       08/04/05 - Remove target guid from nested job creds 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    dsahrawa    06/03/05 - fix for bug 4408128, add command_type to 
Rem                           mgmt_job_exec_idx01 
Rem    pkantawa    06/02/05 - Fix 4305820: Add indexes 
Rem    jaysmith    05/12/05 - indexes for console homepage queries 
Rem    scgrover    02/16/05 - reorder index keys 
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 indexes 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    skini       10/08/04 - Versioning changes 
Rem    skini       07/16/04 - Create index for MGMT_JOB_STATE_CHANGES 
Rem    sgrover     12/02/03 - add index on mgmt_job_exec_summary 
Rem    asaraswa    11/19/03 - rolling back previous change; index isn't needed
Rem    asaraswa    11/14/03 - adding index on mgmt_job_exec_summary to fix bug 
Rem    rdabbott    11/13/03 - add ; 
Rem    rdabbott    11/07/03 - add jb's indexes for run query perf 
Rem    skini       10/01/03 - Add unique index on queue name 
Rem    skini       08/22/03 - Remove paramsrc indexes 
Rem    skini       07/16/03 - Job library support
Rem    skini       06/18/03 - Perf indexes
Rem    skini       06/11/03 - Index foreign keys
Rem    skini       06/08/03 - Fix bug 2990184: add index on parent step id
Rem    skini       09/13/02 - Add lock indexes
Rem    rpinnama    07/18/02 - Add index on parent_step_id for mgmt_job_history table
Rem    skini       07/02/02 - Remove constraints
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem


Rem
Rem Indexes for MGMT_JOB table
Rem
CREATE INDEX MGMT_JOB_IDX01 ON MGMT_JOB(job_name, job_owner, is_library)
       STORAGE (INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_IDX02 ON MGMT_JOB(job_type, job_type_major_version)
       STORAGE (INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_IDX03 ON MGMT_JOB(is_corrective_action, broken)
   COMPRESS 2;

REM
REM Index for MGMT_JOB_EXEC_SUMMARY
REM 
CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX01 ON
   MGMT_JOB_EXEC_SUMMARY(source_execution_id)
   STORAGE(INITIAL 64K NEXT 64K);


CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX02 ON
   MGMT_JOB_EXEC_SUMMARY(job_id)
   STORAGE(INITIAL 64K NEXT 64K);


CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX04 ON
   MGMT_JOB_EXEC_SUMMARY(status, suspend_timeout)
   STORAGE(INITIAL 64K NEXT 64K);


CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX05 ON
   MGMT_JOB_EXEC_SUMMARY(status, status_bucket, scheduled_time)
   COMPRESS 2;

CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX06 ON
   MGMT_JOB_EXEC_SUMMARY(deleted_target_guid)
   STORAGE(INITIAL 64K NEXT 64K);

Rem
Rem Indexes for MGMT_JOB_PARAMETER table
Rem
CREATE INDEX MGMT_JOB_PARAMETER_IDX01 ON
   MGMT_JOB_PARAMETER(execution_id)
   STORAGE(INITIAL 64K NEXT 64K)
   COMPRESS 1; 

Rem
Rem Indexes for MGMT_JOB_HISTORY table
Rem
CREATE INDEX MGMT_JOB_HIST_IDX01 ON
   MGMT_JOB_HISTORY(execution_id)
   STORAGE(INITIAL 64K NEXT 64K); 

CREATE INDEX MGMT_JOB_HIST_IDX02 ON
   MGMT_JOB_HISTORY(parent_step_id)
   STORAGE(INITIAL 64K NEXT 64K); 


Rem
Rem Indexes for MGMT_JOB_EXECUTION table
Rem
CREATE INDEX MGMT_JOB_EXEC_IDX01 ON
  MGMT_JOB_EXECUTION(step_type, command_type, step_status, start_time)
  STORAGE(INITIAL 64K NEXT 64K); 

CREATE INDEX MGMT_JOB_EXEC_IDX02 ON
   MGMT_JOB_EXECUTION(execution_id)
   STORAGE(INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_EXEC_IDX03 ON
   MGMT_JOB_EXECUTION(parent_step_id)
   STORAGE(INITIAL 64K NEXT 64K); 

CREATE INDEX MGMT_JOB_EXEC_IDX04 ON
   MGMT_JOB_EXECUTION(start_time, step_status, emd_url)
   STORAGE(INITIAL 64K NEXT 64K);

REM
REM Indexes for MGMT_JOB_TARGET
REM
CREATE INDEX MGMT_JOB_TARGET_IDX01 ON
    MGMT_JOB_TARGET(job_id, execution_id, target_list_index, target_guid);

create index MGMT_JOB_TARGET_IDX02 on MGMT_JOB_TARGET
   (target_GUID, execution_ID, job_ID);

create index MGMT_JOB_TARGET_IDX03 on MGMT_JOB_TARGET
   (execution_ID);

Rem
Rem Indexes for MGMT_JOB_STEP_COMMAND_LOG table
Rem
CREATE INDEX MGMT_JOB_COMMAND_LOG_IDX01 ON 
   MGMT_JOB_STEP_COMMAND_LOG(step_id)
   STORAGE (INITIAL 64K NEXT 64K);

Rem
Rem Indexes for MGMT_JOB_EMD_STATUS_QUEUE table
Rem
rem This will be useful if a lot of updowns occur at the same time...   
CREATE INDEX MGMT_JOB_EMD_QUEUE_IDX01 ON
       MGMT_JOB_EMD_STATUS_QUEUE(occur_time);

Rem
Rem Indexes for MGMT_JOB_PURGE_CRITERIA table
Rem
ALTER TABLE MGMT_JOB_PURGE_CRITERIA ADD CONSTRAINT 
      FK_JOB_PURGECRIT_PNAME FOREIGN KEY(policy_name) REFERENCES
          MGMT_JOB_PURGE_POLICIES(policy_name) ON DELETE CASCADE;

CREATE INDEX MGMT_JOB_PURGE_CRIT_IDX01 ON
       MGMT_JOB_PURGE_CRITERIA(policy_name);


Rem
Rem Indexes for MGMT_JOB_PURGE_TARGETS table
Rem
ALTER TABLE MGMT_JOB_PURGE_TARGETS ADD CONSTRAINT 
      FK_JOB_PURGETGT_PNAME FOREIGN KEY(policy_name) REFERENCES
          MGMT_JOB_PURGE_POLICIES(policy_name) ON DELETE CASCADE;

CREATE INDEX MGMT_JOB_PURGE_TGT_IDX01 ON
       MGMT_JOB_PURGE_TARGETS(policy_name);


Rem
Rem Indexes for MGMT_JOB_PURGE_VALUES table
Rem
ALTER TABLE MGMT_JOB_PURGE_VALUES ADD CONSTRAINT 
      FK_JOB_PURGEVALS_PNAME FOREIGN KEY(policy_name) REFERENCES
          MGMT_JOB_PURGE_POLICIES(policy_name) ON DELETE CASCADE;

CREATE INDEX MGMT_JOB_PURGE_VALS_IDX01 ON
       MGMT_JOB_PURGE_VALUES(policy_name);

Rem
Rem Indexes for the parameter source tables
Rem
CREATE INDEX MGMT_JOB_PARAM_SOURCE_IDX01 ON
    MGMT_JOB_PARAM_SOURCE(job_type_id);

Rem
Rem Index for the security info and lock info tables
Rem
CREATE INDEX MGMT_JOB_SEC_INFO_IDX01 ON
    MGMT_JOB_SEC_INFO(job_type_id);

CREATE INDEX MGMT_JOB_LOCK_INFO_IDX01 ON
    MGMT_JOB_LOCK_INFO(job_type_id);

Rem
Rem Indexes for MGMT_JOB_EXEC_LOCKS
Rem
CREATE INDEX MGMT_JOB_EXEC_LOCKS_IDX01 ON MGMT_JOB_EXEC_LOCKS(execution_id);

CREATE INDEX MGMT_JOB_EXEC_LOCKS_IDX02 ON MGMT_JOB_EXEC_LOCKS(target_guid);

Rem
Rem Index for display info tables
Rem
CREATE  INDEX MGMT_JT_URI_INFO_IDX01 ON
   MGMT_JOB_TYPE_URI_INFO(job_type_id);

CREATE  INDEX MGMT_JT_DISPLAY_PARAM_IDX01 ON
   MGMT_JOB_TYPE_DISPLAY_PARAM(job_type_id);


CREATE  INDEX MGMT_JT_DISPLAY_INFO_IDX01 ON
   MGMT_JOB_TYPE_DISPLAY_INFO(job_type_id);

Rem
Rem Index for MGMT_JOB_STEP_TARGETS
Rem
CREATE INDEX MGMT_JOB_STEP_TARGETS_IDX01 ON
    MGMT_JOB_STEP_TARGETS(step_id);

CREATE INDEX MGMT_JOB_STEP_TARGETS_IDX02 ON
    MGMT_JOB_STEP_TARGETS(target_guid);

Rem
Rem Index for MGMT_JOB_QUEUES
Rem
CREATE UNIQUE INDEX MGMT_JOB_QUEUE_IDX01 ON
    MGMT_JOB_QUEUES(queue_name)
    INITRANS 4;

Rem
Rem indexes for job runs and job executions UI pages
Rem

create index MGMT_JOB_EXEC_SUMM_IDX03 on MGMT_JOB_EXEC_SUMMARY
   (scheduled_time, status, status_bucket);

create index MGMT_JOB_EXT_TARGETS_IDX01 on MGMT_JOB_EXT_TARGETS
   (target_GUID, execution_ID, job_ID);

create index MGMT_JOB_EXT_TARGET_IDX02 on MGMT_JOB_EXT_TARGETS
   (execution_id,target_guid,job_id);

REM
REM Indexes for MGMT_JOB_STATE_CHANGES
REM 
CREATE INDEX MGMT_JOB_STATE_CHGS_IDX01 ON MGMT_JOB_STATE_CHANGES(execution_id);
CREATE INDEX MGMT_JOB_STATE_CHGS_IDX02 ON MGMT_JOB_STATE_CHANGES(logged);

REM
REM Indexes for MGMT_JOB_CREDENTIALS
REM
CREATE INDEX MGMT_JOB_CREDENTIALS_IDX01 ON
    MGMT_JOB_CREDENTIALS(target_guid);
    
REM
REM Indexes for MGMT_CREDENTIALS2
REM
CREATE INDEX MGMT_CREDENTIALS2_IDX01 ON
    MGMT_CREDENTIALS2(assoc_target_guid);

REM
REM Indexes for MGMT_NESTED_JOB_CRED_INFO
REM

REM using IDX1 instead of IDX01 due to name length constraint
CREATE INDEX MGMT_NESTED_JOB_CRED_INFO_IDX1 ON
    MGMT_NESTED_JOB_CRED_INFO(target_name, target_type);
    
REM
REM Indexes for MGMT_CORRECTIVE_ACTION
REM
CREATE INDEX MGMT_CORRECTIVE_ACTION_IDX01 ON
    MGMT_CORRECTIVE_ACTION(ca_target_guid);

REM
REM Index on target guid for MGMT_JOB_EXEC_CRED_INFO
REM
CREATE INDEX MGMT_JOB_EXEC_CRED_INFO_IDX01 ON
    MGMT_JOB_EXEC_CRED_INFO(target_guid);