Oracle SQL clob queries duplicate data with multiple fields

Oracle SQL clob queries duplicate data. Multi-field COMPARE function data clob fields cannot be group by or distinct during query. Therefore, it is very troublesome to find duplicate data based on clob, most people on the Internet propose to use DBMS_LOB.SUBSTR to solve the problem. DBMS_LOB.SUBSTR converts clob into string segments and then performs group by. In addition, if there are too many words, segment conversion is required. This operation is too troublesome, in addition, it is easy to see the error that the string buffer is too small. After searching the Internet for a long time, we suddenly found that Dbms_Lob.Compare can solve this problem. COMPARE functions the COMPARE function is used to COMPARE the two large objects with a given degree of length for a given offset. This function returns zero. If two large objects have the same length as zero for the specified offset, if the objects do not have the same null value, if amount, offset_1, or offset_2 is less than zero. The type of large objects of the same region must be the same. The following is my final SQL statement. remark is the clob field.

1 Select * From Userinfo Where Id In (2 Select U. Id From Userinfo U, Userinfo User_Info3 Where U. Id! = User_Info.Id4 And U. typeid = User_Info.Typeid5 And u. theTime = User_Info.theTime6 And u. createId = User_Info.createId7 And Dbms_Lob.Compare (u. remark, User_Info.Remark) = 08 Group By u. id9) Order By id;