Strange performance on DELETE

From: Oraclion <inferno74_at_caramail.com>
Date: Sat, 5 Oct 2002 16:05:48 +0200
Message-ID: <anmr66$79$1@wanadoo.fr>

Hi

I've this:
Delete from FAL_DISPO4 where FAL_COMPOSANT6_ID =10;

Oracle Server is 8.1.7.4 (NT 2000)
FAL_DISPO4 have 19 records !!!
a non unique index is on FAL_COMPOSANT6_ID. no delete cascade on others tables.
One user (me) is connected.
Analyse is OK
optimizer mode is CHOOSE (but i've the same time in RULE).

==> two hours !!!

Good week
Oraclion

Script of FAL_DISPO4

DROP TABLE FAL_DISPO4 CASCADE CONSTRAINTS ; CREATE TABLE FAL_DISPO4 (

  FAL_DISPO4_ID          NUMBER (12)   NOT NULL,
  FAL_COMPOSANT6_ID      NUMBER (12)   NOT NULL,
  CP6_SEQ                NUMBER (9)    NOT NULL,
  FAL_FACTORY_IN_ID      NUMBER (12)   NOT NULL,
  STM_STOCK_POSITION_ID  NUMBER (12)   NOT NULL,
  GCO_GOOD_ID            NUMBER (12),
  STM_LOCATION_ID        NUMBER (12)   NOT NULL,
  STM_STM_LOCATION_ID    NUMBER (12)   NOT NULL,
  DI4_COMEBACK_QTY       NUMBER (15,4),
  DI4_REJECTED_QTY       NUMBER (15,4),
  DI4_AVAILABLE_QTY      NUMBER (15,4),
  DI4_PIECE              VARCHAR2 (30),
  DI4_LOT                VARCHAR2 (30),
  DI4_VERSION            VARCHAR2 (30),
  DI4_CHRONO             VARCHAR2 (30),

  CONSTRAINT PK_FAL_DISPO4
  PRIMARY KEY ( FAL_DISPO4_ID )
    USING INDEX

     TABLESPACE USER_DATA_DIVERS PCTFREE 10
     STORAGE ( INITIAL 16384 NEXT 16384 PCTINCREASE 10 ))

   TABLESPACE USER_DATA_DIVERS
   PCTFREE 10
   PCTUSED 40
   INITRANS 1
   MAXTRANS 255
  STORAGE (
   INITIAL 32768
   NEXT 24576
   PCTINCREASE 50
   MINEXTENTS 1
   MAXEXTENTS 2147483645
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE; CREATE INDEX FAL_COMP6_S_FAL_DISP4_FK ON   FAL_DISPO4(FAL_COMPOSANT6_ID, CP6_SEQ)   TABLESPACE USER_DATA_DIVERS PCTFREE 10 STORAGE(INITIAL 40960 NEXT 40960 PCTINCREASE 50 )
;

ALTER TABLE FAL_DISPO4 ADD CONSTRAINT FAL_COMP6_S_FAL_DISPO4  FOREIGN KEY (FAL_COMPOSANT6_ID, CP6_SEQ)   REFERENCES BRISTOL.FAL_COMPOSANT6 (FAL_COMPOSANT6_ID, CP6_SEQ) ; ALTER TABLE FAL_DISPO4 ADD CONSTRAINT FK_FAL_DISP_FAL_FACT__FAL_FACT  FOREIGN KEY (FAL_FACTORY_IN_ID)
  REFERENCES BRISTOL.FAL_FACTORY_IN (FAL_FACTORY_IN_ID) ; ALTER TABLE FAL_DISPO4 ADD CONSTRAINT GCO_GOOD_S_FAL_DISPO4  FOREIGN KEY (GCO_GOOD_ID)
  REFERENCES BRISTOL.GCO_GOOD (GCO_GOOD_ID) ; ALTER TABLE FAL_DISPO4 ADD CONSTRAINT STM_LOCATION1_S_FAL_DISPO4  FOREIGN KEY (STM_LOCATION_ID)
  REFERENCES BRISTOL.STM_LOCATION (STM_LOCATION_ID) ; ALTER TABLE FAL_DISPO4 ADD CONSTRAINT STM_LOCATION2_S_FAL_DISPO4  FOREIGN KEY (STM_STM_LOCATION_ID)
  REFERENCES BRISTOL.STM_LOCATION (STM_LOCATION_ID) ; Received on Sat Oct 05 2002 - 09:05:48 CDT