Delete performance

From: Eric DUCHET <eric.duchet_at_free.fr>
Date: Wed, 24 Nov 1999 11:08:49 GMT
Message-ID: <5HP_3.584$F01.2612380@nnrp2.proxad.net>

Hi,
I have a big problem with a delete query. I try to delete all rows (100 000 rows ) from a table named ITEM. It takes about 1 hour.
This table contains a lot of indexes (see the script after). When i try to copy the table to a temporary table , and then when i delele the temporary table,
the time for deleting the entire table is pretty good (about 3 minutes).

What happens with my ITEM table ?
Why does it take so long time for deleting ? How can i improve my delete ?

Thanx for Your help.

My table structure is
create table ITEM
(

    IT_ITEMCODE            VARCHAR2(13)           not null,
    IT_CODE                VARCHAR2(3)            not null,
    IT_INSERTTIME          DATE                   not null,
    IT_RECORDTYPE          NUMBER(2)              not null,
    IT_CREATETIME          DATE                   null    ,
    IT_ID             VARCHAR2(8)            null    ,
    IT_IDNUMBER            NUMBER(3)              null    ,
    IT_SHORT          VARCHAR2(13)           null    ,
    IT_HITSHORT          VARCHAR2(8)            null    ,
    IT_USERID              VARCHAR2(8)            null    ,
    IT_RECORDID            NUMBER(10)             null    ,

    constraint PK_ITEM primary key (IT_ITEMCODE, IT_CODE, IT_INSERTTIME, IT_RECORDTYPE)

        using index
     tablespace INDEX_1

)
pctfree 0
pctused 98
tablespace DATA_1
storage
(
initial 100M
next 100M
)
/

create index FK_IT_2 on ITEM(IT_SHORT asc) tablespace INDEX_1
/

create index FK_IT_3 on ITEM(IT_CODE asc) tablespace INDEX_1
/

create index FK_IT_4 on ITEM(IT_RECORDID asc) tablespace INDEX_1
/

create index FK_IT_1 on ITEM(IT_RECORDTYPE asc) tablespace INDEX_1
/

create index DI_IT_1 on ITEM(IT_CREATETIME asc) tablespace INDEX_1
/

create index DI_IT_2 on ITEM(IT_ID asc) tablespace INDEX_1
/

create index DI_IT_3 on ITEM(IT_ITEMCODE asc) tablespace INDEX_1
/

create index DI_IT_4 on ITEM(IT_HITSHORT asc) tablespace INDEX_1
/

create index DI_EVT_5 on ITEM(IT_CODE asc, IT_RECORDTYPE asc, IT_RECORDID asc) Received on Wed Nov 24 1999 - 05:08:49 CST