데이터 타입 및 길이의 중요성

• 각 데이터 타입의 고유한 특성이 있다 \-> 컬럼 속성에 맞는 데이터 타입을 사용했느냐 여부에 따라서 많은 차이가 발생
• 적절하지 못한 데이터 타입 선정 \-> 수행속도 저하, 불필요한 자원의 낭비 초래
• 각 데이터 타입의 특성에 따라 정보를 가장 효율적으로 처리하기 위한 적절한 데이터 타입을 선정해야 한다
• CHAR, VARCHAR, NUMBER, DATE, LONG, RAW, LONG RAW, ROWID, LOB 등

1. 데이터 타입 선정 절차

• 물리적 데이터베이스 설계의 가장 마지막 단계에서 데이터 타입 및 길이를 결정
• 데이터 타입 결정 후 길이 결정

1.1 문자타입 vs. 숫자타입

• 연산을 하게 되는 컬럼인 경우 : 숫자타입
• 다른 경우는 숫자 또는 문자로 할 수 있음
• 문자와 숫자를 결합하는 것은 좋지 않음
  • 예) A0001 과 같이 문자코드 + 단순증가 일련번호
  • 차라리 문자코드 부분을 별도 컬럼으로 분리하고 일련번호 부분은 숫자타입으로 처리하는 것이 낫다
  • 예외) 각 자리수마다 특정한 의미가 부여된 경우 : 길이고정, 문자타입으로 지정 \-> 변별력 향상, 사용상의 편의

• 기타 경우
  • 숫자를 가지는 한자리로 지정된 컬럼 : 문자타입으로 지정
  • 숫자타입으로 하는 경우 오히려 저장되는 길이가 늘어난다
  • 다른 컬럼과 비교될때 내부적인 변형 발생 : 인덱스를 사용할 수 없게 될 수 있음

1.2 문자타입

• 일반문자타입(CHAR, VARCHAR) vs. 특수형태문자타입(LONG, RAW, LONG RAW)
• 일반문자타입인 경우
  • 고정길이(CHAR) vs. 가변길이(VARCHAR)
  • 컬럼길이 결정 : 해달 컬럼의 최대길이를 조사
  • 최대길이가 4천바이트가 넘는 경우 : LONG 타입 사용 vs. 여러개의 VARCHAR로 분리

• 미디어(음성, 영상) 정보의 처리
  • 테이블 컬럼으로 지정 vs. 매체 이용

1.3 숫자타입

• 컬럼값이 숫자라고 해서 꼭 숫자타입을 사용하는 거은 아니다
  • 연산이 필요한 경우, 일련번호 등의 경우는 숫자타입 사용
  • 다른 경우에 예기치 못했던 문제 발생 가능

• 문자타입으로 해야 할 필요 검토 : 인덱스로 구성될 인조키, 구분코드 등
• 최대길이 결정
  • 숫자타입은 가변길이로 저장된다 : 입력된 숫자를 2로 나눈 길이 + 부호 1바이트 (Pack Decimal 형태)
• 소수점 이하 자리수 결정
• 년월일(YYMMDD) 형태의 경우 문자타입으로 지정

1.4 날짜타입

• 숫자타입과 같이 자유로운 연산 가능
• 날짜 관련 특별 기능(Date Functions) 사용 가능
• 지역(나라)에 따라서 원하는 유형으로 사용 가능
• 경우에 따라 문제를 발생시킬 수 있으므로 데이터 속성에 따른 적절한 결정 필요

2. 데이터 타입의 선정

• 여러개의 NULL 컬럼이 있는 경우
  • 연속된 NULL 컬럼들의 전체 저장공간은 1바이트 차지
  • NULL 값이 예상되는 컬럼들은 되도록 연속으로 배치하는 것이 유리

2.1 CHAR 타입

CHAR 타입 특성

• 고정길이(2천바이트까지 입력 가능 : 10g 기준)
• 길이지정 안하는 경우 1바이트로 자동지정
• 값이 입력되면 나머지 길이가 공백(Blank)으로 채워진다
• 값을 입력하지 않으면 NULL 값이 입력된다(1바이트 차지)
• 지정된 길이보다 긴 값 저장 시도시 에러 발생
• 가변길이(VARCHAR)에 비해 저장공간이 절약되지는 않는다 : 컬럼 길이를 표시하는 별도의 내부컬럼 가지고 있다
• 값 비교시 수행속도 측면에서 유리하지 않은 경우가 더 많이 발생

CHAR 타입 활용 : 특별한 경우에 한해서만 사용

• 저장되는 모든 데이터가 고정길이를 가지는 경우
  • • 예) 부서코드, 사원번호, 'YYMMDD'로 표시되는 날짜컬럼
