Insert Multiple - Oracle(Tibero) 포팅 삽질기

DB 포팅을 진행할 때 insert multiple 부분에 대한 삽질기

 

목차

 - 문제

 - 삽질 과정 - 1: insert multiple with sequence 

 - 삽질 과정 - 2: mybatis malfunction useGeneratedKeys

 - 해결 방안

 - 고찰 1 (insert의 방법)

 - 고찰 2 (prepared statement와 foreach insert)

 

문제

PostgreSQL/MySQL/MariaDB에서 지원되는 쿼리를 Oracle(Tibero) DBMS로 포팅을 진행한다.

문제의 쿼리는 아래와 같다.

<insert id="insertServiceCodeList" parameterType="serviceCodeVo" useGeneratedKeys="true" keyColumn="code" keyProperty="code">
  insert into service_code ( 
      serv_info_seq_id, name, 
      default_key_type, 
      cre_date,
      creator,
      description,
      state 
  ) values 
  	<foreach collection="list" item="item" separator=","> 
  	( 
  		#{item.servInfoSeqId},
        #{item.name},
        #{item.defaultKeyType},
        now(),
        #{item.creator}, 
        #{item.description},
        0 
	)
    </foreach>
</insert>

DDL seq_id의 Default는 sequence.nextval 또는 auto_increment이다.

 

삽질 과정 - 1: insert multiple with sequence 

 - Oracle은 위의 쿼리 지원안함.

 - Oracle은 insert all을 해야함

 - 근데 insert all 할 때 sequence.nextval이 지원안됨 ㅡㅡ(insert into 단일건에 대해서만 지원함)

 - 이것에 대해서는 2가지 해결 방법이 있음.

 - 해결방안 1

   

Create Sequence service_code_seq;

create or replace function get_seq( seq_name in varchar2 ) 
return 
  number 
is
  v_num number;
  sql_stmt varchar2(64);
begin
  sql_stmt := 'select ' || seq_name || '.nextval from dual';
  execute immediate sql_stmt into v_num;
  return v_num;
end;

<insert id="insertServiceCodeList" parameterType="serviceCodeVo" useGeneratedKeys="true" keyColumn="code" keyProperty="code">
insert all
     <foreach collection="list" item="item" >
        into service_code (
            code,
            serv_info_seq_id,
            name,
            default_key_type,
            cre_date,
            creator,
            description,
            state
        ) values (
            get_seq( 'service_code_seq' ),
            #{item.servInfoSeqId},
            #{item.name},
            #{item.defaultKeyType},
            systimestamp,
            #{item.creator},
            #{item.description},
            0
        )
    </foreach>
    select * from dual;
</insert>

- 해결방안 2

<insert id="insertServiceCodeList" parameterType="serviceCodeVo" useGeneratedKeys="true" keyColumn="code" keyProperty="code">
    insert into service_code (
        code,
        serv_info_seq_id,
        name,
        default_key_type,
        cre_date,
        creator,
        description,
        state
    ) select
        service_code_seq.nextval,
        servInfoSeqId,
        name,
        defaultKeyType,
        systimestamp,
        creator,
        description,
        0
    from (
    <foreach collection="list" item="item" separator="union all"> 
        select
            #{item.servInfoSeqId} servInfoSeqId,
            #{item.name} name,
            #{item.defaultKeyType} defaultKeyType,
            #{item.creator} creator,
            #{item.description} description
        from
            dual
    </foreach>
    )
</insert>

삽질 과정 - 1 - 결론과 동시에 문제

 - 위의 해결방안 1,2 insert multiple with sequence 가능함.

 - 해결방안 2는 추천안함( SQL 실행계획을 분석 하면 인라인뷰를 만들고/병합하느냐고 DB cost 잡아먹음 ㅋㅋㅋㅋ)

 - 문제: MyBatis의 useGeneratedKeys가 지원안됨 ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ

 

삽질 과정 - 2: mybatis malfunction useGeneratedKeys

 - 일단 안됨 그냥 안됨 

 - Syntax Error 뿜뿜~!!!

 - 내가 원하는 것은 시퀀스ID 값을 application에서 리턴받는 것임

 

삽질 과정 - 2: 결론

 - application code 수정은 진짜 정말 싫었지만 결국에 수정이 필수불가결...

 

위의 과정에 대한 삽질 URL

 https://stackoverflow.com/questions/57797374/is-mybatis-enable-to-insert-all-and-return-sequenceusegeneratekeys-in-oracle/57805042#57805042

Is mybatis enable to insert all and return sequence(useGenerateKeys) in oracle?

http://www.gurubee.net/article/81896

Tibero insert all Sequence 질문 드립니다.

해결 방안

 - 일단 나는 Spring 4.x 사용 중

 - Spring 기능 중 Profile과 Interface 활용해서 application code를 수정하기로 했음

 - Spring Bean 등록을 Component-Scan으로 하기 때문에 *Dao.class의 @Repository 어노테이션을 제거 했음

 - 코드는 아래와 같다.

 web.xml

	<!-- option:oracle/another -->
	<context-param>
	    <param-name>spring.profiles.active</param-name>
	    <param-value>oracle</param-value>
	</context-param>

root-context.xml(application-context.xml)

	<!-- =================================================================== -->
	<!-- Register Repository Beans(Data Access Object)                       -->
	<!-- =================================================================== -->
	<beans profile="oracle">
		<bean id="ICodeDao" class="***.dao.CodeOracleDao" />
		<bean id="***Dao" class="***.dao.***Dao" />
		<bean id="***Dao" class="***.dao.***Dao" />
		<bean id="***Dao" class="***.dao.***Dao" />
		<bean id="***Dao" class="***.dao.***Dao" />
	</beans>
	<beans profile="another">
		<bean id="ICodeDao" class="***.dao.CodeDao" />
		<bean id="***Dao" class="***.dao.***Dao" />
		<bean id="***Dao" class="***.dao.***Dao" />
		<bean id="***Dao" class="***.dao.***Dao" />
		<bean id="***Dao" class="***.dao.***Dao" />
	</beans>

service.class

@Resource
ICodeDao dao;

빌드 및 운영에서는 web.xml에서의 oracle/another를 지정하면 됨

 

고찰 1 (insert의 방법)

일단, application code를 수정해야해서 짜증났음;

 

우리가 왜 multiple insert를 하느냐를 집중해야한다. -> 성능 때문에

약 1년 전 아래와 같은 테스트를 했었다(auto commit = false).

해당 테스트는 건별 insert의 속도 비교이다.

 - single insert(prepared statement)

 - foreach multiple insert(mybatis)

 - bulk insert 

결과는 아래와 같다(간단한 내부 테스트이므로 JUnit의 메소드 실행 시간임). 

건별 3가지의 insert 속도 비교

1000건 부터 bulk insert가 효과를 봄 여튼 위의 테스트는 아래 URL 참고해서 작성함

http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html

Bulk insert (multi-row vs. batch) using JDBC and MyBatis

 고찰 2 (prepared statement와 foreach insert)

일단 SQL에서 multiple insert한 이유는 다음과 같다.

application과 DBMS와의 1번의 Round Trip(1번의 네트워크)을 통해 성능 개선을 위했던거임

근데, 막상 1년 전의 테스트를 보니까 prepared statement로 insert하는게 더 나아보임;;ㅡㅡ

 

prepared statement의 경우, DBMS의 바인드 변수를 사용하니까 빠른 것 같음..

여기서 궁금한거는 prepared statement와 네트워크 대역폭이 궁금함.. << 이거는 다음에 알아봐야겠음

 

주저리주저리 끝.