1. 식별자 특징

• 엔터티 내 인스턴스들을 구분할 수 있는 구분 인자
• 유일성 : 인스턴스들을 유일하게 구별할 수 있어야 함
• 최소성 : 유일성을 만족하면서 최소의 개수여야 함
• 불변성 : 식별자로 선정된 데이터는 변하면 안됨
• 존재성 : NOT NULL, 데이터 반드시 존재

※ 자주 이용되는 속성을 주식별자로 지정
※ 명칭, 내역 등과 같이 이름으로 기술되는 것들은 주식별자로 지정하지 않음
※ 복합식별자라도 너무 많은 속성이 포함되지 않도록 한다.

2. 식별자 종류

• 대표성
  가지면 : 주식별자
  안가지면 : 보조식별자

• 업무에 의해 스스로 생성되는 식별자
  스스로 생성되면 : 내부식별자
  외부에서 참조하여 생성되면 : 외부식별자

• 속성의 개수
  단일 속성 : 단일 식별자
  다중 속성 : 복합 식별자

• 대체가능여부
  업무에 의해 생성된 그 자체 : 본질 식별자
  원조 식별자가 복잡해서 인위적으로 만든 식별자 : 인조 식별자

3. 반정규화 기법

• 칼럼 반정규화

중복컬럼 추가 > 조인에 의해 처리시 성능저하 예상시 조인 감소를 위해 중복컬럼 위치
파생컬럼 추가 > 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해, 미리 값을 계산하여 컬럼에 보관
이력테이블 컬럼추가 > 기능성 컬럼 추가(최근값 조회 등..최근 값 여부, 시작 및 종료일자)
PK값에 의한 컬럼 추가 > 복합의미를 갖는 PK를 단일속성으로 구성할 경우, PK안에 이미 데이터가 있지만 일반속성으로 포함하는 경우
응용시스템 오작동을 위한 추가 > 데이터 값 입력 오류 발생시를 대비, 데이터를 임시로 중복하여 보관하는 기법

• 테이블 반정규화

→ 테이블 병합
1:1관계 테이블 병합 > 1:1관계를 통합
1:M관계 테이블 병합 > 1:M관계를 통합
슈퍼/서브타입 테이블 병합 > 슈퍼/서브 관계를 통합

→ 테이블 분할
수직분할 > 컬럼 단위 테이블을 disk I/O 분산처리를 위해 테이블 1:1 분리
수평분할 > 로우 단위로 집중되는 트랜잭션을 분석하여 디스크I/O 및 데이터접근의 효율성 증가(로우 단위로 쪼갬)

→ 테이블 추가
중복테이블 추가 > 다른 업무, 서버가 다른 경우 동일한 테이블 구조를 중복하여 원격조인 제거
통계테이블 추가 > SUM, AVG 먼저 수행하여 계산, 조회시 성능 향상
이력테이블 추가 > 마스터 테이블에 존재하는 레코드 중복
부분테이블 추가 > 하나의 테이블에서 자주 사용되는 컬럼이 집중되어있을때, 디스크I/O를 줄이기 위해 해당 컬럼들을 모아 놓은 테이블 생성

4. ERD 반정규화 기법

• 특정 속성의 조회 빈도수가 높다면 비식별자에서 식별자 관계로 바꾸는 것도 반정규화의 일종임
• 한 테이블의 다량의 속성을 한꺼번에 넣는 것은 반정규화에 해당하지 않는다.

5. 이상현상의 종류

• 삽입 : 데이터를 삽입할때 의도와 다른 값들도 삽입됨, 불필요한 데이터까지 삽입하거나 특정 데이터가 존재하지 않아 삽입이 불가능 할 때모드
• 삭제 : 데이터 삭제시 의도와 달리 다른 데이터도 같이 삭제됨
• 갱신 : 데이터 갱신시 특정 데이터만 갱신되어 데이터 불일치가 발생함

6. TRUNCATE/DROP/DELETE

• TRUNCATE
  → 전체 데이터를 한번에 삭제, delete처럼 하나하나 지울 수 없음
  → CREATE한 초기 상태와 같다.
  → 자동 commit, 되돌릴 수 없다.

