0

I have two identical tables in Oracle (identical by structure) - table_temp and table_total.

I need to insert in table_total rows from table_temp based on following condition - only rows from table_temp with PLAYER_ID value which doesn't exist in table_total should be inserted in table_total.

Table_temp has 112 milions of records.

I tried many solutions but it took too long time to process so I stopped execution. This is my last try I stopped after 7 hours:

INSERT INTO table_total
SELECT * 
FROM table_temp 
WHERE table_temp.player_id NOT IN (SELECT player_id FROM table_total)

What is best/fastest solution for this? Indexing column is not acceptable solution...

punky
  • 125
  • 2
  • 12
  • 1
    Short of indexing, I don't see any obvious way to speed up what you have above. – Tim Biegeleisen Jun 23 '22 at 06:42
  • Try "PL/SQL bulk loads for the forall PL/SQL operator", may be you will get better performance with this. – mksmanjit Jun 23 '22 at 06:50
  • Please provide the execution plan for the statement. What are the sizes of both the tables in terms of # of rows and gigabytes – astentx Jun 23 '22 at 07:02
  • @astentx, I don't see any statement numbers in execution plan for this statement. I know what you want, but I don't see that numbers like usually... – punky Jun 23 '22 at 08:00
  • @punky run `explain plan for ` and then `select * from dbms_xplan.display(format => 'ALL')`. This will show you the (estimated) number or rows and bytes to be processed – astentx Jun 23 '22 at 17:45
  • Without indexes, on 100+ million rows, no wonder it takes ages to complete. – Littlefoot Jun 23 '22 at 20:11

1 Answers1

0

Use a MERGE statement:

MERGE INTO table_total dst
USING table_temp src
ON (src.player_id = dst.player_id)
WHEN NOT MATCHED THEN
  INSERT (player_id, col1, col2, col3)
  VALUES (scr.player_id, src.col1, src.col2, src.col3);
MT0
  • 143,790
  • 11
  • 59
  • 117