헝그리개발자

3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

7. Lock과 트랜잭션 동시성 제어

Lock
- 같은 자원을 액세스 하는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하면서 순차적인 진행을 보장하는 직렬화 수단

공유 Lock (Shared Lock)
- 데이터를 읽기 위해 사용하는 Lock
- 한 자원에 대해 같은 공유 Lock 끼리는 여러개 설정이 가능하다. (동시에 읽기 가능)
- 배타적 Lock 과는 동시 진행 불가.

배타적 Lock (Exclusive Lock)
- 데이터를 변경하고자 할 때 사용하는 Lock
- 트랜잭션이 종료될때까지 유지되며, 다른 공유 Lock / 배타적 Lock 이 동시에 설정 불가하다.

블로킹 (Blocking)
- Lock 경합으로, 다른 트랜잭션에서 Lock 이 걸려서, 대기하고 있는 상태

교착상태 (Deadlock)
- 두개의 세션이 각각 Lock 을 설정한 리소스를 서로 액세스 하려고 블로킹 상태에 있는 상태



SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

READ UNCOMMITTED
- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 행을 문이 읽을 수 있도록 지정

READ COMMITTED
- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 문이 읽을 수 없도록 지정
- MS-SQL Select : Row 레코드를 읽는 동시에 Lock 을 획득하고, 다음 레코드로 이동하는 순간 Lock 이 해제된다.

REPEATABLE READ
- 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 문이 읽을 수 없도록 지정하고 현재 트랜잭션이 완료될 때까지 현재 트랜잭션이 읽은 데이터를 다른 트랜잭션이 수정할 수 없도록 지정
- MS-SQL Select : Row 레코드를 읽는 동시에 Lock 을 획득하고, 최종커밋 또는 롤백 하는 순간 Lock 이 해제된다.

SNAPSHOT
- 트랜잭션의 문이 읽은 데이터가 트랜잭션별로 트랜잭션을 시작할 때 존재한 데이터 버전과 일관성이 유지되도록 지정

SERIALIZABLE
- 가장 엄격한 격리수준. 트랜잭션이 완료될 때까지 다른 사용자는 그 영역에 해당되는 데이터에 대한 수정 및 입력이 불가능

트랜잭션의 4가지 특징 (ACID)

Lock Escalation
- 로우 레벨 Lock 이 페이지, 익스텐트, 테이블 레벨 Lock 으로 점점 확장되는 것.
- 오라클은 로우 속성으로 Lock 을 관리하므로, Lock Escalation 이 발생하지 않는다.



동시성
- DML 수행 시, ROW 단위로 배타적 Lock 이 걸린다.

오라클
- DML 문이 실행되는 동안 해당 ROW 는 update/delete 불가하다. select 는 가능.

MS-SQL
- DML 문이 실행되는 동안 해당 ROW 는 select/update/delete 불가하다.
- 제약조건이나 unique 인덱스 설정이 없으면, insert 하는 동안에 다른 insert 도 가능하다.
- insert 하는 동안에, 다른 select 절이 full scan 하게 되면, select 도 동시에 실행이 불가능하다. => index range 스캔을 하게 되면 select 가능.


TX Lock (Transaction Lock)
- 행 수준(Row-level) 잠금

TM Lock(Table Lock)
- 테이블 잠금, 테이블에 대한 구조적 변경(DDL) 이나, DML(데이터 조작 언어) 작업 시 데이터 무결성을 보장
- SELECT for UPDATE 문 에서도 TM Lock 이 설정된다.


이상현상

Dirty Read
- 다른 트랜잭션이 변경중인 데이터를 읽어서 사용했는데. 다른 트랜잭션이 rollback 되는 경우.

Non-Repeatable Read
- 한 트랜잭션 내에서 값을 두번이상 읽어서 사용하는 중간에 다른 트랜잭션이 해당값을 변경(update / delete) 해서, 값이 달라지는 경우.

Phantom Read
- 한 트랜잭션 내에서 일정 범위의 값을 두번이상 읽어서 사용하는 중간에 다른 트랜잭션이 새로운 값을 추가(insert) 함으로써, 처음 읽을때 없던 값이 나중에 생긴 경우.



비관적 동시성 제어
- 데이터를 읽는 시점부터 Lock 을 설정한다.

