nope I've got the parent-child relationship the wrong way around. mea culpa
--
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
******************************************
"Brian E Dick" <bdick_at_cox.net> wrote in message
news:CtaC9.17242$0U1.1666554_at_news2.east.cox.net...
> 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 - 14:58:39 CST
