Sequence 의 오작동 (무조건 채번되는 Sequence)

오늘은 Sequence 의 오작동 (번호를 건너뛰는 Sequence) 라는 글에 이어서
Sequence 사용시 주의해야 되는 또다른 상황을 설명한다.
예전에 고객사에서 일어난 일이다.
Sequence 채번시 IF 조건에 따라 채번 할수도 혹은 안할수도 있는상황에서
Sequence 가 IF 조건을 무시하고 무조건 채번된다는 보고가 있었다.
아래는 T1 테이블에 INSERT 하는 아주 간단한 SQL 로서 t1_temp 테이블의 id 컬럼값이 null 이면 채번하고
null 이 아니면 id 컬럼값을 그대로 사용하는 로직이 들어있다.

insert into T1 (id, x_padding)
select  decode( id, null, my_seq.nextval, id) as id --> id 가 null 이면 nextval 아니면 id 그대로 사용.
       ,x_padding
  from t1_temp;

하지만 DECODE 문과 관계없이 Sequence 는 무조건 채번되었다.
상황을 재현하기 위하여 테이블및 시퀀스를 생성한다.

CREATE SEQUENCE my_seq                             --> 5만 1번부터 시작하는 SEQUENCE 생성
  START WITH 50001
  CACHE 20;
 
create table t1_temp as                                     --> 작업용 테이블 생성
with generator as (   select  /*+ materialize */  
                                        rownum as id
                               from all_objects
                            where rownum <= 3000 )
select case when rownum > 50000 then null     --> 10만건을 생성 하는데 5만번 까지만 채번함.
                  else rownum
       end id,
       rpad('x',1000)      x_padding
  from generator    v1,
         generator    v2
 where rownum <= 100000; 

create table T1 as               --> 작업용 테이블을 이용하여 10만건을 insert 할 테이블 생성
select *
  from t1_temp
 where rownum < 1; 
 
alter table T1 add constraint T1_PK primary key(id);   --> PK 생성

이제 테이블및 Sequence 가 생성되었으므로 맨위의 SQL 을 실행하고 결과를 SELECT 해보자

insert into T1 (id, x_padding)
select  decode( id, null, my_seq.nextval, id) as id --> id 가 null 이면 nextval 아니면 id 그대로 사용.
       ,x_padding
  from t1_temp;

commit;

select count(*)
  from T1
 where id between 50001 and 100000;

위 count 쿼리의 결과는 0 이다.
분명히 5만1번부터 10만번까지 시퀀스로 채번이 되어 5만건이 count 되어야 한다.
뭔가 이상하지 않은가?
이것을 Sequence 버그라고 생각하는 사람도 있지만 필자는 조금 다르게 생각한다.
이유는 Sequence 의 원리에 있다.
Sequence 는 where 절 조건을 만족하면 무조건 채번이 되어버린다.
즉 decode 나 case 문 등의 분기 로직은 where 절이 아니라 select 절에 있기 때문에 무조건 채번 되는 것이다.
그렇다면 조건에 따라서 채번할수 있는 방법은 없는것일까?

Solution 1:
아래와 같은 간단한 함수를 만들어서 call 한다.

CREATE OR REPLACE function my_func return number as
    v_seq number;
begin

       select my_seq.nextval
         into v_seq
         from dual; 
 
return v_seq;

end;
/

insert into T1 (id, x_padding)
select  decode(id, null, my_func, id) as id        --> id 가 null 인 경우만 채번함수가 실행됨.
       ,x_padding
  from t1_temp;

Solution 2:
insert 문을 id 컬럼 기준으로 null 일 때와 null 이 아닐때로 분리한다.

insert into T1 (id, x_padding)
select  my_seq.nextval
       ,x_padding
  from t1_temp
where id is null
;

insert into T1 (id, x_padding)
select  id
        ,x_padding
  from t1_temp
where id is not null
;

한가지 주의사항은 많은수의 Function call 이 부담이 되는 상황이라면 2번방법을 택해야 한다.
과도한 Function call 은 DBMS 에 무리를 주기 때문이다.
 
편집후기: 위의 글중에 일부가 오류가 있어서 일부 수정되었음을 밝혀둔다.

다중 INSERT 문을 사용해야 한다는 의견이 있었다.

INSERT FIRST                                                             
  WHEN id is not null THEN                                                    
    INTO t1 VALUES( id, x_padding)  
  WHEN id is null THEN                                                  
    INTO t1 VALUES( my_seq.nextval, x_padding)                           
select  id
        ,x_padding
  from t1_temp                  
;

가능할 것처럼 보이지만 위의 SQL 로도 해결이 안된다는걸 알아야 한다.