Re: LONG-running DELETE -- please help

"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?