Performance impact of Delete on table data deletion

Performance impact of Delete on table data deletion
As mentioned in the previous article (), when we use delete to delete data, because the high water level cannot be lowered, a full table scan will cause database performance problems, the truncate can greatly improve this situation. Let's take a look at the test SQL> conn zlm/zlmConnected. -- before creating a table, record the remaining tablespace size. SQL> select * from dba_free_space where tablespace_name = 'zlm ';
TABLESPACE_NAME FILE_ID BLOCK_ID bytes blocks RELATIVE_FNO =---------- ------------ ZLM 6 128 51380224 6272 6 -- create a large table SQL with rows> create Table test1 (int number); table created. SQL> analyze table test1 compute statistics; Table analyzed. SQL> select table_name, num_rows, blocks, empty_blocks, avg_row_len, last_analyzed from dba_tables where table_name like 'test % '; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED tables ------------ -------------- ------------- tables TEST1 0 0 0 0 27-SEP-14 note that when the table structure is created and no data is inserted, the table's high level is 0, no data block SQL> declare is assigned to the table
2 I number: = 0; 3 begin 4 for I in 1 .. 1000000 loop 5 insert into test1 values (I); 6 end loop; 7 commit; 8 end; 9/PL/SQL procedure successfully completed. SQL> select count (*) from test1; COUNT (*) -------- 1000000 -- View high water level status SQL> select header_file, header_block, bytes, blocks, extents from dba_segments where segment_name like 'test % '; HEADER_FILE HEADER_BLOCK bytes blocks extents ----------- ------------ ---------- 6 130 13631488 28 SQL> select block_id, extent_id, bytes, blocks from dba_extents where segment_name like 'test % '; BLOCK_ID EXTENT_ID bytes blocks ---------- 128 0 65536 8 136 1 65536 8 144 2 65536 8 152 3 65536 8 160 4 65536 8 168 5 65536 8 176 6 65536 8 184 7 65536 8 192 8 65536 8 200 9 65536 8 208 10 65536 8 216 11 65536 8 224 12 65536 8 232 13 65536 8 240 14 65536 8 248 8 65536 16 256 1048576 17 128 384 512 18 1048576 128 640 19 1048576 128 768 1048576 128 896 21 1048576 128 22 1024 1048576 128 1152 1048576 128 1280 1048576 128 1408 1048576 128 1536 1048576 128 1664 1048576 128 SQL> select * from dba_free_space where tablespace_name = 'zlm ';
TABLESPACE_NAME FILE_ID BLOCK_ID bytes blocks RELATIVE_FNO partition ---------- ------------ ---------- -------------- ZLM 6 1792 37748736 4608 6 SQL> set autot traceSQL> select count (*) from test1; Execution Plan partition Plan hash value: 3896847026 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | -------------------------------------------------------------- | 0 | select statement | 1 | 2 (0) | 00:00:01 | 1 | sort aggregate | 1 | 2 | table access full | TEST1 | 1 | 2 (0) | 00:00:01 | Statistics defaults 0 recursive cballs 1 db block gets 1599 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL * Net to client 519 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> delete from test1;
1000000 rows deleted. execution Plan hash value: 2642947686 bytes | Id | Operation | Name | Rows | Cost (% CPU) | Time | percent | 0 | delete statement | 1 | 2 (0) | 00:00:01 | 1 | DELETE | TEST1 | 2 | table access full | TEST1 | 1 | 2 (0) | 00:00:01 | Statistics limit 814 recursive cballs 1038983 db block gets 1953 consistent gets 8 physical reads 245334988 redo size 847 bytes sent via SQL * Net to client 769 bytes received via SQL * Net from client 3 SQL * Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1000000 when rows processed used delete to delete a large table TEST1 with 0.2 billion million rows of data, a large amount of redo (more than 1953) is generated, and there are many consistent reads ), read more than 100 million data blocks and perform 814 recursive calls. You can see that using delete to delete table records results in high performance consumption on the database, especially when a large number of rows are deleted.
SQL> set autot offSQL> select count (*) from test1; COUNT (*) ---------- 0 SQL> select header_file, header_block, bytes, blocks, extents from dba_segments where segment_name like 'test % '; HEADER_FILE HEADER_BLOCK bytes blocks extents ----------- ------------ ---------- 6 130 13631488 28

For more details, please continue to read the highlights on the next page:

1 2 Next Page