클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
오라클 데이터베이스 12c DDL의 새로운 기능 0 0 12,606

by 구루비스터디 12c DDL [2023.09.03]


오라클 데이터베이스 12c DDL의 새로운 기능

VARCHAR2, NVARCHAR2 RAW 데이터 타입의 최대 길이 증가

  • VARCHAR2, NVARCHAR2, RAW 데이터 타입이 32,767 BYTE까지 저장가능
  • 확장 데이터 타입의 활성화
select name, value from v$parameter where name = 'max_string_size';

NAME             VALUE
---------------- ----------------
max_string_size  STANDARD


CREATE TABLE EXTEND_TABLE (DATA_VARCHAR VARCHAR2(4001), DATA_NVARCHAR2 NVARCHAR2(2001), DATA_RAW RAW(2001));
CREATE TABLE EXTEND_TABLE (DATA_VARCHAR VARCHAR2(4001), DATA_NVARCHAR2 NVARCHAR2(2001), DATA_RAW RAW(2001))
                                                 *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
@?/rdbms/admin/utl32k.sql    -- recyclebin에 테이블이 있을 경우 에러발생, recyclebin을 비우고 작업하자
SHUTDOWN IMMEDIATE;
STARTUP;


select name, value from v$parameter where name = 'max_string_size';

NAME             VALUE
---------------- ----------------
max_string_size  EXTENDED


CREATE TABLE EXTEND_TABLE (DATA_VARCHAR VARCHAR2(4001), DATA_NVARCHAR2 NVARCHAR2(2001), DATA_RAW RAW(2001));

Table created.

desc extend_table;
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- --------------------------------------------
 DATA_VARCHAR                                                               VARCHAR2(4001)
 DATA_NVARCHAR2                                                             NVARCHAR2(2001)
 DATA_RAW                                                                   RAW(2001)



CREATE TABLE t1 (
  id             NUMBER,
  varchar2_data  VARCHAR2(32767),
  nvarchar2_data NVARCHAR2(16383),
  raw_data       RAW(32767)
);

Table created.

INSERT INTO t1
SELECT 1,
       RPAD('X', 32767, 'X') AS varchar2_data,
       RPAD('X', 16383, 'X') AS nvarchar2_data,
       UTL_RAW.cast_to_raw(RPAD('X', 32767, 'X')) as raw_data
FROM   dual;

SELECT id,
       LENGTH(varchar2_data),
       LENGTH(nvarchar2_data),
       LENGTH(raw_data)
FROM   t1;

        ID LENGTH(VARCHAR2_DATA) LENGTH(NVARCHAR2_DATA) LENGTH(RAW_DATA)
---------- --------------------- ---------------------- ----------------
         1                 32767                  16383            32767

1 row selected.

-- 인덱스가 LOB 타입으로 자동으로 생성됨
SELECT index_name, index_type
  FROM user_indexes
 WHERE table_name = 'T1';


INDEX_NAME                               INDEX_TYPE
---------------------------------------- ---------------------------
SYS_IL0000093481C00004$$                 LOB
SYS_IL0000093481C00003$$                 LOB
SYS_IL0000093481C00002$$                 LOB

  • 블록사이즈가 8K인 경우 B*Tree 인덱스의 최대 키 길이는 6,398바이트이며, 이 길이를 초과하는 데이터를 insert하거나, update 시 실패할 수 있다.
  • substr 함수를 이용한 Function-based Index를 생성하거나 standard_hash 함수를 통해 컬럼 사이즈를 축소하면 해결 가능하다.

-- 크기초과로 인덱스 생성 불가능
create index t1_idx01 on t1(varchar2_data asc);
create index t1_idx01 on t1(varchar2_data asc)
                         *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


Identity 컬럼

  • Sequence generator로부터 정수값의 증가와 감소를 지정할 수 있음
  • 규칙
  • 테이블당 1개만 생성가능
  • number 타입 컬럼만 가능
  • default 지정 불가능
  • Not Null, Not Deferrable 제약조건 암시적으로 정의됨
  • 암호화 가능
  • CTAS 명령을 실행해서 만든 테이블 컬럼은 Identity 컬럼 속성은 상속받지 않음
create table identify_table (s_key number generated as identity primary key, data varchar2(30));
insert into identify_table (data) values ('test');
commit;

select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 test


insert into identify_table (data) values ('test222');
update identify_table set data='11111' where s_key=1;
commit;

select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 11111
         2 test222

