Re: Fragmentation and TRUNCATE vs. DROP

"grz02" <grz01_at_spray.se> wrote in message news:1641ee43.0403180320.6cb8185a_at_posting.google.com...
| I have seen this claim in a few places that for Fragmentation reasons,
| it is better to do TRUNCATE TABLE than DROP TABLE,
| but I havent seen any explanation why that would be so.
|
| Anyone knows whether this is true, and if so, what the difference
| would be?
|
| Here is one such text, but I have seen it mentioned somewhere else,
| too:
|
|

http://www.orapub.com/cgi/genesis.cgi?p1=dwnld&bogus=zip_out&p2=na&p4=paper&p3=doc121.zip
|
| "The dropping issue can also be reduced with the proper storage
| parameter management (i.e., creating fewer extents) and eliminated by
| simply truncating the table"

the issue is related to the table's high-water mark, and in this particular case, the fact that additional extents allocated to the table are not freed when deleting the rows -- but truncating the table (optionally, and by default) frees all allocated extents beyond the original (create-time) allocation

also check out the oracle concepts manual and SQL reference

;-{ mcs Received on Fri Mar 19 2004 - 19:32:17 CST