delete, truncate, drop

2011-02-24  来源:本站原创  分类:Database  人气:72 

Transfer: http://www.chinaunix.net/jh/19/252763.html
Note: This is not to say delete the delete statement with a where clause the same point
truncate and with no where clause of the delete, and drop the data table will be deleted

Different points:
1. Truncate and delete only delete data without deleting the table structure (as defined)
drop statement will remove the structure is dependent on the constraints (constrain), trigger (trigger), index (index); depends on the form of stored procedure / function will be retained, but becomes invalid state.
2.delete statement is dml, this operation will put rollback segement, the transaction is committed after the entry into force; if there is a corresponding trigger, when the execution will be triggered.
truncate, drop a ddl, operations effective immediately, the original data is not placed in the rollback segment can not be rolled back. operation does not trigger the trigger.
3.delete statement does not affect the table occupied by the extent, the high water line (high watermark) to maintain the original position does not move obviously drop table statement released by the space occupied by all
truncate statement released by default see the space to minextents a extent, unless reuse storage; truncate will reset the high water line (back to the beginning).
4 speed, in general: drop>; truncate>; delete
5 Safety: Be careful using drop and truncate, in particular when there is no backup, otherwise the use of too late to cry, want to delete some rows with delete, to bring attention to the where clause. Rollback segments large enough.
Want to delete the table, of course, with a drop
Want to retain all the data tables will be deleted if and unrelated, you can use truncate if and affairs, or want to trigger the trigger, or use the delete.
If the order form inside the debris, can truncate to keep up with reuse stroage, then re-import / insert data

相关文章
  • Delete delete truncate drop of three 2010-08-29

    # Note: This is not to say delete the delete statement with a where clause # The same point # Truncate and without where clause of the delete, and drop the data table will be deleted # # Different points: # 1. Truncate and delete only delete the data

  • (To) delete / truncate / drop difference 2010-11-10

    1. Truncate and delete only delete the data does not remove the table structure (definition) drop table statement to remove the constraints of the structure is dependent on (constrain), trigger (trigger), index (index); dependent on the table stored

  • Three kinds delete delete truncate drop 2010-08-29

    # Note: This is not to say delete the delete statement with a where clause # The same point # Truncate and with no where clause, delete, and drop the data table will be deleted # # Different points: # 1. Truncate and delete only delete data without d

  • (R) delete / truncate / drop difference 2010-11-10

    1. Truncate and delete only delete data without deleting the table structure (as defined) drop statement will remove the structure is dependent on the constraints (constrain), trigger (trigger), index (index); depends on the form of stored procedure

  • delete, truncate, drop 2011-02-24

    Transfer: http://www.chinaunix.net/jh/19/252763.html Note: This is not to say delete the delete statement with a where clause the same point truncate and with no where clause of the delete, and drop the data table will be deleted Different points: 1.

  • truncate and delete and drop the distinction between 2010-07-01

    the difference between truncate and delete truncate table command to quickly delete all records data table, while retaining the structure of the table. This fast delete delete delete from the data table records all the different data tables, delete c

  • truncate drop delete Comparison 2010-12-09

    The same point: 1.truncate and without where clause of the delete, and drop the data table will be deleted. 2.drop, truncate is DDL (Data Definition Language) statement, the Executive will be automatically submitted. Different points: 1. Truncate and

  • TRUNCATE TABLE, delete table, drop table more 2010-10-19

    TRUNCATE TABLE, delete table, drop table more TRUNCATE TABLE is functionally without the same WHERE clause of the DELETE statement: Both delete all the rows in the table. But faster than DELETE TRUNCATE TABLE, and use fewer system and transaction log

  • truncate drop delete more 2010-12-09

    The same point: 1.truncate and without the where clause of the delete, and drop the data table will be deleted. 2.drop, truncate is DDL (Data Definition Language) statement, execution will be automatically submitted. Different points: 1. Truncate and

  • truncate.delete and drop the distinction 2011-05-07

    1. Truncate and delete only delete data without deleting the table structure (as defined) drop statement will remove the structure is dependent on the constraints (constrain), trigger (trigger), index (index); depends on the form of stored procedure

  • Truncate,Delete和Drop的异同点 2014-01-28

    注意:这里说的delete是指不带where子句的delete语句 相同点:truncate和不带where子句的delete, 以及drop都会删除表内的数据 TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同:二者均删除表中的全部行.但TRUNCATE TABLE比DELETE速度快,且使用的系统和事务日志资源少. DELETE语句每次删除一行,并在事务日志中为所删除的每行记录一项.TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据,并且只在事务

  • 数据库中删除语句Drop.Delete.Truncate的相同点和不同点的比较(实例说明) 2014-11-05

    数据库中删除语句Drop.Delete.Truncate的相同点和不同点的比较,通过举例说明让大家了解 数据库删除语句的分别介绍: Delete:用于删除表中的行(注:可以删除某一行:也可以在不删除表的情况下(即意味着表的结构.属性.索引完整)删除所有行) 语法:删除某一行:Delete From 表名称 Where 列名称=值 删除所有行: Delete From 表名称 或 Delete * From 表名称 Drop:用于删除表(注:将表的结构.属性.索引也会被删除.) 语法:Drop T

  • Rapid SQL SERVER database table which records to delete, truncate and delete the contrast 2011-08-27

    With BulkInsert to insert a table inside the database 40W of the data, because the data is updated in real time, so each will first delete all the data inside the table, then insert new data into it, so the efficiency is mainly in the deleted data he

  • The Oracle data recovery delete or drop table 2011-09-28

    In oralce, we sometimes mistakenly deleted some data or delete some of the table; then we need to restore the data. First, delete data recovery delete some data, you can use the following statement to find, this is mainly using Oracle's snapshot feat

  • mysql 删除操作(delete+TRUNCATE) 2014-07-26

    这篇文章主要介绍了mysql 删除操作,主要包括了delete与TRUNCATE的使用,需要的朋友可以参考下 delete 语法 单表语法: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]多表语法: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] tbl_name[.*] [, tbl_name

  • Several ways to delete a table Oracle 2011-09-18

    Delete Table (record and structure) of the language name delete ---- truncate ---- drop DELETE (delete data table record the statement) DELETE FROM table WHERE conditions; Note: delete the record and can not be occupied in the release of ORACLE data

  • Oracle Learning (2) - 15 days Oracle Training 2010-04-07

    Oracle Getting Started (2) SQL part of the 1: database definition statements: DDL (Data Definition Language) operations: (1): Table of establishing Key words: create table table name. eg: create table student - Create a student table ( ID varchar (36

  • Xiao old study on April 17 2010 to the April 23 (see last) 2010-04-17

    1, a workflow module needs and analysis, students can first do the analysis and design in advance. Requirements Description: * Staff leave form completed (such as leave), and then reported. * 1-2 days → report → Project Manager Project Manager for ap

  • drop truncate delete the distinction 2010-03-01

    Similarities: 1.truncate and without where clause of the delete, and drop the data table will be deleted. 2.drop, truncate is DDL statements (Data Definition Language), implementation will automatically be submitted. Differences: 1.drop and delete ju

  • Oracle truncate table, delete, drop table differences 2010-03-13

    Oracle truncate table, delete, drop table differences Keywords: truncate table, delete, and the difference between drop table TRUNCATE TABLE is functionally and not with the same WHERE clause of the DELETE statement: Both delete all the rows in the t