0

I wanted to know if there is a way to optmimize a minus between 2 very large tables (10 to 100 million records), updated more than once a day. I already tried indexing them just to try but as I expected I got worse timings. Are there any other operations that can replace this minus (I tried a not exists but again worse results)?

Edit:

insert  into I$_F_TABLE 
select  *
from TW_F_TABLE 
minus
select *
from F_TABLE;

This is an ugly example, I'm sorry for the indentation and not writing a proper one.

Giacomo Giovannini
  • 177
  • 2
  • 2
  • 14
  • 3
    What do you mean by minus? Are you subtracting values? Posting the query or a example of what the query is doing might help. – Deadron Oct 03 '17 at 14:53
  • 1
    @Giacomo, try something like left-join equivalent to minus between the tables. https://stackoverflow.com/questions/21009748/how-to-replace-a-complex-sql-minus-query-with-left-outer-join-equivalent – Chetan_Vasudevan Oct 03 '17 at 15:20
  • Thanks, I'll try that – Giacomo Giovannini Oct 03 '17 at 15:21
  • So you're using that query to identify rows in TW_F_TABLE that are not present in F_TABLE? The tables must have the same structure for your query to work. But do they have the same primary key? You will probably get better quality answers if you provide the table structures, the number of rows, how many rows you expect to change each day... – Christian Palmer Oct 03 '17 at 15:22
  • The 2 tables have the same structure but not the same primary key. As I mentioned the 2 tables contain around 10 to 100 million records and the number of rows I expect to change are 100 thousands to 1 million. – Giacomo Giovannini Oct 03 '17 at 15:35

0 Answers0