아래 포스팅은 자격검정시험 1,2 문제에 대한 개념정리를 기본으로 하였으며
추가된 버전은 pdf 로 정리하였습니다.
필요하신분 댓글로 메일 주시면 보내드릴게요~
시험들어가기전 봐야할 요약본
1과목 데이터 모델링의 이해
1장 데이터 모델링의 이해
2장 데이터 모델과 SQL
1절 정규화
제 1 정규형 : 모든 속성은 반드시 하나의 값을 가져야 한다.
제 1정규형은 각 컬럼에 원자값(atomic value)만 들어가야 한다는 규칙입니다. 즉, 컬럼에 여러 값이 들어가면 안 됩니다.
1NF 위반 데이터
학생번호 | 학생이름 | 전화번호
----------------------------
1 | 김민수 | 010-1234, 010-5678
2 | 이영희 | 010-9876
1NF 적용 후
학생번호 | 학생이름 | 전화번호
----------------------------
1 | 김민수 | 010-1234
1 | 김민수 | 010-5678
2 | 이영희 | 010-9876
제 2 정규형 : 모든 속성은 반드시 모든 기본키에 종속되어야 한다.
제 2정규형은 제 1정규형을 만족하면서, 부분 함수 종속이 없도록 해야 합니다. 즉, 기본 키의 일부가 아니라, 전체 기본 키에 의존해야 합니다.
"기본 키의 일부가 아닌 전체에 의존한다"는 말은 복합 키에서 모든 기본 키 요소에 대해 종속이 이루어져야 한다는 의미입니다. 즉, 기본 키의 일부만을 기준으로 값이 결정되면 안 되고, 전체가 기준이 되어야 한다는 것입니다. 이 규칙을 지키지 않으면 부분 함수 종속이 발생하고, 이를 제거하기 위해 정규화를 해야 합니다.
2NF 위반 데이터
주문번호(PK) | 상품번호(PK) | 상품명 | 수량
------------------------------
1001 | 001 | 사과 | 10
1001 | 002 | 배 | 5
1002 | 003 | 포도 | 8
여기서 상품명은 상품번호에만 의존하고, 주문번호와는 관계가 없습니다.
즉, 상품번호가 상품명을 결정하는데, 이 부분이 부분 함수 종속에 해당합니다.
2NF 적용 후
주문번호(PK) | 상품번호(PK) | 수량
-----------------------
1001 | 001 | 10
1001 | 002 | 5
1002 | 003 | 8
상품번호(PK) | 상품명
----------------
001 | 사과
002 | 배
003 | 포도
제 3 정규형 : 기본키가 아닌 모든 속성간에는 서로 종속될 수 없다.
제 3정규형은 제 2정규형을 만족하면서, 이행적 함수 종속이 없도록 해야 합니다. 즉, 비기본 키 속성이 다른 비기본 키 속성에 의존하지 않도록 해야 합니다.
이행적 함수 종속이란 A → B, B → C라는 관계가 있을 때, A → C라는 관계도 성립하는 상황을 말합니다. 이 경우, A가 B를 결정하고, B가 C를 결정하므로 A는 C를 간접적으로 결정하는 것입니다.
3NF 위반 데이터
학생번호(PK) | 학생이름 | 학과명 | 교수명
-----------------------------
1 | 김민수 | 컴퓨터 | 이홍수
2 | 이영희 | 수학 | 박지훈
여기서 학과명은 교수명에 의존하고 있습니다.
즉, 학과명을 알고 있으면 교수명도 알 수 있기 때문에, 이는 이행적 함수 종속에 해당합니다.
학과명은 교수명을 결정합니다. 즉, 학과명 → 교수명이라는 관계가 있습니다.
예를 들어, "컴퓨터" 학과의 교수는 항상 "이홍수"라는 정보는 학과명에 의존하는 교수명입니다.
이 경우, 이행적 함수 종속이 발생합니다.
왜냐하면, 학생번호 → 학과명, 그리고 학과명 → 교수명이라는 관계가 성립하므로,
학생번호 → 교수명도 성립하게 되기 때문입니다.
3NF 적용 후
학생번호(PK) | 학생이름 | 학과번호
------------------------
1 | 김민수 | 101
2 | 이영희 | 102
학과번호(PK) | 학과명 | 교수명
----------------------
101 | 컴퓨터 | 이홍수
102 | 수학 | 박지훈
2절 관계외 조인의 이해
3절 모델이 표햔하는 트랜잭션 이해
4절 null 속성이해
5절 본질식별자와 인조식별자
식별자는 대체 여부에 따라 본질식별자와 인조식별자로 나눌수 있다.
본질식별자:
업무에 의해 만들어진 식별자, 실제 데이터에서 자연스럽게 존재하는 유일한 속성을 사용하여 레코드를 식별하는 방식
예를 들어, 주민등록번호, 전화번호, 이메일 주소 등
인조식별자:
본질식별자가 복잡한 구성을 가지고 있으 인위적으로 생성한 식별자
실제 데이터에서 의미가 없는 값을 사용하여 레코드를 식별하는 키
2과목 SQL 기본과 활용
- 정규표현식
. 모든문자 일치 or 대체 문자 구분 \ 다음 문자는 일반문자로 취급 ^ 문자열 시작 $ 문자열 끝 ? 0 또는 1회 일치 * 0 또는그 이상 일치 + 1 또는그 이상 일치 {m} m회 일치 {m,} 최소 m회 일치 {,m} 최대 m회 일치 {m,n} 최소 m회, 최대 n회 일치
- pivot / unpivot
pivot
PIVOT은 행 데이터를 열 데이터로 변환하는 기능. 주로 집계 함수와 함께 사용
원본 데이터
학생번호 | 과목 | 성적
----------------------
1 | 수학 | 90
1 | 영어 | 80
2 | 수학 | 85
2 | 영어 | 95
각 학생별로 과목을 열로 표시
SELECT *
FROM (
SELECT 학생번호, 과목, 성적
FROM 성적
) AS SourceTable
PIVOT (
MAX(성적) FOR 과목 IN ([수학], [영어])
) AS PivotTable;
결과
학생번호 | 수학 | 영어
-------------------
1 | 90 | 80
2 | 85 | 95
unpivot
UNPIVOT은 열 데이터를 행 데이터로 변환하는 기능
원본 데이터
학생번호 | 수학 | 영어
---------------------
1 | 90 | 80
2 | 85 | 95
과목별 성적
SELECT 학생번호, 과목, 성적
FROM (
SELECT 학생번호, 수학, 영어
FROM 성적
) AS SourceTable
UNPIVOT (
성적 FOR 과목 IN ([수학], [영어])
) AS UnpivotTable;
결과
학생번호 | 과목 | 성적
---------------------
1 | 수학 | 90
1 | 영어 | 80
2 | 수학 | 85
2 | 영어 | 95
- 계층형 질의, 셀프조인
- SELECT FROM 테이블 WHERE 조건 START WITH 조건 --root 데이터 시작지점 지정 CONNECT BY [NOCYCLE] 조건 -- (fk) = prior (pk) 부모데이터에서 자식데이터로 전개 , (PK) = PRIOR (FK) 는 역방향 전개 [ORDER SIBILINGS BY 컬럼,컬럼 .. ]
- top n 쿼리
- 윈도우 함수 : 행과 행간의 비교, 연산
SELECT WINDOW_FUNCTION (ARGS) OVER [PARTITION BY 컬럼][ORDER BY 절][WINDOWING 절] FROM 테이블명;그룹 내 순위 함수 Rank
dense_rank
row_number동일한 값에 대해
1-2-2-4 (rank)
1-2-2-3 (dense_rank)
1-2-3-4 (row_number)그룹 내 집계 함수 sum, max, min, avg, count 그룹 내 행 순서 Fisrt_value, last_value
lag
lead
이전 몇번째 행 가져오기 (lag)
이후 몇번쨰 행 가져오기 (lead)
- 그룹 함수
roll up cube grouping set
3과목 SQL 고급 활용 및 튜닝
1장 sql 수행구조
1절 데이터베이스 아키텍처
- database : 디스크에 저장된 데이터 집합
- instance : sga 공유 메모리 영역과 액세스하는 프로세스 집합
undo 사용하는 목적 ( snap shot too old)
- transaction rollback
- transaction recovery
- read consistency
redo 매커니즘과 관련, redo 로그에 대한 설명
- media recovery
- cache recovery
- fast commit
메모리 캐시에 대한 설명
- db 버퍼캐시 - 테이블 블록, 인덱스 블록, undo 블록 캐싱
- 라이브러리 캐시- db 저장 함수/ 프로시저, 트리거 캐싱, sql
- 딕셔너리 캐시 - 테이블정보, 인덱스정보, 데이터파일 정보, 시퀀스 캐싱
- result 캐시 - 결과 집합 캐싱
2절 sql 처리 과정
sql 처리과정
- sql 파싱 : syntax 체크, semantic 체크
- sql 최적화 : 옵티마이저가 나타나는 단계, 딕셔너리에 수집한 오브젝트 통계, 시스템 통계 정보 활용
- 로우 소스 생성 : 실행경로를 실제 실행 가능한 코드 포맷팅 하는 단계
옵티마이저 힌트에 대한 것
- ms 는 힌트 오류가 나면 컴파일 에러가 발생
- , 는 힌트와 힌트 사이에는 쓰면 x 하지만 인자와 인자 사이엔 사용 가능
3절 데이터베이스 i/o 매커니즘
블록 단위로 i/o 하는 오퍼레이션
- 파일에 저장된 데이터 블록을 버퍼캐시에 적재
- 버퍼캐시에 데이터 블록을 읽고 쓸때
- 변경된 데이터 블록을 파일에 쓸때
sql 비교 ( * 와 컬럼 차이)
- 전체 데이터 * 와 컬럼만 가지고 갈때 블록단위 I/O 여서 읽는블록 갯수는 같다.
- 전송하는 bytes 는 다르다
- order by가 같이 있다고 하더라도 * 된건 temp에 소트공간을 더 많이 차지한다.
메모리 버퍼캐시 경유하는 블록 i/o 오퍼레이션
- direct path i/o를 제외한 모든 블록 i/o 오퍼레이션은 버퍼 캐시를 경유한다.
버퍼 캐시 히트율 계산 공식
- sql 트레이스 정보에서는 disk (물리적) query , current (논리적)
- (1 - 물리적io / 논리적 io) * 100
multi-block io : 캐시에서 찾지 못한 특정 블록을 읽으려고 io call 할때마다 그 블록과 인접한 블록을 한꺼번에 읽어 캐시에 적재하는 기능
- db file scattered read 대기 이벤트 발생
- 익스텐트 경계를 넘지 못한다.
2장 sql 분석 도구
1절 예상 실행계획
- 실행계획 확인하는 방법oracle : explain plan for (sql 쿼리) , dbms_xplan.display (null, null, ‘typical’);
- ms : set showplan_text on ;
2절 sql 트레이스
- ms : set statistics profile on
- 10046 트레이스에서 확인할 수 있는 정보 - 하드파싱 횟수 , cpu time, 실제 디스크 읽은 블록 수
Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Parse 1 0.000 0.002 0 0 0 0 Execute 1 0.000 0.000 0 0 0 0 Fetch 1 0.000 0.028 3 4 0 0 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 3 0.000 0.031 3 4 0 0
- sga 메모리에 트레이스 정보를 보려고 할 때gather_plan_staticstics 힌트 지정
-statistics_level all 로 설정
-_rowsource_execution_statistics 파라미터 true 설정
-위 힌트 사용
- dbms_xplan.display_cursor ( sql_id, child_number , ‘’); >> 트레이스 정보를 출력하기 전에 해야하는 것
- dbms_xplan.display_cursor 본 정보
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 45 |00:00:00.01 | 3 | | | |
|* 1 | COUNT STOPKEY | | 1 | | | | | 45 |00:00:00.01 | 3 | | | |
| 2 | VIEW | | 1 | 45 | 5985 | 4 (25)| 00:00:01 | 45 |00:00:00.01 | 3 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 45 | 3105 | 4 (25)| 00:00:01 | 45 |00:00:00.01 | 3 | 6144 | 6144 | 6144 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 45 | 3105 | 3 (0)| 00:00:01 | 45 |00:00:00.01 | 3 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 | | 1 (0)| 00:00:01 | 45 |00:00:00.01 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3절 응답 시간 분석
- sql 하드파싱과 관련된 대기 이벤트 : latch : shared pool
3장 인덱스 튜닝
1절 인덱스 기본 원리
ms : option 절을 활용하려면 힌트 사용 시에 option (table hint (테이블명, index(인덱스명)) 이렇게 사용
- index skip scan : in 조건이 액세스 조건일 때는 skip scan이 불가능하다.ex ) select * from 고객
- where 고객등급 in (’A’,’B’,’C’)
- and 생일 =’0326’
- or 조건에 대해서 index range scan이 가능한 sql을 찾기 위해선 union all로 풀리면서 액세스 조건에 인덱스 들이 있는 sql 들
2절 테이블 액세스 최소화
3절 인덱스 스캔 효율화
- 인덱스 스캔 과정 비효율 낮아야함
- 테이블 액세스 횟수 낮아야함
- null 허용 컬럼 옵션조건을 주었을 때 (예시 SQL - 고객id like :cust_id || ‘%’-고객id null like || ‘%’ 이건 공집합이다. 어차피 null 로 나오는건 없으니까 결과집합이 바뀌지 않음.
- -고객id가 문자형 컬럼이고 :cust_id 에 값을 입력하지 않으면 ‘모든’ 데이터를 스캔하는 일이 벌어짐
- 옵션조건에 nvl, decode문을 사용하면 union all 로 풀리게 된다.
- 옵션조건에 case문을 사용하면 union all 로 풀리지 않게 된다.
4절 인덱스 설계
- 인덱스 액세스 조건에 따라 인덱스 설계
- ‘=’ 조건이 없다면 in-list(union all) 로 풀리는 조건을 선두로 컬럼을 설계하고 그 이후는 필터 조건(선택도가 낮은)이 되도록 설계함.
- 소트 연산 생략 가능한 설계-’=’ 연산자로 사용한 조건절 컬럼 선정-’=’ 연산자가 아닌 조건절 컬럼은 분포 고려해 추가 여부 결정
- -order by 절에 기술한 컬럼 추가
- 부분범위 처리가능 하게 >> 소트 연산생략 가능하도록-in-list 도 ‘=’ 조건이라고 생각할 수 있는데 실행계획에 in-list iterator로 풀려야하며 그럴 경우 정렬 순서가 깨진다.
또한 범위검색 조건 뒤쪽에서 필터로 처리하는 것이 유리하다.
- 선분이력 테이블 인덱스 설계-시작일자와 종료일자 중 어떤것을 선두로 두겠는가?:최근 데이터를 조회할 경우 종료일자가 선두로
- :과거 데이터를 조회할 경우 시작일자가 선두로
4장 조인 튜닝
1절 NL조인
ms : from 절에 나열한 순서대로 조인하고자 할 때 option ( force order )
nl 조인: loop join
hash 조인 : hash join
sort merge 조인 : merge join
- Nested Loops 조인 특징: 랜덤 액세스 위주 조인 방식, 한 레코드씩 순차적으로 진행, oltp에 유리
- 오라클에서 나타나는 nl 조인 실행 계획 종류 [Prefetch, Batch I/O, Vector I/O이 기능이 활성화 되어있음을 나타냄]
# 일반 전통적인 NL 조인 실행계획 --------------------------------------------------------- | Id | Operation | Name | --------------------------------------------------------- | 0 |SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | TABLE ACCESS FULL | DEPARTMENTS | | 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | | 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | --------------------------------------------------------- # Prefetch기능이 적용되었음을 나타내는 NL 조인 실행계획 (inner table 이 위에 나타나는 형태) --------------------------------------------------------- | Id | Operation | Name | --------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | | 2 | NESTED LOOPS | | |* 3 | TABLE ACCESS FULL | DEPARTMENTS | |* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | --------------------------------------------------------- # 배치I/O 기능이 작동할 수 있음을 나타내는 실행계획 (nested loops가 2번 나타나는 형태) ---------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS | | | 2 | NESTED LOOPS | | |* 3 | TABLE ACCESS FULL | DEPARTMENTS | |* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | | 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | ----------------------------------------------------------
2절 소트머지 조인
- Sort Merge 조인 특징 : 스캔위주의 조인 방식, 인덱스 유무에 영향을 받지 않음, 버퍼캐시가 아닌 PGA에 저장, 래치 획득이 없음, 건건이가 아닌 일괄적으로 읽음
- 장점 : ‘=’ 조건이 아닐 때도 사용할 수 있고, 조인 조건이 없을 때도 사용할 수 있음
3절 해시조인
- 해시 조인 처리 과정-해시맵 생성 (build phase) : 작은 테이블 읽어서 해시맵 생성
- -해시맵 탐색 (probe phase) : 큰 테이블 스캔하면서 해시맵 탐색, 조인에 성공하면 클라이언트에 전송
4절 스칼라 서브쿼리
NL조인처럼 한 레코드씩 처리한다
조인에 실패할 경우 null 리턴
PGA에 캐싱하는 효과로 조인 부하 감소
5절 고급조인 기법
5장 SQL 옵티마이저
1절 SQL 옵티마이저 원리
- CBO가 실행계획 수립시 사용하는 정보-인덱스 높이-CPU속도 및 디스크 IO속도
- -중복을 제거한 컬럼 값의 수
- 선택도 1 / NDV (컬럼 값 종류 개수)
- 카디널리티 총 rows / NDV
- 컬럼 통계항목-중복 제거한 컬럼 값의 수-null 값을 가진 레코드 수
- -평균 컬럼 길이
- -최소값과 최대값
- 시스템 통계항목 : app 및 하드웨어 성능 특성-평균적인 single block 속도 / multi block 속도,개수
- -cpu속도
- 히스토그램 유형-도수분포-상위도수분포
- -하이브리드(도수분포 + 높이균형)
- -높이균형
- 인덱스를 이용한 테이블 액세스 비용 계산 시 통계항목-브랜치 레벨-클러스터링 팩터-유효 테이블 선택도
- -유효 인덱스 선택도
- -리프 블록수
2절 SQL 공유 및 재사용
- 바인드 변수를 사용하면 컬럼 히스토그램을 사용하지 못한다.
- 커서-묵시적 커서 : Declare 문 생략하고 dbms가 자동으로 처리-애플리케이션 커서 : 라이브러리에서 SQL 찾는 작업 생략하고 반복 수행하는 기능
- -명시적 커서 : Declare 문 정의
- cursor_sharing 파라미터-기본 값 EXACT
- -FORCE : 상수 값을 바인드 변수로 강제 변환해줌으로 하드파싱 부하를 줄여준다.
3절 쿼리변환
- 서브쿼리 Unnesting-unnest : 서브쿼리를 unnest하여 조인방식으로 유도
- -no_unnest : 서브쿼리를 그대로 둔 채 필터방식으로 유도
- 뷰 Merging-뷰머징이 불가능한 경우
- :집합(set) 연산자 (union,union all, intersect, minus):ROWNUM pseudo 절:분석 함수(Analytic Function)
- :select-list 에 집계 함수(avg, count, max,min,sum) 사용
- :connect by 절
- 뷰머징을 실패하면 조건절 pushing 시도-조건절 pushing 종류
- :조건절 push down > 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어 넣음:조인 조건 push down (NL 조인이 전제조건) > NL 조인 수행 중 드라이빙 테이블에서 읽은 값을 건건이 inner 쪽으로 밀어 넣는것을 말함 , VIEW PUSHED PREDICATE 실행계획에 나타남
- :조건절 pull up > 쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내오는 것
- 불필요한 조인 제거 > 1:M 관계인 두 테이블을 조인하는데 조인문을 제외한 어디에서도 1쪽 테이블 참조하지 않는다면 1쪽 테이블 조인에서 제거
- OR 조건을 Union으로 변환-use_concat : or-expansion유도할 떄 사용하는 힌트
- -no_expend : or-expansion유도할 떄 사용방지하는 힌트
6장 고급 SQL 튜닝 1
1절 소트 튜닝
- 소트연산 종류
order by order by 수행 시 sort order by group by order by 와 group by 같이 사용할 경우
group by 만 수행 할 경우sort group by
hash group byDistinct, union, minus, in, intersect 집합연산자 사용 sort unique
hash uniquewindow sort 윈도우 분석함수 수행 시 window sort sort aggregate 집계함수 사용 ex) sum, max sort aggregate
- 불필요한 sort 제거, 소트영역 작게 사용
- Sort unique 피하기>방안1 : 중복결과가 발생하지 않는다면 union all 연산자를 사용
- >방안2 : in 절을 exists로 변경 시 해당 컬럼으로 사용
2절 DML 튜닝
- merge 문
MERGE INTO 타켓 테이블명 USING 소스 테이블명 ON 조인 조건식 WHERE MATCHED THEN UPDATE SET 수정할 컬럼 = 수정할 값 WHEN NOT MATCHED THEN INSERT [(컬럼1,컬럼2..)] VALUES (값1,값2...);
3절 데이터베이스 call 최소화
-recursive call, user call 최소화 > One SQL 구현
-Array Processing 활용
-Array size
6장 고급 SQL 튜닝 2
4절 파티셔닝
- 파티션 pruning-정적(static) pruning : 파티션 키 컬럼을 상수 조건으로 조회하는 경우
- -동적*dynamic) pruning : 바인드 변수 조건으로 조회하는 경우
- 파티션 인덱스-local prefixed 인덱스-global prefixed 인덱스 : ‘파티션 삭제 변경시 unusable 됨’-비파티션인덱스 : ‘파티션 삭제 변경시 unusable 됨’
- -global nonprefixed 인덱스 > oracle not support
- -local nonprefixed 인덱스
5절 대용량 배치 프로그램 튜닝
- 배치정의 : 일련의 작업을 묶어 일괄 처리하는 것인데 실시간으로 요구하는 on-demand배치도 많이 늘어나는 추세임
- 병렬처리 : 쿼리 서버 집합 간 Inter-operation-parallelism (P > P)이 일어날땐 사용자가 지정한 병렬도의 2배 갯수 서버프로세스가 필요ex) 병렬도: 4일때 서버프로세스는 8개 파이프라인은 16개
- Intra-operation parallelism : 배타적인 범위를 동시에 처리하는 것
- Inter-operation parallelism : 데이터 전송, 서버 집합 간에 통신이 발생한다
- In-Out 오퍼레이션S > P : QC가 병렬 서버 프로세스에게 전달 (통신발생)P > P : 두 개의 병렬 프로세스 집합이 처리 (통신발생)PCWC : 병렬 프로세스 집합이 자식 스텝 까지 처리
- PCWP : 병렬 프로세스 집합이 부모 스텝 까지 처리
- P > S : 병렬 프로세스가 QC에게 전달 (통신발생)
- 병렬 처리 조인 힌트 종류 /*+ pq_distribute(inner_table, outer_distribution, inner_distribution) */
pq_distribute(inner, none, none) full partition wise join 유도할 때 사용
※ 양쪽 테이블 모두 조인 컬럼에 대해 같은 기준으로 파티셔닝 되어 있어야함pq_distribute(inner, partition, none) partital partition wise join 유도할 때 사용
outer table을 inner table 기준으로 파티셔닝 하라.
※ inner table이 조인 컬럼에 파티셔닝 되어 있어야함pq_distribute(inner, none, partition) partital partition wise join 유도할 때 사용
inner table을 outer table 기준으로 파티셔닝 하라.
※ outer table이 조인 컬럼에 파티셔닝 되어 있어야함pq_distribute(inner, broadcast, none) outer table을 broadcast 하라. pq_distribute(inner, none, broadcast) inner table을 broadcast 하라. pq_distribute(inner, hash, hash) 조인 키 컬럼을 해시 함수에 적용 > 양쪽 테이블을 동적 파티셔닝 > 파티션 짝 구성 후 partition wise join을 수행
- 병렬 분배 방식
range order by또는 sort group by를 병렬로 처리할 때 hash 조인키나 Hash Group by를 병렬로 처리할 때 broadcast 모든 병렬 프로세스에게 전송
매우 작은 테이블 일때 사용
동적 파티셔닝 join 또는 partial partition wise 조인 시 활용key 특정 칼럼들을 기준으로 테이블 또는 인덱스를 파티셔닝 할때 사용하는 분배 방식 round-robin 무작위로 분배
6절 고급 SQL 활용
-case 문 활용
-데이터 복제기법 활용
-union all 화용 M:M 관계 조인
-페이징 처리
-윈도우 함수 활용
-with 절 처리 방식
-Materialize 방식:임시 테이블 생성, 반복 재사용 가능, 실행 계획에 temp table transformation 이라고 뜸
- 중간 집합을 만드는 과정에 원본 집합을 읽어 Temp 공간에 기록하는 과정을 수반하면서 DISK I/O가 발생한다.
- 따라서 원본 집합이 작고 동시 수행 빈도가 높을 때에 사용하면 오히려 성능이 저하된다.
- 매우 많은 데이터를 읽어 group by , 조인 등을 통해 집합 크기를 많이 줄일 수 있을 때 수행하면 성능 개선에 도움이 된다.
- 해당 뷰를 읽을 때에도 버퍼 캐시를 경유하여 읽는다.
-Inline 방식:임시 테이블 생성 ❌, 참조된 횟수 만큼 런타임시 실행
7장 Lock과 트랜잭션 동시성 제어
1절 Lock
-블로킹 : lock 경합이 발생해 특정세션이 작업하지 못하고 멈춰 선 상태
-교착상태 : 두 세션이 lock 설정한 리소스를 서로 액세스 하려는 상태
2절 트랜잭션
-ACID (원자성,일관성,영속성,격리성)
-트랜잭션 격리수준과 비일관성 현상
| 레벨 | dirty read (다른 트랜잭션이 수정한 후 커밋하지 않은 데이터를 읽는 현상) |
non-repeatable read (한 트랜잭션내에서 같은 쿼리 2번 수행후 다른 트랜잭션에서 수정하는사이 결과가 다르게 나타나는 현상) |
phantom read (한 트랜잭션내에서 같은 쿼리 2번 수행후 첫 번째 쿼리에서 없던 유령 레코드가 나타나는 현상) |
| read uncommitted | 가능 | 가능 | 가능 |
| read committed (기본) | 불가능 | 가능 | 가능 |
| repeatable read | 불가능 | 불가능 | 가능 |
| serialize read | 불가능 | 불가능 | 불가능 |
3절 동시성 제어
-낙관적 동시성제어 : 같은 데이터를 동시에 수정하지 않을 것이라고 가정
-비관적 동시성제어 : 같은 데이터를 동시에 수정할 것이라고 가정
문장수준 읽기 일관성
트랜잭션수준 읽기 일관성
'오라클 > 튜닝' 카테고리의 다른 글
| 점이력, 선분이력 (0) | 2023.04.16 |
|---|---|
| NL 조인 튜닝 예시 (0) | 2023.02.21 |
| 테이블 random 액세스 쿼리 튜닝 예시 (0) | 2023.02.13 |
| 7장. SQL 옵티마이저 (0) | 2022.06.26 |
| 6장. DML 튜닝 (0) | 2022.06.26 |