낙관적 동시성 제어
- 데이터를 읽는 시점에 Lock 을 설정하지 않는다.
- 다만, 읽은 데이터를 다시 읽거나, 수정하는 시점에 반드시 변경 여부를 확인한다.


SELECT FOR UPDATE
- 해당 행을 읽으면서, 하단에서 변경하기 위해 lock 을 건다.
- Lock 이 걸린 행을 만나면, Lock 이 해제될 때까지 무작정 기다린다.

SELECT FOR UPDATE WAIT n
- Lock 이 걸린 행을 만나면, n 초간 기다리고, 해제되지 않으면 SELECT 문 전체를 종료한다. (Exception)

SELECT FOR UPDATE NOWAIT
- Lock 이 걸린 행을 만나면, 즉시 SELECT 문 전체를 종료한다. (Exception)

snapshot too old 에러
- Table Full Scan 및 해시 조인을 사용하면 에러를 줄일 수 있다.
- 데이터 조회시 Order by 를 사용하면 에러를 줄일 수 있다.
- 튜닝을 통해 줄일수는 있어도 완전히 해소하기는 어렵다.
- Undo 영역의 크기를 증가시키고, commit 을 자주 수행하지 말아야 한다.

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.
 
 
3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

7. Lock과 트랜잭션 동시성 제어

비용기반 옵티마이저 (CBO)
- 통계정보 : 데이터양, 컬럼값의 수, 컬럼값 분포, 인덱스 높이, 클러스터링 팩터, 최대값, 최소값, NULL개수, 히스토그램 등
- 시스템 통계정보 : CPU속도, Single Block I/O 속도, Multi Block I/O 속도, Multi Block 개수 등
- 통계정보를 사용해서 실행계획을 세운다.
- 실행계획 선택에 대한 내부 규칙이 있다.
- 효율적이다.
- 버퍼 캐싱 효과는 고려하지 않는다. (디스크 I/O 를 기준으로 한다.)

규칙기반 옵티마이저 (RBO)
- 통계정보를 전혀 사용하지 않고, 단순히 규칙에만 의존한다.
- 대량 데이터 처리에 적합하지 않다.
- 인덱스 구조, 연산자, 조건절의 형태로 우선순위 규칙에 따라 최적화 한다.
- 인덱스가 있으면 무조건 인덱스를 사용한다. : Full Table Scan 이 우선순위가 가장 낮다.
- 부등호보다 between 이 우선순위가 높다.

옵티마이저 서브엔진
- Query Transformer : 쿼리 변환 과정
- Estimator : 예상 비용 산정
- Plan Generator : 실행계획 후보군 생성

스스로 학습하는 옵티마이저 (Self Learning Optimizer)
- Adaptive Cursor Sharing : 바인드변수에 따라 실행계획을 선택적으로 사용
- Statistics Feedback : 실행계획의 로우수와 실제 로우수가 다를때, 다음번에 이전 로우수에 따른 실행계획이 수립되도록 함
- Adaptive Plans : 런타임에 실행계획을 변경


최적화

전체 처리속도 최적화 (ALL_ROWS)
- 전체를 읽기 때문에 시스템리소스(I/O, CPU, 메모리) 를 적게 사용하는 실행계획을 선택

최초 응답속도 최적화 (FIRST_ROWS, FIRST_ROWS_N)
- 응답속도가 빠른 실행계획을 선택
- 2-Tier 클라이언트/서버 구조 : 커서를 오픈해서 데이터를 Fetch 하다가 중간에 멈출 수 있는 기능
- 인덱스를 많이 선택하고, 해시조인보다 NL 조인을 더 많이 선택한다.

SQL 최적화 과정
- 테이블, 컬럼, 인덱스 정보, 각각 통계 정보를 사용한다.
- 시스템 통계정보(CPU 속도 등) 을 사용한다.
- 옵티마이저 관련 파라미터 사용
- 통계 정보를 사용한다. 통계 정보를 저장하지는 않는다.

SQL 파싱과 최적화 순서
1. Parser : SQL 개별 구성요소를 분석해서 파싱트리를 만든다.
2. Query Transformer : SQL 을 일반적이고 표준적인 형태로 변환
3. Plan Generator & Estimator : 실행 후보군을 도출하고, 비용을 계산
4. Row-Source Generator : SQL 엔진이 실행할 수 있는 프로시저 형태로 포매팅


