[풀스택과정] 데이터베이스 3. 데이터 타입

* 이 글은 제가 공부하기 위해 최대한 사실에 입각해 내용을 적으려고 하지만 일부 내용들이 정확하지 않을 수 있습니다.

   혹시나 잘못된 부분이 있으면 너그럽게 이해해 주시고 피드백 부탁드려요!

---

1. 데이터 타입의 종류

데이터 타입의 종류

• 개요

    – 데이터베이스에 저장되는 데이터는 문자, 숫자, 날짜, 이미지 등 과 같은 다양한 형식으로 표현
    – 실무에서 칼럼에 대한 데이터 타입이나 길이의 결정은 데이터베이스 설계 단계에서 신중하게

      고려해야 하는 중요한 사항
    – 데이터베이스 설계 단계에서 잘못 선정된 데이터 타입은 응용프로그램 개발을 어렵게 하거나 성능을

      저하시키는 요인이 됨

• 오라클에서 지원하는 데이터 타입의 종류

[표 4.1] 오라클에서 지원하는 데이터 타입의 종류

 

 

2. 데이터 타입의 특성

CHAR 데이터 타입

• 개요

    – 고정 길이의 문자열을 저장하며 최대 2,000바이트까지 저장 가능
    – 지정된 길이보다 짧은 데이터가 입력되는 경우, 나머지 공간은 공백으로 채워짐

 

• 특징

    - 데이터 입력 시 사용자가 데이터를 입력하지 않으면 NULL 이 입력
    - 지정된 길이보다 긴 데이터가 입력되면 오류 발생
    - 주소 데이터와 같은 편차가 심한 데이터를 입력할 때 사용하면 저장공간이 낭비될 수 있음
    - 주민등록번호와 같이 길이가 일정하거나 비슷한 경우에 사용하는 것이 좋음

 

 

VARCHAR2 데이터 타입

• 개요

    – 가변 길이의 문자열을 저장하기 위해 사용하는 데이터 타입
    – 최대 4,000 바이트 저장 가능
    – 지정된 길이보다 짧은 문자열이 입력되면 뒷부분은 NULL로 처리되어 저장공간을 낭비하지 않음

•  특징

    – 데이터 입력 시 사용자가 값을 입력하지 않으면 NULL 이 입력
    – 지정된 길이보다 긴 데이터가 입력되면 데이터가 입력되지 않고 오류 발생
    – 입력될 데이터의 편차가 심하거나 NULL 이 많이 입력되는 경우에 사용하는 것이 효율적
    – 실무에서는 CHAR 데이터 타입보다 VARCHAR2를 많이 사용

 

 

문자형 데이터 타입의 비교 방법

• CHAR와 CHAR 데이터 타입의 비교 방법

• CHAR와 VARCHAR2 데이터 타입의 비교 방법

문자형 데이터 타입과 문자 상수 비교 방법

• 비교 방법

    – 문자 상수는 문자 데이터 타입으로 내부 변환되어 문자 타입의 비교 방식에 의해 처리

    – 주의

        • 동일한 문자 상수를 비교하는 경우에도 비교 대상 칼럼의 데이터 타입에 따라 비교 결과가 달라질 수 있음

    – 문자 상수와 CHAR 데이터 타입 비교

        • 문자 상수를 CHAR 타입으로 변환한 후, CHAR와 CHAR 비교 방식으로 비교

    – 문자 상수와 VARCHAR2 데이터 타입 비교

        • 문자 상수를 VARCHAR2 타입으로 변환한 후 VARCHAR2와 VARCHAR2 비교 방식에 의해 비교

 

상수값, CHAR, VARCHAR2 비교

• 예제 데이터 생성

• 상수값과 CHAR와 VARCHAR2 데이터 타입의 비교

CHAR vs VARCHAR2

CHAR 특성 및 비교 메커니즘

