12일차 # 10-28: Cache

- Gap이 발생할 수 있습니다.

     - rollback

     - system crashes

     - used in another table

  1) Gap이 발생하는 경우: 시퀀스를 공유할 때

  SQL> drop table t1;

  SQL> create table t1 (no number(3), name varchar2(30));

  SQL> drop table t2;

drop table t2

          *

ERROR at line 1:

ORA-00942: table or view does not exist

  SQL> create table t2 (no number(4), title varchar2(30));

  SQL> create sequence t_seq;

  SQL> insert into t1 values (t_seq.nextval, 'JAMES');

  SQL> insert into t2 values (t_seq.nextval, 'I Love You');

  SQL> insert into t1 values

         select t_seq.nextval, ename

         from emp

         where deptno = 20;

  SQL> insert into t2 values (t_seq.nextval, 'Gone With The Wind');

  SQL> select * from t1;

       NO NAME

---------- ------------------------------

        2 JAMES ==> 시퀀스 값이 2번부터 나온 이유: deferred segment creation

        5 SMITH    다음 포스트를 참고하세요 (http://gseducation.blog.me/20145825527)

        6 JONES

        7 SCOTT

        8 ADAMS

        9 FORD

6 rows selected.

  SQL> select * from t2;

       NO TITLE

---------- ------------------------------

        4 I Love You ==> 역시 deferred segment creation에 의해 3이 아닌 4가 시퀀스 값으로 나옴

       10 Gone With The Wind

  2) Gap이 발생하는 경우: DML 시 에러 발생

  SQL> insert into t2 values (t_seq.nextval, 'Harry Potter');

  SQL> insert into t2 values (t_seq.nextval, 'Harry Potter and Sorceror''s Stone');

insert into t2 values (t_seq.nextval, 'Harry Potter and Sorceror''s Stone')

                                     *

ERROR at line 1:

ORA-12899: value too large for column "JAVA30"."T2"."TITLE" (actual: 33, maximum: 30)

  SQL> insert into t2 values (t_seq.nextval, 'Harry Potter Sorceror''s Stone');

  SQL> select * from t2;

       NO TITLE

---------- ------------------------------

        4 I Love You

       10 Gone With The Wind

       11 Harry Potter

       13 Harry Potter Sorceror's Stone

  3) Gap이 발생하는 경우: ROLLBACK

  SQL> commit;

  SQL> insert into t2 values (t_seq.nextval, 'Ironman');

  SQL> insert into t2 values (t_seq.nextval, 'Steve Jobs the Chronicle');

  SQL> rollback;

  SQL> insert into t2 values (t_seq.nextval, 'Steve Jobs and the Apples');

  SQL> select * from t2;

       NO TITLE

---------- ------------------------------

        4 I Love You

       10 Gone With The Wind

       11 Harry Potter

       13 Harry Potter Sorceror's Stone

       16 Steve Jobs and the Apples