[SQLD/P] 30회 기출 - 참고사항

Hyo Kyun Lee·2023년 3월 3일

SQLD/P

목록 보기

77/82

1. 도메인

도메인은 속성, attribute에 대한 개념이고, 기본적으로 속성값이 나타날 수 있는 범위라 할 수 있다.
나아가 속성에 대한 데이터 타입이나 크기, 제약사항 등에 대한 의미로도 명시될 수 있다.

2. 반정규화

조인시 디스크 I/O가 너무 많고, 경로가 너무 멀어 조인으로 인한 성능저하가 예상될때 수행
컬럼을 계산하여 읽을때 성능이 저하되는 것이 예상될때 수행
기본적으로 데이터 무결성이 깨질 가능성이 많으므로, 무결성 보장방법을 고려해야 한다.
통계 테이블, 중복 테이블, 이력 테이블 추가 등의 기법이 존재

3. 엔티티 분류

엔티티를 분류하는 방법은 크게 두가지가 존재한다.

유형/무형에 따른 분류

유형엔티티 : 물리적인 형태가 있고 안정적인 엔티티
무형엔티티 : 물리적인 형태가 없는 엔티티
→ 개념엔티티 : 물리적인 형태가 없고 개념적 정보로 구분(조직, 보험상품 등)
→ 사건엔티티 : 업무를 수행함에 따라 발생하며, 비교적 발생량이 많고 각종 통계자료에 이용 가능(주문, 청구, 미납 등)

업무를 구성하는 모습에 따라 발생시점에 따른 분류

기본 엔티티 : 원래 존재하는 정보, 다른 엔티티로 인해 생성되는 것이 아닌 독립적으로 생성, 자신만의 고유한 식별자(사원, 부서, 고객, 상품 등)
중심 엔티티 : 기본 엔티티로부터 발생하며, 업무적인 행위 그 자체, 데이터가 중심 엔티티를 기점으로 많이 발생(계약, 청구, 주문 등)
행위 엔티티 : 두개이상의 부모 엔티티로 부터 발생하며, 데이터가 자주 바뀜(주문목록 및 사원변경이력 등)

4. 반정규화 대상

자주 사용되는 테이블에 접근하는 프로세스의 수가 많고, 항상 일정한 범위만을 조회하는 경우
테이블에 대량의 데이터가 있고, 대량의 데이터 범위를 자주 처리하는 경우, 처리범위를 일정하게 줄이지 않으면 성능을 보장할 수 없는 경우
통계성 프로세스에 의해 통계 정보를 필요로 할때, 별도의 통계 테이블을 작성해야 하는 경우
테이블에 지나치게 많은 조인이 걸려, 데이터를 조회하는 작업이 어려워지는 경우

5. 반정규화 종류

테이블 반정규화 - 테이블 병합

1:1 관계 테이블 병합 : 1:1관계를 통합하여 성능 향상
1:M 관계 테이블 병합 : 1:M관계를 통합하여 성능 향상
슈퍼/서브타입 테이블 병합 : 슈퍼/서브관계를 통합하여 성능 향상

테이블 반정규화 - 테이블 분할

수직분할 : 테이블을 1:1로 분리하여 성능 향상(트랜잭션의 처리되는 유형 파악이 선행되어야 함)
수평분할 : 로우 단위로 트랜잭션이 집중될 경우, 로우단위로 테이블을 분할

테이블 반정규화 - 테이블 추가

중복테이블 추가 : 동일한 테이블 구조를 추가하여 원격조인 제거, 성능 향상
통계테이블 추가 : SUM, AVG 등 계산해둔 컬럼을 미리 추가
이력테이블 추가 : 이력테이블 중 마스터 테이블에 존재하는 레코드를 중복하고, 보통 이 레코드는 기능성 컬럼 추가와 관련
부분테이블 추가 : 하나의 테이블 내 자주 사용하는 컬럼이 있을 경우, 해당 컬럼들을 모아놓은 반정규화된 테이블 별도 생성

컬럼 반정규화

중복컬럼 추가 : 조인에 의한 컬럼 처리시, 조인감소를 위해 중복 컬럼 추가
파생컬럼 추가 : 트랜잭션 처리 시점에 계산되어 발생하는 성능저하를 예방하기 위해 미리 계산한 컬럼을 추가
이력테이블 컬럼 추가 : 최근값, 불특정날 조회, 시작 및 종료일자 등의 기능성 컬럼을 추가(대량의 이력 데이터에서 데이터를 조회하여 성능저하 방지)
PK에 의한 컬럼 추가 : 복합의미를 가지지만 단일 PK로 되어있을 경우, PK에 이미 데이터가 존재하지만 일반속성으로 포함하는 방법
응용 시스템 오작동을 위한 컬럼 추가 : 업무적인 의미는 없으나, 사용자의 처리 오류 등으로 이전 값 복구를 원할때 이전 데이터를 임시로 보관할 컬럼을 추가하는 기법

6. SELECT절에 서브쿼리가 있는 경우