• 컬럼의 길이가 한자리인 경우
  • 데이터 비교시의 문제가 발생하지 않는다
  • 데이터 값이 숫자인 경우도 NUMBER 타입보다 유리 : NUMBER 타입은 Pack Decimal 형태로 저장되어 길이가 늘어난다
• 기타 특별한 경우
  • 저장되는 모든 데이터가 일정길이 이상을 가지고 길이가 길지 않은 경우에 고려 가능 : 되도록이면 가변길이로 지정하는 것이 유리
  • 가변길이로 지정할 경우 많은 체인(Chain) 발생이 우려되는 경우 : PCTFREE 값을 증가시키고 가변길이를 사용하는 것이 유리
  • 로우 생성시에는 컬럼이 채워지지 않고 곧 반드시 데이터가 입력되는 경우의 저장공간 확보 : 가변길이로 지정하고 Default Constraint 사용하거나 PCTFREE 값을 증가시키는 방법이 유리

2.2 VARCHAR 타입

VARCHAR 타입 특성

• 가변길이 문자타입, 최대 4천바이트까지 입력 가능(10g 기준), 컬럼길이 지정 필수
• 값이 입력되면 나머지 길이가 공백으로 채워지지 않는다(입력된 값만큼만 저장)
• 입력된 값의 뒤쪽에 공백이 있는 경우 공백도 저장된다(SQL*Forms에서는 자동으로 오른쪽 공백이 제거되어 저장되는 기능 제공)
• 지정된 길이보다 긴 값 저장 시도시 에러 발생(Validation Check 용도로 사용)
• 컬럼의 길이를 표시하는 별도 내부컬럼이 있다(CHAR 타입에서도 길이 표시용 내부컬럼이 있으므로 CHAR 타입이 유리하지 않음)

VARCHAR 타입 활용

• 저장되는 데이터가 가변길이를 가지는 경우(CHAR 타입으로 선정해야 하는 경우를 제외한 모든 경우)
  • CHAIN 발생을 너무 두려워하지 마라
  • 테이블의 증가유형을 예상하여 적절한 PCTFREE 값 부여
  • 약간의 CHAIN 발생은 거의 드러나지 않는다
• 테이블을 통합했을 때 기본키 값에 따라 컬럼 값이 존재하지 않을 수 있는 컬럼
  • 서브타입(Subtype) 형태로 나타난 엔티티를 수퍼타입(Supertype)으로 올려 하나의 테이블로 생성시키고자 하는 경우
  • 이미 분리된 테이블을 하나의 테이블로 통합시키고자 하는 경우
  • 각 서브타입에만 있는 컬럼들은 반드시 VARCHAR 타입 지정
  • 같은 서브타입에 있는 컬럼들을 연속되도록 지정 : NULL 값 발생시 저장장소 절약되도록 하는 것이 중요

CHAIN 발생이 우려되는 경우 확인방법

• 체인의 발생정도 확인

ANALYZE TABLE table_name COMPUTE STATISTICS;

SELECT CHAIN_CNT
FROM   USER_TABLES
WHERE  TABLE_NAME = 'table_name';

• 체인이 발생한 로우를 임의의 테이블에 저장하여 확인

ANALYZE TABLE table_name
LIST CHAINED ROWS { INTO chained_table_name };

SELECT *
FROM   chained_table_name;

• 저장할 테이블명을 지정하지 않는 경우 'CHAINED_ROWS'라는 시스템 테이블에 저장된다

ANALYZE TABLE table_name
LIST CHAINED ROWS;

SELECT *
FROM   CHAINED_ROWS
WHERE  TABLE_NAME = 'table_name';

• 확인된 체인의 로우가 많은 경우 테이블 재생성 절차
• 체인이 발생한 로우를 찾아 임시테이블에 저장

CREATE TABLE temp_table
 AS
  SELECT *
  FROM   table_name
  WHERE  ROWID IN (SELECT HEAD_ROWID
                   FROM   CHAINED_ROWS
                   WHERE  TABLE_NAME = 'table_name');

• 체인이 발생한 로우를 실제 테이블에서 찾아 삭제

DELETE FROM table_name
WHERE       ROWID IN (SELECT HEAD_ROWID
                      FROM   CHAINED_ROWS
                      WHERE  TABLE_NAME = 'table_name');

• 임시테이블에 저장해 두었던 로우를 실제 테이블에 저장한 후 임시테이블과 'CHAINED_ROWS' 테이블의 해당 로우 삭제

INSERT INTO table_name
SELECT *
FROM   temp_table;

DROP TABLE temp_table;

DELETE FROM CHAINED_ROWS
WHERE       TABLE_NAME = 'table_name';

