DB 설계하다가 궁금한점

1. 일반적인 날짜 값을 위해 char(14) , YYYYMMDDhhmmss 와 Date 의 효율

⇒ 우선 결론!!! DATE 형식을 사용하겠습니다. !!! 단, YYYYMMDD 와 hhmmss 로 구분이 된 경우에만 char 타입을 사용하겠습니다.

  (이유)
일반적으로 데이터의 무결성에 대한 문제를 위해서라면, 속도면에서 보단, date 를 사용하는 것이 보다 효율적임. 하지만 일자까지만 표현하는 부분 예를 들어 YYYYMMDD 만 사용한다면, 이때는 char(8)를 사용하는 것이 좋다.
  참고 : http://www.sqler.com/542863

 

 (공부)

 DATE TYPE 은 내부적으로 7 바이트(byte)의 고정길이 숫자로 관리되어 진다.
 숫자로 구성되어 있기 때문에 +,- 연산이 가능하다. (단 DATE-DATE는 불가능)

 (참고 날짜 함수~)

 - ADD_MONTH(DATE, NUMBER) :  DATE에 NUMBER 만큼의 월을 더한다.
 - MONTHS_BETWEEN(DATE, DATE) :  두 DATE 사이의 개월 차이를 반환한다.
 - NEXT_DAY(DATE, NUMBER) :  DATE 이후에 해당하는 요일의 DATE를 반환. 1(일)~7(토)
 - LAST_DAY(DATE) :  DATE에 해당하는 월의 마지막 DATE를 반환. 
 - TO_CHAR(DATE, ['FORMAT']) :  DATE를 FORMAT에 맞는 문자열 형식으로 반환.
 - TO_DATE(CHAR, ['FORMAT']) :  문자열 형식을 FORMAT에 맞는 DATE 타입으로 반환. 
 ** FORMAT : YYYY(년도), MM(월), DD(일), HH24(시간), MI(분), SS(초)

 - 날짜기간 조회 (0.99999 또는 1-1/24/60/60) 여기서 1은 1일이라고 보시면 됨
 - 날짜컬럼 BETWEEN TO_DATE('20130101', 'YYYYMMDD') AND TO_DATE('20130131', 'YYYYMMDD')+0.99999

 

2. 지정된 자리수의 코드값에서 char 와 varchar   e.g.) 전화번호, 아이피 주소
3. boolean 형태의 데이타 타입을 char(1) 로 하는 것이 좋은가?

 

⇒ 우선 결론!!! 정해진 자리수중 작은 단위은 CHAR로 유지 하되, 나머지 부분에 대해서는 varchar2 를 사용하겠습니다.

 

 

(이유)

많은 사람들이 Char 보다, Varchar2를 사용하라고 하는데, 아래와 같은 주장을 냈습니다.

http://blog.naver.com/PostView.nhn?blogId=lovemylife15&logNo=70157081314
1. CHAR 타입은 고정된 길이만큼 공백으로 나머지 길이를 채우므로 테이블 세그먼트와 인덱스 세그먼트 모두를 최대 공간까지 빠르게 소비하므로 이것만으로도 CHAR 타입의 사용을 피해야 할 이유로 충분하다.
2. 정보를 조회하는 애플리케이션에서 혼란줘 불필요한 오해를 야기 한다.(아래 test 참조)
3. 따라서 Where 조건 마다 trim, rpad 등 불필요한 function 을 사용하므로 때로는 수행계획을 고정시켜 성능에 하등 도움이 되지 않는다.

 

http://database.sarang.net/?inc=read&aid=23855&criteria=oracle&subcrit=&id=&limit=20&keyword=%B1%E8%C8%EF%BC%F6+char+varchar2&page=1
스페이스가 패딩되는 만큼 추가적인 Scan 오버헤드를 유발하므로 실제 성능에서도 손해라는 것을 SQL Trace 증명한 자료가 검색해보시면 있을겁니다

 

 http://blog.naver.com/protokhs/130029328471
1) CHAR는 묵시적 형변환 대상이기 때문
2) 인덱스 대상 컬럼이 CHAR 형식인 경우 SQL Where 절에서 인덱스를 이용하지 못하는 형식으로 컬럼이 비교되는 경우가 많아 성능 저하 현상 발생
3) 오라클은 기본적으로 서로 다른 데이타 유형간에 묵시적인 형변환을 합니다.
인덱스는 항상 쓰지만 올바른 결과를 얻기 위하여는 묵시적 형변환에 의한 부수적 효과를 염두에 두고 프로그램을 개발해야 한다.

 

 

(공부)

 CHAR DATATYPE은 FIXED LENGTH Character String을 저장합니다.　　　　　
 COLUMN LENGTH 는 1 에서 255BYTES 까지의 DATA를 저장할 수 있습니다.
 COLUMN LENGTH가 10 으로 정의되었을때 한번 INSERT 문장을 통해
 5 BYTES를 입력하였다면 나머지 5 BYTES는 SPACE 처리되어
그 DATA의 SIZE는 10 BYTE가 됩니다.

 

 VARCHAR2 DATATYPE은 VARIABLE-LENGTH CHARACTER Strings을 저장합니다.
 COLUMN LENGTH는 1에서 4000BYTES 까지의 DATA를 저장할수 있습니다.
 COLUMN길이가 10 으로 정의되었을때 한번 INSERT 문장을 통해 5 BYTES를 입력하였다면
 나머지 5 BYTES는 NULL 처리되어 그 DATA의 SIZE는 5 BYTE가 됩니다.　

 

