OraFAQ Forum: Performance Tuning » DELETE taking long time

Home » RDBMS Server » Performance Tuning » DELETE taking long time (10g r2)
Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend
DELETE taking long time [message #577986]
Fri, 22 February 2013 07:26
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi ,

We have a table which has 168900 records and my load check service_disruption_number and if found then delete else it insert the records.

I have observe that delete take lots of time and insert is very fast. first i delete all those records and then i run the insert command.

SGA is 1gb set to target at parameter for balancing.

Please suggect best method to delete fast.
A lot of code here
table structure as below

CREATE TABLE FCT_DISRUPTIONS
(
  FCT_DISRUPTIONS_SKEY          NUMBER,
  DIM_INPUT_DATE_DWK            NUMBER,
  DIM_INPUT_TIME_DWK            NUMBER,
  DIM_START_DATE_DWK            NUMBER,
  DIM_START_TIME_DWK            NUMBER,
  DIM_END_DATE_DWK              NUMBER,
  DIM_END_TIME_DWK              NUMBER,
  DIM_DURATION_END_DATE_DWK     NUMBER,
  DIM_DURATION_END_TIME_DWK     NUMBER,
  DIM_TRAFFIC_DATE_DWK          NUMBER,
  DIM_PRIMARY_LOCATION_DWK      NUMBER,
  DIM_INCIDENT_LOCATION_DWK     NUMBER,
  DIM_LOCATION_FROM_DWK         NUMBER,
  DIM_LOCATION_TO_DWK           NUMBER,
  DIM_LINE_DWK                  NUMBER,
  DIM_DIRECTION_DWK             NUMBER,
  DIM_PLATFORM_DWK              NUMBER,
  DIM_STATION_CLOSURE_AREA_DWK  NUMBER,
  DIM_ATTRIBUTION_PARTIES_DWK   NUMBER,
  DIM_INPUT_BY_EMP_DWK          NUMBER,
  DIM_INCIDENT_ASSETS_DWK       NUMBER,
  DIM_PROJECTS_DWK              NUMBER,
  DIM_INCIDENT_TYPE_DWK         NUMBER,
  DIM_INFRACO_CAUSE_CODES_DWK   NUMBER,
  DIM_LU_CAUSE_CODES_DWK        NUMBER,
  DIM_CAUSE_FACTOR_DWK          NUMBER,
  DIM_NO_DEFECT_FOUND_DWK       NUMBER,
  DIM_INCIDENTS_DWK             NUMBER,
  DIM_PRIMARY_FLAG_DWK          NUMBER,
  DIM_BOOKED_ACCESS_DWK         NUMBER,
  DIM_LANDE_FLAG_DWK            NUMBER,
  DIM_ENG_OVERRUN_DWK           NUMBER,
  DIM_ATTRIBUTION_STATUS_DWK    NUMBER,
  SERVICE_DISRUPTION_NUMBER     NUMBER,
  INDICATIVE_NAX                NUMBER,
  INDICATIVE_LCH                NUMBER,
  SERVICE_DELAY_IN_MINUTES      NUMBER,
  TOTAL_DURATION_IN_MINUTES     NUMBER,
  DISRUPTION_COUNT              NUMBER,
  INCIDENT_COUNT_WEIGHTED       NUMBER,
  TRAIN_NO                      VARCHAR2(250 BYTE),
  SD_FAULT_DESC                 VARCHAR2(4000 BYTE),
  DM_LOAD_DELTA_ID              NUMBER,
  DM_LOAD_DATE                  DATE,
  DM_UPDATE_DELTA_ID            NUMBER,
  DM_UPDATE_DATE                DATE,
  DIM_FAILING_LOCATION_DWK      NUMBER,
  DIM_EIRF_DWK                  NUMBER,
  FRCNO                         NUMBER,
  FANDDNO                       VARCHAR2(250 BYTE),
  START_METERS                  NUMBER,
  END_METERS                    NUMBER,
  LENGTH_IN_METERS              NUMBER,
  INFRACO_REF_NUMBER            VARCHAR2(250 BYTE),
  REPORT_MANAGER                VARCHAR2(250 BYTE),
  TEMPORARY_SPEED_RESTRICTION   NUMBER,
  RESTRICTED_SPEED_KPH          NUMBER,
  RESTRICTED_SPEED_MPH          NUMBER,
  PAYMENT_LCH                   NUMBER,
  FAULT_COUNT                   NUMBER,
  JOINT_ATTRIBUTION_PERCENT     NUMBER,
  DIM_START_PLATFORM_DWK        NUMBER,
  DIM_FINISH_PLATFORM_DWK       NUMBER,
  DIM_DETRAINMENT_STATION_DWK   NUMBER,
  DIM_IMPOSED_DATE_DWK          NUMBER,
  DIM_IMPOSED_TIME_DWK          NUMBER,
  DIM_LIFTED_DATE_DWK           NUMBER,
  DIM_LIFTED_TIME_DWK           NUMBER,
  DIM_24_HOURSREPORT_DWK        NUMBER,
  DIM_FINAL_REPORT_RECV_DWK     NUMBER,
  DIM_EMP_CTI_DWK               NUMBER,
  DIM_EMP_INVOLVEMENT_GRP_DWK   NUMBER,
  SOURCE_SYSTEM                 VARCHAR2(30 BYTE)
)
TABLESPACE DATAMART_PDW_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE BITMAP INDEX BIDX10_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INCIDENT_LOCATION_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX11_FCTDISRP ON FCT_DISRUPTIONS
(DIM_LOCATION_FROM_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX12_FCTDISRP ON FCT_DISRUPTIONS
(DIM_LOCATION_TO_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX13_FCTDISRP ON FCT_DISRUPTIONS
(DIM_LINE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX14_FCTDISRP ON FCT_DISRUPTIONS
(DIM_DIRECTION_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX15_FCTDISRP ON FCT_DISRUPTIONS
(DIM_PLATFORM_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX16_FCTDISRP ON FCT_DISRUPTIONS
(DIM_STATION_CLOSURE_AREA_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX17_FCTDISRP ON FCT_DISRUPTIONS
(DIM_ATTRIBUTION_PARTIES_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX18_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INPUT_BY_EMP_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX19_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INCIDENT_ASSETS_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX1_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INPUT_DATE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX20_FCTDISRP ON FCT_DISRUPTIONS
(DIM_PROJECTS_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX21_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INCIDENT_TYPE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX22_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INFRACO_CAUSE_CODES_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX23_FCTDISRP ON FCT_DISRUPTIONS
(DIM_LU_CAUSE_CODES_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX24_FCTDISRP ON FCT_DISRUPTIONS
(DIM_CAUSE_FACTOR_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX25_FCTDISRP ON FCT_DISRUPTIONS
(DIM_NO_DEFECT_FOUND_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX26_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INCIDENTS_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX27_FCTDISRP ON FCT_DISRUPTIONS
(DIM_PRIMARY_FLAG_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX28_FCTDISRP ON FCT_DISRUPTIONS
(DIM_BOOKED_ACCESS_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX29_FCTDISRP ON FCT_DISRUPTIONS
(DIM_LANDE_FLAG_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX2_FCTDISRP ON FCT_DISRUPTIONS
(DIM_INPUT_TIME_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX30_FCTDISRP ON FCT_DISRUPTIONS
(DIM_ENG_OVERRUN_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX31_FCTDISRP ON FCT_DISRUPTIONS
(DIM_ATTRIBUTION_STATUS_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX3_FCTDISRP ON FCT_DISRUPTIONS
(DIM_START_DATE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX4_FCTDISRP ON FCT_DISRUPTIONS
(DIM_START_TIME_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX5_FCTDISRP ON FCT_DISRUPTIONS
(DIM_END_DATE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX6_FCTDISRP ON FCT_DISRUPTIONS
(DIM_END_TIME_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX7_FCTDISRP ON FCT_DISRUPTIONS
(DIM_DURATION_END_DATE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX8_FCTDISRP ON FCT_DISRUPTIONS
(DIM_DURATION_END_TIME_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX9_FCTDISRP ON FCT_DISRUPTIONS
(DIM_TRAFFIC_DATE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX BIDX_48_FCTDISRP ON FCT_DISRUPTIONS
(DIM_START_PLATFORM_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX IDXNU_SERVICE_DISRP_NO ON FCT_DISRUPTIONS
(SERVICE_DISRUPTION_NUMBER)
NOLOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF32FCTDISRP ON FCT_DISRUPTIONS
(DIM_PRIMARY_LOCATION_DWK)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF34FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_EIRF_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF36FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_FINISH_PLATFORM_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF37FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_DETRAINMENT_STATION_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF41FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_IMPOSED_DATE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF42FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_IMPOSED_TIME_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF43FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_LIFTED_DATE_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF44FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_LIFTED_TIME_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF45FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_24_HOURSREPORT_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE BITMAP INDEX XIF47FCT_DISRUPTIONS ON FCT_DISRUPTIONS
(DIM_FINAL_REPORT_RECV_DWK)
LOGGING
TABLESPACE DATAMART_PDW_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX XPKFCT_DISRUPTIONS ON FCT_DISRUPTIONS
(FCT_DISRUPTIONS_SKEY)
LOGGING
TABLESPACE DATAMART_PDW_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE FCT_DISRUPTIONS ADD (
  CONSTRAINT XPK1_FCT_DISRUPTIONS_KEY
 PRIMARY KEY
 (FCT_DISRUPTIONS_SKEY));

ALTER TABLE FCT_DISRUPTIONS ADD (
  CONSTRAINT XFK_FINISH_PALTFORM_DWK 
 FOREIGN KEY (DIM_FINISH_PLATFORM_DWK) 
 REFERENCES DIM_PLATFORM (DIM_PLATFORM_DWK),
  CONSTRAINT XFK_DTR_STATION_DWK 
 FOREIGN KEY (DIM_DETRAINMENT_STATION_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_LOCATIONS (DIM_LOCATIONS_DWK),
  CONSTRAINT XFK_IMPOSED_DATE_DWK 
 FOREIGN KEY (DIM_IMPOSED_DATE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_CALENDAR (CALENDAR_DWK),
  CONSTRAINT XFK_IMPOSED_TIME_DWK 
 FOREIGN KEY (DIM_IMPOSED_TIME_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_TIME (DIM_TIME_DWK),
  CONSTRAINT XFK_LIFTED_DATE_DWK 
 FOREIGN KEY (DIM_LIFTED_DATE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_CALENDAR (CALENDAR_DWK),
  CONSTRAINT XFK_LIFTED_TIME_DWK 
 FOREIGN KEY (DIM_LIFTED_TIME_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_TIME (DIM_TIME_DWK),
  CONSTRAINT XFK_24HR_REP_DWK 
 FOREIGN KEY (DIM_24_HOURSREPORT_DWK) 
 REFERENCES DIM_PDW_OTHERS (DIM_PDW_OTHERS_DWK),
  CONSTRAINT XFK_FINAL_REP_DWK 
 FOREIGN KEY (DIM_FINAL_REPORT_RECV_DWK) 
 REFERENCES DIM_PDW_OTHERS (DIM_PDW_OTHERS_DWK),
  CONSTRAINT XFKDIM_DIRECTION 
 FOREIGN KEY (DIM_DIRECTION_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_DIRECTION (DIM_DIRECTION_DWK),
  CONSTRAINT XFKDIM_INPUT_BY_EMP 
 FOREIGN KEY (DIM_INPUT_BY_EMP_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_EMPLOYEE (DIM_EMPLOYEE_DWK),
  CONSTRAINT XFKDIM_LINE 
 FOREIGN KEY (DIM_LINE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_LINE (DIM_LINE_DWK),
  CONSTRAINT XFKDIM_TRAFFIC_DATE 
 FOREIGN KEY (DIM_TRAFFIC_DATE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_CALENDAR (CALENDAR_DWK),
  CONSTRAINT XFKDIM_DURATION_END_DATE 
 FOREIGN KEY (DIM_DURATION_END_DATE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_CALENDAR (CALENDAR_DWK),
  CONSTRAINT XFKDIM_END_DATE 
 FOREIGN KEY (DIM_END_DATE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_CALENDAR (CALENDAR_DWK),
  CONSTRAINT XFKDIM_START_DATE 
 FOREIGN KEY (DIM_START_DATE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_CALENDAR (CALENDAR_DWK),
  CONSTRAINT XFKDIM_INPUT_DATE 
 FOREIGN KEY (DIM_INPUT_DATE_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_CALENDAR (CALENDAR_DWK),
  CONSTRAINT XFKDIM_CAUSE_FACTOR 
 FOREIGN KEY (DIM_CAUSE_FACTOR_DWK) 
 REFERENCES DIM_CAUSE_FACTOR (DIM_CAUSE_FACTOR_DWK),
  CONSTRAINT XFKDIM_LU_CAUSE_CODES 
 FOREIGN KEY (DIM_LU_CAUSE_CODES_DWK) 
 REFERENCES DIM_LU_CAUSE_CODES (DIM_LU_CAUSE_CODES_DWK),
  CONSTRAINT XFKDIM_ATTRIBUTION_PARTIES 
 FOREIGN KEY (DIM_ATTRIBUTION_PARTIES_DWK) 
 REFERENCES DIM_ATTRIBUTION_PARTIES (DIM_ATTRIBUTION_PARTIES_DWK),
  CONSTRAINT XFKDIM_ATTRIBUTION_STATUS 
 FOREIGN KEY (DIM_ATTRIBUTION_STATUS_DWK) 
 REFERENCES DIM_ATTRIBUTION_STATUS (DIM_ATTRIBUTION_STATUS_DWK),
  CONSTRAINT XFKDIM_ENG_OVERRUN 
 FOREIGN KEY (DIM_ENG_OVERRUN_DWK) 
 REFERENCES DIM_PDW_OTHERS (DIM_PDW_OTHERS_DWK),
  CONSTRAINT XFKDIM_LANDE_FLAG 
 FOREIGN KEY (DIM_LANDE_FLAG_DWK) 
 REFERENCES DIM_PDW_OTHERS (DIM_PDW_OTHERS_DWK),
  CONSTRAINT XFKDIM_BOOKED_ACCESS 
 FOREIGN KEY (DIM_BOOKED_ACCESS_DWK) 
 REFERENCES DIM_PDW_OTHERS (DIM_PDW_OTHERS_DWK),
  CONSTRAINT XFKDIM_PRIMARY_FLAG 
 FOREIGN KEY (DIM_PRIMARY_FLAG_DWK) 
 REFERENCES DIM_PDW_OTHERS (DIM_PDW_OTHERS_DWK),
  CONSTRAINT XFKDIM_NO_DEFECT_FOUND 
 FOREIGN KEY (DIM_NO_DEFECT_FOUND_DWK) 
 REFERENCES DIM_PDW_OTHERS (DIM_PDW_OTHERS_DWK),
  CONSTRAINT XFKDIM_INCIDENT_ASSETS 
 FOREIGN KEY (DIM_INCIDENT_ASSETS_DWK) 
 REFERENCES DIM_INCIDENT_ASSETS (DIM_INCIDENT_ASSETS_DWK),
  CONSTRAINT XFKDIM_INCIDENTS 
 FOREIGN KEY (DIM_INCIDENTS_DWK) 
 REFERENCES DIM_INCIDENTS (DIM_INCIDENTS_DWK),
  CONSTRAINT XFKDIM_PLATFORM 
 FOREIGN KEY (DIM_PLATFORM_DWK) 
 REFERENCES DIM_PLATFORM (DIM_PLATFORM_DWK),
  CONSTRAINT XFKDIM_PROJECTS 
 FOREIGN KEY (DIM_PROJECTS_DWK) 
 REFERENCES DIM_PROJECTS (DIM_PROJECTS_DWK),
  CONSTRAINT XFKDIM_STATION_CLOSURE_AREA 
 FOREIGN KEY (DIM_STATION_CLOSURE_AREA_DWK) 
 REFERENCES DIM_STATION_CLOSURE_AREA (DIM_STATION_CLOSURE_AREA_DWK),
  CONSTRAINT XFKDIM_INCIDENT_TYPE 
 FOREIGN KEY (DIM_INCIDENT_TYPE_DWK) 
 REFERENCES DIM_INCIDENT_TYPE (DIM_INCIDENT_TYPE_DWK),
  CONSTRAINT XFKDIM_INFRACO_CAUSE_CODES 
 FOREIGN KEY (DIM_INFRACO_CAUSE_CODES_DWK) 
 REFERENCES DIM_INFRACO_CAUSE_CODES (DIM_INFRACO_CAUSE_CODES_DWK),
  CONSTRAINT XFKDIM_DURATION_END_TIME 
 FOREIGN KEY (DIM_DURATION_END_TIME_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_TIME (DIM_TIME_DWK),
  CONSTRAINT XFKDIM_END_TIME 
 FOREIGN KEY (DIM_END_TIME_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_TIME (DIM_TIME_DWK),
  CONSTRAINT XFKDIM_START_TIME 
 FOREIGN KEY (DIM_START_TIME_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_TIME (DIM_TIME_DWK),
  CONSTRAINT XFKDIM_INPUT_TIME 
 FOREIGN KEY (DIM_INPUT_TIME_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_TIME (DIM_TIME_DWK),
  CONSTRAINT XFKDIM_FAILING_LOCATION 
 FOREIGN KEY (DIM_FAILING_LOCATION_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_LOCATIONS (DIM_LOCATIONS_DWK),
  CONSTRAINT XFKDIM_PRIMARY_LOCATION 
 FOREIGN KEY (DIM_PRIMARY_LOCATION_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_LOCATIONS (DIM_LOCATIONS_DWK),
  CONSTRAINT XFKDIM_LOCATION_TO 
 FOREIGN KEY (DIM_LOCATION_TO_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_LOCATIONS (DIM_LOCATIONS_DWK),
  CONSTRAINT XFKDIM_INCIDENT_LOCATION 
 FOREIGN KEY (DIM_INCIDENT_LOCATION_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_LOCATIONS (DIM_LOCATIONS_DWK),
  CONSTRAINT XFKDIM_LOCATION_FROM 
 FOREIGN KEY (DIM_LOCATION_FROM_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_LOCATIONS (DIM_LOCATIONS_DWK),
  CONSTRAINT XFKDIM_EIRF 
 FOREIGN KEY (DIM_EIRF_DWK) 
 REFERENCES DIM_EIRF (DIM_EIRF_DWK),
  CONSTRAINT XFK_EM_CTI_DWK 
 FOREIGN KEY (DIM_EMP_CTI_DWK) 
 REFERENCES CONFORMED_DIMENSION.DIM_EMPLOYEE (DIM_EMPLOYEE_DWK),
  CONSTRAINT XFK_EMP_INOLV_GRP_DWK 
 FOREIGN KEY (DIM_EMP_INVOLVEMENT_GRP_DWK) 
 REFERENCES DIM_EMP_INVOLVEMENT_GRP (DIM_EMP_INVOLVEMENT_GRP_DWK));
[EDITED by LF: applied [spoiler] tags to cut a very long message short]
[Updated on: Sat, 23 February 2013 11:12] by Moderator
Report message to a moderator
Re: DELETE taking long time [message #577989 is a reply to message #577986]
Fri, 22 February 2013 07:43
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use MERGE statement.
However, given the number of bitmap indexes you have it will be slow.
Bitmap indexes are for read-only tables not for OLTP ones.
Often, it is faster to invalidate all indexes and rebuild them afterwards.

Regards
Michel
Report message to a moderator
Re: DELETE taking long time [message #577990 is a reply to message #577989]
Fri, 22 February 2013 07:49
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
ok thanks
Report message to a moderator
Previous Topic:	Please help me in rewriting the below query in optimized way
Next Topic:	SQL ordered by Elapsed Time & SQL Module
Goto Forum:
-=] Back to Top [=-
[ Syndicate this forum (XML) ] [
]
Current Time: Wed Dec 18 01:00:51 CST 2024