• 다시 ANALYZE 명령을 수행시키고 체인 여부 확인

ANALYZE TABLE table_name COMPUTE STATISTICS;

SELECT CHAIN_CNT
FROM   USER_TABLES
WHERE  TABLE_NAME = 'table_name';

PCTFREE 참고사항

• 산정된 PCTFREE 값이 너무 커서 부담이 되는 경우
  • 테이블 생성시 해당 컬럼에 DEFAULT CONSTRAINT를 사용하여 원하는 만큼의 공백을 지정하여 미리 저장공간 확보
  • 산정된 PCTFREE 값은 그대로 지정해 주는 것이 바람직
  • 초기에 생성된 테이블에 체인이 많이 발생한 경우에 데이터가 어느정도 확보된 시점에서 재생성시켜 주는 방법도 있다

• 체인이 우려되어 기준치(10) 이상의 PCTFREE를 지정한 경우 주의점(잘못된 방법)
  • 테이블 생성 스크립트에 PCTFREE, PCTUSED, STORAGE 등의 각종 파라메터를 저장해 둔다
  • 기존의 테이블의 이름 변경
  • 저장된 스크립트를 그대로 실행하여 테이블 생성 후 INSERT문으로 기존의 데이터를 다시 입력

• 위 방법은 아주 잘못된 방법
  • 기존 데이터는 최초 저장 이후 나머지 값이 UPDATE되어 이미 로우가 완성되어 있다 : 기존의 데이터는 이제 더이상 늘어나지 않는 경우가 많다
  • 이미 늘어난 로우를 위해서 PCTFREE를 추가로 지정한 효과 : 블럭당 저장되는 로우수가 줄어들어 저장공간 낭비 초래 및 수행속도 저하

• 올바른 방법은?(작성자의 의견)
  • 테이블 생성시 PCTFREE 값을 기본값(10) 또는 더 작은 값으로 지정하여 생성한다
  • INSERT문으로 기존의 데이터를 입력한다
  • ALTER TABLE 명령으로 PCTFREE 값을 늘려준다

• 주의사항
  • NonPartitioned Table의 경우에만 해당
  • 추가적으로 DBMS_REPAIR.SEGMENT_FIX_STATUS 프로시져를 수행하여 이미 저장된 블럭에 대해서 적용되도록 해줘야 한다

2.3 문자타입의 비교법칙

CHAR vs. CHAR

• 두 컬럼 중 길이가 짧은 컬럼에 공백을 추가하여 길이를 동일하게 한다
• 위 그림의 경우 C2 컬럼에 197개의 공백이 추가된다
• 각각의 문자를 비교하여 서로 다른 값이 나타나면 문자값이 큰 컬럼이 크다고 판단하고 비교를 종료
• 값이 같은 경우 전체 문자를 모두 비교(공백 포함)
• 비교작업은 길이가 긴 컬럼만큼 수행되므로 수행속도 저하

CHAR vs. VARCHAR

• 문자값 비교가 먼저 수행된다
• 각각의 문자를 비교하여 서로 다른 값이 나타나면 문자값이 큰 컬럼이 크다고 판단하고 비교를 종료
• 위 그림에서는 3개의 문자까지만 비교, 'C1 > V1'의 결과가 나타난다
• 저장된 결과가 같더라도 '서로 같지 않다'는 결과가 나타나므로 함부로 CHAR 타입 지정하지 않는다

VARCHAR vs. VARCHAR

• 문자값 비교가 먼저 수행된다
• 각각의 문자를 비교하여 서로 다른 값이 나타나면 문자값이 큰 컬럼이 크다고 판단하고 비교를 종료
• 저장된 데이터 값이 같다면 동일한 길이를 가지며, 위의 예에서는 'V1 = V2'라는 결과가 나타난다

상수값의 비교

CHAR vs. 상수값

• C1을 CHAR(200)으로 지정하였다고 가정하고 C1 = 'ABC'를 비교
• 'ABC'는 C1과 동일한 CHAR(200)으로 변환되어 비교 수행된다 : CHAR vs. CHAR 과 같은 방식으로 수행

VARCHAR vs. 상수값

• V1을 VARCHAR2(200)으로 지정하였다고 가정하고 V1 = 'ABC'를 비교
• 'ABC'는 VARCHAR2 타입이 되므로 VARCHAR vs. VARCHAR 와 같은 방식으로 수행

문자타입의 비교법칙 정리

• CHAR 타입과 VARCHAR2 타입을 동일하게 7자리로 지정한 후 'ABC' 입력
  • CHAR 타입에는 4자리의 공백이 채워지고 VARCHAR2 타입에는 'ABC'만 저장되고 길이는 3이 된다
