Re: Dropping Tables - Slow Performance

Normally, I would agree with you, but while TRUNCATE+DROP "Should" be no faster
than just DROP, I have first-hand experience from 7.3.2 in data warehouses that TRUNCATE+DROP
indeed was *considerably* faster than just DROP. As for the theory of dirty blocks being written out,
I don't think that's the case either, because I've seen this behavior on larger tables which had NO
update or insert activity at all. Unless Oracle looks at all the blocks regardless of whether any were
"dirty".

The whole thing is a mystery to me, and as I've mentioned before, I've not tested this behavior in
8.0 to see if it has changed. A number of companies (including Oracle) have made performance
notes that indicate a TRUNCATE+DROP may be faster on larger tables than simply DROP.
So, that tells me others have run into this behavior, and it's not just my imagination, nor that of the
other poster in the group.
<Smile>

Regards,
Tony

> -----Original Message-----
> From: Jonathan Lewis [SMTP:jonathan_at_jlcomp.demon.co.uk]
> Posted At: Wednesday, February 03, 1999 12:51 PM
> Posted To: comp.databases.oracle.server
> Conversation: Dropping Tables - Slow Performance
> Subject: 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 ...
>

        [Rodgers, Tony (Atlanta)] Snip
>
> 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 ?
>

        [Rodgers, Tony (Atlanta)] Snip .

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

        [Rodgers, Tony (Atlanta)] Snip Received on Wed Feb 03 1999 - 14:56:02 CST