oracle diff：如何比较两个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/688537/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
oracle diff: how to compare two tables?
提问by Mark Harrison
Suppose I have two tables, t1 and t2 which are identical in layout but which may contain different data.
假设我有两个表 t1 和 t2，它们的布局相同，但可能包含不同的数据。
What's the best way to diff these two tables?
区分这两个表的最佳方法是什么？
回答by L. Holanda
Try this:
尝试这个：
(select * from T1 minus select * from T2) -- all rows that are in T1 but not in T2
union all
(select * from T2 minus select * from T1) -- all rows that are in T2 but not in T1
;
No external tool. No performance issues with union all.
没有外部工具。没有性能问题union all。
回答by maxyfc
You can try using set operations: MINUSand INTERSECT
您可以尝试使用集合操作：MINUS和INTERSECT
See here for more details:
O'Reilly - Mastering Oracle SQL - Chapter 7 - Set Operations
有关更多详细信息，请参见此处：
O'Reilly - 掌握 Oracle SQL - 第 7 章 - 设置操作
回答by Aaron Digulla
You can use a tool like AQTto create diffs between tables.
您可以使用AQT 之类的工具在表之间创建差异。
Another approach would be to dump the tables to a text file and use a diff tool like WinMerge. With this approach, you can use complex SQL to turn the tables into the same layout, first.
另一种方法是将表转储到文本文件并使用WinMerge 之类的差异工具。使用这种方法，您可以首先使用复杂的 SQL 将表转换为相同的布局。
回答by David Aldridge
For this kind of question I think you have to be very specific about what you are looking for, as there are many ways of interpreting it and many different approaches. Some approaches are going to be too big a hammer if your question does not warrant it.
对于这类问题，我认为您必须非常具体地说明您在寻找什么，因为有很多解释它的方法和许多不同的方法。如果您的问题不值得，某些方法将是一个太大的锤子。
At the simplest level, there is "Is the table data exactly the same or not?", which you might attempt to answer with a simple count comparison before moving on to anything more complex.
在最简单的层面上，有“表数据是否完全相同？”，您可能会尝试通过简单的计数比较来回答，然后再进行更复杂的处理。
At the other end of the scale there is "show me the rows from each table for which there is not an equivalent row in the other table" or "show me where rows have the same identifying key but different data values".
在比例尺的另一端，有“向我显示每个表中在另一个表中没有等效行的行”或“向我显示哪些行具有相同的标识键但不同的数据值”。
If you actually want to sync Table A with Table B then that might be relatively straightforward, using a MERGE command.
如果您确实想将表 A 与表 B 同步，那么使用 MERGE 命令可能相对简单。
回答by Joan Vilari?o
Fast solution:
快速解决方案：
SELECT * FROM TABLE1
MINUS
SELECT * FROM TABLE2
No records should show...
没有记录应该显示...
回答by Devart
You may try dbForge Data Compare for Oracle, a **freeGUI tool for data comparison and synchronization, that can do these actions over all database or partially.
您可以尝试dbForge Data Compare for Oracle ，这是一个用于数据比较和同步的**免费GUI 工具，它可以对所有数据库或部分数据库执行这些操作。
回答by Rex CoolCode Charles
In addition to some of the other answers provided, if you wanted to look at the differences in table structure with a table that might have the similar but differing structure, you could do this in multiple ways:
除了提供的一些其他答案之外，如果您想查看表结构与可能具有相似但不同结构的表的差异，您可以通过多种方式执行此操作：
First- If using Oracle SQL Developer, you could run a describe on both tables to compare them:
首先- 如果使用 Oracle SQL Developer，您可以在两个表上运行描述来比较它们：
descr TABLE_NAME1
descr TABLE_NAME2
Second- The first solution may not be ideal for larger tables with a lot of columns. If you only want to see the differences in the data between the two tables, then as mentioned by several others, using the SQL Minus operator should do the job.
第二- 第一个解决方案可能不适合具有大量列的较大表。如果您只想查看两个表之间数据的差异，那么正如其他几个人所提到的，使用 SQL Minus 运算符应该可以完成这项工作。
Third- If you are using Oracle SQL Developer, and you want to compare the table structure of two tables using different schemas you can do the following:
第三- 如果您使用 Oracle SQL Developer，并且想要比较使用不同模式的两个表的表结构，您可以执行以下操作：
- Select "Tools"
- Select "Database Diff"
- Select "Source Connection"
- Select "Destination Connection"
- Select the "Standard Object Types" you want to compare
- Enter the "Table Name"
- Click "Next" until you reach "Finish"
- Click "Finish"
- NOTE: In steps 3 & 4 is where you would select the differing schemas in which the objects exist that you want to compare.
- 选择“工具”
- 选择“数据库差异”
- 选择“源连接”
- 选择“目标连接”
- 选择要比较的“标准对象类型”
- 输入“表名”
- 单击“下一步”直到到达“完成”
- 点击“完成”
- 注意：在步骤 3 和 4 中，您可以选择要比较的对象所在的不同架构。
Fourth- If the tables two tables you wish to compare have more columns, are in the same schema, have no need to compare more than two tables and are unappealing to compare visually using the DESCR command you can use the following to compare the differences in the table structure:
第四- 如果您希望比较的两个表有更多列，在同一架构中，不需要比较两个以上的表，并且使用 DESCR 命令进行视觉比较不吸引人，您可以使用以下内容来比较差异表结构：
select
a.column_name || ' | ' || b.column_name,
a.data_type || ' | ' || b.data_type,
a.data_length || ' | ' || b.data_length,
a.data_scale || ' | ' || b.data_scale,
a.data_precision || ' | ' || b.data_precision
from
user_tab_columns a,
user_tab_columns b
where
a.table_name = 'TABLE_NAME1'
and b.table_name = 'TABLE_NAME2'
and (
a.data_type <> b.data_type or
a.data_length <> b.data_length or
a.data_scale <> b.data_scale or
a.data_precision <> b.data_precision
)
and a.column_name = b.column_name;
回答by Jar Yit
Try This,
尝试这个，
alter session set "_convert_set_to_join"= true;
更改会话集“_convert_set_to_join”= true;
The other alternative is to rewrite the SQL query manually [replacing the minus operator with a NOT IN subquery] evidences about 30% improvement in execution time .
另一种选择是手动重写 SQL 查询 [用 NOT IN 子查询替换减号运算符] 证明执行时间缩短了 30%。
select *
from A
where (col1,col2,?) not in
(select col1,col2,? from B)
union all
select * from B
where (col1,col2,?) not in
(select col1,col2,? from A);
I have referred from this post click here
我从这篇文章中提到点击这里
回答by Peter
Below is my solution - taking into account that the diffed tables can have duplicate rows. The accepted answer does not take this into account which would give you wrong results in case of duplicates. I am taking care of duplicate rows by numbering them using row_number() and then comparing the numbered rows:
下面是我的解决方案 - 考虑到差异表可能有重复的行。接受的答案没有考虑到这一点，这会在重复的情况下给你错误的结果。我通过使用 row_number() 对它们进行编号来处理重复的行，然后比较编号的行：
-- TEST TABLES
create table t1 (col_num number,col_date date,col_varchar varchar2(400));
create table t2 (col_num number,col_date date,col_varchar varchar2(400));
-- TEST DATA
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in both');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in both');
insert into t1 values (null,null,'I am in both with nulls');
insert into t2 values (null,null,'I am in both with nulls');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in T1 only');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in T2 only');
insert into t1 values (null,null,'I am in T1 only with nulls');
insert into t2 values (null,null,'I am in T2 only with nulls');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 but not in T2');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 but not in T2');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 but not in T1');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 but not in T1');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 and once in T2');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 and once in T2');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 and once in T2');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 and once in T1');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 and once in T1');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 and once in T1');
-- THE DIFF
-- All columns need to be named in the partition by clause, it is not possible to just say 'partition by *'
-- The column used in the order by clause does not matter in terms of functionality
(
select 'In T1 but not in T2' diff,s.* from (
select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t1 t
minus
select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t2 t
) s
) union all (
select 'In T2 but not in T1' diff,s.* from (
select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t2 t
minus
select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t1 t
) s
);
回答by Kumar
select * from table1 where table1.col1 in
(select table2.col1 from table2)
select * from table1 where table1.col1 in
(select table2.col1 from table2)
Assuming col1is the primary key column and this will give all rows in table1respective to the table2column 1.
假设col1是主键列，这将给出table1与第table21 列相关的所有行。
select * from table1 where table1.col1 not in
(select table2.col1 from table2)
select * from table1 where table1.col1 not in
(select table2.col1 from table2)
Hope this helps
希望这可以帮助