SQL 파싱
- 소프트파싱 : SQL 이 라이브러리 캐시에 존재하면 이를 사용하여 실행
- 하드 파싱 : SQL 이 라이브러리 캐시에 존재하지 않으면 최적화 및 로우 소스 생성 단계까지 모두 거쳐서 실행

선택도(Selectivity)
- 조건절에 의해 선택될 것으로 예상되는 레코드 비중 : 1 / NDV

카디널리티(Cardinality)
- 조건절에 의해 선택될 것으로 예상되는 레코드 수 : 전체레코드수 * 선택도

I/O 비용 모델의 비용(Cost)
- 예상되는 디스크 I/O Call 횟수

CPU 비용 모델의 비용(Cost)
- 예상소요시간을 Single Block I/O 횟수로 환산한 값


서브쿼리
- 하나의 SQL 문장에서 괄호로 묶은 별도의 쿼리 블록

인라인 뷰
- FROM 절에 나오는 서브쿼리로 레코드를  선택하는 쿼리다.

중첩된 서브쿼리
- WHERE 절이나 HAVING 절과 같은 조건절에서 사용되는 서브쿼리로, 메인 쿼리의 데이터를 필터링하거나 비교할 때 사용

스칼라 서브쿼리
- 주로 SELECT 절이나 다른 쿼리의 일부로서 단일 값을 반환하는 서브쿼리

no_unnest
- 중첩된 서브쿼리를 각각 그대로 FILTER 방식으로 처리한다.
- 서브쿼리의 결과가 작을 때 유리하다. : 캐싱
- 조건절의 스칼라 서브쿼리 : 실행계획에 FILTER 로 나타나게 된다.

unnest
- 중첩된 서브쿼리를 풀어낸다.
- 조건절의 스칼라 서브쿼리 : 실행계획에 NL 조인으로 나타나게 된다.
- unnest 를 사용하게 되면, rownum 조건을 제거해야 한다.

use_concat
- OR 조건을 union all 로 변환해준다.

no_expand
- OR 조건을 union all 로 변환하지 않고 그대로 수행하도록 한다.


merge
- 뷰머징 : 인라인뷰를 해체하여 상위 쿼리로 올린다.

no_merge
- 인라인뷰를 해체하지 않고 그대로 수행

push_subq
- 일반적으로, 조건절의 FILTER 조건인 서브쿼리는 제일 마지막에 실행된다.
- push_subq 를 사용하면, FILTER 조건인 서브쿼리를 먼저 실행되도록 한다.
- /*+ no_unnest push_subq */


push_pred
- 메인테이블과 인라인뷰의 JOIN 시에, 조인조건을 인라인뷰 안으로 유도한다. : 인라인뷰 성능향상
- /* no_merge push_pred */



바인드 변수
- 파라미터 드리븐 방식으로 SQL을 작성하는 방법
- SQL 의 재사용 가능
- 바인드 변수 사용 시 옵티마이저는 균등 분포 가정하고 비용을 계산한다.


애플리케이션 커서 캐싱
- Parse Call을 발생시키지 않고 SQL을 반복 수행하는 기능
- Parse Call이 최초 한번만 발생하고 이후로는 발생하지 않는다.

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.
 
 
3과목. SQL 고급활용 및 튜닝
1. SQL 수행 구조

2. SQL 분석 도구

3. 인덱스 튜닝

4. 조인 튜닝

5. SQL 옵티마이저

6. 고급 SQL 튜닝

7. Lock과 트랜잭션 동시성 제어

NL(Nested-Loop) 조인
- 중첩된 Loop 로 조인을 하는 구조이다.

- 랜덤 액세스 위주의 조인 방식 : 대량 데이터에서 불리
- 소량데이터 조인시 유리
- DB버퍼캐시에서 읽으므로, 블록에 래치 획득 및 체인 스캔 과정을 거친다. : 대량 데이터에서 불리
- 인덱스를 이용한 조인으로, 인덱스 구성 전략에 의해 성능이 크게 달라진다.
- 후행 테이블의 인덱스 효율성이 중요하다.
- 소량 데이터에 유리, OLTP 시스템에 적합
- 한 레코드씩 순차적으로 진행 : "부분범위 처리"가 가능
- 조인 대상 레코드가 많아도, ArraySize 에 해당하는 최초 n 건을 빠르게 출력 가능