• 비교결과
  • 1), 2) 번의 경우는 정상적인 비교결과를 얻을 수 있으나 3) 의 경우에는 COL1이 크다는 결과가 나타난다
  • COL1 = 'ABC' 이고, COL2 = 'ABC' 이면 당연히 COL1 = COL2 가 되어야 함에도 불구하고 서로 다르다는 결과가 나타난다
  • 결론 : CHAR 타입은 사용하되 반드시 '약속이 선행'된 상태에서 사용해야 하며, 가능한 VARCHAR 타입을 사용하는 것이 유리하다

2.4 NUMBER 타입

• 숫자를 저장하는 데이터 타입
• 가변길이로 저장
• 숫자라고 해서 반드시 NUMBER 타입을 써야 하는 것은 아니다
• 비교가 일어날 때 변화가 일어나는 특성을 정확히 알고 사용해야 한다

NUMBER 타입 특성

• 표현 가능한 범위
  • 영(Zero), 음수, 양수, 소수점
  • 1.0 * 10 ^-130 ~ 9.9 * 10 ¹²⁵ 까지 가능
  • 컬럼 길이는 38자리까지 지정 가능
  • 소수점 지정은 -84 ~ 127자리까지 가능
• 팩데시멀(Pack Decimal) 형태로 저장 : (입력된 숫자의 길이/2) + 1
  • 저장공간을 절약할 수 있다(1자리 숫자의 경우는 저장공간 낭비되므로 CHAR 타입 사용)
  • 비교 수행시 변화에 의해서 경우에 따라 인덱스를 사용하지 못할수도 있다
• 소수점 자리수 지정
  • 소수점을 지정하지 않았거나 지정된 소수점자리 이상 입력시 : 에러 없이 지정된 자리로 자동 반올림되어 저장됨
  • 사용할 소수점 자리를 정확히 지정해야 한다
  • 음수\(-\)의 소수점 지정시 : 정수자리쪽(좌측)으로 지정한 만큼 반올림되어 절사 처리된다
• 예시 1) NUMBER(5)로 지정된 컬럼
  • 0, -5, 30 입력 : 입력한 값이 그대로 저장된다
  • 123.56 입력 : 124로 반올림된 값이 저장된다
  • 123456 입력 : 길이초과로 에러 발생
• 예시 2) NUMBER(5,2)로 지정된 컬럼
  • 0, 123.45 입력 : 입력한 값이 그대로 저장된다
  • 123.456 입력 : 소수점 3번째 자리에서 반올림되어 123.46 저장
  • 1234.56 입력 : 길이초과로 에러 발생
• 예시 3) NUMBER(5,-2)로 지정된 컬럼
  • 12345, 123.45 입력 : 10단위에서 반올림되어 각각 12300, 100 저장
  • 30 입력 : 0 저장

NUMBER 타입 활용

• 연산이 필요한 컬럼
  • 가변길이로 저장되므로 정수부분은 충분히 지정해도 무방(특별히 길이제한 필요 없는 경우 그냥 NUMBER로 지정)
  • 소수점은 반올림되어 저장되므로 사용할 값을 감안하여 소수점 자릿수 결정
• 기본키에 포함되는 일련번호 : 반드시 숫자타입으로 지정
  • '00001'과 같은 문자타입 사용은 저장공간 낭비 및 사용상 불편
  • LIKE 검색을 사용하지 않는다 : 범위처리 필요시 BETWEEN 사용
• 문자타입과 비교시 상대 문자타입을 숫자로 바꾸어 비교
  • 내부적인 사용제한 발생시킨다 : 원하지 않는 액세스 경로 생성
  • 인덱스로 생성할 컬럼은 문자타입으로 하는 것이 좋다
• LIKE 'char%' 비교시 문자타입으로 바뀌므로 인덱스를 사용하지 않는다
  • 인덱스로 생성할 컬럼은 반드시 문자타입으로 지정

10g 신규 숫자타입 : BINARY_FLOAT, BINARY_DOUBLE

• NUMBER 타입을 보완하기 위해 추가된 숫자 타입
• IEEE(Institute for Electrical and Electronic Engineering)의 부동소수점 표기 표준에 기반한 타입
• NUMBER 타입에 비해 적은 저장공간 차지
  • NUMBER 타입 : 최대 22바이트까지 차지
  • BINARY_FLOAT 타입 : 5바이트 차지(Single precision 32-bit floating point number)
  • BINARY_DOUBLE 타입 : 9바이트 차지(Double precision 64-bit floating point number)
• NUMBER 타입에 비해 넓은 역역을 표시 가능 : 크거나 작은 영역
• 일반적으로 NUMBER 타입보다 연산속도가 빠르다 : H/W에서 직접 연산 수행(NUMBER 타입은 변환이 선행되어야 한다)
• 닫힌 연산 수행
  • 연산결과 숫자 또는 특수값이 리턴된다
  • 특수값 목록

