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!

Besides what everyone else said, the delete statement might take longer and longer if you have highwatermark issues in any of the objects, or if there is locking taking place as any keys are being messed with.

Try creating all mentioned tables and indices from scratch and see if the problem changes. Also be sure you haven't defined the same thing more than once, go through [select object_name from all_objects where object_name like '%IND%']. And be sure you haven't done some weirdo statistics if you are using CBO (or RBO with CHOOSE).

jg

--
@home is bogus.
TKB Forever and ever and ever and ever and ever and ever and ever and
ever...