소트머지조인
- 실시간으로 인덱스를 생성한다.
- 첫번째 집합은 조인컬럼에 인덱스가 있으면 인덱스를 사용하고, 없으면 PGA 영역에 저장한다.
- 두번째 집합은 정렬해서 PGA 영역에 저장한다.
- 조인조건이 = 이 아닐때, 조인조건이 없을때도 사용가능하다.
- 버퍼영역이 아닌 PGA 영역에서 조회하므로, 대량 데이터에서 NL 조인보다 빠르다. : 래치 획득 과정이 없어서

Hash 조인
- 작은 집합을 읽어서 PGA 에 해시맵으로 저장한다.
- 해시맵을 사용하여 탐색하기 때문에 조인컬럼에 인덱스가 없어도 상관이 없다. : 인덱스가 성능에 영향을 주지 않음
- 소트머지조인보다 Temp테이블스페이스 사용량이 적다.
- 해시키로 조회하므로 조인 조건이 = 일때만 사용가능하다.
- 양쪽 모두 대량 데이터에서 NL 조인보다 빠르다.
- 수행빈도가 낮고, 쿼리 수행시간이 오래 걸리는 대량 데이터 조인시 유리하다.
- DW/OLAP / Batch 프로그램에 사용.

join 힌트

Oracle

MS-SQL

스칼라 서브 쿼리

- 스칼라 서브 쿼리는 단일 값만을 반환해야 한다. (스칼라)
- 오라클에서는 서브쿼리 입출력값을 내부캐시에 저장해 두기 때문에 성능향상에 좋다.
- 서브쿼리의 효율은 코드명이나 상품명을 변환시 사용 : 카디널러티가 적은 테이블에 사용하면 좋다.

스칼라 서브쿼리 Execution Plan
- 서브쿼리가 위에 나오고, 메인쿼리가 아래에 나온다.


부분범위처리
- 조건을 만족하는 전체범위를 처리하는 것이 아니라 일단 운반단위(Array Size) 까지만 처리 하여 추출하는 처리방식
- TOP n / ROWNUM 사용 등
- 인덱스 사용이 가능하도록 조건절을 구사하고, 조인은 NL위주로 처리하고, Order By절이 있어도 소트연산을 생략할 수 있도록 인덱스를 구성 : index 의 2번째 컬럼부터 order by 컬럼을 추가
- 페이징 처리 (게시판 등)

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.

3과목. SQL 고급활용 및 튜닝

1. SQL 수행 구조
2. SQL 분석 도구
3. 인덱스 튜닝
4. 조인 튜닝
5. SQL 옵티마이저
6. 고급 SQL 튜닝
7. Lock과 트랜잭션 동시성 제어

B*Tree 인덱스 (Balanced Tree) 구조
- 브랜치 블록의 각 로우는 하위블록에 대한 주소값을 갖는다.
- 리프 블록의 키 값은 테이블 로우의 키 값과 같다.
- 리프 블록 끼리는 이중연결리스트(double linked list) 구조이다.

인덱스 ROWID
- (오브젝트번호 + ) 데이터파일번호 + 블록번호 + 블록내로우번호
- 데이터파일 상의 블록 위치를 가리킨다. 논리적인 주소 (물리적인 주소는 아니다.)
- 버퍼 캐시에서 찾을때는 해시 알고리즘을 이용한다.


인덱스 클러스터링 팩터
- 인덱스에 저장된 순서와 테이블에 저장된 데이터의 저장 순서가 얼마나 일치하는지를 나타내는 값 
- 최대값은 인덱스에 저장된 항목의 개수 
- 최소값은 테이블 블록의 개수

테이블 블록 수에 가까울수록 좋고, 테이블 레코드 수에 가까울수록 나쁘다.

인덱스 클러스터링 팩터가 좋으면, 테이블 액세스 단계에서 읽어들이는 블록(데이터) 가 줄어든다.
인덱스 리빌드 해도 클러스터링 팩터가 좋아지지 않는다. 테이블을 재생성 해야 한다.




인덱스에는 구성 컬럼값이 모두 NULL 인 값은 저장하지 않는다.


IOT (index organizaion table)
- Table Random 엑세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성
- PK 순으로 정렬상태를 유지한다.
- IOT 테이블의 일반 인덱스에는 PK 컬럼도 함께 저장된다.

- MS-SQL 의 Clustered Index 와 유사한 개념


인덱스 스캔 방식