insert into identify_table values (3, '3333');
insert into identify_table values (3, '3333')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

-- 컬럼을 재정의하면 데이터를 직접 입력할 수 있다.
alter table identify_table modify ( s_key number generated by default as identity);

insert into identify_table values (5, '55555');
commit;

select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 11111
         2 test222
         5 55555


-- 현재 시퀀스보다 큰 수를 수동으로 입력하고
-- 이후 자동으로 시퀀스로 채번하게 되면 UNIQUE 오류가 발생할 수 있다.
 select * from identify_table;

     S_KEY DATA
---------- ------------------------------
         1 11111
         2 test222
         5 55555
        21 3333
        22 4444
        23 test55555

insert into identify_table (data) values ('666666');
insert into identify_table (data) values ('77777');
insert into identify_table (data) values ('77777')
*
ERROR at line 1:
ORA-00001: unique constraint (JIGI.SYS_C0010318) violated


-- 시퀀스가 자동으로 생성됨
select sequence_name, cache_size, last_number from user_sequences;

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
ISEQ$$_93500                           20          41

-- 테이블 생성 스크립트를 뽑으면 아래와 같음
CREATE TABLE JIGI.IDENTIFY_TABLE
(
  S_KEY  NUMBER                                 DEFAULT "JIGI"."ISEQ$$_93500".nextval NOT NULL,
  DATA   VARCHAR2(30 BYTE)
);


Cascade Truncate 문

  • 부모 테이블을 Truncate해서 자식 테이블까지 연쇄적으로 Truncate 되는 문장을 지원함
create table parent(id number primary key);
create table child(cid number primary key, pid number);
alter table child add constraint fk_parent_child foreign key(pid) references parent(id) on delete cascade;

insert into parent values(1);
insert into parent values(2);
insert into child values(1,1);
insert into child values(2,1);
insert into child values(3,2);
commit;

select * from parent;

        ID
----------
         1
         2

select * from child;

       CID        PID
---------- ----------
         1          1
         2          1
         3          2


truncate table parent cascade;

select * from parent;
no rows selected

select * from child;
no rows selected


Invisible 컬럼

  • 아래의 경우 컬럼명이 보이지 않음
  • Select * from table 과 같은 와일드카드 문자를 사용한 문장
  • SQL*PLUS에서 DESC 명령
  • PL/SQL %ROWTYPE 선언
  • INSERT INTO SELECT * 문장
  • 참고 : inivisible로 변경된 컬럼은 가장 마지막으로 위치가 바뀐다. 테이블의 컬럼을 순서를 바꾸고 싶은 경우 위치하고 싶은 컬럼 뒤 모든 컬럼을 invisible -> visible 함으로서 컬럼의 순서를 조정할 수 있다

create table invisible (id number, invisible_col number invisible);
desc invisible;

 Name                             Null?    Type
 -------------------------------- -------- --------------------------------------------
 ID                                        NUMBER


insert into invisible values (1, 1);
insert into invisible values (1, 1)
            *
ERROR at line 1:
ORA-00913: too many values


insert into invisible (id, invisible_col) values (1, 1);

select * from invisible;

        ID
----------
         1

select id, invisible_col from invisible;

        ID INVISIBLE_COL
---------- -------------
         1             1



오라클 시퀀스를 기반으로 한 디폴트 값

  • identity 컬럼과는 다르게 여러개의 컬럼에 선언가능
  • 시퀀스가 미리 생성되어 있어야 한다.
  • 시퀀스가 삭제된 상태에서 insert 작업시 에러가 발생한다.
  • 테이블 소유자와 디폴트 값을 할당하는 컬럼의 유저는 시퀀스의 select 권한이 필요하다.
  • 시퀀스가 적용된 컬럼에 수동으로 값을 지정하여 입력할 수 있다.
create sequence test_seq start with 1;
create table seq_table (id number default test_seq.nextval, test varchar2(10));

insert into seq_table values(null, '1');
insert into seq_table values(default, '2');
insert into seq_table (test) values('3');
insert into seq_table values(100, '4');

commit;

select * from seq_table;

        ID TEST
---------- ----------
           1
         1 2
         2 3
       100 4


insert into seq_table (test) values('5');
commit;

select * from seq_table;

        ID TEST
---------- ----------
           1
         1 2
         2 3
       100 4
         3 5


NULL 값이 입력되었을 때의 디폴트 값

