分类: 服务器与存储
2008-07-26 14:42:59
CREATE TABLE A_1
SELECT A.* FROM A
WHERE A.ID IN (SELECT ID FROM B)
WHERE A.ID IN (SELECT ID FROM C)
WHERE A.ID IN (SELECT ID FROM D)
WHERE A.ID IN (SELECT ID FROM E)
CREATE TABLE A_2
SELECT A.* FROM A
WHERE A.ID NOT IN (SELECT ID FROM B)
WHERE A.ID IN (SELECT ID FROM C)
WHERE A.ID NOT IN (SELECT ID FROM D)
WHERE A.ID IN (SELECT ID FROM E)
CREATE TABLE A_3
SELECT A.* FROM A
WHERE A.ID NOT IN (SELECT ID FROM B)
WHERE A.ID IN (SELECT ID FROM C)
WHERE A.ID IN (SELECT ID FROM D)
WHERE A.ID NOT IN (SELECT ID FROM E)
......
每一步的执行步骤都在8-10分钟左右, 总共有类似的步骤7步, 全部耗时约60分钟. 于是将过程改写了一下:
CREATE TABLE A_TEMP
SELECT A.*, B.ID B_ID, C.ID C_ID, D.ID D_ID, E.ID E_ID
FROM A,
(SELECT DISTINCT ID FROM B) B,
(SELECT DISTINCT ID FROM C) C,
(SELECT DISTINCT ID FROM D) D,
(SELECT DISTINCT ID FROM E) E
WHERE A.ID = B.ID(+) AND A.ID = C.ID(+)
AND A.ID = D.ID(+) AND A.ID = E.ID(+);
CREATE TABLE A_1 AS
SELECT * FROM A_TEMP
WHERE B_ID IS NOT NULL AND C_ID IS NOT NULL
AND D_ID IS NOT NULL AND E_ID IS NOT NULL;
CREATE TABLE A_2 AS
SELECT * FROM A_TEMP
WHERE B_ID IS NULL AND C_ID IS NOT NULL
AND D_ID IS NULL AND E_ID IS NOT NULL;
CREATE TABLE A_3 AS
SELECT * FROM A_TEMP
WHERE B_ID IS NULL AND C_ID IS NOT NULL
AND D_ID IS NOT NULL AND E_ID IS NULL;
......
改造后, 第一步花了15分钟, 后面每一步只花1分钟, 前后一想, 省了大约40几分钟.