Index Range Scan
- 인덱스 루트에서 리프 블록까지 수직적 탐색을 하고, 필요한 범위만큼 수평적 탐색하는 스캔 방식
- 인덱스 선두컬럼이 조건절에 있어야 한다.

Index Unique Scan
- 수직적 탐색으로만 데이터를 찾는 스캔 방식
- 인덱스 컬럼이 모두 조건절에 있는 경우( = 조건)

Index Skip Scan
- 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해서 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 액세스
- 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식
- 조건절에 빠진 선행 인덱스 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value 개수가 많을 때 유용
(선행컬럼이 datetime 일때는 불리, 날짜값(년월일)만 있을때 유리)

- 힌트 : index_ss
- 선두 컬럼이 부등호, between, like 같은 범위 조건일때도 사용 가능
- 선두 컬럼이 in ( list ) 조건일때는 사용 불가

Index Full Scan
- 수직적 탐색 없이 인덱스 리프 블록 처음부터 끝까지 수평적으로 탐색하는 방식
- 인덱스 힌트 사용하고, 해당 인덱스의 선두컬럼이 조건절에 없는 경우 사용된다.
- 인덱스 필터 조건을 만족하는 데이터가 적어야 효과적이다.

Index Fast Full Scan
- 논리적인 인덱스 트리 구조를 무시하고, 인덱스 세그먼트 전체를 Multiblock I/O 방식으로 스캔
- 물리적으로 디스크에 저장된 순서대로 인덱스 리프 블록을 읽음.
- 결과 레코드의 순서는 실제 데이터 순서와 다르게 나옴
- 힌트 : index_ffs

Index Range Scan Descending
- Index Range Scan과 기본적으로 동일한 스캔, 인덱스를 뒤에서부터 앞쪽으로 스캔
- 힌트 : index_desc


인덱스 컬럼의 가공
- 인덱스 컬럼이 가공되면, index range scan 이 불가해 진다.
- 부정형 비교는 index range scan 이 불가하다.
- is not null 조건은 index range scan 이 불가하다.

묵시적 형변환

문자와 숫자를 비교하는 경우, 자동으로 숫자로 형변환이 일어난다.

테이블 Random 액세스

인덱스 Leaf 노드에는 ROWID 가 있어, 인덱스에 없는 컬럼을 참조시,
테이블 ROWID 로 Random 액세스를 하게 된다.

실행계획에는 아래와 같이 표시된다.

테이블 랜덤 액세스가 많아지면, 전체 테이블 스캔(Full Table Scan) 보다 느려질 수 있다.
(인덱스 손익 분기점)



배치 I/O
- 테이블 랜덤 액세스시, 버퍼에서 블록을 찾지 못하면, 디스크에서 읽게 된다.
이때, 매번 디스크에서 읽지 않고, 모았다가, 일괄로 찾아오는 방법으로 I/O 효율을 높이는 방법이다.

배치 I/O 가 수행되면, 랜덤 액세스 방식이 아래처럼 변경된다.

배치 I/O 가 수행되면, 버퍼에 없는 블록끼리 모아서 처리 되므로, 결과집합의 정렬 순서가 바뀌게 되고,

실행결과에 SORT ORDER BY 가 나타나면, 부분범위 처리가 불가능해 진다.




인덱스 튜닝

col1 이 인덱스 선두 컬럼일 때, in (list) 방식이 유리하다.

col2 가 인덱스 후행 컬럼일 때는 in(list) 보다 filter 방식이 유리하다.

선두컬럼 "or" 조건은 union all 로 처리하는게 유리하다.

후행컬럼 or 조건은 union all 로 처리하는게 불리하다.

LIKE 검색

like :val || '%'

시작값으로 조회할때만 index range scan 이 가능하다.
null 허용 컬럼에서 null 값을 찾을때는 like '%' 로 변환되어, null 인 값을 못 찾게 된다.
숫자형 컬럼일때 또는 가변 컬럼일때..   123 인 값을 찾고자 해도, 앞에 123으로 시작하는 모든 데이터를 찾게 된다. (결과의 오류)
index 선두 컬럼에 대해 like 검색시 null 값으로 조회하면, 모든 index 를 찾아야 하므로, 비효율이 발생한다.



소트연산 생략

실행계획에 ORDER BY 가 나타나야 하지만, 인덱스에 있으면, ORDER BY 가 생략된다.
인덱스에 2번째부터 순서대로 order by 컬럼이 나와야 한다.

