OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 23.4.0.24.05 ai Free
방법 : 오라클 23ai 신기능 SQL 도메인(Domain)
오라클 23ai 부터 SQL 도메인이라는 기능이 생김
기존에는 컬럼별로 개별적인 데이터 정의와 제약 조건을 관리하거나 메타 시스템을 별로도 도입하여 도메인을 정해준뒤 그 기준에 맞게 컬럼 형식을 테이블 생성시 넣어줬어야 했음
SQL 도메인 기능은 이런 신용카드 번호나 이메일 주소와 같은 공통 값에 대한 선택적 속성과 제약 조건을 캡슐화(encapsulates)함
SQL 도메인을 정의한 후 테이블 컬럼에 이를 연관시켜, 해당 도메인의 속성과 제약을 명시적으로 적용할 수 있음. 이를 통해 데이터를 중앙에서 정의하고, 애플리케이션 전반에서 값의 일관성을 보장하여 데이터 품질을 향상시킬수 있음
그리고 미리 정의한 도메인을 여러 테이블에서 반복해서 사용할수 있다는점이 장점임
SQL 도메인은 데이터베이스 구조를 단순화하고, 일관성 유지에 중점을 둔 기능으로, 특히 대규모 데이터베이스 시스템에서 데이터 형식 관리와 유지보수 효율성을 크게 향상시킬 수 있음
주요 특징
1. 재사용 가능성: 여러 테이블이나 컬럼에 적용 가능한 공통 데이터 정의를 제공함
2. 데이터 일관성 보장: 데이터 형식, 제약 조건을 SQL 도메인에 한 번 정의하면 여러 테이블에 쉽게 적용하여 일관성을 유지할 수 있음
3. 유지 관리 용이성: 여러 테이블의 컬럼에 동일한 규칙을 적용할 때, SQL 도메인 정의만 변경하면 해당 규칙이 모든 관련 테이블에 적용됨
이점
- 효율적인 스키마 관리: 여러 테이블에서 반복되는 컬럼 정의 작업을 줄이고 일관성 유지에 도움을 줌
- 중앙 집중적 제어: 비즈니스 규칙이나 데이터 형식이 변경될 때, SQL 도메인 정의만 수정하면 관련 모든 컬럼에 변경 사항이 반영되므로 유지보수가 간편해짐
- 가독성 향상: 데이터베이스 설계 시, 도메인 이름만으로 컬럼의 역할과 데이터 형식이 명확해짐
구문
|
1
2
3
4
5
6
7
8
|
CREATE DOMAIN [IF NOT EXISTS] DomainName AS <Type> [STRICT]
[ DEFAULT [ON NULL..] <expression>]
[ [NOT] NULL]
[ CONSTRAINT [Name] CHECK (<expression>) [ ENABLE | DISABLE] ..]*
[ VALIDATE USING <json_schema_string>]
[ COLLATE collation ]
[ DISPLAY <expression> ]
[ ORDER <expression> ]
|
상세 구문설명은 공식 문서 참고
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html
관련 뷰
dba_domains : 도메인 목록 확인
dba_domain_cols : 도메인 정의된 컬럼 확인
dba_domain_constraints : 도메인에 정의된 제약 조건 확인
dba_json_domain_schema_columns : JSON 스키마 제약 조건이 적용된 도메인 컬럼 확인
관련 함수
domain_name : 도메인의 이름을 반환함
domain_display : 표시 표현식에 따라 포맷된 값을 반환함
domain_order : 도메인의 값을 특정 순서(예: 오름차순 또는 내림차순)로 정렬하는 데 사용함
domain_check : 도메인 제약 조건을 확인하고, 값이 해당 제약을 만족하는지 TRUE 또는 FALSE를 반환함
domain_check_type : 도메인의 데이터 타입과 일치하는지 확인하고, 일치 여부에 따라 TRUE 또는 FALSE를 반환함
테스트
1. 신규 도메인 생성 후 테이블 생성
2. 만들어진 테이블에 신규 도메인을 생성
3. 내장 도메인 사용
테스트
1. 신규 도메인 생성 후 테이블 생성
도메인 생성
customer_name 이라는 도메인에는 100자리 문자형이며, 특수문자는 허용되지 않음
account_number 이라는 도메인에는 13자리 숫자형이며, 앞자리가 3333으로 시작해야함
|
1
2
3
|
SQL>
create domain customer_name as varchar2(100) check (regexp_like(value, '^[A-Za-z가-힣 ]+$'));
create domain account_number as number(13) check (value between 3333000000000 and 3333999999999);
|
이렇게 만들어진 도메인을 이용해 테이블 생성이 가능함
|
1
2
3
|
SQL> create table customer_account (name domain customer_name, acc_number domain account_number);
Table created.
|
테이블 구조 확인
|
1
2
3
4
5
|
SQL> desc customer_account
Name Null? Type
----------------------- -------- ----------------------------------
NAME VARCHAR2(100) IMSI.CUSTOMER_NAME
ACC_NUMBER NUMBER(13) IMSI.ACCOUNT_NUMBER
|
desc 명령 사용시 Type 뒤에 도메인도 표시됨
데이터 삽입
유효한 이름과 계좌번호를 삽입하는 예시
|
1
2
3
|
SQL> insert into customer_account (name, acc_number) values ('John Doe', 3333123456789);
1 row created.
|
유효하지않은 이름과 잘못된 계좌번호를 각각 삽입하는 예시
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> insert into customer_account (name, acc_number) values ('John@Doe', 3333123456789);
insert into customer_account (name, acc_number) values ('John@Doe', 3333123456789)
*
ERROR at line 1:
ORA-11534: check constraint (IMSI.SYS_C007713) involving column NAME due to domain constraint IMSI.SYS_DOMAIN_C0052 of domain IMSI.CUSTOMER_NAME violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
SQL> insert into customer_account (name, acc_number) values ('Jane Doe', 1234567890123);
insert into customer_account (name, acc_number) values ('Jane Doe', 1234567890123)
*
ERROR at line 1:
ORA-11534: check constraint (IMSI.SYS_C007712) involving column ACC_NUMBER due to domain constraint IMSI.SYS_DOMAIN_C0053 of domain IMSI.ACCOUNT_NUMBER violated
Help: https://docs.oracle.com/error-help/db/ora-11534/
|
check 제약에 걸려 에러가 발생함
2. 만들어진 테이블에 신규 도메인을 생성하여 적용하는 경우
기존 도메인 및 테이블 삭제
|
1
2
3
4
|
SQL>
drop table customer_account purge;
drop domain customer_name;
drop domain account_number;
|
신규 테이블 생성(acc_number 를 문자형 13자리로 생성함)
|
1
2
3
|
SQL> create table customer_account (id number, name varchar2(100), acc_number varchar2(13));
Table created.
|
샘플 데이터 삽입
|
1
2
3
4
5
6
|
SQL>
insert into customer_account (id, name, acc_number) values (1, 'John Doe', 3333123456789);
insert into customer_account (id, name, acc_number) values (2, 'Eric@Son', 3333929293813);
insert into customer_account (id, name, acc_number) values (3, 'Bane Mom', 1234567890123);
insert into customer_account (id, name, acc_number) values (4, 'Timi Lee', 'tempdataaaa');
commit;
|
도메인 생성
customer_name 이라는 도메인에는 100자리 문자형이며, 특수문자는 허용되지 않음
account_number 이라는 도메인에는 13자리 숫자형이며, 앞자리가 3333으로 시작해야함
|
1
2
3
|
SQL>
create domain customer_name as varchar2(100) check (regexp_like(value, '^[A-Za-z가-힣 ]+$'));
create domain account_number as number(13) check (value between 3333000000000 and 3333999999999);
|
기존 테이블에 도메인 적용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> alter table customer_account modify (name) add domain customer_name;
alter table customer_account modify (name) add domain customer_name
*
ERROR at line 1:
ORA-02293: cannot validate (IMSI.) - check constraint violated
Help: https://docs.oracle.com/error-help/db/ora-02293/
SQL> alter table customer_account modify (acc_number) add domain account_number;
alter table customer_account modify (acc_number) add domain account_number
*
ERROR at line 1:
ORA-11517: the column data type does not match the domain column
Help: https://docs.oracle.com/error-help/db/ora-11517/
|
에러 발생함
에러 발생되는 데이터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
set lines 200 pages 1000
col name for a20
col acc_number for 9999999999999
select id, name, acc_number
, domain_check(customer_name, name) name_d_chk, domain_check_type(customer_name, name) name_dt_chk
, domain_check(account_number, acc_number) acc_d_chk, domain_check_type(account_number, acc_number) acc_dt_chk
from customer_account;
ID NAME ACC_NUMBER NAME_D_CHK NAME_DT_CHK ACC_D_CHK ACC_DT_CHK
---------- -------------------- ------------- ----------- ----------- ----------- -----------
1 John Doe 3333123456789 TRUE TRUE TRUE TRUE
2 Eric@Son 3333929293813 FALSE TRUE TRUE TRUE
3 Bane Mom 1234567890123 TRUE TRUE FALSE TRUE
4 Timi Lee tempdataaaa TRUE TRUE FALSE FALSE
|
설명
domain_check : 지정된 값이 도메인의 제약 조건을 만족하는지 검사하여 TRUE 또는 FALSE를 반환함
'John Doe','Bane Mom','Timi Lee' 는 customer_name 도메인의 규칙을 만족하므로 TRUE, 'Eric@Son'는 규칙을 만족하지 않으므로 FALSE로 표시됨
'John Doe','Eric@Son' 는 account_number 도메인의 규칙을 만족하므로 TRUE, 'Bane Mom','Timi Lee'는 규칙을 만족하지 않으므로 FALSE로 표시됨
domain_check_type : 값이 도메인의 데이터 유형과 일치하는지 검사하여 데이터 유형이 올바르면 TRUE, 아니면 FALSE를 반환함
'Timi Lee'의 acc_number가 데이터 유형에 맞지않고 규칙도(3333으로 시작) 위반했으므로 도메인 검사와 유형 검사 모두 FALSE로 표시됨
문제 데이터를 변경 또는 삭제
|
1
2
3
4
5
6
|
SQL> update customer_account set name = 'Eric Son' where id = 2;
1 row updated.
SQL> delete customer_account where id in (3,4);
2 row deleted.
|
기존 테이블에 도메인 적용
|
1
2
3
4
5
6
7
8
9
10
|
SQL> alter table customer_account modify (name) add domain customer_name;
Table altered.
SQL> alter table customer_account modify (acc_number) add domain account_number;
alter table customer_account modify (acc_number) add domain account_number
*
ERROR at line 1:
ORA-11517: the column data type does not match the domain column
Help: https://docs.oracle.com/error-help/db/ora-11517/
|
name 컬럼은 도메인 적용이 되었지만 acc_number 컬럼은 데이터 타입이 달라 수정되지 않음
customer_account 테이블의 acc_number 컬럼 타입 변경
|
1
2
3
4
5
6
|
SQL> alter table customer_account modify acc_number number(13);
alter table customer_account modify acc_number number(13)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
Help: https://docs.oracle.com/error-help/db/ora-01439/
|
데이터가 들어있기 때문에 변경이 불가능함
컬럼을 추가하고 신규 컬럼으로 데이터를 업데이트 해준뒤 기존 컬럼을 삭제하는 작업이 필요함
컬럼 추가
|
1
2
3
|
SQL> alter table customer_account add new_acc_number domain account_number;
Table altered.
|
기존 컬럼의 데이터로 신규 컬럼 값 업데이트
|
1
2
3
|
SQL> update customer_account set new_acc_number = acc_number;
2 rows updated.
|
기존 컬럼 삭제
|
1
2
3
|
SQL> alter table customer_account drop column acc_number;
Table altered.
|
신규 컬럼을 기존 컬럼 이름으로 리네임
|
1
2
3
|
SQL> alter table customer_account rename column new_acc_number to acc_number;
Table altered.
|
데이터 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000
col name for a20
col acc_number for 9999999999999
select id, name, acc_number
, domain_check(customer_name, name) name_d_chk, domain_check_type(customer_name, name) name_dt_chk
, domain_check(account_number, acc_number) acc_d_chk, domain_check_type(account_number, acc_number) acc_dt_chk
from customer_account;
ID NAME ACC_NUMBER NAME_D_CHK NAME_DT_CHK ACC_D_CHK ACC_DT_CHK
---------- -------------------- -------------- ----------- ----------- ----------- -----------
1 John Doe 3333123456789 TRUE TRUE TRUE TRUE
2 Eric Son 3333929293813 TRUE TRUE TRUE TRUE
|
모두 정상임
3. 내장 도메인 사용
도메인을 만들어서 사용할 수도 있지만 내장된 도메인을 사용할수도 있음
내장 도메인은 dba,all_domains 뷰에서 확인 가능함
|
1
2
3
4
5
6
7
8
9
10
|
SQL> select owner, name from dba_domains where owner = 'SYS';
OWNER NAME
-------------------- ------------------------------
SYS EMAIL_D
SYS IPV4_ADDRESS_D
SYS MODE_D
SYS POSITIVE_NUMBER_D
SYS MEAN_D
..
|
이 도메인들의 내용을 보고 싶은 경우 dbms_metadata.get_ddl 을 이용해 확인할 수 있음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
SQL>
set long 1000 longc 10000
select dbms_metadata.get_ddl('SQL_DOMAIN', 'EMAIL_D','SYS') domain_ddl;
DOMAIN_DDL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DOMAIN "SYS"."EMAIL_D" AS VARCHAR2(4000) CHECK (REGEXP_LIKE (email_d, '^([a-zA-Z0-9!#$%&*+=?^_`{|}~-]+(\.[A-Za-z0-9!#$%&*+=?^_`{|}~-]+)*)@(([a-zA-Z0-9]([a-zA-Z0-9-]*[a-zA-Z0-9])?\.)+[a-zA-Z0-
9]([a-zA-Z0-9-]*[a-zA-Z0-9])?)$')) ENABLE ANNOTATIONS("PERSON_INFO")
SQL> select dbms_metadata.get_ddl('SQL_DOMAIN', 'IPV4_ADDRESS_D','SYS') domain_ddl;
DOMAIN_DDL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DOMAIN "SYS"."IPV4_ADDRESS_D" AS VARCHAR2(15) CHECK (REGEXP_LIKE(ipv4_address_d,'^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$')) E
NABLE ANNOTATIONS("TECH_INFO")
SQL> select dbms_metadata.get_ddl('SQL_DOMAIN', 'POSITIVE_NUMBER_D','SYS') domain_ddl;
DOMAIN_DDL
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE DOMAIN "SYS"."POSITIVE_NUMBER_D" AS NUMBER CHECK (positive_number_d > 0) ENABLE
|
참고
특정 도메인이 포함된 테이블이 존재하는 상태에서 해당 도메인을 삭제하려고 하면 에러가 발생함
|
1
2
3
4
5
6
|
SQL> drop domain account_number;
drop domain account_number
*
ERROR at line 1:
ORA-11502: The domain ACCOUNT_NUMBER to be dropped has dependent objects.
Help: https://docs.oracle.com/error-help/db/ora-11502/
|
이 경우 해당 테이블을 먼저 수정한 후 도메인을 삭제해줘야함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
set lines 200 pages 1000
col owner for a20
col name for a20
col type for a20
select owner, name, type
from all_dependencies
where referenced_name='ACCOUNT_NUMBER' --domain name
and referenced_owner='IMSI' --domain owner
and referenced_type= 'DOMAIN';
OWNER NAME TYPE
-------------------- -------------------- --------------------
IMSI CUSTOMER_ACCOUNT TABLE
|
테이블에서 도메인 제거
|
1
2
3
|
SQL> alter table CUSTOMER_ACCOUNT modify drop domain ACCOUNT_NUMBER;
Table altered.
|
도메인 삭제
|
1
2
3
|
SQL> drop domain account_number;
Domain dropped.
|
또는 테이블을 수정하지 않고, force 옵션을 사용할수도 있음
이 경우 domain 의 check 제약조건은 없어지고 데이터 타입만 남게되는듯함
|
1
2
3
|
SQL> drop domain PhoneNumber force;
Domain dropped.
|
결론 :
신규 도메인을 생성하고 신규 테이블을 생성할때는 도메인설정이 어렵지 않지만
기존에 존재하는 테이블에 신규 도메인 생성 후 적용하려 할때는 데이터 내용이나 데이터 타입문제로 바로 적용하는것이 어려울 수 있음
이 경우 신규 컬럼을 추가해서 업데이트 하거나 용량이 너무 크다면 추후 차세대 프로젝트시 변경하는 방식을 적용해야 할듯함
그리고 SQL 도메인 기능중 내장 도메인을 사용해도 되고 개별적으로 도메인 규칙을 잘 만들어서 활용하면 데이터의 일관성과 품질이 좋아질수 있을듯함
차세대 프로젝트에서 미리 도메인을 잘 설정해놓고 그대로만 테이블들을 만들다면 기존보다 깔끔하게 관리가 가능할것으로 보임
23ai의 또 다른 신기능인 Annotation 기능과 함께 쓰면 메타데이터 관리가 더 편해질것같음
추후에는 메타 관리 프로그램에서 메타 업데이트를 반영하면 바로 db 내에도 저장되게끔 하는 식으로도 사용할수 있을듯함
참고 : 오라클 23ai 신기능 테이블 주석 기능(Annonation) ( http )
참조 :
https://positivemh.tistory.com/1163
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1647
https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/application-data-usage.html#GUID-5CAE7944-B094-469C-B6C9-6153CDC2EFEA
https://docs.oracle.com/en/database/oracle/oracle-database/23/odpnt/featSQLDomains.html
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/domain_check.html
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/domain_check_type.html
https://docs.oracle.com/en/error-help/db/ora-11502/?r=23ai
https://blogs.oracle.com/coretec/post/less-coding-with-sql-domains-in-23c
https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3835&clear=RR,180&session=113461509713298
https://www.youtube.com/watch?v=3ySJHOAjOjY
https://oracle-base.com/articles/23/domains-23
https://eclipsyssolutions.com/blog/oracle-23ai-enumeration-domains-list-of-values-in-the-database
https://juliandontcheff.wordpress.com/2023/04/11/sql-domains-in-oracle-database-23c-an-example/
https://kosate.github.io/blog/oracle/23c-sql-domain/
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 23ai 신기능 sql 이력 확인뷰 v$sql_history (0) | 2024.10.31 |
|---|---|
| 오라클 23ai 신기능 bigfile 테이블스페이스 shrink (0) | 2024.10.28 |
| 오라클 23ai 신기능 테이블 주석 기능(Annonation) (0) | 2024.10.18 |
| 오라클 23ai 신기능 bigfile 테이블스페이스 기본값 (0) | 2024.10.18 |
| 오라클 23ai 신기능 개발자를 위한 DB_DEVELOPER_ROLE 신규 Role (0) | 2024.10.15 |