SQLD 자습 정리

 

1) 데이터 모델링의 이해
엔티티, 속성, 관계, 식별자 

2) 데이터 모델과 성능
정규화, 반정규화, 대용량 데이터 처리, DB 구조와 성능, 분산 DB

3) SQL 기본과 활용
DDL, DML, TCL, WHERE절, GROUP BY, HAVING 절, ORDER BY 절
표준조인, 집합연산자, 계층형 질의, 서브쿼리, 그룹함수, 윈도우함수
DCL, 절차형 SQL, 옵티마이저, 인덱스 기본, 조인 수행 원리

 

 

 

 

 

 

 

1) 데이터 모델링의 이해

1. 도메인의 특징

- 속성에 대한 데이터 타입과 크기 지정, 각 속성에 대한 제약사항(not null, FK) 지정

- 단, 속성에 대한 단순 Check 조건 지정은 ㄴㄴ

 

2. 식별자 관련

- 주식별자는 null 값을 가지면 안 됨, 값도 변경되면 안 됨

- 생성 여부가 스스로 이루어져 분류되는 식별자는 내부 식별자

- 외부로부터 파생되어 이루어지는 식별자는 외부 식별자라고 한다. 

 

3. 엔티티 

- 속성과 값을 포함할 수 있는 구분성이 있는 독립된 개체

- 단, 속성은 한 개의 속성값으로만 구성되야 함(정규화)

- 모든 컬럼을 하나의 테이블에 넣는 반정규화는 없음 (N대N 조건을 확인해야 됨)

-> 튜닝을 한다고 무작정 한 테이블에 모든 데이터를 넣으면 검색이나 관리는 용이할 수도 있겠지만

데이터 특성에 따른 테이블 관계를 정확하게 고려하지 못하면 되려 독이 됨.

- 엔티티를 생성하는 것은 속성을 제거하는 것이 아닌 새로운 테이블을 만드는 것 

- 따라서 하나의 테이블에 종속되지 않도록 여러 개의 테이블을 만들고 속성을 분리해내는 것이 엔티티를 생성하는 것임 (엔티티=테이블) 

- 엔티티가 새로 생긴 수 = 속성의 중복 값을 제거한 수

 

4. 반정규화 

- 검색 빈도가 높을 경우 주로 시행

- 두 테이블의 관계를 추가하는 관계 반정규화 하기

- 비식별자 관계를 식별자 관계로 하기

- 통계나 일일기록 컬럼이 필요할 땐 해당 기능의 테이블을 추가하기

-> 실시간성 데이터를 요구하는 서비스들이 간혹 있을 때 사용하는 방법으로,

정보성 테이블에서 데이터를 추출하는 것보다 거래용 테이블을 따로 만드는 방법 등에서 사용

 

5. 데이터 모델링의 3요소 = Thing, Attributes, Relationship

- 논리적 모델링의 외래키는 물리 모델에서 반드시 구현되지는 않음

- 개념적 모델링이 가장 추상적인 단계임 (개념->논리->물리)

- 모델링 순서 역시 개념적 -> 논리적 -> 물리적

- 논리적 모델링은 최종적으로 데이터 모델링이 완료된 상태라고 볼 수 있음

 

* Row Chaining

로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 못하고 두 개의 블록에 걸쳐서 저장되는 현상

* Row Migration

UPDATE Query를 할 때, 늘어나는 저장 공간이 없어져서 다른 블럭으로 ROW를 옮기는 현상

3) SQL 성능과 이해

1. TRUNCATE

- 테이블의 모든 행 값을 지워버림, 특정 행(row)를 지울 순 없음

- 테이블의 구조는 남겨둔다는 면에서 Drop와는 다름

- 행을 삭제하는 Delete table과는 다르게 정상적인 복구가 불가능하며 시스템 부하도 더 작음

2. ORDER BY

- Order by + 숫자

ex) ORDER BY 3

3번째 열(속성)을 기준으로 정렬하라는 뜻

- 기본은 오름차순(ASC)이고 컴마로 구분함

따라서 컬럼명이랑 DESC/ASC가 함께 있는 경우에 앞에 있든 뒤에 있든 해당 정렬 방식을 따름

단, 먼저 정렬방식이 정해진 경우엔 제외

