DROP INDEX PATS.U_NOTIFICATION_QUERYSTRING;

CREATE UNIQUE INDEX PATS.U_NOTIFICATION_QUERYSTRING ON PATS.NOTIFICATION_MASTER
(QUERYSTRING)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_PATIENT_INST_AND_VISTAIEN;

CREATE UNIQUE INDEX PATS.U_PATIENT_INST_AND_VISTAIEN ON PATS.PATS_PATIENT
(INSTITUTION_FK, VISTA_IEN)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.ROC_NUMBER_PHONE_FAX;

CREATE INDEX PATS.ROC_NUMBER_PHONE_FAX ON PATS.ROC_PHONE_FAX
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.ROC_NUMBER_ROC_CONT_ENT;

CREATE INDEX PATS.ROC_NUMBER_ROC_CONT_ENT ON PATS.ROC_CONTACTING_ENTITY
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.SYS_C003094;

CREATE UNIQUE INDEX PATS.SYS_C003094 ON PATS.FACILITY_SERVICE_OR_SECTION
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.STATUS;

CREATE INDEX PATS.STATUS ON PATS.REPORT_OF_CONTACT
(STATUS)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_TS_TREATMENT_STATUS;

CREATE UNIQUE INDEX PATS.U_TS_TREATMENT_STATUS ON PATS.TREATMENT_STATUS
(TREATMENT_STATUS)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_TS_UPPERCASE_NAME;

CREATE UNIQUE INDEX PATS.U_TS_UPPERCASE_NAME ON PATS.TREATMENT_STATUS
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_USER_IDENTIFIER;

CREATE UNIQUE INDEX PATS.U_USER_IDENTIFIER ON PATS.PATS_USER
(USER_IDENTIFIER)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ROLLUP_ID;

CREATE UNIQUE INDEX PATS.PK_ROLLUP_ID ON PATS.PATS_ROLLUP_NATL_DATA
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.ROC_NUMBER_ROC_METHOD;

CREATE INDEX PATS.ROC_NUMBER_ROC_METHOD ON PATS.ROC_METHOD_OF_CONTACT
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_ROC_PATIENT;

