Shulou(Shulou.com)05/31 Report--

This article shows you how to delete and truncate in MySQL and Oracle, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

There are still some differences between delete,truncate in MySQL and Oracle, and understanding these differences may be helpful in dealing with problems and understanding problems.

Let's try a few simple tests. We create two tables test_del,test_tru to compare delete,truncate operations. We have a temporary table t_fund_info with millions of data.

Create test_del

> create table test_del select *from t_fund_info;

Query OK, 1998067 rows affected (34.77 sec)

Records: 1998067 Duplicates: 0 Warnings: 0

Create test_tru

> create table test_tru select *from t_fund_info;

Query OK, 1998067 rows affected (35.21 sec)

Records: 1998067 Duplicates: 0 Warnings: 0 At this time, let's look at the corresponding files. In the common way in MySQL, there will be separate data files and configuration files for each table.

We can see that there are 4 new files, all of the same size.

-rw-rw---- 1 mysql mysql 9545 Nov 22 09:52 test_del.frm

-rw-rw---- 1 mysql mysql 9545 Nov 22 09:53 test_tru.frm

-rw-rw---- 1 mysql mysql 390070272 Nov 22 09:53 test_del.ibd

-rw-rw--- 1 mysql mysql 390070272 Nov 22 09:54 test_tru.ibd We started testing the difference between the two.

> delete from test_del;

> truncate table test_tru; At this time, look at the files under the directory again, and you can see that the data file of test_tru has been completely released, and the file size after delete remains unchanged.

-rw-rw---- 1 mysql mysql 9545 Nov 22 09:52 test_del.frm

-rw-rw---- 1 mysql mysql 9545 Nov 22 09:53 test_tru.frm

-rw-rw---- 1 mysql mysql 390070272 Nov 22 09:55 test_del.ibd

-rw-rw--- 1 mysql mysql 98304 Nov 22 09:55 test_tru.ibd So how do delete tables free up what space? Consider resetting the storage engine.

> alter table test_del engine=innodb;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0 The whole process is very fast, and after the operation to view the file, the size has shrunk.

-rw-rw---- 1 mysql mysql 9545 Nov 22 09:53 test_tru.frm

-rw-rw---- 1 mysql mysql 98304 Nov 22 09:55 test_tru.ibd

-rw-rw---- 1 mysql mysql 9545 Nov 22 09:58 test_del.frm

-rw-rw--- 1 mysql mysql 98304 Nov 22 09:58 test_del.ibd Here you need to understand whether it is related to modifying the operation of the storage engine or the data inside. In the above scenario, this is a more special example. Let's drop the table test_del, and rebuild it again.

> drop table test_del;

Query OK, 0 rows affected (0.01 sec)

> create table test_del select *from t_fund_info;

Query OK, 1998067 rows affected (33.82 sec)

Records: 1998067 Duplicates: 0 Warnings: 0 At this point let's look at modifying the storage engine when there is a lot of data.

You can see that the timing of the operation is still very different.

> alter table test_del engine=innodb;

Query OK, 1998067 rows affected (28.07 sec)

Records: 1998067 Duplicates: 0 Warnings: 0 Why is it so much slower? This is because the underlying operation of this operation is to copy data. MySQL just helps you do these things, you can see that in the process of operation will create a temporary table.

rw-rw---- 1 mysql mysql 9545 Nov 22 09:53 test_tru.frm

-rw-rw---- 1 mysql mysql 98304 Nov 22 09:55 test_tru.ibd

-rw-rw---- 1 mysql mysql 9545 Nov 22 10:01 test_del.frm

-rw-rw---- 1 mysql mysql 390070272 Nov 22 10:02 test_del.ibd

-rw-rw---- 1 mysql mysql 9545 Nov 22 10:02 #sql-2931_463ab4.frm

-rw-rw--- 1 mysql mysql 276824064 Nov 22 10:03 #sql-2931_463ab4.ibddelete and truncate still have certain applicable scenarios. In addition, there is another way to use them in MySQL, which is quite distinctive, that is, selective deletion. For example, delete the first n data sorted by id in test_del. You can use order by limit in delete.

For example:

> delete from test_del order by id desc limit 2;

Query OK, 2 rows affected (3.56 sec) In addition, MySQL and Oracle also have a big difference is that MySQL table data files are completely reused, and in Oracle there are many ways, such as append,reuse, etc., this itself and design also have a relationship.

For example, in MySQL we delete the data of a table and then reinsert it, then this space is completely reused. The original documents will hardly change.

Truncate operation is a fast operation in MySQL, data is fleeting, there are some differences in Oracle, and it is possible to recover these data.

For example, we create a table space on the Oracle side and create two tables test_del,test_tru;

create tablespace

create tablespace test_data datafile '/U01/app/oracle/oradata/newtest2/test_data.dbf' size 10M autoextend on;

Create table test_del,test_tru

create table test_del tablespace test_data as select *from all_objects;

create table test_tru tablespace test_data as select *from all_objec

collect statistics

exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'TEST_DEL');

exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'TEST_TRU');

At this point, let's test the difference between the two.

The difference here is that if we delete the data, the table test_del has no data, but the query will still scan the whole table, the scanned data block is basically the same as before cleaning, and the cost will be higher.

SQL> delete from test_del;

68314 rows deleted.

select *from test_tru;

The truncate operation is a very fast process in Oracle and MySQL. In Oracle, the data will not be erased directly, and the data will still exist. It will only be recovered when triggered under certain conditions.

We use dbms_rowid to parse this

SQL> select dbms_rowid.ROWID_RELATIVE_FNO(rowid) as file#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) as block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) as row#,a.object_id from test.test_tru a where rownum delete from test_del order by id desc limit 2;

In Oracle it might be so much, regardless of which analysis functions are used

delete from test_del where rowid in (select rowid from (select rowid from test_del order by id desc) where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.