The difference between delete,truncate and drop

1, truncate and delete only delete data without deleting the structure of the table (definition), while the drop statement will delete the structure of the table is dependent on the constraint (constrain), trigger (trigger), indexed (index); Stored procedures/functions that depend on the table are preserved, but become invalid states.

2, the DELETE statement is DML, this operation will be put into rollback segement, after the transaction is committed, and if there is a corresponding trigger, execution will be triggered.

3, Truncate,drop is the DDL, the operation takes effect immediately, the original data does not put in rollback segment, cannot rollback, the operation does not trigger trigger.

4, DELETE statement does not affect the extent occupied by the table, high watermark (higher watermark) to maintain the original position, truncate statement by default, the data space is released, unless the use of reuse storage; The truncate resets the high watermark (back to the beginning), and the drop statement frees all the space occupied by the table.

5, statement execution speed, generally speaking: drop> truncate > Delete

6, careful use of drop and truncate, especially when there is no backup, want to delete some data rows with delete, note with the WHERE clause.

7, want to keep the table and delete all the data, if not related to the transaction, with truncate, if it is related to the transaction, or want to trigger trigger, or delete.

8, if it is to organize the internal fragments of the table, you can use truncate to keep up with reuse stroage, and then re-import/insert data.

9. For a table referenced by the foreign KEY constraint, you cannot use TRUNCATE TABLE, and you should use a DELETE statement that does not have a WHERE clause.

Also, if there is no backup, will drop,delete,truncate be able to recover?

From the intuitive sense, it seems that the problem is not content, since all have been drop, or truncate, is a DDL statement, the data should be back, if it is delete, and do a commit, the data should not come back.

For this problem, at least from the Oracle's point of view, the answer is possible, and in some cases it is possible. Let's take a single analysis:

The first is the drop operation, in Oracle, the default is the function of the Recycle Bin, that is, a table did a drop operation, actually from the physical table is not deleted, but simply replaced by a long name

If the Recycle Bin is turned on (default on)

Sql> Show Parameter RecycleBin

NAME TYPE VALUE

---------- ------- ------

RecycleBin string on

So this time the drop table will be able to recover, or not necessarily, why, there are several reasons to consider:

For example, the table where the table space resources are tight, it is likely that the table in the Recycle Bin data will be recycled to store other data;

Or a user's quota (quota) is not sufficient, this time is likely to use the Recycle Bin resources will be limited;

Or, in some scenarios, the tables you create are placed directly under the system table space, and even if you have enough space, you cannot restore them directly.

So with these analyses, there's still a lot of possibilities for the drop operation, but the first statement is probably, not sure.

Then look at the truncate operation, this operation even from the point of view of data recovery, is powerless. But our answer is still possible, this time still want to say flashback database this characteristic.

For example, a point in time truncate a very important table. We can try flashback database if circumstances permit

Of course, this feature you also have a system-level settings, by default, flashback database features are not enabled

Sql> select flashback_on from V$database;

flashback_on

------------------

NO

You need to use ALTER DATABASE flashback on in the Mount phase of the database to enable it because the flashback log records some data changes at the database level, so it is bound to have a certain impact on the performance and resources of the database. But in a test environment, the informal environment can still be used boldly.

For example, we are not sure truncate time, we can constantly flashback, until we find satisfactory results unknown

If we find that the data is what we expect at a certain point in time, we can use EXP to export the data, and then we need to take the data in.

Finally, the delete operation, which from the Oracle level, has more options.

The delete operation uses the Undo resource to generate the corresponding undo_sql, and if we delete the data and commit it, we can consider the Flashback table feature:

Flashback table Test to timestamp to_timestamp (' 2015-08-01:12:02:27 ', ' yyyy-mm-dd:hh24:mi:ss ');

We can also find out the data from that point in time:

SELECT * FROM test as of timestamp to_timestamp (' 2015-08-01:14:09:37 ', ' yyyy-mm-dd:hh24:mi:ss ');

Then create a table and import the data into the query so that the data is restored.

Here is Oracle's learning material, which we hope will bring you help,

http://www.wyzc.com/index.php?a=course&m=Index&c=channel&channelId=3&tg=3006123630

This article is from the "11393400" blog, please be sure to keep this source http://11403400.blog.51cto.com/11393400/1758833

The difference between delete,truncate and drop