• 명료한 반올림
  • NUMBER 타입이 10진수를 사용하는 것에 비해 2진수 사용 : 가장 가까운 2진수로 반올림 처리됨
• 사용 예시

CREATE TABLE binary_test (
  bin_float BINARY_FLOAT,
  bin_double BINARY_DOUBLE
);

INSERT INTO binary_test (
  bin_float, bin_double
) VALUES (
  39.5f, 15.7d
);

INSERT INTO binary_test (
  bin_float, bin_double
) VALUES (
  BINARY_FLOAT_INFINITY, BINARY_DOUBLE_INFINITY
);

• 수치 연산이 많이 수반되는 어플리케이션 개발시 BINARY_FLOAT와 BINARY_DOUBLE을 사용하는 것을 고려할것

2.5 DATE 타입

• 날짜를 저장하는 데이터 타입
• 고정길이로 저장되나 보여지는 형태는 다르다
• 특정한 경우 비교에서는 컬럼의 내부적 변형이 발생하여 인덱스를 사용하지 못하는 경우도 있다
• 숫자처럼 덧셈 및 뺄셈이 가능
• 다양한 날짜처리함수 이용

DATE 타입 특성

• 년도(세기 포함), 월, 일, 시, 분, 초로 구성된 날짜와 시간을 저장
• BC 4712년 1월 1일 ~ AD 4712년 12월 31일까지 저장할 수 있다
• 다양한 날짜처리함수를 이용하여 우리가 원하는 형태로 출력받을 수 있다
• 저장되는 형태와 보여지는 형태가 일치하지 않는다 : 비교 수행시 타입 변화 수반되는 문제점 내포
• DATE 타입 컬럼에는 시분초까지 저장된다 : 조건비교시 상수값은 주로 날짜까지만 지정하므로 '='로 비교하는 경우 원하는 결과를 얻을 수 없다
  • 예시) '95년 9월 1일'에 EMP 테이블에 아래와 같이 데이터를 입력했다고 가정

SELECT *
FROM   EMP
WHERE  hiredate = '01-sep-95';

• • 위의 SQL을 수행시킨 결과는 '박문수'만 추출된다
  • '홍길동'은 입력한 순간의 정확한 시간이 저장되고, SQL에서 부여한 상수값은 지정된 시간이 없으므로 비교될 때 '00시 00분 00초'라는 시간을 가진 DATE로 변화되어 날짜는 같더라도 시간이 다르므로 추출되지 않게 된다
• 년도나 날짜를 비교할 때 일반 문자타입처럼 'LIKE'를 사용할 수 있다
  • 상수값에 사용된 '%'를 DATE 타입으로 변화시킬 수 없으므로 컬럼 자신을 'TO_CHAR'로 변화시킨다 : 컬럼에 내부적인 변경이 일어나 인덱스를 사용할 수 없게 된다
• 기존에 정의한 DATE 타입을 도저히 수정할 수 없는 경우의 해결

SELECT *
FROM   EMP
WHERE  hiredate BETWEEN '01-sep-95' and to_date('01-sep-95') + 0.99999;

SELECT *
FROM   EMP
WHERE  TO_CHAR(hiredate,'yymmdd') = '950901';

SELECT * FROM EMP WHERE HIREDATE LIKE '%95';

SELECT * FROM EMP WHERE SUBSTR(HIREDATE,8,2) = '95';

SELECT * FROM EMP WHERE to_char(HIREDATE,'YY') = '95';

• DATE 타입의 기본 형태는 'DD-MON-YY' 형식
  • 기본 형태를 바꾸기 위해서 'NLS_DATE_FORMAT'을 사용한다면 자신이 원하는 형식으로 바꿀 수 있다 : 예) 'YYMMDD'
  • 별도의 함수를 사용하지 않고 'YYMMDD' 형식으로 데이터를 입출력할 수 있으나, 내부저장은 마찬가지로 년도(세기 포함), 월, 일, 시, 분, 초로 저장되므로 근본적인 문제가 해결되지는 않는다
• 날짜를 입력하지 않고 시간만 입력한다면? : 년도(세기 포함), 월, 일은 디폴드 값으로 입력된다

SELECT TO_CHAR(TO_DATE('10:52:32','HH24:MI:SS'),'YY/MM/DD HH24:MI:SS')
FROM   DUAL;

• 특별히 날짜를 지정하지 않으면 해당 월의 첫날(1일)로 간주한다
• 결과) 수행한 날짜가 95년 9월 25일이라면 출력 결과는 '95/09/01 10:52:32'가 된다

