"地瓜哥"博客网

　　前两天，测试组在造数据的时候，遇到一个挺有趣的问题。当时写了一个SQL，自我感觉良好，但是实际执行起来，效率及其低下。后来，经过我们的DBA的改造，效率得到了提升。著一小文以记之。

--现在有两个表，结果如下：

--地区表，有两千条数据。主键area_id的值就是从1到2000。
CREATE TABLE area (
   area_id  DECIMAL(6, 0) PRIMARY KEY,
   area_name VARCHAR(50)
);

--人员表，有一亿条数据。主键person_id没有任何规律，数据差别也比较大。
CREATE TABLE person (
   person_id  DECIMAL(10, 0) PRIMARY KEY,
   person_name VARCHAR(20) ,
   rlt_area_id DECIMAL(6, 0)
);

--给person添加外键。（在设计时，可能会设计这个外键。
--但是，在创建数据库时，为了提高性能，并不一定会建立这个外键约束。）
ALTER TABLE person ADD CONSTRAINT fk_ref FOREIGN KEY(rlt_area_id) 
	REFERENCES area(area_id) ON DELETE CASCADE ON UPDATE RESTRICT;  

	
--在制造测试数据时，person中的rlt_area_id字段，没有添加相应的值。
--操作：
--使用SQL语句，更新person表中的rlt_area_id的值，
--将人员尽可能的平均分配给每个地区。

--第一种想法
--由于两个表直接除了area_id之外，其余没有明显的关联字段。
--person_id相差较大，不可利用。person_name则是字符类型的。
--所以，就需要构造出一个字段。
--而 row_number就是一个不错的选择。所以，构造字段的SQL如下：
SELECT row_number () OVER () AS rn, i.person_id FROM person i;

--然后，对于rn除以2000取模，就能和area_id构造一个相等的关联。
--综合起来，SQL如下：
UPDATE person p
   SET p.rlt_area_id =
          (SELECT area_id
             FROM    area a
                  LEFT JOIN
                     (SELECT row_number () OVER () AS rn, i.person_id
                        FROM person i) ip
                  ON ip.person_id = p.person_id
                 AND mod (ip.rn, 2000) + 1 = a.area_id
           FETCH FIRST 1 ROWS ONLY);

--这个SQL满足条件，但是实际执行起来，效率却极其低下。
--运行了十几分钟还没执行完成。后来，就直接停止了。


--后来，在请教我们的DBA之后，根据这个思路，
--把这个SQL分成了“三步走”战略，效率得到了很大提升。

--以下是使用临时字段进行更新的步骤：
--增加临时字段记录序号
ALTER TABLE person ADD rn INTEGER; 
reorg TABLE person;
update person set rn = row_number() over();
 
 --如果area中的没有数据比较有规律的字段，则也可以题那就一个临时字段
 --注：以下按照增加临时字段的情况来说明
ALTER TABLE area ADD rn INTEGEr; 
reorg TABLE area;
UPDATE area SET rn = row_number() over();
 
--创建索引提高更新速度
CREATE INDEX idx_area_rn ON area(rn);
 
--查询维表记录数
SELECT count(*) FROM area ;  --1955
--更新事实表字段
UPDATE person p
   SET p.rlt_area_id =
       (SELECT area_id FROM area a WHERE mod(p.rn, 1955) = a.rn);
 
--检查更新结果
SELECT count(*) FROM person WHERE rlt_area_id IS NOT NULL;
SELECT cnt, count(*)
  FROM (SELECT rlt_area_id, count(*) cnt FROM person GROUP BY rlt_area_id)
 GROUP BY cnt;
 
--删除临时字段及索引
DROP INDEX idx_area_rn;
ALTER TABLE person DROP COLUMN rn;
ALTER TABLE area DROP COLUMN rn;
reorg TABLE person;
reorg TABLE area;
 
--更新的方法效率比较低，最好在生成的时候进行构造。
--思路，先造出来一定量的数据，比如1024（2的十次方）条，
--然后，反复执行下面这个SQL，就可以造出数据。
--注意：personCount需要根据person表中的记录数，进行修改。
INSERT INTO person
  SELECT p.person_id + personCount, p.name, a.area_id
    FROM (SELECT row_number() over() AS rn, ip.* FROM person ip) p,
         (SELECT row_number() over() AS rn, ia.* FROM area ia) a
   WHERE mod(p.rn, 2000) + 1 = a.rn;
--这个SQL是我根据DBA给的SQL上补充的，不知道理解是否正确。
--等回公司总部，向我们的DBA确认一下。



--注： 
reorg TABLE <tbschema>.<tablename>
--有两个用途：
--A、 通过重构行来消除“碎片”数据并压缩信息，对表进行重组。
--     表名必须是用<dbschema>   全限定的。
--B、reorg还有一个功能就是可以将表中的数据按照某个索引关键字的顺序排列，
--     从而可以减少某些查询I/O的数量。

--另注：
--创建表的SQL是我根据Oracle写的，添加外键的SQL是搜索的网上的。
--所以，如果不对，敬请指正。谢谢！