create table sales_information (
  store_id number
, sales_date date default on null to_date('20991231', 'YYYYMMDD') not null
, sale_number number
, product_id number
, quantity number default on null 0 not null
, constraint pk_sales_information primary key (product_id, sales_date, sale_number)
);


insert into sales_information values (1, null, 1, 12345, null);
select * from sales_information;

  STORE_ID SALES_DATE          SALE_NUMBER PRODUCT_ID   QUANTITY
---------- ------------------- ----------- ---------- ----------
         1 2099-12-31 00:00:00           1      12345          0


select table_name, column_name, default_on_null from user_tab_columns where table_name ='SALES_INFORMATION';

TABLE_NAME                     COLUMN_NAME          DEF
------------------------------ -------------------- ---
SALES_INFORMATION              STORE_ID             NO
SALES_INFORMATION              SALES_DATE           YES
SALES_INFORMATION              SALE_NUMBER          NO
SALES_INFORMATION              PRODUCT_ID           NO
SALES_INFORMATION              QUANTITY             YES


시퀀스 관련 새로운 기능

  • 세션단위로 생성되는 시퀀스

conn scott/tiger
create global temporary table gtt (id number, seq_number number);
grant all on gtt to jigi;
create sequence seq_session start with 1 session;
grant all on seq_session to jigi;

insert into gtt values ( 1, seq_session.nextval);
insert into gtt values ( 2, seq_session.nextval);
select * from scott.gtt;

        ID SEQ_NUMBER
---------- ----------
         1          1
         2          2

commit;

select * from scott.gtt;
no rows selected

insert into gtt values ( 1, seq_session.nextval);
insert into gtt values ( 2, seq_session.nextval);
select * from scott.gtt;

    ID SEQ_NUMBER
---------- ----------
         1          3
         2          4

commit;

conn jigi/oracle

select * from scott.gtt;

no rows selected

insert into scott.gtt values ( 1, scott.seq_session.nextval);
insert into scott.gtt values ( 2, scott.seq_session.nextval);

 select * from scott.gtt;

        ID SEQ_NUMBER
---------- ----------
         1          1
         2          2

commit;


select * from scott.gtt;

no rows selected

insert into scott.gtt values ( 1, scott.seq_session.nextval);
insert into scott.gtt values ( 2, scott.seq_session.nextval);

select * from scott.gtt;

        ID SEQ_NUMBER
---------- ----------
         1          3
         2          4


동일한 컬럼에 대한 다른 종류의 멀티 인덱스

  • 동일 컬럼에 서로 다른 인덱스 타입으로 2개 이상의 인덱스를 만들 수 있다.
  • 단, 1개의 인덱스만 visible로 허용된다. 나머지 인덱스는 invisible로 생성가능
  • 이 기능은 가용성을 유지하는 목적으로 유용하다. 기존과 다른 새로운 인덱스를 새롭게 만들어야 하는 경우, invisible 속성으로 새로운 유형의 인덱스를 만들고 기존 인덱스를 삭제한 후 새롭게 만든 인덱스를 visible 하면 된다.
create table emp_tab as select * from scott.emp;
create index emp_tab_ix01 on emp_tab(hiredate, deptno);

-- 동일컬럼에 비트맵 인덱스를 바로 생성하려고 하면 에러가 난다.
create bitmap index emp_tab_ix02 on emp_tab(hiredate, deptno);
create bitmap index emp_tab_ix02 on emp_tab(hiredate, deptno)
                                            *
ERROR at line 1:
ORA-01408: such column list already indexed

-- 두번째 인덱스를 생성하기 위해 처음 인덱스를 invisible로 변경하면 인덱스가 생성된다.
alter index emp_tab_ix01 invisible;

Index altered.

create bitmap index emp_tab_ix02 on emp_tab(hiredate, deptno);

Index created.


with 쿼리의 새로운 기능

  • with 절 내에서 PL/SQL 기능을 사용할 수 있다.
  • 단, PL/SQL 내에서 이러한 형태의 with 절은 사용할 수 없다

create table test_table (id number);
insert into test_table values (1);
insert into test_table values (2);
commit;

select * from test_table;

        ID
----------
         1
         2

with
function plus_one(p_id number)
return number
is
begin
    return p_id + 1;
end;
select plus_one(id) from test_table;
/

PLUS_ONE(ID)
------------
           2
           3
"구루비 데이터베이스 스터디모임" 에서 2016년에 "클라우드 데이터베이스 Oracle 12c 가이드" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4258

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입