Shulou(Shulou.com)06/01 Report--

1.1 look up redundant duplicate records in the table

-- query all duplicated data

Select DETAIL_ID,COMMENT_BODY,count (1)

From BBSCOMMENT

Group by DETAIL_ID,COMMENT_BODY

Having count (1) > 1;-1955 articles

Or

Select rownum,DETAIL_ID,COMMENT_BODY from

(select DETAIL_ID,COMMENT_BODY, (count (1) over (partition by DETAIL_ID,COMMENT_BODY)) rk

From BBSCOMMENT)

Where rk > 1

1.2 shows all non-redundant data

This command shows all the non-redundant data

Select min (COMMENT_ID) as COMMENT_ID,DETAIL_ID,COMMENT_BODY

From BBSCOMMENT

Group by DETAIL_ID,COMMENT_BODY;-21453, the reason why this value is not equal to the total number of records in the table-1955 is that some of the 1955 records are repeated more than once.

1.3 if the number of records is small (thousand level), you can make the above statement into a subquery and delete it directly.

-- if the amount of data in the table is not very large (less than 1000 items), you can make the above statement into a subquery and delete it directly.

Delete from BBSCOMMENT where COMMENT_ID not in (

Select min (COMMENT_ID)

From BBSCOMMENT

Group by DETAIL_ID,COMMENT_BODY

782 seconds, in my case, 20,000 records, more than 2,000 repeated records (too slow! )

1.4 another method of deletion

This statement can also achieve the above functions, but it is not easy to test, the data has been deleted by me

-- Delete condition 1: records with duplicate data; condition 2: keep records with minimum rowid.

Delete from BBSCOMMENT a

Where

(a. DETAILING IDM. A. Content body) in (select DETAIL_ID,COMMENT_BODY from BBSCOMMENT group by DETAIL_ID,COMMENT_BODY having count (1) > 1)

And rowid not in (select min (rowid) from BBSCOMMENT group by DETAIL_ID,COMMENT_BODY having count (1) > 1)

Or

Delete from BBSCOMMENT a

Where rowid not in

(select min (row_id) from BBSCOMMENT group by DETAIL_ID,COMMENT_BODY)

Note: the rowid is the only id that marks and records the physical location. Each row of data in a table in an oracle database has a unique identifier, or rowid, which is usually used within oracle to access data. Rowid requires 10 bytes of storage and is displayed in 18 characters. This value indicates the physical location of the row in the oracle database. You can use rowid in a query to indicate that the value is included in the query results.

1.5 large amount of data is still convenient and fast to use PL/SQL.

Declare

-- define the storage structure

Type bbscomment_type is record

(

Comment_id BBSCOMMENT.COMMENT_ID%type

Detail_id BBSCOMMENT.DETAIL_ID%type

Comment_body BBSCOMMENT.COMMENT_BODY%type

);

Bbscomment_record bbscomment_type

-variables that can be compared

V_comment_id BBSCOMMENT.COMMENT_ID%type

V_detail_id BBSCOMMENT.DETAIL_ID%type

V_comment_body BBSCOMMENT.COMMENT_BODY%type

-- other variables

V_batch_size integer: = 5000

V_counter integer: = 0

Cursor cur_dupl is

-- take out all duplicate records

Select COMMENT_ID, DETAIL_ID, COMMENT_BODY

From BBSCOMMENT

Where (DETAIL_ID, COMMENT_BODY) in (

-- these records are duplicated.

Select DETAIL_ID, COMMENT_BODY

From BBSCOMMENT

Group by DETAIL_ID, COMMENT_BODY

Having count (1) > 1)

Order by DETAIL_ID, COMMENT_BODY

Begin

For bbscomment_record in cur_dupl loop

If v_detail_id is null or (bbscomment_record.detail_id! = v_detail_id or nvl (bbscomment_record.comment_body,'')! = nvl (v_comment_body,'') then

-- enter for the first time, change records, and re-assign values

V_detail_id: = bbscomment_record.detail_id

V_comment_body: = bbscomment_record.comment_body

Else

-- other records are deleted

Delete from BBSCOMMENT where COMMENT_ID = bbscomment_record.comment_id

V_counter: = v_counter + 1

If mod (v_counter, v_batch_size) = 0 then-- every number of entries submitted commit;end if;end if;end loop;if v_counter > 0 then-- Last submission commit;end if;dbms_output.put_line (to_char (v_counter) | | 'records deleted!')

Exception

When others then

Dbms_output.put_line ('sqlerrm-- >' | | sqlerrm)

Rollback

End

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.