Foreign keys must reference either unique or primary key constraints which
are enforced via indexes, so unless he's really done something funky there
should be an index on TBLINITIATIVE.INITIATIVEID. Also, a delete on the
referenced table cascades to the table with the foreign key, so it is more
important to have an appropriate index on the foreign key column
TMMADMINISTRATORINITIATIVE.INITIATIVEID.
Please correct me if I am wrong on either of these points.
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
news:3dd7ad59$0$9448$cc9e4d1f_at_news.dial.pipex.com...
> "dib42700" <dib42700_at_glaxowellcome.com> wrote in message
> news:01c28db5$3ab40af0$6601a8c0_at_us0097739...
> > The following Oracle DELETE statement is EXTREMENTLY long-running:
> >
> > delete from tmmAdministratorInitiative
> > where InitiativeID = 1
> > and employeeid = '00701280K'
> >
> > Here's the DDL for the table:
> >
> > CREATE TABLE TMMADMINISTRATORINITIATIVE (
> > EMPLOYEEID VARCHAR2(20) NOT NULL
> > ,INITIATIVEID NUMBER(10) NOT NULL
> > ,CRTDT DATE
> > ,CRTUSR VARCHAR2(50)
> > ,MODDT DATE
> > ,MODUSR VARCHAR2(50)
> > ,CONSTRAINT U_ADMIN_INITIATIVE UNIQUE ( EMPLOYEEID, INITIATIVEID )
> > ,CONSTRAINT FK_EMPLOYEEID_2 FOREIGN KEY ( EMPLOYEEID ) REFERENCES
> > TBLEMPLOYEE ( EMPLOYEEID )
> > ,CONSTRAINT FK_INITIATIVEID_2 FOREIGN KEY ( INITIATIVEID ) REFERENCES
> > TBLINITIATIVE ( INITIATIVEID ) ON DELETE CASCADE
> > );
> >
> > There's no primary key because this is a"linkage" table in a
many-to-many
> > relationship.
> >
> > There are just two rows in the table!
> >
> > Any help understanding why the DELETE statement takes so long would be
> > greatly appreciated!
>
> You say there are only two rows in the table. Do you mean in total or that
> match the criteria? How many rows does Oracle think are in the table
> (num_rows column in a XXX_tables view).
>
> In addition I notice that you have an on delete cascade against
> tblinitiatve. If Initiativeid in this table is unindexed that will cause
you
> problems.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
> An explain plan would help?
>
>
>
Received on Mon Nov 18 2002 - 12:39:30 CST
