Takeaway:Speaking of cleaning up historical data,The partition tableAs simple as this, we only need to truncate the history partition that needs to be deleted. However, for an ordinary large table, clearing history data is a manual task. The traditional approach is to combine DELETE with batch commit, but the speed is often not ideal. Then you shouldHow to improve delete efficiency of a single table?

Can I learn how to partition a table and logically partition a single table to speed up deletion? At this point, let’s review the physical storage structure of a single table: segment-extent-block. An area is the minimum allocation unit of a segment, and an area contains multiple blocks. So can we use the physical properties of the area or block to simulate the partition? I tried using partitions, why not blocks? Excluded because a database block can store no more than 1000 rows.

We use ROWID to divide each row into segments. Here we introduce the internal Oracle function dbMS_rowid. Rowid_create to help us divide ROWID into segments. The code is as follows:

1SQL> select A.FILE_ID, 2 3 A.EXTENT_ID, 4 5 A.BLOCK_ID, 6 7 A.BLOCKS, 8 9 ' rowid between ' || '''' || 10 11 dbms_rowid.rowid_create(1, 12 13 b.data_object_id, 14 15 a.relative_fno, 16 17 a.block_id, 18 19 0) || '''' || ' and ' || '''' || 20 21 dbms_rowid.rowid_create(1, 22 23 b.data_object_id, 24 25 a.relative_fno, 26 27 a.block_id + blocks - 1, 28 29 999) || '''; ' 30 31 from dba_extents a, dba_objects b 32 33 where a.segment_name = b.object_name 34 35 and a.owner = b.owner 36 37 and b.object_name = 'JASON' 38 39 and b.owner = 'SCOTT' 40 41 order by a.relative_fno, a.block_id;Copy the code

The output based on the segmented information is as follows.

The graph is output according to the information after the partition

With the above fragment information, we just need to put in the conditions to be filtered and use anonymous blocks to delete in batches. The specific implementation is as follows:

 1SQL> declare
 2
 3  cursor cur_rowid is
 4
 5    select dbms_rowid.rowid_create(1,
 6
 7                                   b.data_object_id,
 8
 9                                   a.relative_fno,
10
11                                   a.block_id,
12
13                                   0) begin_rowid,
14
15           dbms_rowid.rowid_create(1,
16
17                                   b.data_object_id,
18
19                                   a.relative_fno,
20
21                                   a.block_id + blocks - 1,
22
23                                   999) end_rowid
24
25      from dba_extents a, dba_objects b
26
27     where a.segment_name = b.object_name
28
29       and a.owner = b.owner
30
31       and b.object_name = 'JASON'
32
33       and b.owner = 'SCOTT'
34
35     order by a.relative_fno, a.block_id;
36
37  r_sql varchar2(4000);
38
39begin
40
41  FOR cur in cur_rowid LOOP
42
43    r_sql := 'delete SCOTT.jason where OBJECT_TYPE=' || '''' || 'INDEX' || '''' ||
44
45             ' and rowid between :1 and :2';
46
47    EXECUTE IMMEDIATE r_sql
48
49      using cur.begin_rowid, cur.end_rowid;
50
51    COMMIT;
52
53  END LOOP;
54
55end;

Copy the code

In the concrete implementation process, we only need to replace the corresponding SQL statement and user name object.

Although ROWID fragments are constructed by area for deletion, the efficiency is several times higher than that of simple delete, but the whole execution process is not parallel and requires manual operation in different Windows, so the implementation process is complicated. So is there a more efficient way?

The DBMS_PARALLEL_EXECUTE package is available for efficient DML operations on large tables starting with 11G R2. The ability to customize the degree of parallelism makes the DBMS_PARALLEL_EXECUTE package an optimal choice. The implementation code is as follows:

