[정리] PARTITIONING 기법
RANGE PARTITION(범위 지정)
- 대상 테이블이 날짜/숫자값으로 분리가 가능하고 각 영역별로 트랜잭션이 분리 되면 RANGE PARTITION을 적용 가능하다.
데이터 보관 주기에 따라 테이블에 데이터를 쉽게 지우는 것(파티션 테이블 DROP)이 가능하므로 **데이터 보관 주기에 따른 테이블 관리가 용이하다.
LIST PARTITION(특정 값 지정)
- 대용량의 데이터를 특정 값에 따라 분리, 저장하는 기법이다.
- 핵심적인 코드값 등으로 기본키가 구성되어 있고, 대량의 데이터가 있는 곳에서 LIST PARTITION을 이용 가능하다.
- RANGE PARTITION과 같이 데이터 보관 주기에 따라 쉽게 삭제하는 기능을 제공하지 않는다.
HASH PARTITION(해시 적용)
- 지정된 HASH 조건에 따라 해싱 알고리즘이 적용되어 테이블이 분리된다.
- 설계자는 테이블에 데이터가 정확하게 어떻게 들어갔는지 알 수 없다.
- 성능 향상을 위해 사용된다.
- RANGE PARTITION과 같이 데이터 보관 주기에 따라 쉽게 삭제하는 기능을 제공한다.
[정리] 속성의 분류
특성에 따른 분류
- 기본 속성: 업무 분석을 통해 바로 정의한 속성
- 설계 속성: 원래 업무상 존재하지는 않지만, 설계하면서 도출해내는 속성
- 파생 속성: 다른 속성으로부터 계산되거나 변형이 되어 생성되는 속성
엔터티 구성 방식에 따른 분류
- 기본키 속성: 엔터티를 식별할 수 있는 속성
- 외래키 속성: 다른 엔터티와의 관계에서 포함된 속성
- 일반 속성: 엔터티에 포함되어 있고 기본키, 외래키에 포함되지 않는 특성.
[정리]ERD 표기법
바커 표기법
- Optional 관계를 실선과 점선으로 표시
- 식별자 앞에 #기호를 표시
IE 표기법
- Optional 관계를 O와 |로 표시
[정리] 식별자와 비식별자 관계
- 식별자 관계에서 자식 엔터티의 기본기가 부모 엔터티에 종속적으로 구성되고 비식별자 관계에서 자식 엔터티의 기본기가 부모 엔터티에 독립적으로 구성된다.
- 식별자는 강한 관계, 비식별자는 약한 관계를 나타낸다.
- 식별자 관계는 상속받은 주식별자 속성을 타 엔터티에 이전이 필요하고, 비식별자 관계는 차단이 필요하다.
- 식별자 관계는 실선으로, 비식별자 관계는 점선으로 표시한다.
| 항목 | 식별자 관계 | 비식별자 관계 |
|---|---|---|
| 목적 | 강한 연결관계 표현 | 약한 연결관계 표현 |
| 자식 주식별자 영향 | 자식 주식별자의 구성에 포함됨 | 자식 일반속성에 포함됨 |
| 표기법 | 실선 표현 | 점선 표현 |
| 연결 고려사항 | - 반드시 부모 엔터티 종속 - 자식 주식별자 구성에 부모 주식별자 포함 필요 - 상속받은 주식별자 속성을 타 엔터티에 이전 필요 | - 약한 종속 관계 - 자식 주식별자 구성을 독립적으로 구성 - 자식 주식별자 구성에 부모 주식별자 부분 필요 - 상속받은 주식별자 속성을 타 엔터티에 차단 필요 - 부모쪽의 참여가 선택관계 |
[정리] 정규화 절차
| 정규화 절차 | 설명 |
|---|---|
| 제1정규화 | - 속성(Attribute)의 원자성을 확보한다. - 속성의 중복값을 제거한다. - 기본키(Primary)를 설정한다. |
| 제2정규화 | - 기본키가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성을 제거(분해)한다. - 부분 함수 종속성이란, 기본키가 2개 이상인 칼럼으로 이루어진 경우에반 발생한다. 기본키가 1개이면 이 과정을 스킵한다. - 복합 인스턴스에 대해 각 인스턴스의 종속적 중복을 삭제한다. |
| 제3정규화 | - 기본키를 제외한 칼럼 간에 종속성을 제거한다. - 즉, 이행 함수 종속성을 제거한다. - 일반 속성의 종속성을 제거한다. |
| BCNF | - 기본키를 제외하고 후보키가 있는 경우, 후보키가 기본키를 종속시키면 분해한다. - BCNF는 복수의 후보키가 있고, 후보키들이 복합 속성이어야 하며, 서로 중첩되어야 한다. |
| 제4정규화 | 여러 칼럼들이 하나의 칼럼을 종속시키는 경우, 분해하여 다중값 종속성을 제거한다. - 다치 종속성을 제거한다. |
| 제5정규화 | 조인에 의해서 종속성이 발생되는 경우 분해한다. |
[정리] 반정규화
중복 관계 추가
- 반정규화로 데이터를 처리할 때, 여러 경로에 걸쳐서 발생하는 조인 연산에 의한 성능 저하를 예방하기 위해서는, 추가적인 관계를 맺는 관계의 반정규화 기법을 사용하는데, 그 중 하나가 중복 관계 추가이다.
테이블 수평 분할
- 파티션 테이블의 장점
- 데이터 조회 시에 엑세스(Access) 범위가 줄어들기 때문에 성능이 향상된다.
- 데이터가 분할되어 있기 때문에 I/O의 성능이 향상된다.
- 각 파티션을 독립적으로 백업 및 복구 할 수 있다.
[정리] 서브쿼리
- 메인쿼리에서는 서브쿼리의 결과 칼럼을 사용할 수 없다.
- 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
- 단, 서브쿼리 중에서 INLINE VIEW의 칼럼은 메인 쿼리에서도 사용 가능하다.
- 서브쿼리에서는 ORDER BY를 사용하지 못한다.
- 서브쿼리는 단일행(Single Row) 또는 복수행(Multi Row) 비교 연산자와 함께 사용할 수 있다.
- 메인쿼리 레벨이 1이고 서브쿼리가 M이면 항상 메인쿼리 레벨인 1로 결과 집합이 생성된다.
- 서브쿼리는 괄호로 감싸서 사용한다.
- 서브쿼리는 비교 연산자와 함께 사용 가능하다.
- 서브쿼리는 SELECT절, FROM절, WHERE절 등에서 사용 가능하다.
[정리] 숫자형 함수
- ABS: 절댓값을 계산하는 함수이다.
- ROUND: 지정된 자릿수까지 반올림을 하는 함수이다.
- CEIL: 올림을 하는 함수이다.
- FLOOR: 내림을 하는 함수이다.
- TRUNC: 지정된 자릿수 이후의 값을 절삭하는 함수이다.
[정리] 오라클에서 날짜의 연산
- 특정 날짜에 1을 더하면 하루를 더한 결과와 같다.
- 1/24 = 1시간
- 1/24/60 = 1분
- 1/24/(60/10) = 10분
[정리] SQL문 실행계획 읽는 법
- SQL문 실행 계획은 우선 안에서 밖으로 읽으면서, 같은 레벨에서는 위에서 아래의 순서로 읽는다.
- 예시: 3, 4, 2, 6, 5, 1
1. Nested Loop
2. Hash Join
3. Table Access (Full) Tab1
4. Table Access (Full) Tab2
5. Table Access (By rowid) Tab3
6. Index (Unique Scan) PK_Tab3[정리] 명시적 커서
커서란?
- 행 단위 작업을 추가로 제어해야 할 경우 사용되는 기능이다.
- 행 단위 작업이란 예를들면 특정 테이블에서 SELECT한 Email 별로 작업을 해야 하는 경우를 말한다.
- 즉 Email 별로 특정 값을 변경 시킬 때,한번에 처리하는 쿼리를 만들 수 있다.
커서의 종류
- 명시적(Explicit) 커서: 사용자가 선언해서 생성 후 사용하는 SQL 커서. 주로 여러개의 행을 처리하고자 할 경우 사용.
- 묵시적(Implicit) 커서: 오라클에서 자동으로 선언해주는 SQL 커서. **사용자는 생성 유무를 알 수 없다.
명시적 커서
- 커서 선언:
- 사용할 커서에 이름을 부여하고 이 커서에 대한 쿼리를 작성.
CURSOR 커서명[(매개변수1, 매개변수2, ..)] IS SELECT 문장;
- 커서 열기
- 사용할 커서를 오픈한다.
OPEN 커서명[(매개변수1, 매개변수2, ...)];
- 패치 단계에서 커서 사용
- 결과 집합의 행(ROW) 수는 보통 1개 이상이므로 각각의 개별 행(ROW)에 접근하기 위해서는 반복문을 사용한다.
LOOP FETCH 커서명 INTO 변수1, 변수2, ...; EXIT WHEN 커서명%NOTFOUND; END LOOP;
- 커서 닫기
- 커서 사용이 끝났으면 닫아준다.
CLOSE 커서명
[정리] TOP N 질의문
- SQL Server에서 상위 N개의 데이터를 출력할 때
TOP N질의문을 사용할 수 있는데, 이 때 N에 해당하는 값이 동일한 경우, 동일한 데이터를 함께 출력하는 WIT TIES 옵션을 ORDER BY절과 함께 사용한다.
[정리] Hash Join의 순서
- 선행 테이블에서 주어진 조건을 만족하는 행을 필터링한다.
- 1번에서 필터링한 행을 대상으로 선행 테이블의 조인키를 기준으로 해서 해시 함수를 적용하여, 해시 테이블을 생성한다.
- 1번, 2번 작업을 선행 테이블에서 조건을 만족하는 모든 행을 수행한다.
- 후행 테이블에서 주어진 조건을 만족하는 행을 필터링한다.
- 4번에서 필터링한 행을 대상으로 후행 테이블의 조인키를 기준으로 해시 함수를 적용하여, 선행테이블에서의 해시 함수 반환값과 같은 반환값을 반환하는 행을 찾는다.
[정리] 해쉬 조인
- 동등 조인에서만 사용할 수 있다.
- 데이터 건수가 적은 테이블을 선행 테이블로 두는 것이 유리하다.
- 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있다.
- 자연 조인 시 드라이빙 집합 쪽으로 조인 액세스량이 많아 Random 액세스 부하가 심할 때 유리하다.
[정리] 인덱스
B-Tree Index
- 브랜치 블록과 리프 블록으로 구성되며, 브랜치 블록은 분기를 목적으로 하고, 리프 블록은 인덱스를 구성하는 칼럼의 값으로 정렬된다.
CLUSTERED Index
- 인덱스의 리프 데이터가 곧 데이터 페이지다.
BITMAP Index
- 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하는 구조이다.
- 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해 설계되었다.
[정리] ROLE
- DBMS 유저와 다양한 권한들사이에서 중개 역할을 수행하는 명령어이다.
- ROLE은 GRAND 명령어로 부여하고, REVOKE 명령어로 회수할 수 있다.
- 여러 사용자에게 꼭 다른 Role이 아니라 같은 Role도 부여할 수 있다.
- CREATE ROLE권한을 가진 유저에 의해 생성된다.
- 다양한 유저에게 다양한 권한을 효율적으로 관리하기 위한 '명령어'이다.X
- ROLE은 DBA 권한을 가진 유저에 의해서 생성된다.X
- 여러 사용자에게는 서로 다른 Role을 부여해야한다. X
[정리] 비절차적 데이터 조작어(DML)
- As-Is
- 비절차적 데이터 조작어(DML)는 사용자가 무슨(What) 데이터를 원하는 지만 명세한다.
- To-Be
- 비절차적 데이터 조작어(DML)는 사용자가 무슨(What) 데이터를 원하는 지만을 명세하지만, 절차적 데이터 조작어(Procedure DML)는 어떻게 (How) 데이터를 접근해야 하는지 명세한다. 절차적 데이터 조작어로는 PL/SQL(오라클), T-SQL(SQL 서버) 등이 있다.
[정리] ANSI SQL
- 조인 조건과 조회 조건이 분리되어야함.
- 조회 조건은 WHERE절로 분리되어야 한다.
- 조인 조건절(ON절)에 사용한 조건절은 조인 전 조건으로 작용하며, ON절 이후 WHERE 전에서 쓰인 조건절은 조인 후 조건절로 사용된다.
[정리] Oracle 계층형 질의
- START WITH절은 계층 구조의 시작점을 지정하는 구문이다.
- ORDER SIBLINGS BY절은 형제 노드 사이에서 정렬을 지정하는 구문이다.
- 순방향 전개란 부모 노드로부터 자식 노드 방향으로 전개하는 것을 말한다.
- 루트 노드의 LEVEL 값은 1이다.
[정리] Nested Loop Join
- 먼저 선행 테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다.
- 랜덤 방식으로 엑세스한다.
- 결과를 가능한 빨리 화면에 보여줘야 하는 온라인 프로그램에 적당하다.