인덱스 구성 : col1 + col2 + col3

소트 연산이 생략되면, 부분범위 처리가 가능해진다.





MS-SQL 인덱스 힌트

PK 인덱스 사용, PK(Clustered Index) 가 없는 테이블은 테이블 풀 스캔
select * from tablename with(index(0))

PK 인덱스 사용, PK(Clustered Index) 가 없는 테이블은 오류
select * from tablename with(index(1))

인덱스명 사용
select * from tablename with(index=table_ix1)
select * from tablename with(index(table_ix1))

테이블 풀 스캔
select *
from
where ...
option( forcescan )

select *
from tablename
where ...
option( table hint( tablename, index(table_ix1) ) )

select *
from tablename with ( forceseek(table_ix1 (col1) ) )

SQLD / SQLP 공통 : 제1과목은 데이터 모델링의 이해 이다.

1과목. 데이터 모델링의 이해

- 데이터 모델링의 이해
- 데이터 모델과 SQL

엔터티
- 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적, 영속적으로 존재하는 단위
- 인스턴스의 집합

속성
- 엔터티에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위
- 엔터티에 속하는 개체들의 특성
- 엔터티를 설명하고 인스턴스의 구성요소가 된다.
- 하나의 속성은 하나의 속성값을 가진다.

관계
- 엔터티와 엔터티 사이의 연결 관계성
- 두개의 엔터티로부터 인스턴스를 연관짓는 행위
- 인스턴스 간에 논리적인 연관관계로 존재 또는 행위로서 연관성이 부여된 상태



도메인
- 속성이 가질 수 있는 값의 범위
- 엔터티 내에서 속성에 대한 데이터 타입, 크기, 제약사항을 지정하는 것.



엔터티 분류

유형무형에 따른 분류

발생시점에 의한 분류

스키마
외부 스키마 : 데이터베이스의 사용자 관점
개념 스키마 : 통합 관점, 데이터베이스 관리자 관점
내부 스키마 : 물리적 관점, 시스템 설계자 관점


관계(Relationship) 표기법
- 관계명
- 관계차수 : 1:1, 1:N, N:N
- 관계선택사양 : 필수, 옵션

식별자
- 엔터티를 대표하는 속성으로, 인스턴스를 구분할 수 있다.

주식별자/보조식별자
- 주식별자 : 유일성, 최소성, 존재성, 불변성을 만적하는 대표 식별자
- 보조식별자 : 유일성은 만족하지만 대표성을 만족하지 못하는 식별자

내부식별자/외부식별자
- 내부식별자: 엔터티 내부에서 스스로 생성되는 식별자
- 외부식별자: 다른 엔터티와의 관계로 생성되는 식별자

단일식별자/복합식별자
- 단일식별자 : 하나의 속성
- 복합식별자 : 2개 이상의 복합 속성

본질식별자/인조식별자
- 본질식별자 : 비지니스 프로세스에서 만들어지는 식별자
- 인조식별자 : 인위적으로 만들어지는 식별자




데이터베이스 모델링의 특징
- 추상화, 단순화, 명확화

모델링의 3단계
- 개념적 모델링 : 추상적, 업무 중심적, 포괄적인 수준의 모델링
- 논리적 모델링 : 세부속성, 식별자, 관계를 표현, 정규화
- 물리적 모델링 : 데이터베이스 성능을 고려한 설계


정규화
- 데이터의 일관성, 유연성, 중복을 제거하고 데이터를 분해를 하는 과정
- 이상현상을 제거하는 설계 기법이다.

제1정규화
- 속성의 원자성 확보 : 속성은 하나의 속성값을 가져야 한다.
- 기본키(PK) 설정

제2정규화
- 기본키 중에 특정 컬럼에만 종속된 경우, 부분 함수 종속성 제거

제3정규화
- 이행 함수 종속성 제거


이상현상

반정규화
- 데이터베이스의 성능 향상을 위해 데이터의 중복을 허용하거나, 조인을 줄이는 목적으로 하는 성능 향상 방법


 
분산데이터베이스
- 병렬처리로 빠른 응답이 가능, 용량확 장이 쉬움
- 보안이나 무결성이 안좋다. 여러 네트워크에 분산되어 관리와 통제가 어렵다.