ex) ORDER BY 성적 DESC, 몸무게, ASC 키 => 성적은 내림차순, 몸무게와 키는 오름차순

- SELECT보다 후행으로 연산됨


3. TRIGGER

- INSERT DELETE UPDATE 등이 TABLE에 행해질 때 암묵적으로 수행되는 Procedure (따라서 자동실행임)

- VIEW를 대상으로는 실행되지 않으며 별도의 DB Space에 저장

* BEFORE : INSERT, DELETE, UPDATE 문이 실행되기 전에 실행

* AFTER  : INSERT, DELETE, UPDATE 문이 실행된 후에 실행

-> FOR EACH ROW를 사용해서 특정 행의 데이터 변화가 생길 때 실행하고 해당 행의 실제값을 제어

* CREATE OR REPLACE로 생성하며 INSERT OR UPDATE ON 테이블명으로 자동실행

반면 일반 PROCEDURE 은 COMMIT 후, EXEC를 해야 실행됨

 

4. nested loop join

- Simple NL 조인 : 두 테이블을 상대로 카티전 프로덕트를 한 후 조인 조건에 맞지 않은 행들을 제거

- Indexed NL 조인 : 인덱스를 통해 NL 조인을 간소화한 것으로 조인 조건으로 사용되는 칼럼에 반드시 인덱스가 존재해야 함

 

5. sort-merge join

- 조인하려는 두 테이블을 조인 속성으로 정렬해서 sorted lists를 만든 후 merge 하는 기법으로 인덱스가 없는 NL 조인보다 성능 좋음

6. hash join

- sort-merge 조인의 경우 정렬 비용 부담이 크고 NL 조인은 반복 탐침하는 비효율이 있기 때문에

두 테이블의 크기를 비교한 후, 큰 집합에서 작은 집합을 대응해가면서 비교

따라서 포함을 통한 비교('=' 연산자 활용)이기 때문에 Non Equal Join 작업은 불가능함

 

7. coalesce

해당 컬럼들 중에서 NULL이 아닌 첫 번째 값을 반환하는 함수

ex) coalesce(A,B,C,D...) => A, B가 null이 아니고 C가 null이면 C를 반환

☆ start with 조건1 connect by 조건2

: 일명 계층형 쿼리

조건1에는 root rows가 오고(최상위 조건),

조건2에는 root row와 child row의 관계를 명시해야 함

조건1에서는 서브쿼리를 허용하는 반면, 조건2에서는 불가능

* prior : 조건 검색의 순서를 정하는 한정자. 부모 조건을 한정하기 위해

- 여러 번 사용할 수 있지만 무한 루프 발생할 수 있음

ex) prior 자식컬럼 = 부모컬럼 => 부모에서 자식으로 TOP-DOWN

-> PRIOR 절에서는 누가 부모고 자식인지만 밝혀내면 됨

- WHERE 절 뒤에 나오며 WHERE 조건에 맞는 행을 출력하는게 관건


8. CUBE vs ROLLUP vs GROUPING SETS

: ROLLUP은 파티션별로 소계를 하는 것이라면 CUBE는 파티션, 열, 소계 별로 다차원적으로 집계를 해줌

- GROUPING SETS는 GROUP BY 를 UNION 한 결과임

** CUBE 함수 사용하고 몇 개의 행이 출력되는지 계산하는 문제
대순서 ┐
1) GROUPING 하기
GROUP BY(컬럼명) : 컬럼명으로 묶은 후, 나머지 컬럼은 전부 중복 제거 DISTINCT 해야 함
GROUP BY 함수가 들어가면 더 이상 기본 조인이 아님!
2) 1차 - ROLLUP 하기
각 그룹별로 소계를 내기, 따라서 행 하나가 추가
3) 1번째 컬럼에 대해 CUBE를 하기 (즉, 세로 단위로 ROLLUP을 하는 것임)
이 때 주의해야 할 것은 중복 제거(DISTINCT)를 해야함
4) 2번째 칼럼이 있다면 +1 하기 
2차 - ROLLUP과 유사한 기능

SELECT 컬럼1, 컬럼2, 컬럼3
-> SELECT에 나오는 컬럼을 기준으로 계산해야 함
먼저 조인을 해서 기본적인 행 개수를 구하고,
칼럼1로 묶어서 GROUP을 한 후, DISTINCT 칼럼2를 한 값을 가산한다. 


