| drop vs truncate. [message #37442] |
Thu, 07 February 2002 13:59  |
bhasker
Messages: 5
Registered: February 2002
|
Junior Member |
|
|
I need to delete the rows in a table completely.
I can achieve this with truncate command.
But my dba says that truncate command doesn't completely free up all the space.
but drop and recreating table will free up all the
space that was used by this table.
does any of guys have any idea about truncating table is better or droping and recreating the table is better
|
|
|
|
| Re: drop vs truncate. [message #37445 is a reply to message #37442] |
Thu, 07 February 2002 17:34  |
seng
Messages: 191
Registered: February 2002
|
Senior Member |
|
|
here are the way to delete data from table
- delete(DML) - delete data, slow and can rollback ( redo log is generated) , and space is still occupied until certain condition to freeout. this is suitable for small data deletion.
- trundate - delete data, fast and can't rollback ( no redo log is generated). most of space is free out. this is suitble for large data deletion .
- drop and create table - delete data and structure (schema) might affect other table if drop table with cascade, fast and can't rollback at all. space is freeout. this is suitable for structure(schema) change.
From my experience, in development environment, we won't touch data structure(schema) if it is not changed in structure for new program & business logic. The developer is more only allow to use delete or truncate. The structure changed is on the hand of dba.
For your information, the space won't become a seriuos problem compare the database structure. DBA has fragmentation method to freeout the space.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