• DELETE
  → 컬럼을 삭제한다.
  → from 삭제 가능
  → 모든 데이터를 삭제해도 내부적으로는 하나하나 삭제함
  → 그렇기에 부하가 크다.
  → storage는 초기화 전임, roll back 가능

• DROP
  → DROP TABLE EMP;
  → 테이블 자체를 아예 삭제, 모든 인덱스도 삭제
  → 자동 commit, 되돌릴 수 없음

각각의 차이는 이곳에서 참고

7. ORDER BY 1,2;

• 첫번째, 두번째 컬럼순서대로 정렬하고 첫번째 컬럼이 동률일 경우 두번째 컬럼을 기준으로 정렬한다.
• 기본은 오름차순(ASC)이다.
• SELECT 절에서 나타나지 않은 컬럼이라면, 숫자(1,2)는 사용할 수 없다.
• FROM에서 table 직접 사용시 숫자 사용 가능, 서브쿼리를 통해 사용시 숫자 사용 불가

8. CONNECY_BY 부가기능

• CONNECT_BY_ROOT : LEVEL = 1인, 즉 해당 로우에 대한 루트 노드의 컬럼값 출력
• CONNECT_BY_ISLEAF : 리프노드(최하위) 여부, 최하위일 경우 1 반환
• CONNECT_BY_ISCYCLE : 순환관계발생여부, 순환관계면 1 반환
• SYS_CONNECT_BY_PATH(컬럼, 구분자) : 계층구조 생성시, 해당 컬럼값의 계층구조(path) 출력

CONNECT BY 부가기능은 이곳을 참고

9. 실행계획

• 소량인 테이블을 선행으로
• 조인하는 컬럼에 인덱스가 있어야 Nested loop join이 효율적이다.
• nested -> sort merge -> hash

10. 집계함수 column 별 사용

• SUM(COL1) + SUM(COL2)
• SUM(COL1), MAX(COL2)
• COL1, COL2 각각 계산한후 집계함수 결과로 나오거나, 각각의 column 결과로 출력됨

10. HAVING 단독사용

• table 자체를, 즉 모든 컬럼을 하나의 group by 기준으로 보고 별도 group by 구분없이 하나로 합치고자 할 때 HAVING 단독 사용 가능

SELECT COUNT(*)
FROM TABLE
HAVING COUNT(*) > 5;

따라서 해당 table의 전체 행이 5개 이상이라면 해당 COUNT() 갯수가 출력될 것이고, COUNT() 개수가 5개 이하라면 결과 없음(공집합)으로 나타날 것이다.

HAVING 단독사용은 이곳을 참고

11. outer join (+)

• A = B(+) -> A를 기준으로 B를 OUTER JOIN한다.

12. 다중 outer join(+)

• 기준이 되는 table을 기준으로 하고, outer join하는 테이블의 데이터가 없다면 그대로 null이 출력된다.
• 단 outer join을 여러번 하더라도, 단 하나의 조건에서 inner join이 있을 경우 null값에 대한 column이 선별되어 결과값에 출력되지 않을 수 있으므로 이것에 유의

outer join 참고자료1

13. NULL 관련 함수

• coalesce(A, B) -> A, B 값을 넣어 NULL이 아닌 가장 첫번째 값을 출력하고, 모두 NULL이면 NULL 출력
• NULLIF(A, B) -> A,B가 같으면 NULL 출력, 다르면 A 출력
• ISNULL(A, B) -> A가 NULL이면 B로 출력
• NVL(A, B) -> A가 NULL이면 B로 출력
• NVL(NULL, A) -> NULL은 NULL이므로(true), A그대로 출력됨

14. ROW_NUM, RANK의 차이

• 동일한 PARTITION, ORDER BY 했어도 순위를 매기는 방식이 차이가 있기 떄문에 결과는 다를 수 있다.
• ROW_NUM -> 동률이 있어도 1,2,3,4,5..로 순번
• RANK -> 동률이 있을 경우 1,2,2,4...로 순번
• 즉 Rank의 경우 동일한 순번이 나타날 수 있기에, ROW_NUM에 적용한 조건절을 그대로 적용한다고 해도 다른 결과가 나타날 수 있다.

15. ROLL BACK

