AnySQL.net

    在优化数据迁移的过程中, 发现有这样一段脚本, 要将一个表的数据分到很多的小表中. 如下所示(A表比较大, 其他的表相对较小):

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几分钟.