트랜잭션의 4가지 특징 (ACID)

데이터베이스가 오래되면, 인덱스에도 조각화(Fragmentation)가 되고,
어떨때는 쿼리플랜으로 보면, 인덱스가 선택되지 않는 경우도 있고,
이래저래 데이터베이스가 좀 느려졌다 싶을때,

가끔 인덱스를 REBUILD 해주면, 속도가 빨라지곤한다.
데이터 양에 따라 시간이 많이 걸리기 때문에,
사용자가 없는 새벽이나 유휴시간에 작업해야 한다.

데이터베이스 안에 많은 양의 테이블이 있기 때문에 일일이, 테이블을 지정해서 해주면,
노가다가 되기 때문에 자동화해서, 한번의 실행으로 모든 테이블의 인덱스를 REBUILD 해주는 방법이다.

1.
데이터베이스 안에 모든 테이블 목록을 가져오는 쿼리다.

SELECT '[' + table_catalog + '].[' + table_schema + '].[' + table_name + ']' as tableName
FROM DB명.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE' AND table_name not like 'sys%'

2.
이를 커서를 이용해서, 모든 테이블에 대해 아래의 인덱스 리빌드 쿼리를 실행할 것이다.

ALTER INDEX ALL ON [DB명].[dbo].[TABLE명] REBUILD WITH ( fillfactor = 90)

아래는 전체 풀 소스 이며, 커서를 통해, 모든 테이블을 처리하며,
동적쿼리문을 작성해서 실행한다.

DECLARE @db_nm  varchar(500) -- DB명
DECLARE @table_nm varchar(1000) -- 테이블명
DECLARE @cmd  nvarchar(4000) -- 동적쿼리
DECLARE @FillFactor int  -- 인덱스 채우기비율

SET @FillFactor = 90   -- 인덱스의 90%를 채운다.

SET @db_nm = 'nv_blog'

-- DB에서 테이블목록을 불러서 커서에 저장 (sys로 시작하는 테이블은 제외)
-- 테이블명 구조 : [DB명].[dbo].[TABLE명]
SET @cmd = 'DECLARE curTB cursor for SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName
FROM ' + @db_nm + '.INFORMATION_SCHEMA.TABLES
WHERE table_type = ''BASE TABLE'' AND table_name not like ''sys%''';

EXEC (@cmd)

-- TABLE 커서 오픈
OPEN curTB

FETCH NEXT FROM curTB INTO @table_nm
WHILE @@fetch_status = 0
begin
 -- 테이블 별 인덱스를 리빌드함
 SET @cmd = 'ALTER INDEX ALL ON ' + @table_nm + ' REBUILD WITH ( fillfactor = ' + convert(varchar(3), @FillFactor ) + ')';

 print 'Work - ' + @cmd

 EXEC (@cmd)

 print 'Done - ' + @cmd

 FETCH NEXT FROM curTB INTO @table_nm
end

-- TABLE 커서 종료
CLOSE curTB
DEALLOCATE curTB

PS. 이 쿼리는 MS-SQL 2012 이후 버전에서 실행되었습니다.

MS-SQL 의 데이터베이스 물리적 파일인 .MDF .LDF 파일의 용량이 갈수록 커지면서,
하드디스크 용량을 많이 차지 한다면, 아래와 같은 방법으로 용량을 줄여보자.

ALTER DATABASE DB명 SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(DB명_Data)
go
DBCC SHRINKFILE(DB명_Log)
go
ALTER DATABASE DB명 SET RECOVERY FULL
go

위의 쿼리로 MDF, LDF 파일의 용량을 모두 줄일수 있지만,
MDF 는 데이터 파일이라, 용량이 그다지 줄지는 않을것이다.
아래와 같이 Log 파일만 줄여도 상관이 없다.

ALTER DATABASE DB명 SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(DB명_Log)
go
ALTER DATABASE DB명 SET RECOVERY FULL
go

데이터 파일과 로그파일의 논리적 이름을 적어줘야 하는데, 어떤걸로 되어 있는지 모를경우,
데이터베이스 속성에서 아래와 같이 논리적 이름을 확인할 수 있다.

또는,
sp_helpdb DB명

명령으로도 쉽게 확인이 가능하다.

쿼리의 실행전,실행후 용량 비교이다.

PS. 이 쿼리는 MS-SQL 2012 이후 버전에서 실행되었습니다.