The difference between truncate and delete and the basic concept of DDL,DML,DCL,TCL

The difference between truncate and delete and the basic concept of DDL,DML,DCL,TCL


Understand DDL,DML,DCL,TCL Basic Concepts First

For SQL languages, there are two components:

DML (Data Manipulation language): They are select, UPDATE, INSERT, DELETE, just like its name, these 4 commands are the languages used to manipulate the data in the database.

DDL (data Definition Language): DDL is more than DML, the main commands are create, ALTER, drop, etc., the DDL is mainly used in the definition or change of tables (table) structure, data type, table links and constraints, such as the initialization of work, Most of them are used when creating tables. Answer by: Zhongsojun | Level Three | 2009-9-1 19:30



SQL language is divided into four categories: Data Query Language DQL, Data manipulation language DML,

Data definition language DDL, Data Control Language DCL.

1 Data Query Language DQL

Data Query Language DQL The basic structure is the SELECT clause, the FROM clause, where

A query block consisting of a clause:

SELECT < field Name table >

From < table or view name >

WHERE < query conditions >

2 Data Manipulation language

Data manipulation language DML mainly has three kinds of forms:

1) Inserting: Insert

2) Updated: Update

3) Deletion: Delete



3 Data Definition Language DDL

Data definition Language DDL is used to create various objects in a database-----tables, views,

indexes, synonyms, clusters, etc. such as:

CREATE Table/view/index/syn/cluster



4 Data Control Language DCL

The Data Control Language DCL is used to grant or reclaim certain privileges to access the database and to control

The database manipulates the time and effect that the transaction takes place, monitors the database and so on. Such as:

1 Grant: Authorization.

2) ROLLBACK [WORK] to [savepoint]: Back to a certain point.

Roll Back---ROLLBACK

The rollback command causes the database state to revert to the last-committed state. The format is:

sql>rollback;

3 Commit [WORK]: Submit. Www.2cto.com

When a database is inserted, deleted, and modified, only when the transaction is committed to the data

Library is complete. Before a transaction is committed, only the person who operates the database can be entitled to see

To do things, others can only be seen after the final submission is complete.

There are three types of submitted data: explicit commit, implicit commit, and autocommit. Following points

Don't explain these three types.

(1) Explicit submission

Commits that are done directly with the commit command are explicit commits. The format is:

sql>commit;

(2) Implicit submission

Submissions that are done indirectly with SQL commands are implicitly committed. These commands are:

Alter,audit,comment,connect,create,disconnect,drop,

Exit,grant,noaudit,quit,revoke,rename.

(3) Automatic submission

If the autocommit is set to ON, after the insert, modify, DELETE statement is executed,

The system will automatically commit, which is automatic submission. The format is:

Sql>set autocommit on;




&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&& &&&&&&&&&&&&&&&&&&&&&& &





Example compares the differences between truncate and deletes in Oracle
The way to delete data in a table is Delete,truncate,
They all delete the data in the table, not the table structure, and delete deletes the entire table's data or deletes one or N of the table's data that satisfies the criteria.
And truncate can only delete the entire table of data, we generally put the delete operation as a delete table, and the truncate operation is called truncated table.
Truncate action vs. delete operation
Operation Roll back high waterline space efficiency
Truncate can't fall recycled fast
Delete can be unchanged without recycling slow


Let's look at their differences by using an instance
1. Roll Back
The first thing to understand is two.
1. Data deletion in Oracle can be rolled back because it puts the original data in the Undo table space,
The 2.DML statement uses the undo tablespace, the DDL statement does not use Undo, and the delete is a DML statement, TRUNCATE is a DDL statement, and the Outer DDL statement is implicitly committed.
So truncate can not be rolled back, and the delete operation.
Compare two operations (create a new table first and insert the data)
Sql> CREATE TABLE T
2 (
3 I number
4);
Table created.
Sql> INSERT INTO T values (10);
Sql> commit;
Commit complete.
Sql> select * from T;
I
----------
10

Delete Deletes, and then rolls back
Sql> Delete from T;
1 row deleted.
Sql> select * from T;
No rows selected
#删除后回滚
sql> rollback;
Rollback complete.
Sql> select * from T;
I
----------
10

Truncate truncate the table and then rolls back.
sql> truncate TABLE t;
Table truncated.
sql> rollback;
Rollback complete.
Sql> select * from T;
No rows selected

The Visible delete Delete table can also be rolled back, and truncate truncate the table cannot be rolled back. (If the delete operation is not committed)