– Fixed length character string ( 최대 2,000 characters )
– Internal code: 96(Datatype Code)
– 데이터가 고정길이인 경우 적용
– 칼럼의 길이가 짧고 거의 모든 데이터의 길이가 일정 Size를 넘는 경우에 적용하면 이득이 있다.
– 부분적으로 입력 시 나머지 BLANK가 채워짐
– 전혀 값을 주지 않으면 Null 상태로 입력 ( Nullable경우)
– 칼럼의 길이가 짧고 거의 모든 데이터의 길이가 일정 Size를 넘는 경우에 적용하면 이득이 있다.
– 특히 가변길이로 지정 시 많은 Chain 현상이 발생이 우려되는 경우나 Row 생성 후 바로 데이터 입력 시에 default constraint ‘ ‘(blank)를 지정하는 것도 좋은 방법임
– 지정된 길이보다 긴 데이터 입력 시 에러 발생
– 길이의 편차가 심한 경우에는 block 저장 공간의 낭비 및 수행 속도 저하를 가져옴
– Length가 서로 다르면 짧은 쪽에 space를 추가하여 길이를 같게 한 후 비교함.
– 서로 다른 문자가 나올 때까지 비교 ( 크기 비교도 같음 )
– BLANK의 수만 다르다면 서로 같은 값

 

VARCHAR2 특성 및 비교 메커니즘

– Variable length character string ( 최대 4,000 characters ) 
– Internal code: 1(Datatype Code)
– 데이터가 가변인 경우나 Null 값 입력이 많은 경우에 적용 유리
– 다른 타입에 비해 제한이 적고 일부 입력 시 뒷부분은 NULL
– 지정된 길이보다 긴 데이터 입력 시 오류발생
– Chaining 현상이 발생할 가능성이 많으나 이는 적절한 PCTFREE값을 설정하거나 default constraint를 설정함으로써 피할 수도 있다.
– 참고로 어떤 E컨설팅에서 수행하는 project의 경우는 모두 VARCHAR2 type을 사용하는 경우도 있음
– 서로 다른 문자가 나올 때까지 비교
– 길이가 다르면 짧은 것이 끝날 때까지만 비교
– 길이도 같고 Type도 같다면 비교 시 같다고 판단

 

문자타입 비교사례

 

NUMBER 데이터 타입

• 개요

– 숫자를 저장하며, 가변 길이 데이터 타입으로 최대 38 자리까지 저장 가능

– 저장 가능한 숫자의 범위 : 1.010-130 ~ 96.9···10125

– 소수는 NUMBER(precision, scale) 형식으로 입력

• 지정된 자리 이하에서 반올림되어 저장

• precision : 소수점을 포함하는 전체 자리 수

• scale : 소수점이하 자리 수

• precision을 지정하지 않고 숫자를 입력하는 경우

– 입력되는 숫자 값의 크기만큼 저장공간이 할당됨

• scale을 지정하지 않고 소수점을 입력하는 경우

– 소수점 이하는 반올림되어 정수 값만 저장됨

 

DATE 데이터 타입

• 개요

– 세기, 년도, 월, 일, 시간, 분, 초의 날짜와 시간 정보를 저장하기 위한 데이터 타입

– 7비트의 고정길이 필드에 저장

– 날짜나 시간에 대한 연산이나 이력 정보를 관리하기 위해 사용

– 퍼스널 오라클의 기본 날짜 형식 : YY/MM/DD 형식

• 예 : "년/월/일(05/0101)"

• 기능

– TO_DATE 함수 사용 가능

• 문자 형태로 저장된 데이터를 날짜 형식으로 변환 (6장에서 자세히 설명)

– SYSDATE 함수 사용 가능

• 시스템의 현재 날짜와 시간을 반환하는 함수

– 주의

• 날짜 데이터 입력 시 월, 일을 지정하지 않으면 입력되는 시점을 기준으로 해당 월과 1일이 입력, 시간을 지정하지 않으면 12:00:00으로 입력

• 시간 정보가 입력된 경우에는 "=" 비교 시 시간 정보도 함께 비교

 

 

LOB 데이터 타입

• 개요

– 텍스트, 그래픽, 이미지, 동영상, 사운드 등과 같이 구조화되지 않은 대용량의 텍스트나 멀티미디어 데이터를 저장하기 위한 데이터 타입

