Oracle:比较包含CLOB的表并获取差异的最快方法 [英] Oracle: Fastest way to compare tables containing CLOB and get diff

问题描述

假设我有两个带有列Col1，Col2和Col3的表，分别是VARCHAR2， CLOB 和NUMBER类型.

Suppose I have two tables with columns, Col1, Col2 and Col3 which are VARCHAR2, CLOB and NUMBER types respectively.

如何获取这些表的差异? (即表B 中存在的记录列表，但表A 中不存在的记录列表)

How can I get the diff of these tables? (i.e The list of records that exist in the Table B, but not in the Table A)

Table A:
╔═══════╦═════════════════╦══════╗
║ Col1  ║      Col2       ║ Col3 ║
╠═══════╬═════════════════╬══════╣
║ P1111 ║ some_long_text1 ║ 1234 ║
║ P1111 ║ some_long_text1 ║ 1233 ║
║ P1111 ║ some_long_text2 ║ 1233 ║
╚═══════╩═════════════════╩══════╝

Table B:
╔═══════╦═════════════════╦══════╗
║ Col1  ║      Col2       ║ Col3 ║
╠═══════╬═════════════════╬══════╣
║ P1111 ║ some_long_text1 ║ 1234 ║
║ P1111 ║ some_long_text1 ║ 1235 ║
║ P1112 ║ some_long_text2 ║ 1233 ║
╚═══════╩═════════════════╩══════╝

Expected results:
╔═══════╦═════════════════╦══════╗
║ Col1  ║      Col2       ║ Col3 ║
╠═══════╬═════════════════╬══════╣
║ P1111 ║ some_long_text1 ║ 1235 ║
║ P1112 ║ some_long_text2 ║ 1233 ║
╚═══════╩═════════════════╩══════╝

推荐答案

要比较LOB类型，可以使用DBMS_LOB.COMPARE函数.

To compare LOB types you can use DBMS_LOB.COMPARE function.

SELECT table_b.* 
  FROM table_b
  LEFT JOIN table_a
    ON table_b.col1 = table_a.col1
   AND DBMS_LOB.COMPARE(table_b.col2, table_a.col2) = 0
   AND table_b.col3 = table_a.col3
 WHERE table_a.col1 IS NULL;

这篇关于Oracle:比较包含CLOB的表并获取差异的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！