3. varchar2와 nvarchar2

⇒ 특이사항없습니다. 다국어를 지원할 일이 있다면, 자료형 앞에 n을 붙여라

 

(공부)

varchar2와 nvarchar2는 가변형 문자열을 저장할때 사용합니다.
varchar2는 영문 데이터형과 테이블에 설정된 기본 언어 타입을 저장할때 사용합니다.
nvarchar2는 유니코드 지원을 위한 데이터형입니다.
nvarchar2는 varchar2보다 동일 데이터 저장시 공간을 2배 사용하며, 다국어 지원이 필요한 DB 작업일 경우에 적당한 데이터형입니다.

4. 이력 테이블(로그)에 NUMBER 로 지정된 시퀀스를 사용하는 것이 좋은것인가? 대안이 있는가?

⇒ 우선 결론, 업무적으로 필요로 한 사항에 대해서는 시퀀스를 붙이는데, 해당 이력테이블이 데이터가 대량으로 적재될 것으로 예상되는 곳에 성능을 고려한 기능상의 컬럼을 추가해서 , 이후 테이블 통합/분리를 고려하도록 하겠으나, 현재는 이렇게 적용된 이력테이블은 없습니다.

 

(공부)

시간에 따라 발생하는 데이터 형식을 이력이라 하고, 이런 이력 데이터는 시간에 따라 발생하고, 동일한 컬럼의 유형에 발생한다.
또한 시간에 따라 반복적으로 발생하기 때문에 다른 테이블에 비해 대량의 데이터가 테이블에 적재될 가능성이 크다.
이 특성은 데이터베이스에 발생하는 트랜잭션의 성능 저하로 이어지는 경우가 있다.
실전에서 적용하는 이력의 유형은 테이블의 구분에 따라, 컬럼과 로우 단위에 따라,
그리고 이력 데이터 의 발생 방법에 따라 분류할 수 있다.

이력을 분류하는 방법은 다음과 같다.

 - 테이블 구분에 따라 구분하는 방법
 - 컬럼과 로구에 따라 구분하는 방법
 - 이력 데이터 발생 방법에 따라 구분하는 방법

대량 데이터가 하나의 테이블에 집약되는 특징 탓에 데이터를 처리하는 SQL 구문의 양이 증가하여 성능이 저하되는 경우가 많은데, 이러한 현상을 극복하기 위해 데이터 모델러는 특별한 고려사항을 추가하여 모델링을 적용해야 한다.

즉 업무적으로 먼저 이력 유형을 결정하고,
그 이후에 이력 테이블에 데이터가 대량으로 적재될 것이 예상되면,
성능을 고려한 기능상 컬럼을 추가하거나, 테이블 통합/분리를 고려하면 된다.
이력은 시간에 따라 발생이력, 변경이력, 진행 이력의 형식으로 구분 할 수 있다.
발생이력과 변경이력은 어떤 특점 시점에 정보가 발생하고 마지막에 생성된 정보가 빈번하게 이용된다.
발생이력과 변경이력은 최종 생성된 데이터를 구분하기 위한 기능성 컬럼이 필요하고,
진행이력은 연속적인 특징이 있으므로 생성된 시점과 완료된 시점에 대한 기능성 컬럼이 필요하다.

 

1) 의미적으로 구분이 가능한 코드성 테이블(중복에 대한 검사가 의미적으로 가능한 테이블들)은 굳이 number의 자동 키 생성에 의한 키를 기본키로 사용할 필요는 없다는 것이다.
2) 반대로 의미적으로 구분이 불가능한 테이블들(예를 들어 거래 테이블 - 거래 테이블의 경우 해당 거래의 유일성을 시스템적으로 검증하기가 거의 불가능하다. 대부분 실세계와의 검증만이 유일한 검증이 되곤 한다.)
 시간선 상에서 증가 성향을 갖는 테이블 들. 이런 테이블에는 숫자의 자동생성 컬럼이 기본 키로 가장 유력한 후보가 될 수 있다.
3) 물론 2의 경우에도 특정 정보가 키로서 상속될 경우 조회에 성능향상을 가져올 수 있는 경우나(예를 들어 거래 기본정보의 날짜가 키에 포함되어 거래 세부내역에 상속되고 실제의 조회는 거래 세부내역만으로 이루어지는 경우가 많다든가 하는 경우)
 논리적인 의미상 특정 컬럼의 변경이 타시스템에 영향을 미쳐 별도의 작업을 유발시켜야 하는 경우처럼 외부키 제약 조건을 통해 키컬럼의 변경 자체를 승인하지 않고자 전략적 견지에서 키에 포함시키는 경우등의 논리적 설계의 필요상 필요하다면 굳이 자동 숫자 채번을 가져가지 않아도 될 것이다.
4) 마지막으로 반드시 고려할 사항은 사용 개발 도구이다. 사용개발 도구에 따라 가장 큰 숫자의 사용 상한선이 결정되기 때문이다. 예를 들어 .NET은 숫자를 22자리까지인가를 사용할 수 있다. 문자열에는 이러한 제한이 없다.