– 최대 4GB까지 저장

• 종류

• BLOB 

- 그래픽 이미지, 동영상, 사운드와 같이 구조화되지 않은 바이너리 데이터를 저장하기 위해 사용

• CLOB

- e-Book, html과 같은 대용량의 텍스트 데이터 저장

• NCLOB

- 국가별 문자셋(유니코드) 데이터 저장

• BFILE

- 바이너리 데이터를 파일 형태로 저장, 읽기 전용이므로 내용 변경 불가능

 

ROWID 데이터 타입

• 개요

– 테이블에서 행의 위치를 지정하는 논리적인 주소 값

– 데이터베이스 전체에서 중복되지 않는 유일한 값

– 테이블에 새로운 행이 삽입되면 테이블 내부에서 의사 칼럼(pseudo column) 형태로 자동적으로 생성

– 특정 레코드를 랜덤 액세스하기 위한 가장 빠른 방법

– ROWID는 데이터베이스에 저장되지 않음

– SELECT 명령문으로 조회는 가능하나, INSERT, UPDATE와 같은 DML 문으로 임의적으로 변경 불가능

 

 

ROWID 데이터 타입

• 형식

– 데이터 객체 번호(data object number)

• 데이터 객체 생성 시 할당(32bit)

– 상대적 파일 번호(relative file number)

• 데이터가 저장되는 물리적인 데이터 파일 번호(10bit)

– 블록 번호( block number)

• 데이터 파일 내에서 행을 포함하는 블록 위치(22bit)

– 행 번호(row number)

• 블록 헤더 내에서 행 디렉터리 슬롯의 위치(16bit)

 

 

[참조] Oracle ROWID (1/2)

• Oracle 테이블의 모든 Row는 ROWID라고 하는 물리적인 Address로 assign–Non-clustered table에 대해서는 이러한 address는 유일
• 내부적으로 ROWID는 binary 값을 가짐

• 외부적으로 Display 될 때에는 VARCHAR2 type의 column에 저장된 3 part의 hexdecimal string으로 표현 (Oralce 7)

– 첫 번째 부분( position 1 through 8 ) : block ID

– 두 번째 부분( position 10 through 13 ) : block 내부에서 row의 sequence number

– 세 번째 부분( position 15 through 18) : data file ID를 나타낸다. 

• Oracle 8에서 Object와 Partition의 개념이 도입되면서 Database의 Segment를 식별하기 위한 Data Object Number가 추가 -> A four-piece FORMAT

– 지수가 64인 Encoding 형태로 사용

– 18 문자 넓이를 가짐

– OOOOOOFFFBBBBBBRRR

• OOOOOO : Database Segment를 식별하기 위한 Data Object Number (오브젝트번호)

• FFF : Tablespace에 상대적인 Datafile Number (데이터파일번호)

• BBBBBB : Row를 포함하는 Data Block Number (블록번호)

• RRR : Block에서의 Row의 Slot (로우스롯번호)

• Rule-based optimizer에서 ROWID를 이용하여 데이터를 액세스 하는 것이 가장 상위로 랭크된 액세스 방법이며 가장 빠른 방법

• Pseudo-column 임

– 참조만 가능하며 데이터베이스에 값이 저장되지 않음

 

[참조] Oracle ROWID의 변화

• ROWID는 단지 Transaction의 길이 동안만 Consistence의 유지가 보장

– User 프로그램 내부에 하나 이상의 Transaction이 존재한다면, ROWID가 변화할 수도 있음

• Multi-user 환경에서 예상되는 문제점

– 다른 user가 현재 특정 user가 사용하고 있는 ROWID를 invalidate 하게 하는 UPDATE나 INSERT, DELETE 작업을 수행할 수도 있음

– INSERT나 length를 줄이는 UPDATE 작업 등에서 내부적으로 자동으로 ROWID를 변경할 수 있음

 

[참조] Pseudo-column

• 참조만 될 뿐 데이터베이스에 따로 저장되지 않음

• Oracle의 Pseudo-column은 테이블의 실제적인 칼럼은 아니므로 테이블의 describe을 통해서는 보이지 않지만 매우 유용하게 사용

