如何让insert时重复记录被覆盖？

[hzbigdog]

有两个表
A表(大型表，有2-3千万行记录)
-------
12
134
12
12
12
12
1234
567

B表(大表，有1-2千万行记录)
--------
12
134
1234
567

B表是目标表，要将A表的记录插入到B表。但是把A表的记录往B表插入时，可能有B报表中已经有这个值，如何把A表以最高的效率全部插入B表，并且保证B表中不会产生重复数据项。

不用指针，能否用一个insert语句办到？帮帮忙！啊！
注意主要是A表非常大

[boydgmx]

如果希望一条语句，那就只能用merge了，参见：

http://community.csdn.net/Expert/topic/4598/4598784.xml

不过你的数据量如此大，建议还是分开更新、插入两个步骤更快，

因为merge是逐行进行的，对于大表会很难看。

[zhpsam109]

使用merge，具体请参考：
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm#SQLRF01606

[chenzhj13141983]

MARK

[zhangdinghua]

alter database open
*
ERROR 位于第 1 行:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 192763 更改 1237346 时间 03/03/2006 17:26:10
ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ZDH\REDO01.LOG'

该如何解决？

[cenlmmx]

不要什么都用merge了,会死得很惨.
先不管重复全部插入(如果重复比例不大).
1. 对大表操作最重要的要减少日志量,那就要alter table 大表 nologging,否则系统光写日志就忙不过来了;
2. 再一个使用append来插入,它会直接把新数据插到HWM后面,速度很快.
3. 如果你有多个CPU,可以考虑开并行,设并行度.

删除重复数据
看看TOM怎么删除重复记录的.
plz explain how to remove duplicate records from a large table containing about
5 million records in a single run and with a lesser time.
i tried it with following query but it takes 10 hours of time.

delete from test1 where rowid not in (select min(rowid) from test1 group by
rc_no);

even after incraesing the rollback segs tablespace to 7gb
we are not getting desired results and while using not in clause and cursor we
generally come across this kind of problem

thanks

and we said...
I'd generate the set of rowids to delete using analytics and then delete them..
like this:


ops$tkyte@ORA9IR2> create table t as select * from cust;

Table created.

Elapsed: 00:00:03.64
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*), count(distinct cust_seg_nbr) from t;

COUNT(*) COUNT(DISTINCTCUST_SEG_NBR)
---------- ---------------------------
1871652 756667

Elapsed: 00:00:05.30
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> delete from t
2 where rowid in ( select rid
3 from ( select rowid rid,
4 row_number() over
5 (partition by cust_seg_nbr order by
rowid) rn
6 from t
7 )
8 where rn <> 1 )
9 /

1114985 rows deleted.

Elapsed: 00:01:46.06
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*), count(distinct cust_seg_nbr) from t;

COUNT(*) COUNT(DISTINCTCUST_SEG_NBR)
---------- ---------------------------
756667 756667

Elapsed: 00:00:02.48



As for the RBS -- it'll get as big as it needs to be in order to process the
delete -- every index will make it "larger" and take longer as well (index
maintainence is expensive)

if you are deleting "alot of the rows" you might be better off disabling
indexes, doing the delete and rebuilding them.


OR, creating a new table that just keeps the "right records" and dropping the
old table:


ops$tkyte@ORA9IR2> create table t as select * from cust;

Table created.

Elapsed: 00:00:02.41
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*), count(distinct cust_seg_nbr) from t;

COUNT(*) COUNT(DISTINCTCUST_SEG_NBR)
---------- ---------------------------
1871652 756667

Elapsed: 00:00:04.60
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2
2 as
3 select cust_seg_nbr
4 from ( select t.*, row_number() over (partition by cust_seg_nbr order by
rowid) rn
5 from t
6 )
7 where rn = 1
8 /

Table created.

Elapsed: 00:00:10.93
ops$tkyte@ORA9IR2> drop table t;

Table dropped.

Elapsed: 00:00:00.56
ops$tkyte@ORA9IR2> rename t2 to t;

Table renamed.

Elapsed: 00:00:00.01
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*), count(distinct cust_seg_nbr) from t;

COUNT(*) COUNT(DISTINCTCUST_SEG_NBR)
---------- ---------------------------
756667 756667

Elapsed: 00:00:01.18

[zhangdinghua]

to cenlmmx(学海无涯苦作舟) ； 在线等！！！！！

alter database open
*
ERROR 位于第 1 行:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 192763 更改 1237346 时间 03/03/2006 17:26:10
ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ZDH\REDO01.LOG'

该如何解决？

[cenlmmx]

找到REDO01.LOG的镜像,copy过来覆盖REDO01.LOG,再recover database,再open.
如果没有就clear log group,再用不完全恢复,再open resetlogs