• oracle에서는 DDL을 실행하면 auto commit된다.
• SQL에서는 DDL을 실행하면 auto commit된다, auto commit을 해제하였다면 SQL Server에서는 auto commit하지 않는다.

16. SQL EXP함수

• exp(n) : e의 n제곱을 반환

17. INSERT 구문 유의사항

• INSERT INTO EMP (1,2,3,4..) VALUES( 1,2,3..)

이때 COLUMN명을 적지 않고, EMP VALUES(1,2,..)만을 한다면 모든 컬럼에 대해 누락없이 삽입하는 데이터들을 넣어야 한다.

18. ORDER BY 특징

• 기본적으로 오름차순이다.
• oracle에서는 null을 가장 큰 값으로, SQL에서는 가장 작은 값으로 인식한다.
• order by에서 사용하는 case문은 정렬의 우선순위를 정해준다.
• 테이블에 컬럼이 있다면, select 구문에 사용되지 않은 컬럼도 order by 구문에 사용될 수 있다.
• 단, 서브쿼리를 from절로 사용한다면 컬럼 순서(1,2)를 order by절에 사용할 수 없다.

order by 관련 참고는 이곳에서

19. 조인 기법

• hash join은 정렬 작업 없으므로, (기능적으로만 보았을떄) 정렬이 부담되는 대량배치 작업에 유리
• 단 hash join과 sort join은 메모리 사용을 과다하게 요구하므로, 메모리 관점에서는 nested loop join이 유리할 수는 있다.
• 대용량의 데이터를 가진 두개 테이블 조인시 hash join이 nested보다 일반적으로는 유리하다.
• 옵티마이저는 인덱스가 없다면 sorted merge join을 선호한다.
• 배치작업에 보통 사용되는, 선호하는 정렬은 sorted merge join이다.

20. connect by가 없는 계층형 쿼리

• A.manager_id = B.employee_id로 조인조건이 나와있다면, A가 직원이고 B가 상사인 관계임을 짐작할 수 있다. -> A기준으로 left outer join한다면, A직원은 가장 최고상사이므로 최상위 순서에서 B는 NULL이 된다.
• table에서 from table 조건이나 형태만 나타나있을 경우(A가 LEFT OUTER JOIN이고, B에 NULL이 있다면 A의 manager_id가 NULL, 즉 A가 최상위 상사가 되므로 A의 manager_id와 B의 employee_id가 조인조건으로 되어야 한다.
• 헷갈리면 역으로 생각해보자.

21. GROUP BY, window

• group by와 window 함수는 같이 쓸 수 없다.

22. with절, with절을 통한 계층형 쿼리

WITH A AS
(
	SELECT ~~
) 

• 이 with절을 선언하고, 아래 AS절에서 SQL문 작성시 with절을 완전히 정의하기 전에 with절을 또 사용할 수 있다.
• 보통 UNION ALL을 사용하고, 최초 절을 최상위 계층으로 지정한다.
• 그후 SELECT절을 사용하는데, from에서 with절을 사용하고 join 조건으로 연결한다.
• 이러면 계층형 구조가 되는데, 아래 하위 계층으로 내려가면서 결과가 누적이 된다(컬럼에 a+b와 같이 부모와 자식의 합을 구하는 요소가 있을 경우)

23. 연산순서

• NOT > AND > OR 연산 순으로 진행한다.
• ~~ > 200 OR ~~ AND ~~ -> ( ) OR ( AND )

24. NTILE

• NTILE(4) -> 전체 건수를 4등분 한다.
• 4등분할때 꼭 같은 숫자로 나누는건 아니고, 적절하게 등분한다.

25. LAG

LAG(A, offset)

• A컬럼의 offset 만큼 이전의 값을 읽어온다.

26. LEAD

LEAD(A, Offset)

• A컬럼의 offset 만큼 이후의 값을 읽어온다.

27. 참고자료

delete, truncate, drop 차이점 - https://prinha.tistory.com/entry/SQL-DELETE-TRUNCATE-DROP-%EC%B0%A8%EC%9D%B4%EC%A0%90
connect by 부가기능 - https://widecheon.tistory.com/368
having 단독 사용 - https://ggmouse.tistory.com/447
(+)(outer join) 다중 사용 - https://data-make.tistory.com/24
order by 특징 - https://javafactory.tistory.com/115