– CURRVAL, NEXTVAL ( sequence number generator )

– LEVEL ( for hierarchical queries )

– ROWNUM ( a row's relative number )

 

ROWID 데이터 타입

• 사용 예

– 학생 테이블에서 모든 ROWID와 학번을 출력하여라

 

ROWNUM

Rownum은 1로 시작해야 작동하는 제한사항이 있다.
ROWNUM은 1로 시작하지 않으면 원하는 결과를 뽑지 못한다. 

TIMESTAMP 데이터 타입

• 개요

– DATE 타입의 확장된 형태로서 백만분의 일초 단위까지 표현 가능

– milli second의 기본 값은 6자리, 최대 9자리까지 사용 가능

• 종류

– TIMESTAMP WITH TIME ZONE

• TIMESTAMP 데이터 타입에 지역 시간대(time zone)를 함께 저장

• 지역 시간대는 세계 표준시간대(universal time)의 표준 시간을 기준으로 현 지역 시간대를 확산한 것

– TIMESTAMP WITH LOCAL TIME ZONE

• 사용자 데이터베이스의 지역 시간대를 따름

• 지역 시간대를 다른 곳으로 옮겨도 TIME ZONE를 변경할 필요는 없음

 

• 사용 예

– TIMESTAMP 데이터 타입을 포함한 테이블을 생성하고 데이터를 입력하여라

TIP! DUAL 테이블

• DUAL테이블은 데이터 딕셔너리와 함께 Oracle에 의해 자동으로 생성되는 테이블입니다. 

- DUAL테이블은 사용자 SYS의 스키마에 있지만 모든 사용자는 DUAL이라는 이름으로 액세스 할 수 있습니다. 

- DUAL테이블은 VARCHAR2(1)으로 정의된 DUMMY라는 하나의 열이 있으며 값을 가지는 하나의 행도 포함되어 있습니다. 

- DUAL테이블은 사용자가 계산이나 사용자 함수등을 실행하고자 한 경우에 유용합니다. 

• 아래와 같은 명령을 실제로 사용하는 개발자(또는 DBA)의 수가 얼마나 될 것이라 생각하십니까? 

select USER into <some variable> from DUAL
아마 거의 모든 이들이 사용하고 있을 것입니다. DUAL은 호출될 때마다 새로운 논리적 I/O(Buffer I/O)를 생성합니다. 이 기능은 매우 유용하게 활용됩니다. DUAL은 <somevariable> := USER와 같은 구문만큼이나 자주 사용되고 있습니다. 하지만 오라클 코드는 DUAL을 특수한 형태의 테이블로서 취급하며, 따라서 일반적인 튜닝 방법은 적용할 수 없다는 문제가 있습니다. 
Oracle Database 10g에서라면 이에 관련한 걱정은 할 필요가 없습니다. DUAL이 특수한 테이블이기 때문에, 논리적 I/O를 나타내는 consistent gets의 값도 줄어들며, event 10046 trace에서 확인할 수 있는 것처럼 optimization plan도 다른 형태로 나타납니다. 

 

• Oracle 9i에서 사용되는 DUAL의 FULL TABLE SCAN 대신, 10g에서는 FAST DUAL optimization plan을 사용하고 있다는 점을 주목하시기 바랍니다. 이러한 기능 개선을 통해 DUAL 테이블을 자주 사용하는 애플리케이션의 연속적인 읽기 작업 성능이 대폭적으로 향상되었습니다. 

ORACLE 9i TRACE 결과

ORACLE 10G TRACE 결과

 

 

3. 데이터 타입의 결정 전략

데이터 타입의 결정 전략

• 응용 프로그램 개발 시 오류 감소
• SQL 명령문의 검색 성능에 영향
• 문자형 데이터

– 데이터의 길이가 가변인지, 고정인지 고려

• 데이터가 숫자인 경우

– 숫자 데이터 타입 또는 문자형 데이터 타입으로 지정할지 고려

• 데이터가 날짜 타입인 경우

– 문자 데이터 타입으로 지정하는 편이 효율적