如果希望一条语句,那就只能用merge了,参见:http://community.csdn.net/Expert/topic/4598/4598784.xml不过你的数据量如此大,建议还是分开更新、插入两个步骤更快,因为merge是逐行进行的,对于大表会很难看。
解决方案 »
- 分区表的全部查询问题。
- 新手,求写一个Linux自动登陆ORACLE数据库的.sh文件
- 连接ORACLE时 出现ORA-28547错误
- 求教一个关于动态数据库的设计
- ora-14452 试图创建,更改或删除正在使用的临时表中的索引
- toad for oracle 和 pl/sql 哪个用的比较多,哪个比较好呢?
- 新手请教
- 关于Procedure的一个问题
- pb环境,同样的sql在ms sql server执行正常但是在oracle中无数据,为何?谢谢帮助
- 救命呀!!!!!!!!!1
- 呵呵概念问题:oracle的包装上写着:50用户。这里的50用户是不是连接数为50之后就不能再有新的连接进来的拉?
- oracle数据库中的表因事务处理挂起无法查看,如何处理?
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm#SQLRF01606
*
ERROR 位于第 1 行:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 192763 更改 1237346 时间 03/03/2006 17:26:10
ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ZDH\REDO01.LOG'该如何解决?
先不管重复全部插入(如果重复比例不大).
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 problemthanks 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.48As 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
*
ERROR 位于第 1 行:
ORA-00368: 重做日志块中的校验和错误
ORA-00353: 日志损坏接近块 192763 更改 1237346 时间 03/03/2006 17:26:10
ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ZDH\REDO01.LOG'该如何解决?
如果没有就clear log group,再用不完全恢复,再open resetlogs