1SQL> SET SERVEROUTPUT ON 2 3SQL> BEGIN 4 5 DBMS_PARALLEL_EXECUTE.DROP_TASK ('test_task'); 6 7EXCEPTION WHEN OTHERS THEN 8 9NULL; 10 11END; 12 13/ 14 15 16 17SQL> DECLARE 18 19 l_task VARCHAR2(30) := 'test_task'; 20 21 l_sql_stmt VARCHAR2(32767); 22 23 l_try NUMBER; 24 25 l_status NUMBER; 26 27BEGIN 28 29 -- Create the TASK 30 31 DBMS_PARALLEL_EXECUTE.CREATE_TASK (task_name => l_task); 32 33 -- Chunk the table by the ROWID 34 35 DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID 36 37 ( 38 39 TASK_NAME => L_task, 40 41 TABLE_OWNER => 'JOE', <<< user name 42 43 TABLE_NAME => 'OB2', <<< table name 44 45 BY_ROW => TRUE, <<< value is TRUE, 46 47 chunk_size => 2500 <<< User-defined chunk size, which indicates 2500 acts as one chunk 48 49). 50 51 -- DML to be execute in parallel 52 53 l_sql_stmt := 'delete OB2 where object_type = ''SYNONYM'' and rowid BETWEEN  54 55 :start_id AND :end_id'; <<< SQL statement to execute 56 57 -- Run the task 58 59 DBMS_PARALLEL_EXECUTE.RUN_TASK 60 61 (62 63 TASK_NAME => l_task, 64 65 SQL_STMT => l_SQL_STMT, 66 67 LANGUAGE_FLAG => DBMS_SQL.NATIVE, 68 69 PARALLEL_LEVEL => 2 <<< User-defined execution parallelism 70 71); 72 73 -- If there is error, RESUME it for at most 2 times. 74 75 l_try := 0; 76 77 l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task); 78 79 WHILE(l_try < 2 and l_status ! = DBMS_PARALLEL_EXECUTE.FINISHED) 80 81 LOOP 82 83 l_try := l_try + 1; 84 85 DBMS_PARALLEL_EXECUTE.RESUME_TASK(l_task); 86 87 l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(l_task); 88 89 END LOOP; 90 91 -- Done with processing; drop the task 92 93 DBMS_PARALLEL_EXECUTE.DROP_TASK(l_task); 94 95EXCEPTION WHEN OTHERS THEN 96 97 DBMS_OUTPUT.PUT_LINE('Error in the code :' || SQLERRM); 98 99END; 100, 101 /Copy the code

As shown in the above script, the DBMS_PARALLEL_EXECUTE package is easy to use, and you only need to modify the remarks section in red to execute. ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID: ROWID Here is not detailed, if you want to further understand, you can search relevant information.

DBMS_PARALLEL_EXECUTE the basic principle of DBMS_PARALLEL_EXECUTE is to shard a large table by a specified chunk size (the number of rows or blocks can be specified by chunk size), and then perform parallel delete or other DML operations on multiple shards. After each fragment is completed, it is submitted immediately. At last, job is called for concurrency control.

Therefore, if you want to call the DBMS_PARALLEL_EXECUTE package, in addition to having access to the package, you must also have the permission to create jobs.

The basic execution flow of DBMS_PARALLEL_EXECUTE package is as follows.

1) Call create_task() to create a task.

Call create_chunk_by_rowid() to create the chunking rule.

3) Write DML operation statements that you need to execute.

4) Call run_task() to run the task.

5) Call drop_task() to delete the task after it is executed.

The DBMS_PARALLEL_EXECUTE package involves the following views:

1DBA_PARALLEL_EXECUTE_TASKS
2
3DBA_PARALLEL_EXECUTE_CHUNKS
4
5dba_scheduler_jobs
Copy the code

You can use the preceding views to monitor the task execution in real time.

“The END”

This article is excerpted from DBA’s Guide to Oracle with Your Left hand and MySQL with your Right

Recommended words: experience summary and best practice of senior database experts in the field of database, from simple to profound analysis of the operation and maintenance methodology of typical cases. This book is written by senior Oracle and MySQL technical experts of Metron Technology, accumulating the author’s years of experience and practical experience. It is also one of the few database technology books combining Oracle and MySQL on the market at present.