linuxism

좋은 테이블 설계의 4가지 규칙

1. 테이블의 각 필드는 고유한 종류의 정보를 나타내야 한다.

2. 각 테이블은 테이블에서 하나나 여러개의 필드로 구성된 고유 식별자나 기본키를 갖고 있어야 한다.

3. 각 고유한 키본키 값에 대해 데이터 열의 값은 테이블의 주제와 관련이 있어야 하고 그 주제를 완전히 설명할 수 잇어야 한다.

4. 어떤 필드(기본키로 지정한 필드)의 데이터를 바꿀 때 다른 필드의 데이터에 영향을 끼치지 않고 바꿀수 있어야 한다.

======================================================================================

1. 테이블 생선 전에 정규화 및 각 컬럼의 데이터 유형을 계획한다.

2. Block 영역을 위해 PCTFREE, PCTUSED를 충분히 고려한다.

  컬럼수가 많은 테이블에서 데이터를 처리하게 되면 디스트 I/O양이 증가하여 성능이 저하됨

▣ 행 이주 (Row Chaining) 현상 

- Block 길이가 너무 길어 데이터 Block 하나에 저장되지 않고, 두개 이상의 Block에 걸쳐 하나의  Row가 저장되어 있는 형태

- Block에 더 이상 Free 한 공간이 부족 할 경우 Update 발생시 해당 행 자체가 다른 Block으로 이동하는 현상

- 주로 Varchar2 타입을 가진 컬럼에서 발생한다.

- Update가 자주 발생하는 테이블을 파악하여 PCTFREE 파라미터 값을 조절한다.

- PCTFREE = 10 의미는 테이블이 생성될 때 할당된 블록의 행 데이터 영역의 크기에서 10% 공간만큼은 INSERT문 실행시에 사용하지 말고 Update 될 때에만 사용하도록 미리 확보하는 것이다.

▣ Row Migration 

-  데이터 블럭에서 수정이 발생하면 수정된 데이터를 해당 데이터 블럭에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장

▣ PCTFREE

- pctfree는 실제 Row길이가 커지는 비율만큼 예상하여 산정한다.

- 이를 예상하기 힘들기 때문에 다음과 같이 사용 가능하다.

   => 5  : Update가 거의 발생하지 않거나 Update시 Row길이가 거의 증가되지 않는 테이블에 젹용

      Sample : 코드 테이블, 통계 테이블

    => 10 : 보통의 테이블에 적용

    => 15 : Row길이가 증가되는 Update가 자주 발생되는 테이블에 적용

▣ PCTUSED

- pctused는 트랜잭션의 유형과 공간관리 정책에 의해 산정합니다.

- 이를 예상하기 힘들기 때문에 다음과 같이 사용 가능합니다.

  => 40 : Delete와 Insert가 많은 테이블에 적용 (특히 배치작업이 자주 이루어지는 테이블)

  => 60 : 보통의 테이블에 적용

  => 90 : Delete가 거이 이루어지지 않는 테이블에 적용 ( 코드 테이블 )

▣ PCTINCREASE

- pctincrease는 데이터의 Extent시 데이터의 증가 크기를 결정하는데 영향을 준다

- 테이블 파라메타 "pctincrease'는 '0'으로 하는것이 테이블 관리 및 운영 측면에서 유리하다.

 ( 테이블 증가 크기 = Next + ( Next * pctincrease))

3. Column의 데이터 타입

 ▣ 문자타입, 숫자타입의 결정

  - 가능한 문자와 숫자를 같이 결합하는 것은 좋지 못하다.

  - Number 타입 지정시 내부적인 변형을 일으켜 인덱스를 타지 못하는 경우 주의

  - 연산을 하거나 일련번호 등이 아닌 경우 Number 타입을 피한다.

  - 한자리 Number 타입은 피한다.

▣ Char

 - 입력 문자열의 길이가 일정한 경우 가능한 Char 타입을 사용하며 Varchar2, Varchar 타음은 가급적 사용하지 않는다.

- 디스크 공간의 효율적 활용 및 Insert, Update시 속도 향상과 fragmentation 방지를 위해 Varchar 보다는 Char 타입이 효율적이다.

- Char 타입의 출력 시 Right Trim 처리가 필요할 수 있으므로 주의한다.

