Re: Dropping Tables - Slow Performance

Well,

My personal opinion is that TRUNCATE + DROP should be no faster than a simple DROP, however, just for the sake of argument ...

A drop command results in all the dirty blocks for all the relevant segments being flushed from the buffer to disk (yes, I know it's daft; the same thing happens when a rollback segment is shrunk, and there is a (not ridiculous) argument why it is done), so there is a DBWR write overhead before a drop takes place.

What if a TRUNCATE as it drops the high water mark somehow tags the blocks in the db_block_buffer as empty so that they do not have to be written, but can be discard like temporary blocks ?

Examination and proof (or otherwise) of concept is left as an exercise.

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Karl R wrote in message
<918063419.11542.0.nnrp-06.c1ede19d_at_news.demon.co.uk>...
>>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 - 11:51:20 CST