DATE 타입 활용

• 빈번한 날짜 연산이 필요한 컬럼
  • 시간을 저장할 필요가 없을 때는 문자타입을 사용하는 것이 좋다
  • 문자타입의 날짜형식(YYMMDD)으로 지정하더라도 연산을 하는 데는 큰 문제가 없다
  • DATE 타입을 사용하지 않는 경우의 날짜 연산 예시

• 수주 테이블(ORDERT)에서 DATE 타입으로 지정된 판매일자(SAL_DATE)가 95년 9월에 발생한 데이터들을 읽어서 오늘부터 10일전 사이에 판매된 매출수량(SAL_QTY)의 합과 월말에 판매한 매출수량의 합을 부서(SAL_DEPT)별로 구하는 SQL

SELECT   sal_dept,
         sum(decode(sign(10 - (sysdate - SAL_DATE)),1,sal_qty)),
         sum(decode(SAL_DATE,last_day('01-SEP-95'),sal_qty))
FROM     ordert
WHERE    to_char(sal_date,'YYMM') = '9509'
GROUP BY sal_dept;

• 판매일자의 데이터 타입이 CHAR 타입인 경우 : SAL_DATE 컬럼을 TO_DATE(SAL_DATE,'YYMMDD')로만 바꾸어 주면 동일한 결과를 얻을 수 있다

SELECT   sal_dept,
         sum(decode(sign(10 - (sysdate - TO_DATE(SAL_DATE,'YYMMDD'))),1,sal_qty)),
         sum(decode(TO_DATE(SAL_DATE,'YYMMDD'),last_day('01-SEP-95'),sal_qty))
FROM     ordert
WHERE    sal_date like '9509%'
GROUP BY sal_dept;

• 장점 : 동일한 연산 기능을 수행하면서 조건절에서 인덱스를 사용할 수 있다

• DATE 타입으로 지정된 경우의 인덱스 사용 : BETWEEN 사용

SELECT   sal_dept,
         sum(decode(sign(10 - (sysdate - SAL_DATE)),1,sal_qty)),
         sum(decode(SAL_DATE,last_day('01-SEP-95'),sal_qty))
FROM     ordert
WHERE    sal_date BETWEEN '01-SEP-95' AND LAST_DAY('01-SEP-95') + 0.99999
GROUP BY sal_dept;

• 위 방법으로 도저히 인덱스를 사용하도록 할 수 없는 경우

SELECT *
FROM   TAB1 a,
       TAB2 b
WHERE  a.COL1 = b.COL2
       AND a.DAT1 = b.DAT2
       AND a.FLD = 'ABC';

• DAT1과 DAT2는 발생한 시간이 저장되어 있다면 일자는 같지만 시간이 서로 틀리므로 원하는 값을 얻을 수 없다
• 다음의 SQL 사용 : a.FLD 인덱스를 먼저 액세스하는 'Nested Loop' 조인이 가장 유리하다고 가정

SELECT *
FROM   TAB1 a,
       TAB2 b
WHERE  a.COL1 = b.COL2
       AND TO_CHAR(a.DAT1,'YYMMDD') = TO_CHAR(b.DAT2,'YYMMDD')
       AND a.FLD = 'ABC';

• DAT1과 DAT2의 인덱스를 사용할 수 없으므로 수행속도가 매우 나빠질 수 있다
• BETWEEN을 사용하여 인덱스를 사용하게 할 수는 있다

SELECT *
FROM   TAB1 a,
       TAB2 b
WHERE  a.COL1 = b.COL2
       AND b.DAT2 BETWEEN TRUNC(a.DAT1) AND TRUNC(a.DAT1) + 0.99999
       AND a.FLD = 'ABC';

• 주의할 점
  • a.DAT1 BETWEEN TRUNC(b.DAT2) AND TRUNC(b.DAT2) + 0.99999 로 사용해서는 안된다 : 드라이빙 테이블인 TAB1의 FLD 인덱스를 통해 처리범위가 결정되며, a.COL1과 a.DAT1은 단지 b.COL2와 b.DAT2에게 상수값을 제공하는 역할만 하게 되므로 변형이 일어나도 상관 없지만, DAT2에 변형이 일어나면 인덱스를 사용할 수 없게 된다
• TAB2의 인덱스의 순서를 반드시 'COL2+DAT2'로 해야 한다 : 'DAT2+COL2'로 결합인덱스가 생성된 경우 선행컬럼인 DAT2가 BETWEEN으로 사용되었으므로 COL2를 비록 '='로 사용하였더라도 처리범위를 줄여주지 못한다

