试验表包括200万记录，其中20万记录是重复的。
比较新建表然后改名和原表删除两种去重方法。
首先初始化数据。
1.新建一个表，删除原表，然后将新建的表改名，大约用时90s.
CREATE TABLE temp
AS
SELECT *
FROM test_t a
WHERE a.ROWID = (
SELECT MIN (b.ROWID)
FROM test_t b
WHERE a.name = b.name
);
CREATE INDEX idx ON temp (name);
ALTER TABLE temp ADD CONSTRAINT temp_pk PRIMARY KEY (id);
drop table test_t purge;
alter table temp rename to test_t;
2.源表删除，大约用时2分钟。
DELETE test_t a
WHERE a.ROWID > (
SELECT MIN (b.ROWID)
FROM test_t b
WHERE a.name = b.name
);
王工实验的原贴
http://blog.csdn.net/wzy0623/article/details/10199005
比较新建表然后改名和原表删除两种去重方法。
首先初始化数据。
-
CREATE TABLE test_t
-
(
-
id NUMBER (8) NOT NULL PRIMARY KEY,
-
name VARCHAR2 (32)
-
);
-
-
BEGIN
-
FOR i IN 1 .. 1800000
-
LOOP
-
INSERT INTO test_t (id, name)
-
VALUES (i, SYS_GUID ());
-
END LOOP;
-
-
COMMIT;
-
END;
-
/
-
-
INSERT INTO test_t (id, name)
-
SELECT 1800000 + (id / 9), name
-
FROM test_t
-
WHERE MOD (id, 9) = 0;
-
-
COMMIT;
-
-
CREATE INDEX idx_test_anme
-
ON test_t (name);
-
- ANALYZE TABLE test_t COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;
CREATE TABLE temp
AS
SELECT *
FROM test_t a
WHERE a.ROWID = (
SELECT MIN (b.ROWID)
FROM test_t b
WHERE a.name = b.name
);
CREATE INDEX idx ON temp (name);
ALTER TABLE temp ADD CONSTRAINT temp_pk PRIMARY KEY (id);
drop table test_t purge;
alter table temp rename to test_t;
-
SQL> set timing on
-
SQL> CREATE TABLE temp
-
2 AS
-
3 SELECT *
-
4 FROM test_t a
-
5 WHERE a.ROWID = (SELECT MIN (b.ROWID)
-
6 FROM test_t b
-
7 WHERE a.name = b.name);
-
-
表已创建。
-
-
已用时间: 00: 00: 58.48
-
SQL>
-
SQL>
-
SQL> CREATE INDEX idx
-
2 ON temp (name);
-
-
索引已创建。
-
-
已用时间: 00: 00: 24.11
-
SQL>
-
SQL> ALTER TABLE temp ADD CONSTRAINT temp_pk PRIMARY KEY (id);
-
-
表已更改。
-
-
已用时间: 00: 00: 04.88
-
SQL>
-
SQL> drop table test_t purge;
-
-
表已删除。
-
-
已用时间: 00: 00: 00.94
-
SQL>
-
SQL> alter table temp rename to test_t;
-
-
表已更改。
-
-
已用时间: 00: 00: 00.47
- SQL>
DELETE test_t a
WHERE a.ROWID > (
SELECT MIN (b.ROWID)
FROM test_t b
WHERE a.name = b.name
);
-
SQL> DELETE test_t a
-
2 WHERE a.ROWID > (SELECT MIN (b.ROWID)
-
3 FROM test_t b
-
4 WHERE a.name = b.name);
-
-
已删除200000行。
-
- 已用时间: 00: 02: 12.25
http://blog.csdn.net/wzy0623/article/details/10199005
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29254281/viewspace-776216/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
查看引起阻塞的Session
请登录后发表评论
登录
全部评论
