I've gone through the same thing trying to find out what happens in a
DROP that (with a large table) makes it take longer than a
TRUNCATE+DROP. I heard somewhere (like you) that the DROP actually
invokes a DELETE first, before dropping the table. I don't know if
that's true, but at least in Oracle 7.3 in a data warehouse I saw DROPS
take a LONG time to complete on large tables. That's why we started
doing TRUNCATES before DROPS. Perhaps with Oracle8 this is not a
problem any more.
> -----Original Message-----
> From: Karl R [SMTP:kroyle_at_hotmail.-REMOVE-com]
> Posted At: Wednesday, February 03, 1999 12:37 PM
> Posted To: comp.databases.oracle.server
> Conversation: Dropping Tables - Slow Performance
> Subject: Re: Dropping Tables - Slow Performance
>
> >Hm, is this based on any practical experience? If it is, can you
> >elaborate why would TRUNCATE+DROP be any faster then simple DROP?
>
> I attended an excellent Oracle DBA course last year (with Oracle
> Training),
> and the advice given was that TRUNCATE+DROP was faster than DROP,
> since DROP
> did a large number of DELETEs (DML) first, whereas TRUNCATE (DDL) did
> the
> same thing without rollback.
>
> When I came back from the course, I tried this and it appeared to be
> true.
> However, I tried to repoduce those findings today, but failed - a
> straight
> drop seemed much faster than I expected...certainly too fast to be
> creating
> rollback entries - e.g. <1 second to drop a 200,000 row, 5MB table.
>
> I scoured the documentation for something to clarify this, but it was
> quite
> ambiguous. Perhaps this has been optimised in later Oracle versions?
> I'm
> clutching at staws here though.
>
> If anyone knows... ;-)
>
Received on Wed Feb 03 1999 - 12:07:26 CST