• 결합인덱스의 활용
  • 시간이 들어있는 DATE 컬럼은 날짜만 있는 것에 비해 보다 양호한 분포도를 가지므로 DATE 컬럼이 선행하는 결합인덱스가 더 유리할 것으로 생각되지만 실제로는 그 반대 : 대부분의 경우 시간을 조건에 기술할 수 없으므로 'LIKE'나 'BETWEEN'을 사용할 수 밖에 없고, 선행하는 컬럼이 '='이 아니면 뒤에 있는 컬럼이 아무리 '='로 사용되었더라도 이미 인덱스의 처리범위는 줄어들지 않는다
  • DATE 타입을 사용하지 않고 'YYMMDD' 형식의 문자타입을 사용한 경우는 모든 컬럼이 '='로 사용되었으므로 인덱스 처리범위는 최소한으로 줄어들게 된다
• 반드시 인덱스를 생성해야 하며 'LIKE'나 'BETWEEN' 등으로 자주 사용되지만 특정한 경우에는 시간까지 관리할 필요가 있는 컬럼 : 'YYMMDD' 형식의 문자타입으로 지정하되 시간을 관리하기 위한 컬럼(NUMBER 타입)을 별도로 추가하는 것이 좋다
  • TO_CHAR 함수에서 'SSSSS'는 해당 일자의 시작(00시 00분 00초)부터 현재까지를 초(Sec)로 환산한 값 : DATE 타입을 사용했을 때와 동일한 시간을 저장할 수 있다

INSERT INTO table (col1, col2, ..., date_col, time_col)
VALUES     (:var1, :var2, ..., '950925', TO_CHAR(SYSDATE,'SSSSS'));

• 어떤 컬럼간의 시간 차이를 분(Minute)으로 환산하는 연산

