클라우드 데이터베이스 Oracle 12c 가이드 (2016년)
오라클 데이터베이스 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 5NULL 값이 입력되었을 때의 디폴트 값
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
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.