9. COUNT(*)에 HAVING으로 추가로 조건을 줄 수 있으며 조건에 해당하지 않는다면 공집합(0rows)가 나옴

10. LEFT OUTER JOIN

WHERE A1.col1 = B1.col1(+)

A1에 있는 컬럼을 전부 가져온 후 B1과 맞지 않는 것은 null로 채우기

근데 단순 칼럼 갯수 집계할 때는 col1에 대한 교집합 뿐임

 

11. Oracle에선 UPDATE 쿼리가 ROLLBACK되지 않는다고 한다.

12. ORACEL에서 not exists를 쓸 때 조건이 맞지 않는 필드는 null로 표시된다.

13. DELETE FROM 테이블명

-> 테이블에 있는 데이터 지우는 쿼리

 

14. DROP TABLE 테이블명

-> 테이블을 없애는 쿼리

15. INTERSECT : 결과의 교집합으로, 중복된 행을 하나로 표시 => DISTINCT + JOIN

조건이 걸려 있는 테이블 쪽으로 LEFT OUTER JOIN 하면 해당 반대 테이블에 없는 항목도 조회 가능 

=> (어지간하면 먼저 나온 테이블 쪽으로 L조인 걸기)

15. NTILE(숫자) OVER (PARTITION ~~)

- 파티션만큼 나온 행을 숫자로 나눈 다음, 각 행에 등급을 매김

- 100개의 행이 나오고 5라는 숫자를 넣으면 상위 20%, 40%, 60%, 80%, 100%를 매기게 되며, 각 행의 등급을 다시 수로 표시

ex) NTILE(5) OVER (PARTITION ~) 

=> PARTITION에서 나온 칼럼 값들에서 등급을 매겨서 수로 표시

 

16. LAG(컬럼명, offset, (default))

: 해당 컬럼의 이전 값을 가져오되, offset을 적용받는다.

: null값인 경우엔 default값을 출력

또한, Partition by를 적용받기 때문에, 해당 소그룹에서 조건에 맞는 컬럼만 가져온다.

17. ALL 

ex) ALL(30,50) => 30보다 작고 50보다 작음. 즉. 30보다 작은 것

 

18. SQL SERVER에선 IDENTITY로 지정된 칼럼에 값을 넣으면 에러가 남

ex) IDENTITY[ (seed, increment) ]

IDENTITY(1,1) 에서 INSERT INTO 테이블명 VALUES(1,'A') 는 오류임

seed는 첫번째 행이 입력될 때의 시작값을 의미. 따라서 값을 넣으면 오류

19. Non equal join과 같은 부정형 비교는 인덱스 사용이 불가능

두 테이블을 전부 카디젼 프로덕트를 한 후, 조건(칼럼1 <> 칼럼2)에 맞는 행을 제거

단, NULL은 체크하지 않는다. NULL이 있는 컬럼을 제외하고 재구성하기

20. TOP n [WITH TIES]

WITH TIES는 TOP N의 마지막 행으로 표시되는 추가 행의 데이터가 같은 경우 해당 행도 추가로 반환해주도록 함
ex) TOP 3을 지정했는데 3에서 같은 값을 가지는 행이 두 개가 나오면, 예외적으로 4개의 행을 반환

21. Natural Join

- 테이블만 입력하고 컬럼명을 입력할 수 없음 명시적으로 칼럼을 조인하기 때문임

ex) 테이블1 NATURAL JOIN 테이블2 (ON 테이블1.컬럼A = 테이블2.컬럼B) => 틀림
     테이블1 NATURAL JOIN 테이블2 // 로 수정

- SELECT 절에 테이블 식별자 사용 불가능

ex) SELECT DEPT.STUDENT_NUMBER (X) -> SELECT STUDENT_NUMBER

- Cross JOIN 처럼 Where절에 조건을 걸 수 없음

22. USING 절을 사용한 조인

ex) SELECT 컬럼
      FROM 테이블1 JOIN 테이블2 USING(조인할 컬럼)

23. MERGE INTO 테이블명1 USING 테이블명2 ON (조인조건)