SELECT ((TO_DATE(출고일||출고시간,'YYMMDDSSSSS') - TO_DATE(입고일||입고시간,'YYMMDDSSSSS'))*24*60
FROM ordert
WHERE sal_date = '950925';

• 날짜와 시간을 결합하여 'YYMMDDSSSSS' 형식으로 지정하여 TO_DATE 함수를 사용하여 DATE 타입으로 변화시키면 완벽한 '년월일시분초' 형식의 DATE로 변환된다
• 시간을 저장하는 컬럼에 'HH24MISS'의 방식으로 저장해도 동일한 방법으로 처리 가능
• 주로 날짜 연산에만 사용하거나 시간의 기록(Logging)이 필요한 경우 DATE 타입 사용
• 인덱스를 생성할 필요가 있다면 'LIKE'를 쓰지 말것(결합인덱스의 선행컬럼이 아닌 경우에는 가능하나 인덱스 처리범위를 줄이는 데 기여하지 못한다)
• 'BETWEEN'으로 바꾸어 사용할 수 있는 경우 결합인덱스의 뒷부분에서만 사용되는 경우 문제 발생하지 않는다
• 시간은 입력되지 않고 날짜만 입력되는 경우 DATE 타입 사용 고려 가능
• '년월'만 비교할 때 절대 'LIKE'를 사용해서는 안되며 반드시 'BETWEEN'을 사용해야 한다 : 'LIKE'가 사용된 모든 DATE 타입 컬럼은 내부적인 변형이 일어나 인덱스를 사용할 수 없다
• 잘못된 사용 예
• 'LIKE'를 사용했으므로 인덱스를 사용할 수 없다

SELECT empno, ename, job, deptno, sal
FROM   emp
WHERE  hiredate LIKE '%95';

• 'NLS_DATE_FORMAT'을 'YYMMDD' 형식으로 하더라도 'LIKE'를 사용했으므로 마찬가지로 인덱스를 사용할 수 없다

SELECT empno, ename, job, deptno, sal
FROM   emp
WHERE  hiredate LIKE '95%';

• 'LIKE'를 'BETWEEN'으로 바꾸기 위한 방법 : 인덱스 사용

SELECT empno, ename, job, deptno, sal
FROM   emp
WHERE  hiredate BETWEEN '01-jan-95' AND TO_DATE('31-dec-95') + 0.99999;

• 다른 테이블과 조인시에 연결고리로 사용되는 컬럼은 비교되는 상대컬럼과 반드시 동일한 데이터 타입이어야 한다
• DATE 타입의 날짜컬럼과 문자타입의 날짜컬럼이 혼용되어 있는 경우 조인에서 한쪽 컬럼에 변화가 일어나야 하므로 '연결고리 이상' 상태가 발생

2.6 LONG 타입

• 아주 긴 문자열을 저장(최대 2기가바이트까지 저장)
• VARCHAR2 타입과 거의 동일한 특징
• 몇 가지 제한사항들이 있으므로 충분히 검토 후 사용

2.7 RAW, LONG RAW, ROWID 타입

• RAW, LONG RAW 타입
  • 헥사데시멀(Hexa Decimal) 형태로 데이터를 저장
  • 그래픽, 이미지, 음성 등의 정보 관리(주로 적은 사이즈)
    • 사이즈가 큰 이미지나 많은 양의 이미지는 광파일 등을 사용
    • 자주 사용되는 데이터는 테이블에서 관리하고 가끔씩 사용하는 데이터는 다른 장치를 활용하는 것이 경제적, 효과적
  • 기본적인 제약사항
    • RAW : VARCHAR2와 유사
    • LONG RAW : LONG 타입과 유사
    • 수정 불가능(저장, 추출만 가능)

• ROWID 타입
  • DBMS의 로우정보를 관리하기 위한 데이터 타입
  • 실제로는 인덱스에만 ROWID가 존재(로우가 존재하는 테이블에는 없다)
  • SQL로 추출할 때는 18바이트로 출력(실제로는 6바이트로 저장된다)
  • 변수에 저장하고자 할때
    • PL/SQL에서는 ROWID 타입을 지정 가능(그 외에는 18자리 문자타입으로 지정해야 한다)
  • 클러스터링 되어 있지 않은 테이블에 대해서는 항시 유일한 ROWID를 갖는다
  • 멀티 테이블로 클러스터링된 테이블에서 같은 ROWID를 가진 서로 다른 테이블의 로우가 존재
  • 로우가 Export, Import 되지 않는 한 변동되지 않는다
  • 로우 입력시 ROWID를 사용자가 지정할 수 없다
  • 인덱스의 구성요소가 된다
  • SELECT문에서 ROWID를 요구하여 출력할 수 있다

2.8 LOB 타입

2.9 XML 타입

3. 데이터 길이의 결정

3.1 VARCHAR 타입의 길이 결정

• 가능한 충분히 최대치를 부여하는 것이 효과적
  • 실제 입력된 길이만 저장된다 : 공간이용 측면에서 불이익이 전혀 없다
  • 지정된 길이는 체크(Check) 기능만 할 뿐이다 : 일정 길이 이상인 데이터의 입력을 제한하고자 할 때
  • 지나치게 큰 경우에는 불편을 초래하기도 한다 : SQL*Forms 에서는 화면 컬럼의 길이 조정 필요
• IBM의 경우 : 한글이 저장될 때 'SO', 'SI'가 같이 저장되는 경우에 눈에 보이지 않는 값을 위해 여유있는 길이 지정 필요

3.2 CHAR 타입의 길이 결정

• 가능한 최소 길이로 지정해야 한다
• 충분히 크기를 예상할 수 있는 컬럼에만 지정(사번, 부서코드 등)
• 길이가 긴 경우는 반드시 VARCHAR2 사용
• 컬럼 길이가 한자리인 경우는 반드시 CHAR 사용(길이 지정하지 않아도 된다)

3.3 NUMBER 타입의 길이 결정

• 가능한 크게 지정하는 것이 좋다
  • 지정한 컬럼 길이에 관계 없이 무조건 가변길이로 저장됨
  • Application에서 사용될 범위의 최대값에 맞추어 결정 : 체크 기능으로 사용
• 굳이 길이를 제한하지 않는 경우에는 길이를 표시하지 않고 'NUMBER'로만 지정 가능
• 소수점 이하는 반올림되어 저장되므로 관리하고자 하는 정확한 소수점 자리수를 지정
  • 참고) NUMBER(P,S)로 지정할 때 P는 정수부분과 소수점까지 포함된 전체 데이터의 크기를 의미하므로 주의 필요
• 오라클에서 제공하는 데이터 타입 변환 함수
  • 원하는 형태의 데이터 타입으로 변경
  • 연산이나 비교가 수행될 때 필요에 따라 자동으로 변환이 이루어짐 : 규칙을 숙지하여 컬럼의 타입 및 길이를 결정
  • TO_NUMBER(), TO_CHAR(), TO_DATE(), HEXTORAW(), RAWTOHEX(), ROWIDTOCHAR(), CHARTOROWID() 등등
• 도메인(Domain)의 활용
  • 같거나 유사한 속성을 가지는 컬럼들은 동일한 도메인을 갖도록 한다
  • 향후에 예기치 못한 문제 발생을 미연에 방지
  • 변화에 능동적으로 대처 용이
• 일관성의 측면을 충분히 고려하는 자세 필요
  • 시스템 설계 개발 전과정에서 필요
  • 시스템의 품질 향상, 비용 절약하는 기반

4. 참고정보

4.1 Oracle Built-in Datatypes

4.2 오라클 DB2 데이터타입 비교