insert into (select from)

insert into (select from)

2010-08-05 星期四 晴朗

最近在搞大服务推送项目，与DW那边有些合作，DW通过DHW将数据插入到我们这边的一张临时表，我们这边通过跑Standalone将数据从临时表插入到正式表。因为正式表和临时表的字段非常接近，所以考虑能否使用insert into ... (select .. from ...)。临时表的定义是：member_id, company_id, recommend_service。正式表的定义是：id, gmt_create, gmt_modified, member_id, company_id, recommend_service。其中id是定义的SEQ。我们知道要采用insert into select from，必须字段完全匹配。那么正式表多出的那三个字段我们需要在select中指定。gmt_create和gmt_modified比较简单，直接就是sysdate。关键是ID，他取至于SEQ_RECOMMEND_SERVICE定义。我们应用（通过iBatis）或者在SQL中，都是这样获取下一个序列号的：SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID FROM DUAL。但是这样的SQL语句是非法的：

INSERT INTO recommend_service

  (id, gmt_create, gmt_modified, member_id, company_id, recommend_service)

 (SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID

     FROM dual, SYSDATE, SYSDATE, SELECT member_id, company_id, recommend_service

     FROM recommend_service_tmp)

原因是有两个SELECT FROM。

将后面的一个SELECT去掉，实验了一下，还是不行，

INSERT INTO recommend_service

  (id, gmt_create, gmt_modified, member_id, company_id, recommend_service)

  (SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID

     FROM dual, SYSDATE, SYSDATE,  member_id, company_id, recommend_service

     FROM recommend_service_tmp)

因为有两个FROM。确切的说是后一个FROM没有对应的SELECT。

那么将FROM dual去掉？这是一个疯狂的想法，我们一直以为SEQ就是从dual表中获取，但是真的吗，dual不过是oracle为了满足SQL语法而构造的虚拟表。去掉前面的FROM dual，意味着如下SQL语句：

INSERT INTO recommend_service

  (id, gmt_create, gmt_modified, member_id, company_id, recommend_service)

  (SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID, 

      SYSDATE, SYSDATE,  member_id, company_id, recommend_service

     FROM recommend_service_tmp)

关键是SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID FROM dual，与SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID FROM recommend_service_tmp有什么区别？

实验了一下， SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID FROM xxx_table，会一次性取出select count(*) from xxx_table个SEQ，而SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID FROM dual，每次只获取一个SEQ。对于我们这个应用是正确的。

实验了一下上面的insert into (select from)语句，果然快多了（整个过程从1分半钟（ProcessTime=90887）下降为14妙左右（ProcessTime=14182ms））。使用insert into (select from) 可以避免先将临时表的数据读取到应用，应用再将其插入到正式表。带来的最大好处就是性能的提升和安全。因为如果先将临时表数据一次性读入内存中，如果数据量很大的情况（我们这里是10万左右），那么很可能会导致内存溢出。如果分批读取插入，又需要避免重复插入，另外回滚也是个问题。总之，insert into (select from)提供了非常KISS的解决方案。

另外，使用这个ibatic是不行的：

<insert id="insertWithSelectFrom">

<selectKey resultClass="java.lang.Integer" keyProperty="id">

SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID FROM DUAL

     </selectKey>

INSERT INTO recommend_service

   (id, gmt_create, gmt_modified, member_id, company_id, recommend_service)

   values

   (#id:DECIMAL#,

           SYSDATE,

           SYSDATE,

           select 

           member_id,

           company_id,

           recommend_service

      FROM recommend_service_tmp)

</insert>

应该是这样的：

<insert id="insertWithSelectFrom">

INSERT INTO recommend_service

   (id, gmt_create, gmt_modified, member_id, company_id, recommend_service)

  (SELECT SEQ_RECOMMEND_SERVICE.NEXTVAL AS ID,

          SYSDATE,

          SYSDATE,

          member_id,

          company_id,

          recommend_service

     FROM recommend_service_tmp)

</insert>

有点诡异的是如果你使用ibatis的：

 public Integer insertWithSelectFrom() {

        return (Integer) getSqlMapClientTemplate().insert("RECOMMEND_SERVICE_SYNC.insertWithSelectFrom");

    }

他其实并没有返回插入的记录数。

但是使用

 public Integer insertWithSelectFrom() {

        return (Integer) getSqlMapClientTemplate().update("RECOMMEND_SERVICE_SYNC.insertWithSelectFrom");

    }

则正确的返回了插入的记录数。

PS：truncate table VS delete from 

在插入临时表数据到正式表之前，我们需要先将正是表数据清空，如果不需要事务处理，那么使用truncate table xxx_table是非常快的。但是笔者实验了一下，发现权限不足：

ARK: PreparedStatement#executeUpdate

Sql:   truncate table recommend_service    

Error: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1

ORA-20001: You can not execute ddl  on  this object except on the  local machine20100805 00:55:32 ORA-20001  user: ALIBABA1949 program:  IP: 10.16.42.63 host: ubuntu object: RECOMMEND_SERVICE DDL:  truncate table recommend_service    

ORA-06512: at line 49

Takeway

insert into (select into) | oracle seq | truncate table