the same things with 9i.
"Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
news:MPG.18c98de9a0fa61df9896d8_at_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 - 22:31:44 CST