2. High Waterline
All Oracle tables have a ceiling for data (much like a reservoir's highest level of history),
We call this cap "high water mark" or HWM. This HWM is a marker (a data block is used to record high water marks, etc.)
Used to indicate how many blocks of data have been allocated to this table. The HWM usually increases by 5 blocks at a time.
The DELETE statement does not affect the block of data that the table occupies, and the high watermark (watermark) keeps the original position fixed
Truncate statement by default, space is released unless the reuse storage is used; Truncate will reset the high waterline
Compare the two operations below
Sql> analyze table T estimate statistics;
Table analyzed.
Sql> Select Segment_name,blocks from dba_segments where Segment_name=upper (' t ');
Segment_name BLOCKS
------------------------------ ----------
T 24
Sql> Select Table_name,blocks,empty_blocks from User_tables where Table_name=upper (' t ');
TABLE_NAME BLOCKS Empty_blocks
------------------------------ ---------- ------------
T 20 3

User_tables. The BLOCKS column represents the number of database blocks that have been used in the table, that is, the watermark.
Note: User_tables. BLOCKS empty_blocks (20+3=23) than dba_segments. Blocks one less database block,
This is because a database block is reserved for use as a table header. Dba_segments. BLOCKS represents the number of all the database blocks assigned to this table.
User_tables. Blocks represents the number of database blocks (watermarks) that have been used.
Delete Deletes the table,
Sql> Delete from T;
10000 rows deleted
Sql> commit;
Commit complete.
Sql> analyze table T estimate statistics;
Table analyzed.
Sql> Select Table_name,blocks,empty_blocks from User_tables where Table_name=upper (' t ');
TABLE_NAME BLOCKS Empty_blocks
------------------------------ ---------- ----------------------------------------------------------------
T 20 3

Truncate TRUNCATE TABLE
sql> truncate TABLE t;
Table truncated.
Sql> analyze table T estimate statistics;
Table analyzed.
Sql> Select Table_name,blocks,empty_blocks from User_tables where Table_name=upper (' t ');
TABLE_NAME BLOCKS Empty_blocks
------------------------------ ---------- --------------------------------------------------------
T 0 7

Visible, delete table, block (high watermark) unchanged, and TRUNCATE TABLE blocks (high watermark) into 0
Now we also see blocks+empty_blocks=7, that is, Oracle allocation area default once 7+1 (header) = 8 blocks;
The role of the Gaoshui line: HWM has the following effects on the operation of the database:
A full table scan is usually read out until all the HWM tags belong to the table database block, even if there is no data in the table.
b Even if there are free database blocks below HWM, type to use the APPEND keyword when inserting data, then use the block of HWM above when inserting, HWM automatically grow at this time.
So high watermark is an important parameter in Oracle optimization
3. Space
Since the high watermark is used to indicate how many blocks of data have been allocated to the table, the high watermark can also be understood as the space occupancy of the table.
Even if delete deletes all the data in the table, HWM is still the original value, so there is so much space to allocate to the table that its space is not recycled.
The TRUNCATE table has a high watermark of 0, and now it means there is no space allocated, that is, its space is recycled.
4. Efficiency
To see delete,truncate that's more efficient, build a large table, and then see how long it takes to delete the tables separately.
There is a rather figurative analogy: the leader gives you two books to throw away, delete is you keep in front of the copier, the pages of the page to be torn off to make a copy,
Another page to throw into the trash can, truncate is directly to the two books thrown into the trash, that fast that slow self-evident.
First insert 100,000 records in the table and open the time
Sql> set timing on;
Sql> begin
2 for I in 1..100000 loop
3 INSERT into T values (' 10 ');
4 commit;
5 end Loop;
6 end;
7/
Pl/sql procedure successfully completed.
elapsed:00:01:12.50

Delete Table
Sql> Delete from T;
100000 rows deleted.
elapsed:00:00:20.09

Truncate TRUNCATE TABLE
#先把表回滚
sql> rollback;
Rollback complete.
elapsed:00:00:17.36
Sql> Select COUNT (*) from T;
COUNT (*)
-------------------
100000
elapsed:00:00:00.01
sql> truncate TABLE t;
Table truncated.
elapsed:00:00:00.20

Visible deletes the same size table, delete takes 20.09 seconds, and truncate only uses 0.2 seconds.
This article is from the "Pursuit" blog, please be sure to keep this source http://chenxy.blog.51cto.com/729966/168459