-- 获取表B中记录最大id
select max(id) from B 
-- 根据上面获取的id；提取最新的A表记录；根据userid去重；并插入中间表TMP_B;
INSERT INTO TMP_B
  SELECT id, userid, info
    FROM (select id,
                 userid,
                 info,
                 ROW_NUMBER() OVER(PARTITION BY userid ORDER BY id) RN
            FROM A
           WHERE id > ?)
   WHERE RN = 1;
-- 把上面获取的记录；不存在则插入表B
insert into B
  select id, userid, info
    from TMP_B
   where userid not in (select userid from B);
            
-- 或者用not exists
insert into B
  select id, userid, info
    from TMP_B tb
   where not exists (select 1 from B b where b.userid = tb.userid);

select id, userid, info
    from TMP_B
   where userid not in (select userid from B);  
-- 插入实验数据

-- 在表B插入百万条记录
insert into B select level, dbms_random.string('x',8), 'lottu'||level from dual connect by level <= 1000000;
-- 在表TMP_B插入十万条记录
insert into TMP_B select level, dbms_random.string('x',8), 'lottu'||level from dual connect by level <= 100000;

insert into B
select id, userid, info  from TMP_B tb
where not exists (select 1 from B b where b.userid = tb.userid);
100000 rows created.
Elapsed: 00:00:00.52
Execution Plan
----------------------------------------------------------
Plan hash value: 3462170537
------------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)|Time     |
------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT     |     |   115K|  4389K|     |  2994   (1)|00:00:36 |
|   1 |  LOAD TABLE CONVENTIONAL | B     |     |     |     |          |     |
|*  2 |   HASH JOIN ANTI     |     |   115K|  4389K|  4960K|  2994   (1)|00:00:36 |
|   3 |    TABLE ACCESS FULL     | TMP_B |   115K|  3601K|     |   137   (1)|00:00:02 |
|   4 |    TABLE ACCESS FULL     | B     |  1375K|  9403K|     |  1372   (1)|00:00:17 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."USERID"="TB"."USERID")
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
      0  recursive calls
       3221  db block gets
       6320  consistent gets
     35  physical reads
    3649980  redo size
    845  bytes sent via SQL*Net to client
    866  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
     100000  rows processed

merge into B b
using TMP_B tb
on (tb.userid = b.userid)
WHEN NOT MATCHED THEN
  INSERT (b.id, b.userid, b.info) values (tb.id, tb.userid, tb.info);

Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 2722554344
--------------------------------------------------------------------------------------
 
| Id  | Operation         | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time|
--------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |         |     115K|    7203K|         |    5298   (1)| 00:01:04 |
|   1 |  MERGE             | B     |         |         |         |          ||
|   2 |   VIEW             |         |         |         |         |          ||
|*  3 |    HASH JOIN OUTER   |         |     115K|    8553K|    4960K|    5298   (1)| 00:01:04 |
|   4 |     TABLE ACCESS FULL| TMP_B |     115K|    3601K|         |     137   (1)| 00:00:02 |
|   5 |     TABLE ACCESS FULL| B     |    1336K|      56M|         |    1373   (1)| 00:00:17 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("TB"."USERID"="B"."USERID"(+))
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
       5496  consistent gets
      0  physical reads
      0  redo size
    844  bytes sent via SQL*Net to client
    896  bytes received via SQL*Net from client
      3  SQL*Net roundtrips to/from client
      1  sorts (memory)
      0  sorts (disk)
      0  rows processed

INSERT INTO B
 SELECT *
FROM A on conflict (userid) do nothing;