Re: fastest way of deleting all rows in a table

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Fri, 28 Feb 2003 23:17:34 GMT
Message-ID: <MPG.18c98de9a0fa61df9896d8@news.la.sbcglobal.net>

shravanakumar.ks_at_siemens.com said...
> Hi All,
>
> We are using Oracle 9i on Solaris 8.
>
> I would like to know, which is the fastest way of deleting all rows from
> a table and why ?
>
> a. Truncate table .....
> b. delete table .....
> c. drop table .... ( an re-create the table afterwards )
>
> can some one shed light on this ?
>
> Thanks,
>
> Best Regards,
> Shravana Kumar.
>

As pointed out in another thread, sometimes you have to forego the "fastest" way (truncate). See the following for one reason why you can't always use TRUNCATE TABLE:

SQL> create table actpln (
  2 actpln_id number not null,
  3 fum_id number,
  4 constraint pk_actpln_id

  5      primary key (actpln_id)
  6      using index
  7        tablespace appindx

  8 )
  9 tablespace appdata;

Table created.

SQL> create table fum (
  2 fum_id number not null,
  3 constraint pk_fum_id

  4      primary key (fum_id)
  5      using index
  6        tablespace appindx

  7 )
  8 tablespace appdata;

Table created.

SQL> alter table actpln
  2 add constraint fk_actpln_fum
  3 foreign key (fum_id)
  4 references fum (fum_id);

Table altered.

SQL> truncate table actpln;

Table truncated.

SQL> truncate table fum;
truncate table fum

               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> This was done under 8i. Don't know if it's changed in 9i.

-- 
/Karsten
DBA > retired > DBA

Received on Fri Feb 28 2003 - 17:17:34 CST