在Oracle中用减号操作比较两个表 [英] Compare two tables with minus operation in oracle

问题描述

某些表的数据需要在我的系统中更新(或删除，插入). 但是我想知道更新，删除和插入了哪些数据. 因此，在更改数据之前，我将以不同的模式备份表 就像这样:

Some tables' data need to be updated(or deleted , inserted) in my system. But I want to know which data are updated,deleted and inserted. So before the data are changed ,I will backup the table in different schema just like this:

create table backup_table as select * from schema1.testtable

并且数据更改后，我想找到backup_table之间的区别 和testtable，我想将差异保存到备份架构中的表中.

and after the data are changed,I want to find the difference between backup_table and testtable ,and I want to save the difference into a table in the backup schema.

我将运行的sql如下:

the sql I will run is like this:

 CREATE TABLE TEST_COMPARE_RESULT
 AS
 SELECT 'BEFORE' AS STATUS, T1.*
 FROM (
        SELECT * FROM backup_table
        MINUS
        SELECT * FROM schema1.testtable
 ) T1
 UNION ALL
 SELECT 'AFTER' AS STATUS, T2.*
 FROM (
        SELECT * FROM schema1.testtable
        MINUS
        SELECT * FROM backup_table
  ) T2

我担心的是，我听说减号运算会使用 很多系统资源.在我的系统中，某些表大小将超过700M.所以我想 知道oracle将如何读取内存(PGA ??)或临时表空间中的700M数据吗? 以及如何确保资源足以进行比较操作?

What I am worried about is that I heared about that the minus operation will use a lot of system resource.In my sysytem, some table size will be over 700M .So I want to know how oracle will read the 700M data in memory (PGA??) or the temporary tablespace? and How I should make sure that the resource are enough to to the compare operation?

推荐答案

减号确实是一项资源密集型任务.它需要读取两个表并进行排序以比较两个表.但是，Oracle有先进的技术可以做到这一点.如果无法将两个表都加载到内存(SGA)中.是的，它将使用临时空间进行排序.但我建议您尝试一下.只需运行查询，看看会发生什么.数据库不会受到影响，并且您始终可以停止执行语句.

Minus is indeed a resource intensive task. It need to read both tables and do sorts to compare the two tables. However, Oracle has advanced techniques to do this. It won't load the both tables in memory(SGA) if can't do it. It will use, yes, temporary space for sorts. But I would recommend you to have a try. Just run the query and see what happens. The database won't suffer and allways you can stop the execution of statement.

您可以采取哪些措施来提高查询性能:

What you can do to improve the performance of the query:

首先，如果您确定不会更改任何列，请不要包括它们. 所以，最好写:

First, if you have columns that you are sure that won't changed, don't include them. So, is better to write:

select a, b from t1
minus 
select a, b from t2

比使用select * from t多，这是因为工作量较小.如果这两列多于该列.

than using a select * from t, if there are more than these two columns, because the work is lesser.

第二，如果要比较的数据量对于您的系统来说确实很大(临时空间太小)，则应尝试按块比较它们:

Second, if the amount of data to compare si really big for your system(too small temp space), you should try to compare them on chunks:

select a, b from t1 where col between val1 and val2
minus 
select a, b from t2 where col between val1 and val2 

当然，除了minus之外，另一种可能性是拥有一些日志列，比如说update_date.选择update_date大于过程开始的位置将显示更新的记录.但这取决于您如何更改数据库模型和etl代码.

Sure, another possibility than minus is to have some log columns, let's say updated_date. selecting with where updated_date greater than start of process will show you updated records. But this depends on how you can alter the database model and etl code.

这篇关于在Oracle中用减号操作比较两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！