CREATE INDEX PATS.FK_ROC_PATIENT ON PATS.REPORT_OF_CONTACT
(PATIENT_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_ROC_INSTITUTION;

CREATE INDEX PATS.FK_ROC_INSTITUTION ON PATS.REPORT_OF_CONTACT
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_ROC_INFOBY;

CREATE INDEX PATS.FK_ROC_INFOBY ON PATS.REPORT_OF_CONTACT
(INFO_TAKEN_BY_USER_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_ROC_TREATMENT_STATUS;

CREATE INDEX PATS.FK_ROC_TREATMENT_STATUS ON PATS.REPORT_OF_CONTACT
(TREATMENT_STATUS_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.DATE_OF_CONTACT;

CREATE INDEX PATS.DATE_OF_CONTACT ON PATS.REPORT_OF_CONTACT
(DATE_OF_CONTACT)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.DATE_OVERDUE;

CREATE INDEX PATS.DATE_OVERDUE ON PATS.REPORT_OF_CONTACT
(DATE_OVERDUE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_CC_INST;

CREATE INDEX PATS.FK_CC_INST ON PATS.CONGRESSIONAL_CONTACT
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_COMPS_INST;

CREATE INDEX PATS.FK_COMPS_INST ON PATS.COMPS
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_FSOS_VISN;

CREATE INDEX PATS.FK_FSOS_VISN ON PATS.FACILITY_SERVICE_OR_SECTION
(VISN_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_HL_INST;

CREATE INDEX PATS.FK_HL_INST ON PATS.HOSPITAL_LOCATION
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_PATIENT_INST;

CREATE INDEX PATS.FK_PATIENT_INST ON PATS.PATS_PATIENT
(INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_ROC_COMP;

CREATE INDEX PATS.FK_ROC_COMP ON PATS.REPORT_OF_CONTACT
(COMP_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_ROC_CONGRESSIONAL_CONTACT;

CREATE INDEX PATS.FK_ROC_CONGRESSIONAL_CONTACT ON PATS.REPORT_OF_CONTACT
(CONGRESSIONAL_CONTACT_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_ROC_ENTBY;

CREATE INDEX PATS.FK_ROC_ENTBY ON PATS.REPORT_OF_CONTACT
(ENTERED_BY_USER_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.FK_USER_INST;

CREATE INDEX PATS.FK_USER_INST ON PATS.PATS_USER
(PARENT_INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_BOILER_ID;

CREATE UNIQUE INDEX PATS.PK_BOILER_ID ON PATS.BOILERPLATE_RESOLUTION_TEXT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_CC_ID;

CREATE UNIQUE INDEX PATS.PK_CC_ID ON PATS.CONGRESSIONAL_CONTACT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_CE_ID;

CREATE UNIQUE INDEX PATS.PK_CE_ID ON PATS.CONTACTING_ENTITY
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_COMPS;

CREATE UNIQUE INDEX PATS.PK_COMPS ON PATS.COMPS
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_DMLOG_ID;

CREATE UNIQUE INDEX PATS.PK_DMLOG_ID ON PATS.DMLOG
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_HOSPITAL_LOCATION0;

CREATE UNIQUE INDEX PATS.PK_HOSPITAL_LOCATION0 ON PATS.HOSPITAL_LOCATION
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ID;

CREATE UNIQUE INDEX PATS.PK_ID ON PATS.TREATMENT_STATUS
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ISSUE_CATEGORY_CODE;

CREATE UNIQUE INDEX PATS.PK_ISSUE_CATEGORY_CODE ON PATS.ISSUE_CATEGORY
(ISSUE_CATEGORY_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ISSUE_CODE;

CREATE UNIQUE INDEX PATS.PK_ISSUE_CODE ON PATS.ISSUE_CODE
(ISSUE_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_MOC_ID;

CREATE UNIQUE INDEX PATS.PK_MOC_ID ON PATS.METHOD_OF_CONTACT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_NATIONAL_PATS_PARAMETERS;

CREATE UNIQUE INDEX PATS.PK_NATIONAL_PATS_PARAMETERS ON PATS.NATIONAL_PATS_PARAMETERS
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_NOTIFICATION_DETAIL_ID;

CREATE UNIQUE INDEX PATS.PK_NOTIFICATION_DETAIL_ID ON PATS.NOTIFICATION_DETAIL
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_NOTIFICATION_MASTER;

CREATE UNIQUE INDEX PATS.PK_NOTIFICATION_MASTER ON PATS.NOTIFICATION_MASTER
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_PADLOC_ID;

CREATE UNIQUE INDEX PATS.PK_PADLOC_ID ON PATS.PAD_LOCATION
(ID)
LOGGING
TABLESPACE M_PATS_DATA_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_PATIENT_ID;

CREATE UNIQUE INDEX PATS.PK_PATIENT_ID ON PATS.PATS_PATIENT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_PATS_PATIENT_RACE;

CREATE UNIQUE INDEX PATS.PK_PATS_PATIENT_RACE ON PATS.PATS_PATIENT_RACE
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ROCCE_ID;

CREATE UNIQUE INDEX PATS.PK_ROCCE_ID ON PATS.ROC_CONTACTING_ENTITY
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ROCISS_ID;

CREATE UNIQUE INDEX PATS.PK_ROCISS_ID ON PATS.ROC_ISSUE
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ROCMOC_ID;

CREATE UNIQUE INDEX PATS.PK_ROCMOC_ID ON PATS.ROC_METHOD_OF_CONTACT
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ROCPF_ID;

CREATE UNIQUE INDEX PATS.PK_ROCPF_ID ON PATS.ROC_PHONE_FAX
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_ROC_NUMBER;

CREATE UNIQUE INDEX PATS.PK_ROC_NUMBER ON PATS.REPORT_OF_CONTACT
(ROC_NUMBER)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.PK_USER_ID;

CREATE UNIQUE INDEX PATS.PK_USER_ID ON PATS.PATS_USER
(ID)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.ROC_NUMBER_ISSUE;

CREATE INDEX PATS.ROC_NUMBER_ISSUE ON PATS.ROC_ISSUE
(ROC_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_BOILER_ISSCODE_INST;

CREATE UNIQUE INDEX PATS.U_BOILER_ISSCODE_INST ON PATS.BOILERPLATE_RESOLUTION_TEXT
(INSTITUTION_FK, ISSUE_CODE_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_CCNAME_INSTITUTION;

CREATE UNIQUE INDEX PATS.U_CCNAME_INSTITUTION ON PATS.CONGRESSIONAL_CONTACT
(INSTITUTION_FK, OFFICE_OR_PERSON_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_CE_NAME;

CREATE UNIQUE INDEX PATS.U_CE_NAME ON PATS.CONTACTING_ENTITY
(CONTACTING_ENTITY_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_CE_ROLLUP_CODE;

CREATE UNIQUE INDEX PATS.U_CE_ROLLUP_CODE ON PATS.CONTACTING_ENTITY
(ROLLUP_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_CE_UPPERCASE_NAME;

CREATE UNIQUE INDEX PATS.U_CE_UPPERCASE_NAME ON PATS.CONTACTING_ENTITY
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_COMP_NAME_INST;

CREATE UNIQUE INDEX PATS.U_COMP_NAME_INST ON PATS.COMPS
(UPPER_CASE_NAME, INSTITUTION_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_FSOS_NAME_VISN;

CREATE UNIQUE INDEX PATS.U_FSOS_NAME_VISN ON PATS.FACILITY_SERVICE_OR_SECTION
(UPPER_CASE_NAME, VISN_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_INST_AND_HLNAME;

CREATE UNIQUE INDEX PATS.U_INST_AND_HLNAME ON PATS.HOSPITAL_LOCATION
(INSTITUTION_FK, LOCATION_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_ISSCAT_UPPERCASE_NAME;

CREATE UNIQUE INDEX PATS.U_ISSCAT_UPPERCASE_NAME ON PATS.ISSUE_CATEGORY
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_ISSUE_CATEGORY_NAME;

CREATE UNIQUE INDEX PATS.U_ISSUE_CATEGORY_NAME ON PATS.ISSUE_CATEGORY
(ISSUE_CATEGORY_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_ISSUE_CODE_NAME_AND_CATEGORY;

CREATE UNIQUE INDEX PATS.U_ISSUE_CODE_NAME_AND_CATEGORY ON PATS.ISSUE_CODE
(ISSUE_CATEGORY_FK, ISSUE_CODE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_ISSUE_CODE_UPPERCASE_NAME;

CREATE UNIQUE INDEX PATS.U_ISSUE_CODE_UPPERCASE_NAME ON PATS.ISSUE_CODE
(UPPER_CASE_NAME, ISSUE_CATEGORY_FK)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_MOC_NAME;

CREATE UNIQUE INDEX PATS.U_MOC_NAME ON PATS.METHOD_OF_CONTACT
(METHOD_OF_CONTACT_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_MOC_ROLLUP_CODE;

CREATE UNIQUE INDEX PATS.U_MOC_ROLLUP_CODE ON PATS.METHOD_OF_CONTACT
(ROLLUP_CODE)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
DROP INDEX PATS.U_MOC_UPPERCASE_NAME;

CREATE UNIQUE INDEX PATS.U_MOC_UPPERCASE_NAME ON PATS.METHOD_OF_CONTACT
(UPPER_CASE_NAME)
LOGGING
TABLESPACE M_PATS_INDEX_01
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOPARALLEL;