SELECT A,
(SELECT A FROM TABLE WHERE ... ) AS A_SUBQUERY

가능은 하다. 다만 반드시 스칼라 서브쿼리이어야 한다.
즉, 하나의 행을 반환하던지 아니면 하나의 값만 반환해야 한다.
두개이상의 값이 반환된다면 오류 발생한다.

SELECT A.COL1, 
(SELECT COL3 FROM B WHERE A.COL1 = B.COL1) AS B_COL3

여기서 A.COL1의 값이 나오고, B Table에서 COL3의 값을 select하는데, 출력되는 COL3의 값은 반드시 A.COL1에 따라 한가지만 존재해야만 한다.
즉, 조인하면서 A.COL1에 상응하는 값이 B table에 두개 이상 존재할 경우에는 해당 select 문은 오류 발생.

7. IN의 조건이 괄호일 경우

WHERE (COL1, COL2) IN ( ('A', 50) )

COL1, COL2가 'A', 50에 해당하면 참이다.

8. IN NULL

기본적으로 NULL에 대한 비교는 IN과 같은 비교연산자로 할 수 없다(해당 연산은 제외).
IN NULL -> 연산 및 처리 불가, 이를 비교하려면 반드시 IS NULL로 처리해야 함
WHERE COL IN ('A', NULL) -> A에 해당하는 값만 출력됨, NULL값은 비교 자체가 안돼서 출력 안됨

※ 윈도우 집계 함수는 NULL 컬럼은 집계 대상에서 제외하고 산출

9. SQL 실행계획

안 -> 밖(더 안쪽에 index가 있다면, full scan 이후에 index scan)
위 -> 아래

SELECT
 NESTED1
  NESTED2
   T1
   T2
    INDEX
  NESTED3
   T3

실행순서 : T1->INDEX->T2->NESTED2->T3->NESTED3->SELECT

10. 트리거

DML문이 수행되었을때 자동으로 실행되도록 작성된 프로그램
DELETE ON TRIGGER시 :OLD는 삭제 전 데이터를, :NEW는 삭제 후 데이터를 나타낸다.
UPDATE TRIGGER시 :OLD에는 수정 전, :NEW에는 수정 후 값이 들어간다.
테이블 ,뷰, DB작업을 대상으로 정의할 수 있다.

11. 내장 함수

COALESCE(A,B) -> A,B 컬럼을 조사하고 NULL이 아닌 가장 첫번째 값을 반환한다. 모두 NULL이라면 NULL값을 반환한다.
CASE COL WHEN ~ -> case ~ when 구문에 따라 반환하고, 모두 만족하지 않으면 NULL(ELSE NULL)값을 반환하거나 그대로 반환
DECODE(A,B,C) -> A 컬럼의 값이 B이면 C를 반환
NULLIF(A,B) -> A와 B의 값이 같으면 NULL 반환, 다르면 A값 반환

12. 문자열 데이터 유형

CHARACTER : 고정 문자열
VARCHAR : 가변 문자열

13. NVL(A,B)

A의 값이 NULL이면 B의 값으로 대체하여 출력

14. LEFT / RIGHT JOIN

기본적으로 outer join이라 생각하면 된다.
LEFT JOIN = LEFT OUTER JOIN

15. PK 관련 유의사항

PK설정시 기본적으로 null insert는 불가
단 update시 null로 set될 수는 있다.
부모, 자식 관계가 있을때 부모를 먼저 데이터 삽입한 이후에 자식 데이터를 삽입할 수 있다.

16. 파티셔닝

주로 반정규화 이전에 물리적으로 성능 향상을 고려해보는 옵션 중 하나로, 테이블이나 인덱스를 파티션 단위로 나누어 저장하는 것을 의미한다.
index scan도, 이 수가 많아지게 되면 오히려 full table scan보다 성능이 저하되는 경우도 있는데, 파티셔닝을 하게 되면 이러한 성능 저하를 방지할 수 있다.

17. 파티셔닝 기법

range : 키값의 범위(날짜 등)에 따라 분할
hash : 키값에 해시함수를 적용하고, 이에 대한 반환 값에 따라 분할, 각 로우의 저장 위치 예측이 불가능하며 데이터 분포를 고르게 할때 사용
list : 불연속적이고 대용량 데이터를 분할하고자 할 때 사용, 사용자가 그룹핑한 기준에 따라 구분하며 보통 컬럼에 구분인자가 없을 때 사용한다.
composite : range와 list의 조합

18. MERGE INTO

MERGE INTO A
USING TABLE B
ON (A.COL1 = B.COL1)
WHEN MATCHED THEN ~~~
WHEN NOT MATCHED THEN ~~

A는 update, insert할 테이블이고 B는 비교 대상 테이블이다.
ON은 조인 조건이고, 해당 ON 조건이 일치할 경우에는 MATCHED 구문을 실행하고 일치하지 않을 경우에는 NOT MATCHED 구문을 실행한다.
MATCHED, NOT MATCHED 내의 구문들은 모두 종속적인 구문으로, 하나만 실행될 수 없다. 반드시 모두 실행된다.