- 날짜 혹은 시간을 표현할 경우 Date, DateTime 타입보다는 Char 타입을 사용하도록 한다.

  년월일을 저장할 경우 8자리, 시분초까지 저장할 겨우에는 14자리를 사용한다

  Sample >>

     Char(8)  : 날짜 - YYYYMMDD

     Char(14) : 날짜, 시간 - YYYYMMDDHHMI24SS

▣ Varchar2/Varchar

- 2000 Byte 이상의 데이터를 저장할 필요가 있을 경우, Long 혹은 Text 타입을 사용해야 한다.

▣ Long/Text

- 입력 문자열의 길이가 짧은 경우 가급적 사용하지 않는다.

- 각종 함수, 인덱스, Like 검색 등을 사용할 수 없다.

- Long 형은 하나의 테이블에 둘 이상의 컬럼을 만들수 없다.

   Text 형은 둘 이상의 컬럼을 허용한다.

▣ Number

 - 연산이 필요한 컬럼, 일련번호

 - 인덱스로 생성시킬 컬럼은 반드시 문자 타입

▣ Long

 - 하나의 테이블에 하나의 LONG 타입과 허용

 - 비교 불가

 - 인덱스 생성 못함

 - SQL 함수 사용 불가

 - PL/SQL에서 사용 불가

 - 서브쿼리 리스트 사용 불가

4. 제약조건

- 기본 키(Primary Key) 명칭은 테이블 명칭의 앞(또는 뒤)에 'PK_' 를 추가한다.

- 기본 키는 가급적 인덱스용 테이블스페이스에 생성토록 한다.

- Insert Query 시 성능 향상을 도모할 경우에는 가급적 Foreign Key를 지정하지 말아야 한다.

  설계서 상에는 논리적으로 선언해야 하며, 개발 완료 후 운영용 데이터베이스에서는 삭제해도 무방하다. 개발 및 테스트 단계에서 참조 무결성이 검증되어야 한다.

- 프로그램 개발 완료 후 각 테이블의 데이터를 추출(Select)하는 모든 쿼리를 검사한다. 이중에서 가장 많은 빈도로 호출되는 쿼리를 선택한 후, 쿼리의 응답 속도가 최상이 될 때까지 인덱스를 조정한다.

- 최빈도의 쿼리에 대한 인덱스 형성 후, 그보다 낮은 빈도로 접근하는 쿼리에 대한 인덱스를 생성하거나, 두 쿼리의 성능에 균형(balance)을 조절할 수 있도록 첫번째 인덱스를 조정한다.

- 앞서 하나 혹은 두 인덱스에 의해서 다른 쿼리의 성능이 떨어지는 현상이 발행하지 않는지 확인한다.

- 가급적 null 값이 허용되고, null 데이터를 많이 포함하는 커럼은 인덱스 대상에서 제외한다.

- 데이터 값이 편차가 적은 컬럼 또한 인덱스 대상에서 제외한다.

- 인덱스 생성으로 인한 성능 향상을 얻을 수 있는 경우는 인덱스를 포함하는 테이블이 데이터가 많거나, 향후 Row 의 수가 급격히 증가할 것이 예상되는 경우이다.

- 일반적으로 1000 ~ 10000 건 이하의 행을 지니는 테이블에서는 인덱스를 만들어도 성능 향상 효가가 없거나, 최적화기(optimizer)에 의해서 인덱스가 무시될 수도 있다.

- 하나의 테이블에 가급적 3개 이상의 인덱스를 만들지 않아야 한다. 인덱스가 많을 경우, INSERT, UPDATE, DELETE 시 쿼리의 반응 속도가 느려지게 되며, 디스크 사용량이 급증한다.

4. Null값이 예상되는 컬럼들을 가능한 연속된 순서로 지정한다.

   연속된 여러 개의 컬럼이 마지막 컬럼까지 Null값을 가진다면 그 전체가 1 바이트를 가진다.

5. Null 값으로 입력되는 경우 반드시 VARCHAR2 타입 사용

6. 저장될 테이블 스페이스를 지정한다.

7. 필요한 경우 NOLOGGING 키워드를 사용한다.

8. 필요한 경우CACHE 키워드를 사용한다.

ALTER DATABASE `db` CHARACTER SET utf8 COLLATE 'utf8_general_ci';