WHEN MATCHED THEN
UPDATE ~
DELETE ~
WHEN NOT MATCHED THEN
INSERT ~
즉, 테이블명1과 테이블명2을 합치되, 일치한 경우에는 MATCHED 절을 따르고 아닌 경우엔 NOT MATCHED 절을 따름

24. RANGE BETWEEN - (1- UNBOUNDED PRECEDING, 2- UNBOUNDED FOLLOWING, 3- CURRENT ROW)

: 파티션의 범위를 한정하는 예약어.

차례대로,

1 : 파티션의 첫번째 행인 start_point만 들어갈 수 있음

2 : 파티션의 마지막 행인 end_point만 들어갈 수 있음

3 : 둘 다 가능

ex) SUM(SAL) OVER(PARTITION BY JOB ORDER BY NO RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) SAL1

25. MAX(LEVEL) : 가장 깊은 계층을 구하는 키워드

LEVEL(컬럼명)을 기입 할 필요가 없음, LEVEL 자체로만 해도 깊이 값을 가져옴

26. NUMBER가 아닌 VARCHAR로 비교할 경우 따옴표를 무조건 써야 한다.

ex) col1 > 0, col1 >'0'

27. WHERE 1=2로 틀면 무조건 0 rows 출력함

28. ALTER

ALTER COLUMN : 컬럼의 데이터 타입을 바꾸는 쿼리 (in SQL SERVER)

ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입(데이터크기))

ALTER TABLE 을 해도 이전의 데이터는 바꿔지지 않는다.

ex) ALTER TABLE 테이블명 MODIFY(컬럼명 DEFAULT 10);

ex) ALTER TABLE 테이블명 ADD(컬럼명 데이터타입(데이터크기));

29. SELECT 절에 있는 서브 쿼리문은 무조건 단일 행만 리턴해야 한다.

30. NULL에 관한 지식

1) WHERE 조건의 COL1 IN (NULL) 은 blank를 찾지 못함

2) NVL(null,'')은 blank가 아닌 null을 반환함

3) INSERT INTO 테이블명 VALUES('')은 NULL 삽입 오류 발생 ORA-01400

4) blank '' 은 무엇과 비교해도 true가 될 수 없음

'' = '' -> false

'' = NULL -> false

IN, <>, != 등등  -> false

5) TRIM('')은 NULL을 반환함

6) '' IS NULL은 참

즉, '' -> null 이지만 null -> ''은 안된다.

''는 비어있는 데이터, null은 아직 지정하지 않은 데이터를 의미하기 때문임

7) insert 조건에 컬럼명1 < 10을 할 경우, 값이 null인 컬럼은 체크하지 않고 insert 가능

 

31. FK 조건이 걸린 테이블에 INSERT/UPDATE할 경우엔 무조건 다른 테이블의 PK가 있어야 함

32. COMMIT 조건

- DDL, DCL이 있는 경우에는 자동으로 COMMIT
- CREATE, ALTER, RENAME, TRUNCATE, GRANT, REVOKE

33. ORACLE에서 || , Concat은 SQL SERVER에선 + 기호로 대체

 

 

 

 

몰랐던 사실을 위주로 정리

 

1. Oracle을 포함한 DBMS에서는 하나의 레코드를 읽더라도 해당 레코드가 속한 블록 단위로 I/O를 함

 -> 따라서 참조하는 블록의 갯수는 SQL 성능을 좌우하는 가장 중요한 지수로 옵티마이저의 판단에 큰 영향을 미침

 -> Single Block I/O와 Multi Block I/O로 나뉘며 인덱스 스캔 시에는 Single이 대량의 데이터를 조회할 땐 Multi를 사용

(인덱스의 경우 LRU 기법으로 조회 후 저장을 하게 되는데 여러 개를 한 번에 읽어오면 우선순위에서 미뤄질 수 있음)

 

2.  필요한 최소 블록만 읽도록 하려면 From 절을 줄이면 된다. SELECT 절에 Sum, Case 절을 더 두던가 해야함 

 

3. Coalesce는 두 개의 값 중에서 null 값이 아니고 먼저 나온 값을 결과로 select할 때 사용

 

4. nullif(칼럼1,칼럼2) : 두 칼럼의 값이 똑같을 때 null 값으로 반환 

-> NVL 반대 느낌

 

5. non-EQUI JOIN

-> =  연산자가 이닌 Between, >, >=, <, <= 등으로 조인을 수행