□ 시험준비과정
○ 본격적으로 공부에 임하여 1달간 준비후 시험에 임함
○ 총 2회 시험에 임했으며 초기 시도에는 공부를 안하고봤고 마지막 시도때는 공부를 하고 임했음
○ 초기 당시에 공부를 안하고 시험에 임했지만, 난이도는 많이 어려웠던것으로 기억함
(참고로 작성자는 IT 쪽에서 DB관리 및 DB 모델링 직무를 겸임하고 있음)
○ 생각보다 어려워, 인강수강을 병행하며 시험에 임한 결과 합격함
○ 세부 공부방법
- 인강병행하며 쿼리문은 직접 작성하며 외우다시피 실습/복습을 반복함
- 1과목 이론적인 부분들은 따로 문서화(첨부파일과 같이)하여 외우기보다는 하루에 두번씩 읽었음
→ 자연스럽게 외워짐
- 문제풀이
→ 첫 시험때 튜닝/쪽에서 많이 출제된 것으로 기억하여 튜닝쪽을 문제 풀이시 반복했지만 막상 2차 시험때 한문제도 안나옴
→ 2주간 인강 및 내용정리한 것을 지속해서 복습/정리하며 공부하였고 3주차때 노랭이 문제풀이 및 떠돌아다니는 CBT 문제풀이 진행
→ 4주차에는 틀린 문제들 기반으로 반복적으로 풀고 문제자체를 이해하려고 노력함 + CBT 풀이시 총점 70점 이하면 해당 문제 다시풀거나 틀린문제를 외워서 웬만하면 70점 초과 점수를 받을정도로 공부함
- 개인적으로 회차마다 난이도가 매우 상이한 것으로 판단됨
□ 느낀점
: 직장다니면서 공부하긴 빡세고, 현업자들은 알겠지만 그렇게 막 지대하게 도움을 주는 내용은 없음. 하지만 분명 도움이 어느정도 되는부분은 있음. 나같은 맨땅헤딩하는 자들을 구원하고자 아래와 같이 텍스트 정리 및 파일 첨부를함.
=====================================요약========================================
==================== ※ 이미지/그림이 첨부된 요약본은 첨부된 파일 다운요망==================
=================== 별도 문제가 필요한 분은 따로 요청하면 송부해드리겠음===================
===아래 텍스트 내용중 열로 알수없는 숫자나 텍스트로 기재된 부분은 이미지가 있던 부분으로 첨부파일 참고하시길 바람====
1. 데이터 모델링
가. 실제 세계에서 우리가 관심을 가지고 있는 대상을 알기 쉽도록 표현해 놓은 대상
나. 복잡한 현실세계를 데이터 베이스로 표현하기 위해서 단순화 시켜서 표현한 것
다. 업무 파악의 정의 ▶ 해당 업무에 어떠한 데이터가 있는지, 업무가 필요한 정보는 무엇인지 분석하는 과정
라. 모델링 특징(단추명) ***
1) 단순화 : 누구나 쉽게 이해할 수 있도록 표현
2) 추상화(모형화/가설적) : 현실세계를 간략히 표현
3) 명확화 : 명확하게 의미가 해석되어야 하고 한 가지의 의미를 가짐
2. 데이터 모델링의 단계 ***
가. 개념 데이터 → 논리 데이터 → 물리데이터
나. 개념 데이터 모델링
1) 기업업무 분석 후 핵심 엔터티(Entity/테이블) 추출
2) 관계다이어그램(ERD) 작성
3) 업무에 대한 구조화 / 가장 추상적인 모델
4) 포괄적이고 전사적인 모델링
다. 논리 데이터 모델링
1) 핵심 엔터티와 엔터티들 간의 관계를 이용하여 세부적인 속성들,
식별자 확정
2) 업무부터 인간이 결정해야할 부분 모두 정의하는 과정
3) 정규화 / 데이터 표준화는 논리 데이터 모델링에서 수행
4) 여러 업무들을 모두 표현할 수 있어 재사용 높음
라. 물리 데이터 모델링
1) 모델이 컴퓨터 하드웨어에 어떻게 저장될지 결정
2) 특성, 구현 환경 등을 고려하여 스키마 도출
3) 가장 구체적인 모델링
4) 데이터 개체/속성/제약조건 등 전반적으로 정의(세팅) 및 적용
5) 성능, 보안성, 가용성을 고려하여 구축
3. 데이터 모델링 중요성(파간품) *
가. 파급효과 : 데이터 모델 변경시 구축 프로젝트에 큰 위험 요소
나. 간결한 표현 : 정보요구사항과 한계 정확하고 간결하게 표현
다. 데이터 품질 : 데이터 구조 문제로 데이터 품질의 문제가 발생
4. 데이터 품질과 관련된 유의점(중비일) *
가. 중복 : 데이터베이스가 여러 장소에 같은 정보를 저장하는 잘못 하지 않도록함
나. 비유연성 : 데이터 모델이 수시로 변경되면 유지보수의 어려움 가중
다. 비일관성 : 데이터 중복이 없더라도 비일관성 발생
5. 데이터 모델링 필수요소(EAR)
가. 엔터티(Entity) : 업무가 관리하고자 하는 복수의 대상
나. 속성(Attribute) : 대상들이 갖는 속성
다. 관계(Relataionship) : 대상들간의 관계
6. 엔터티(Entity) ***
가. 업무상 분석해야하는 대상(Instance)들로 이루어진 개체집합
나. 엔터티(Entity) : 업무에서 저장되고 관리되어야하는 데이터 집합(복수)
다. 인스턴스(Instance) : 엔터티에 속해있는 개별 개체들 의미(단수)
라. 특징
1) 식별여부 : 유일한 식별자(Unique Identifier)를 필수
2) 인스턴스들의 집합 : 최소 2개 이상의 인스턴스 존재
3) 속성 : 엔터티는 반드시 속성(변수)를 자기고 있어야함(2개 이상)
4) 업무 : 업무에서 관리되어야하는 집합
5) 관계 : 엔터티는 다른 엔터티와 최소한 한 개 이상의 관계를 가짐
7. 엔터티의 생성시점에 따른 분류(기중행) ***
가. 기본 엔터티 + 중심엔터티 + 행위엔터티
나. 기본 엔터티 : 업무에 원래 존재하는 정보이며 독립적으로 생성가능하며 부모역할을 하게됨 ex) 고객 테이블
나. 중심엔터티 : 기본엔터티로부터 발생되고 업무에 있어서 중심적인 역할 ex) 구매 테이블
다. 행위엔터티 : 두 개 이상의 부모 엔터티로부터 바생되고 내용이 자주 바뀌거나 데이터양이 증가 ex) 구매내역 테이블
고객정보 테이블
구매정보 테이블
구매내역 테이블
PK 고객번호
PK 구매번호
PK 구매번호
이름
고객번호
주문일자
주소
고객이름
매출액
8. 엔터티의 형태에 따른 분류(유개사) ***
가. 유형엔터티 : 물리적 형태가 있는 엔터티. 안정적, 지속적인 특성
나. 개념엔터티 : 물리적 형태가 없는 엔터티(형태X) ex)등급
다. 사건엔터티 : 업무를 수행함에 따라 발생하는 엔터티(형태X) ex)주문
고객정보 테이블
구매등급 테이블
구매내역 테이블
PK 고객번호
FK 구매번호
PK 구매번호
이름
누적 구매액
주문일자
주소
고객 등급
매출액
9. 속성(Attribute) ***
가. 업무상 분석해야하는 대상의 성질, 특징을 의미
나. 인스턴스로 관리하고자 하는 더 이상 분리되지 않는 최소의 데이터 단위
다. 하나의 인스턴스는 속성마다 반드시 하나의 속성값을 가짐
라. 명명법
1) 해당 업무에서 사용하는 이름 부여
2) 서술식 속성명 사용 X
3) 약어의 사용 제한
4) 유일한 명칭으로 지정
10. 속성의 특성에 따른 분류(기설파) ***
가. 기본속성 : 업무로부터 추출된 모든 속성. 가장 일반적이고 많이 존재 ex) 책, 이름, 출판사 등
나. 설계속성 : 기본 속성 외, 업무를 규칙화하기 위해 새로 만들어지거나 기존 속성을 변형하여 만들어지는 속성 ex)코드성
다. 파생속성 : 다른 속성에 영향을 받아 발생하는 속성. 보통 계산된 값들 ex)매출액, 총판매수
라. 도메인(Domain / 값의 범위) : 각 속성의 가질 수 있는 값의 범위
11. 식별자 ***
가. 엔터티 내의 인스턴스들을 각각 구분할 수 있는 구분자
나. 엔터티 내에서 인스턴스들을 구분할 수 있는 구분자임
다. 대표성 여부에 따른 분류
1) 주식별자(PK) : 각 인스턴스들 구분. 데이터 모델링시 대표성을 지닌 식별자. 유일성, 최소성, 불변성, 존재성 만족.
2) 보조 식별자 : 각 인스턴스들 구분. 대표성을 가지지 못하는 식별자
※ 주식별자들만 타 엔터티에 외부식별자를 생성하여 참조관계 형성
라. 식별자의 스스로 생성여부에 따른 분류
1) 내부식별자(PK) : 엔터티 내부에서 스스로 만들어진 식별자
2) 외부식별자(FK) : 타엔터티와의 관계를 통해 타 엔터티로부터 받아오는 식별자
※ 외부식별자는 물리 데이터 모델링시 외래키(FK)로 구성
부서정보 테이블
사원정보 테이블
PK 부서번호
PK 사원이름
부서이름
FK 부서번호
지역
직무
마. 식별자 속성의 수에 따른 분류
1) 단일 식별자 : 하나의 속성으로 구성된 식별자
2) 복합 식별자 : 둘 이상의 속성으로 구성된 식별자
사원정보 테이블
수강 테이블
PK 사원번호
PK/FK 사원번호
주민번호
PK 수강일자
부서번호
-
바. 식별자의 대체 여부에 따른 분류
1) 본질 식별자 : 업무에 의해서 만들어지는 식별자 ex) 사원번호
2) 인조 식별자 : 업무에 의해서 만들어지지 않으나 복잡한 구성을 가지고 있어 인위적으로 만들어낸 식별자 ex) 주문 일련번호
12. 관계 ***
가. 엔터티 간의 연관성을 표현 나. 관계명 1) 항상 현재형으로 표현 2) 애매한 동사로 표현 X 다. 관계차수 1) 1:1 관계
2) 1:M 관계
3) M:M 관계
라. 관계 필수/선택조건
1) 필수조건 : |
- 수험생이 반드시 있어야 수험번호가 생성됨
2) 선택조건 : ○
- 학과는 반드시 있지만 학생은 있을수도 있고 없을 수도 있다.
마. 관계 저의시 점검사항
1) 관계 연결에 대한 규칙이 서술되어있는지 여부
2) 관계연결이 동사로 구성되어있는지 여부
3) 두 개의 엔터티 사이에 연관규칙이 존재여부
4) 엔터티 사이에 정보의 조합의 발생여부
13. 식별자/비식별자 관계 ***
가. 식별자 관계 : 부모 엔터티의 주식별자가 자식 엔터티의 주식별자로 상속된 경우(강한관계)
1) 과도한 식별자 관계는 SQL 구문을 복잡하게 하고 오류가능성 증가
나. 부모엔터티의 주식별자가 자식엔터티의 일반 속성으로 상속된 경우(약한관계)
1) 과도한 비식별자 관계는 불필요한 조인을 유발
다. 강한관계 = 실선
라. 약한관계 = 점선
식별자 관계
비식별자 관계
관계 강도
강한 관계
약한 관계
자식 주식별자
포함여부
자식의 주식별자에 포함
자식의 일반속성에 포함
표기방법
실선 표현
점선 표현
데이터 모델링
고려사항
자식 엔터티는 부모 엔터티에 종속
상속받은 주식별자의 속성을 다른 엔터티에 이전필요
자식 주식별자를 별도 구성
상속받은 주식별자의 속성이 상속되는 것을 차단하기 위해 고려
단점
SQL 명령문 복잡해지며 오류가능성 커짐
과도한 조인을 유발하게됨
14. ERD(Entity Relation Diagram) ***
가. IE(Infromation Engineering)
나. Barker
※IE = ○, Barker = 점선+실선
다. 표기법
1) IE : 테이블(엔터티)명 外
2) Barker : 테이블(엔터티)명 內
라. 속성
1) IE : 식별자와 일반속성 별도 분리
2) Barker : #(식별자), *(필수값), o(선택값)
※식별자관계
▶ I/E : 식별자 = 실선 / 비식별자 = 점선
▶ Barker : 식별자 = | / 비식별자 = | 없음
※관계선택사양
▶ I/E : 반드시 포함 실선 + o
▶ Barker : 반드시 포함 점선 + 점선
마. ERD로 알 수 있는 방법(차선명) ***
1) 관계 차수
2) 관계 선택사양
3) 관계명
바. ERD 그리는 순서
1) 엔터티를 그림
2) 엔터티를 적절하게 배치
3) 엔터티간 관계 설정
4) 엔터티간 관계 기술
5) 관계 필수 여부 기술
※ 관계명은 반드시 기재 안해도 됨
15. 3단계 스키마 ***
가. 스키마 : 데이터관리시스템(DBMS)을 이루는 객체들을 이루는 구조
나. 구조(외개내)
1) 외부스키마
가) 사용자 관점
나) 여러 개의 외부 스키마가 존재
2) 개념스키마
가) 데이터베이스의 관리자(DBA)관점
나) 데이터베이스 전체가 어떻게 구성되어 있고, 어떻게 데이터가 저장되어 있는지, 데이터들 간의 상호 관계는 어떠한지 알려주는 단계
다) 전체적인 논리족 구조 파악
라) 보통 스키마 하면 개념스키마를 지칭하는 경우가 많음
3) 내부스키마
가) 데이터가 실제로 물리적 데이터베이스에 어떻게 저장되는지 보여줌
다. 사상
1) 논리적사상 = 외부스키마 – 개념스키마
2) 물리적사상 = 개념스키마 - 내부스키마
16. 데이터 독립성 종류
가. 데이터 독립성 : 데이터 구조가 변경되더라도 상위 단계에 영향을 미치지 않는 속성
나. 논리적 독립성 : 개념 스키마가 변경되어도 외부 스키마에는 영행을 미치지 않음
다. 물리적 독립성 : 내부 스키마가 변경되어도 외부/내부 스키마에 영향을 미치지 않음
17. Row Migration 현상
: 기존의 행 길이보다 증가된 경우, 블록 내의 데이터들 간의 여유공간이 부족한 현상이 발생. 원래 정보의 위치를 저장하고 실제 데이터는 다른 블록에 저장될 수 있음. 이를 Row Migration이라고함. Row Migration은 저장된 데이터가 Update 될 경우 블록 내의 공간이 부족하여 발생하는 현상
18. Row Chaning 현상
: 하나의 데이터가 여러 개의 블록에 분산되어 저장하게 되는 현상. Row Chaning이 발생하면 특정 정보를 찾기 위해 여러 개의 블록을 조회하게 되므로 DISC I/O가 증가하고 성능이 나빠짐.
19. 정규화
가. 정규화란 데이터의 중복과 데이터 이상 현상을 줄이기 위한 데이터베이스 설계기법입(이상현상, 삽입이상, 갱신이상, 삭제 이상 극복)
나. 트레이드 오프관계 : 정규화를 진행하면 조인이 발생되고 출력 성능이 느려질 수 있으나, 테이블의 크기가 작아지면서 컬럼이 적어지므로 정보의 입력/수정/삭제가 빨라짐. 정규화를 한다고 성능이 좋아지는 보장은 없음.
다. 제1정규형 : 제1정규화는 테이블 속성의 원자성을 확보하고 주식별자를 설정하는 과정 ex) 한 속성에는 단 한 개의 값만 적재
라. 제2정규형 : 제2정규화는 주식별자가 2개 이상의 속성으로 이루어진 부분 함수적 종속을 제거하는 과정
마. 제3정규형 : 제3정규화는 주식별자를 제외한 컬럼 간의 종속성인 이행 함수 종속성을 제거하는 과정
(한 테이블 속성 X, Y, Z가 있을 때 X→Y 그리고 Y→Z가 성립되는 경우)
20. DDL(Data Definition Language)
가. 테이블 중심 작업 나. 오브젝트(데이터 베이스에 저장된 저장객체)를 생성, 수정, 삭제 등 관리하기 위한 언어 다. CREATE : 오브젝트 생성 명령어 라. ALTER : 오브젝트 구조변경 명령어 마. DROP : 오브젝트 삭제 명령어 바. TRUNCATE : 오브젝트를 전부 삭제하고 공간을 반납하는 명령어
□ CREATE
- CREATE TABLE + 테이블명 (컬럼명+데이터타입+제약조건);
- CREATE TABLE + 테이블명 + AS + 서브쿼리; [테이블 구조복사]
□ ALTER
- 컬럼추가ALTER TABLE + 테이블명 + ADD + (컬럼명+데이터타입+제약조건);
- 컬럼수정ALTER TABLE + 테이블명 + MODIFY + (컬럼명+데이터타입+제약조건);
- 테이블명 변경
ALTER TABLE + 테이블명 + RENAME TO + (테이블명);
- 컬럼의 삭제
ALTER TABLE + 테이블명 + DROP COLUMN + 컬럼명;
- 컬럼명 변경
ALTER TABLE + 테이블명 + RENAME COLUMN + 변경전 컬럼명 + TO + 변경후 컬럼명
□ DROP
- DROP TABLE + 테이블명 + [CASCADE CONSTRAINT];
※CASCADE CONSTRAINT 입력을 하면 해당 테이블과 관계있던 참조되는 제약조건에 대해서도 삭제됨
□ TRUNCATE
- TRNCATE TABLE + 테이블명;
21. DML(Data Manipulation Language)
가. 테이블에 적재된 데이터관련 작업
나. INSERT
다. UPDATE
라. DELETE
□ INSERT
- 데이터 추가
INSERT INTO + 테이블명 + (컬럼명1, 컬럼명2, 컬럼명3) + VALUES(값1, 값2, 값3)
- 데이터 추가(컬럼생략)
INSERT INTO + 테이블명 + VALUES(값1, 값2, 값3);
- 모든 컬럼이 있지 않는 경우 → 빈 컬럼은 NULL로 채워짐
INSERT INTO + 테이블명 + (컬럼명1, 컬럼명2) + VALUES(값1, 값2, 값3);
- NULL을 명시적 지정
INSERT INTO + 테이블명 + (컬럼명1, 컬럼명2, 컬럼명3) + VALUES(값1, 값2, NULL);
- 한번에 여러 개의 행을 넣는 방법
INSERT ALL + INTO + 테이블명 + (컬럼명1, 컬럼명2, 컬럼명3) + VALUES(값1, 값2, 값3) + INTO + 테이블명 + (컬럼명1, 컬럼명2, 컬럼명3) + VALUES(값1, 값2, 값3)
□ UPDATE
- UPDATE + 테이블명 + SET + 컬럼1 = 수정값1 + WHERE + 조건문
□ DELETE
- DELETE FROM + 테이블명 + WHERE + 조건절
22. TCL(Transaction Control Language)
가. 하나의 업무 단위를 의미로 동시성과 일관성을 제어함.
- 특성(원일고지)
①원자성 : 트랜잭션 정의연산 모두 실행 OR 모두 실행x
②고립성 : 현재 트랜잭션 연산이 다른 트랜잭션에 영향 받으면 안됌
③지속성 : 트랜잭션이 진행되면 해당 내용은 영구적으로 저장
④일관성 : 트랜잭션이 실행된 이후에도 일관성 있는 데이터베이스 유지
나. DDL(CREATE,ALTER,DROP,TRUNCATE) 자동 커밋
다. DML(INSERT,UPDATE,DELETE) COMMIT전까지 디스크에 반영 안됌
라. COMMIT : 트랜잭션을 완료하고 디스크에 반영. COMMIT 후 복구 불가
마. ROLLBACK : 트랜잭션 취소. 마지막 COMMIT 지점으로 돌아감
바. SAVEPOINT : ROLLBACK시 돌아가는 저장포인트 지정.
※SQL서버는 자동 COMMIT이 되지만, 명시적 트랜잭션을 원할 경우 AUTO COMMIT OFF로 설정해야하며 DDL, DML 명령어시 직접 COMMIT 해야함
23. DCL(Data Control Language)
가. 데이터베이스에 접근하고 객체들을 사용할 수 있는 PRIVILEGES를 부여하거나 철회하는 Data Control LANGUAGE 나. GRANT : 데이터베이스 사용자에게 권한 부여
다. REVOKE : 데이터베이스 사용자에게 부여된 권한 회수
□ GRANT + 권한명 + ON + 객체명 + TO + 유저명
□ REVOKE + 권한명 + ON + 객체명 + FROM + 유저명
24. 명령문 순서
가. 서술
: Select – From – Where – Group by – having – order by
나. 연산**
: From – where - Group by – having – Select – order by
* *=Asterask
25. NULL
가. 모르는 값 or 공백
나. NULL != 0
다. NULL과의(NULL상대로) 산술연산은 모두 NULL로 출력
라. NULL 비교연산(=, !=, <, >, <> 안됨, 앞 비교연산을 쓰면 오류발생)
1) IS NULL
2) IS NOT NULL
마. NULL = 3, NULL > 3, NULL <> 3을 쓰면 UNKNOWN 이 나옴
(UNKNOWN이 사실상 최후의 논리구조로 WHERE구조로 들어가면 FALSE로 작용)
바. NULL 비교연산 논리
사. NULL 함수
1) NVL(값1, 값2) : 값1이 IS NULL이면 값2, IS NOT NULL이면 값1
▶ NULL 뛰기
**SQL 서버에서는 ISNULL(값1, 값2) 사용
2) NVL2(값1, 값2, 값3) : 값1일 IS NULL 이면 값3, IS NOT NULL이면 값 2
▶ NULL 뛰기
3) NULLIF(값1, 값2) : 값1 = 값2는 NULL, 값1 != 값2는 값1
▶ 같이 널(NULL)자, 처음보는 애들.
4) COLALESCE(값1, 값2) : NULL이 아닌 첫 번째 값 반환
▶ NULL지 않고 처음 집들어간 애 누구.
아. ORACLE 정렬에서 무한대 의미 / SQL SERVER 정렬에서 최소값 의미
자. (SELECT절) NULL = 3 등과 같은 비교연산시, UNKNOWN이 반환되어 오류
차. (WHERE절) NULL = 3 등과 가같은 비교연산시, UNKNOWN이 조건절에 들어가서 거짓(FALSE)가 반환
□ NULL
- SELECT + 필드명 + FROM + 테이블명 + WHERE + IS NULL / IS NOT NULL
□ NVL(WHERE절에도 쓸 수 있음)
- SELECT + NVL(필드명, 값1) + FROM + 테이블명
□ NVL2(WHERE절에도 쓸 수 있음)
- SELECT + NVL2(필드명, 값1, 값2) + FROM + 테이블명
□ NULLIF(WHERE절에도 쓸 수 있음)
- SELECT + NULLIF(값1, 값2) + FROM + 테이블명
□ COLALESCEF(WHERE절에도 쓸 수 있음)
- SELECT + COALESCE(필드명) + FROM 테이블명
26. ORDER BY
가. 특정 컬럼을 기준으로 오름차순 혹은 내림차순 정렬
나. WHEREM ROWNUM등과 함께 쓸 수 있음
다. ORDER BY 정렬 시점은 모든 실행이 끝난 이후 데이터 출력
라. ORDER BY는 메모리를 많이 사용하며 성능저하의 요인이 됨
마. 컬럼번호를 이용해서 정렬할 수 있음
바. 출력되지 않은 컬럼을 이용해서 정렬도 가능
사. 정렬 연산시점(마지막에 위치)
: FROM – WHERE – GROUPBY – HAVING – SELECT – ORDER BY
아. 복수컬럼을 이용한 정렬 가능. 첫 번째 컬럼기준으로 먼저 정렬 후
그다음 컬럼을 기준으로 정렬 진행
자. ORACLE에서 NULL은 무한대(최대값), SQL서버에서 NULL은 최소값
27. 집계함수
가. 복수행(다중행)함수 → 여러 행을 연산/개체수준의 정보를 그룹으로 처리
(그룹수준)
나. 집계함수는 다른 컬럼과 섞어서 같이 사용 불가
다. 집계함수는 NULL을 제외하고 연산함 ***
1) * 는 NULL을 포함하여 집계함. 예외임.
2) EX) COUNT(필드명) = NULL 제외 후 연산
3) EX) COUNT(*) = NULL 포함 연산
라. 종류(컬럼명)
1) SUM(컬럼명)
2) AVG(컬럼명)
3) COUNT(컬럼명)
4) MAX(컬럼명)
5) MIN(컬럼명)
마. 집계함수는 WHERE 절에 올 수 없음
*연산순서로 인하여 그럼.
*GROUP BY부터 그룹수준이며, WHERE절까지는 개체수준임.
바. COUNT(DISTINCT 컬럼)
□ 집계함수
- SELECT + SUM(컬럼명) / AVG(컬럼명) / COUNT(컬럼명), / MAX(컬럼명) / MIN(컬럼명) + FROM + 테이블명
□ COUNT(DISTINCT컬럼)
- SELECT + COUNT(DISTINCT 컬럼명) + FROM 테이블명
28. GROUP BY
가. GROUP BY + 컬럼명
나. SELECT + 필드명 + FROM + 테이블명 + WHERE + 조건 + GROUP BY
+ 컬럼명
다. 개체수준이 그룹수준으로 묶임
라. 연산순서
: FROM-WHERE-(개체수준)-GROUP BY-(그룹수준)-HAVING-SELECT-ORDER BY
마. 집계함수는 GROUP BY ~ ORDER BY 까지 적용
바.집계함수는 WHERE절에 올수 없음. WHERE절까지는 개체수준임.
사. 집계함수는 NULL을 제외하고 연산하지만 GROUP BY는 NULL을 그룹으로
지정할 수 있음
아. 집계함수 사용시 사실은 GROUP BY NULL이 생략된 것임
- GROUP BY NULL = GROUP BY()
자. GROUP BY 두 개 이상의 컬럼사용 : 입력한 컬럼들의 값을 조합으로
그룹화 진행
차. 중첩된 집계함수 활용 EX) AVG(SUM(SAL)) / 이때 선택 컬럼의 단일
여부를 주의해야함. 선택컬럼일 단일 개체수준 즉 행수가 다르면 에러뜸
29. HAVING
가. SELECT+FROM+WHERE+GROUP BY+HAVING+조건식
나. GROUP BY 없이 HAVING 사용가능
30. 그룹함수
가. GROUPING SETS : 지정 컬럼 그룹별 집계산출
1) GROUP BY + GROUPING SETS(컬럼명, 컬럼명)
▶ ①SELECT EMPNO, SUM(SAL) FROM EMP GROUP BY EMPNO
▶ ②SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO
▶ ① + ② = GROUP BY GROUPING SETS(EMPNO, DEPTNO);
▶ 푸는 방법 : NULL을 모두 제거 후 남는 값에 대한 컬럼들이
GROUP BY GROUPING SETS(컬럼명, 컬럼명)에 옴.
※ GROUP BY 생략 = GROUP BY NULL = GROUP BY () 과 같으며 이는 전체
합계를 의미
나. ROLL UP : GROUP BY의 확장 형태이며 지정된 상위컬럼(앞에 있는
인수) 기준으로 집계됨. 합계와 소계를 보여줌. 계층구조형태임.
1) GROUPING SETS(필드명, NULL / () ) 형태와 똑같음
2) SELECT JOB, SUM(SAL) FROM EMP GROUP BY GROUPING
SETS(JOB, ()) = SELECT JOB, SUM(SAL) FROM EMP GROUP BY
ROLLUP(JOB)
3) 인수가 두개이상일때 ROLLUP((A,B)) = GROUPING SETS(A,B) +
GROUPING SETS(A) + GROUPING SETS(NULL)
3) GROUP BY 컬럼B, ROLLUP(컬럼A)
다. CUBE
1) 다차원집계
2) 모든 컬럼의 집계를 모두 해줌
3) CUBE(A,B) = GROUP BY (A,B) + GROUP BY (A) + GROUP BY (B) +
GROUP BY NULL
□ GROUPING SETS : 필드 그룹별 집계 산출
- SELECT + 필드1, 필드2, 집계함수 + FROM + 테이블 + WHERE + 조건 + GROUP BY + GROUPING SETS(필드1,필드2)
□ ROLLUP : 합계와 소계 산출****************
- SELECT + 필드1, 필드2, 집계함수 + FROM + 테이블 + WHERE + 조건 + GROUP BY + ROLLUP(필드1, 필드2)
▶ 필드 1이 상위컬럼
▶ GROUPING SETS( (필드1, 필드2) ) 결과 + GROUPING SETS(필드1[상위컬럼]) 결과 + GROUPING SETS(NULL) 결과
31. WINDOW 함수
가. 개체수준을 처리하는 함수임
나. 실시간 집계 함수
다. WINDOW 함수는 SELECT 절에 들어가는 함수임
1) PARTITION BY + ORDER BY
2) 개체별 정보를 살려놓은 후 정렬함
라. SELECT WINDOW 함수() OVER (행분할, 행정렬, 대상행지정)
1) 행분할 : PARTITION BY (생략가능)
2) 행정렬 : ORDER BY (생략가능)
3) 대상행 지정 : ROWS 또는 RANGE
마. 순위함수
1) RANK 함수 : 공동 등수가 있을 때 후순위를 건너뜀, 위치 기준
EX) 5000, 5000 5000, 4000 / 1, 1, 1, 4
2) DENSE_RANK 함수 : 값 기준
EX) 5000, 5000 5000, 4000 / 1, 1, 1, 2
3) ROW_NUMBER : 순위와 무관하게 행 번호 부여
EX) 5000, 5000 5000, 4000 / 1, 2, 3, 4
바. 집계함수 (WINDOW절)
1) SUM, MAX, MIN, AVG, COUNT
2) 누계, 실시간 함수
3) ORDERBY를 생략하면 총 집계값으로 채워짐, PARTITION BY는 사용
하고 ORDER BY를 생략하면 그룹별 총 집계값만 채워짐
2) OVER(PARTITION BY 집계범위 ORDER BY 정렬기준
사. 행 순서함수
1) FIRST_VALUE, LAST_VALUE, LAG 함수, LEAD 함수
- FIRST_VALUE
- LAST_VALUE
- LAG
아. 비율함수
1) RATIO_TO_REPORT
2) PERCENT_RANK
3)NTILE 함수 : 파티션 별로 N등분하여 조를 나눈다.
자. ROWS & RANGE
1) 모든 행은 CURRENT ROW가 될 수 있음
2) ROWS는 행의 수를 선택할 때 사용하고 RANGE는 값의 범위를 선택할 때 사용한다.(둘중 원하는 것을 사용 가능)
3) ROWS는 CURRENTROW를 기준으로 범위 지정
*JOB 기준으로 그룹화 후 봉급기준으로 정렬하면 JOB별 UNBOUNDED
PRECEDING 적용되어 연산진행
* ROWS UNBOUNDED PRECEDING : 윈도우 함수의 연산을 맨 위에부터
현재행까지 계산
* ROWS 1 PRECEDING : 윈도우 함수의 연산은 한 칸 위 행부터 현재행
까지 계산(**ROWS 1 FOLLOING은 안됌)
4) ROWS BETWEEN A AND B
5) RANGE : ROWS 가 행 위치를 기준으로 행을 선택한다면 RANGE는
컬럼의 값을 기준으로 연산에 참여할 행을 선택
*위와 같이 동일값이 나오면 RANGE 함수를 쓴것임
※RANGE 150 PRECEDING : 현재 칼럼의 값을 기준으로 작은 값에서 150 이하로 차이가 나는 행들을 선택적으로 계산.
※RANGE UNBOUNDED PRECEDING : 현재 칼럼의 값(포함하여)을 기준으로 작은 값을 모두 선택하여 계산한다.
※RANGRE BETWEEN 150 PRECEDING AND 150 FOLLOWING : 현재 칼럼의 값보다 작은 값에 150 이하로 차이가 나고 현재 칼럼 값 기준으로 큰 값에서 150 이하 차이나는 행들을 선택
※RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 현재 칼럼의 값보다 작은 값을 가지는 행부터 현재 행까지 모두 선택한다. RANGE UNBOUNDED PREDEING 와 동일.
6) WINDOWING 생략시, 기본 윈도우 범위는 RANGE BETWEEN UNBOUNDING
PRECEDING AND CURRENT ROW와 동일
32. 함수
1) SELECT*FROM DUAL (기본 1*1)
2) FLOOR(숫자) : 소수점 버림
3) CEIL(숫자) : 소수점 올림
*SQL서버 → CEILING
4) MOD(숫자1,숫자2)
5) ROUND(숫자, 자리수) : 반올림
6) TRUNC(숫자, 자리수) : 버림
7) ABS(숫자) : 절대값
8) MOD(값1,값2) : 나머지 구하기
9) LOWER(문자열) : 대문자 → 소문자
10) UPPRER(문자열) : 소문자 → 대문자
11) INITCAP(문자열) : 첫글자를 대문자로 나머지는 소문자
12) LENGTH(‘문자열’) : 문자열 길이
13) LENGTHB(‘문자열’) : 문자의 바이트 수
14) CONCAT(‘문자1’, ‘문자2’) : 문자를 합칩(단, 두 개밖에 안됌)
15) SUBSTR(‘문자열’,숫자) : 숫자번재 글자를 포함하여 문자열 출력
16) SUTBSTR(‘문자열’,숫자1,숫자2) : 숫자1부터 숫자2 수치만큼 출력
17) LPAD(‘문자열1’, 숫자,‘문자열2’) : 문자열1 좌측에 문자열2를
숫자 바이트만큼 추가
18) RPAD(‘문자열1’, 숫자,‘문자열2’) : 문자열1 우측에 문자열2를
숫자 바이트만큼 추가
19) LTRIM(‘문자열1’,‘문자열2’) : 문자열1 좌측에서부터 문자열2가
나올때까지 모두 제거
20) RTRIM(‘문자열1’,‘문자열2’) : 문자열1 우측에서부터 문자열2가
나올때까지 모두 제거
21) SYSDATE : 현재날짜
22) 형변환 함수 **
- TO_NUMBER(‘문자열’)
- TO_CHAR(숫자)
- TO_CHAR(날짜, ‘YYYY-MM-DD HH:MI PM’ 또는 ‘MM/DD/YY’)
- TO_DATE(‘문자열’,형식)
23) CASE 함수
- CASE WHEN 조건식 THEN 반환값1 WHEN 조건식 THEN 반환값2
ELSE 반환값3 END
- ELSE 사용하지 않으면 NULL(공백)이 출력됨**
SELECT
ENAME, EMPNO,
CASE WHEN SAL < 1000 THEN 'C등급'
WHEN SAL < 2000 THEN 'B등급'
ELSE 'A등급'
END AS GRADE
FROM EMP
24) DECODE 함수
- DECODE(컬럼명, 값1, 반환값1, 값2, 반환값2, 값3, 반환값3)
- 값별 반환값 지정
- 값별 반환값을 지정 안하면 모두 NULL로 나옴
30. 조인
가. 테이블은 수평결합과 수직결합을 할 수 있음
나. JOIN은 수평결합
다. 조인조건은 테이블 간 하나의 기준이 있어야함
라. CROSS JOIN : 조건 없이 2개의 테이블을 하나로 조인하는 것.
카테시안 곱이 발생
마. INNER JOIN : CROSS JOIN 결과에서 공통된 컬럼 기준으로 데이터 산출
바. NATURAL JOIN : 두테이블의 공통 컬럼을 자동으로 지정하여 조인.
등가조인일때만 사용가능하며, 엘리어스 ON, WHERE절 사용불가
＊NATURAL JOIN은 공통컬럼이 앞에 한 개만 오며, INNER JOIN 활용하는 상황에서 *을 사용할경우 공통 컬럼이 중간에 2번 표시됨.
사. OUTER JOIN : 선행테이블(좌) 혹은 후행 테이블(우)을 기준으로 조인
조건에 만족하지 않는 행들을 포함하여 출력.
(1) LEFT OUTER JOIN
(2) RIGHT OUTER JOIN
(3) FULL OUTER JOIN
아. N개의 테이블을 조인한다고 할 때 조인 개수는 N-1개임.
자. SELF JOIN : 계층구도
(1) 순방향 전개 : 부모노드 – 자식노드
□ CROSS JOIN
- SELECT * FROM 테이블명1 CROSS JOIN 테이블명2
- SELECT * FROM 테이블명1 A, 테이블2 B
□ INNER JOIN
- SELECT * FROM 테이블1 A INNER JOIN 테이블2 B ON A.공통컬럼=B.공통컬럼;
- SELECT*FROM 테이블1 A JOIN 테이블2 USING(공통컬럼);
- SLECT *FROM 테이블1 A, 테이블2 B WHERE A.공통컬럼 = B.공통컬럼;
- SELECT*FROM 테이블1 A JOIN 테이블2 B ON A.공통컬럼=B.공통컬럼
□ NATURAL JOIN
- SELECT * FROM A NATURAL JOIN B;
□ LEFT OUTER JOIN
- SELECT * FROM 테이블 1 LEFT OUTER JOIN 테이블2 ON 조인 조건식
- SELECT*FROM 테이블1, 테이블2 WHERE 테이블1 컬럼 = 테이블2 컬럼(+);*ROW가 부족한 행에 + 붙임. LEFT OUTER JOIN은 + 우측, RIGHT OUTER JOIN + 좌측.
31. 계층형 질의
가. 계층형 전개
(1) ROOT DATA LV.1 – LEAF DATA LV3 : ROOT – SIBLING – LEAF
(2) 순방향(프자부 – 부자순!!) / 역방향
(3) SELECT + 필드명 + FROM + 테이블 + STRAT WITH + 조건 + CONNECT BY PRIOR 컬럼 = 컬럼;
* 계층형 전개는 레벨순으로 진행되며 START WITH으로 ROOT 노드(ROOT데이터)(스타트 레벨) 지정
* PRIOR은 이전레벨을 지칭. PRIOR이나오면 이전레벨이라고 해석.
이전레벨의 데이터가 현제레벨의 데이터이다!!!!
(4) 계층형 질의의 WHERE 절은 계층형 전개 진행 후 진행됨.
↑ LEVEL 1인 EMPNO는 LEVEL 2인 MGR과 같다
□ 계층형
- SELECT + 컬럼 + FROM + 테이블명 + WHERE 조건절 + START WITH + 부모데이터 정보 + CONNECT BY PRIOR + 부모와 자식의 계층조건;
32. 서브쿼리
가. 스칼라서브쿼리 : 1행, 1열
나. GROUP BY 절에는 서브쿼리 사용 못함
다. SELECT, FROM, WHERE, HAVING, ORDER BY 절에 사용가능
라. 메인쿼리의 한행이 출력될때마다 서브쿼리가 한번 실행됨. 즉,
메인 쿼리가 총 N행이라면 서브쿼리는 N번 실행됨
마. 인라인뷰 : FROM 절에 SELECT 문을 사용하여 가상의 테이블을
만드는 효과임. 인라인뷰는 동적인 뷰임.
바. 상호연관쿼리 : 상호연관쿼리란 메인쿼리의 컬럼값을 서브쿼리에서
참조하는 쿼리형태임.
33. 다중행 서브쿼리의 연산자
가. IN : 서브쿼리의 결과 중 하나라도 일치하면 조건은 TRUE
나. ANY, SOME : 서브쿼리의 결과와 하나이상 일치하면 조건은
TRUE. IN은 = 의미, ANY/SOME은 =, <, > 사용가능
다. ALL : 서브쿼리의 결과와 모두 일치해야 조건이 참이됨
라. EXISTS : 데이터의 존재여부를 확인하는 쿼리에 해당. 만족하는
값이 없으면 공집합 반환.
34. 수직결합
가. JOIN은 수평결합
나. 컬럼의 이름과 수가 일치해야함
다. UNION : 합집합, 정렬, 중복 데이터 배제
라. UNION ALL : 합집합, 중복 데이터 가져옴
마. INTERSECT : 교집합
바. MINUS : 차집합(SQL SEVER : EXCEPT)
35. 성능 데이터 모델링개요
가. 성능향상을 목으로 하는 모델링 / 설계단계시 진행
나. 정규화, 반정규화, 테이블 통합, 테이블 분할, 조인구조, PK/FK 등
다. 분석/설계 단계에 수행해야함. 비용이 제일 저렴.
라. 사전에 진행할수록 비용이 덜 듬
마. 분석/설계 단계에서 데이터베이스 처리성능을 향상시킬 수 있는
방법을 주도 면밀하게 고려
1) 정규화를 정확히 수행 / 기본적으로 정규화 완벽히 수행해야함
2) 용량 산정을 수행
3) 트랜잭션 유형 파악** / CRUD 매트릭스를 파악
4) 반정규화 수행 / 파악된 용량과 트랜잭션 유형을 근거로 진행
5) 모델조정, PK/FK 조정, 슈퍼타입/서브타입 조정 / PK&FK 컬럼순서 고려
6) 성능관점에서 데이터 모델 검증
36. 정규화와 성능
가. 결정자에 의해 함수적 종속을 가지고 있는 일반 속성을 의존자로 하여
입력/수정/삭제 이상현상을 제거
나. 한 테이블의 데이터 용량이 최소화
다. 속도가 빨라질수도 있고 느려질수도 있음
라. 입력/수정/삭제 모조건 향상 & 조회 향상OR저하
마. 정규화 : 좀 더 작은 단위의 테이블로 설계하는 과정
1) 정규형 : 정규화 결과에 의해 도출된 데이터 모델이 갖춰야할 특성
2) 함수적 종속성 : 함수적 종속성에 근거하여 정규화 진행
EX) 고객명은 고객주민등록번호에 함수적 종속성이 있음
- 주민번호 > 이름, 출생지, 주소
3) 결정자 : 컬럼 a를 결정자라고함 EX) 주민등록번호는 결정자
4) 다치종속 : 컬럼 a에 의해 컬럼 b의 값을 다수 알 수 있을 때
바. 유연성 극대화 / 재활용 높아짐 / 중복이 최소화됨
※ 정규화를 한다면 하나의 인덱스만 만들어도됨
1차 정규화
복수의 속성값을 갖는 속성 분리, 원자성 확보
2차 정규화
부분종속 속성을 분리
3차 정규화
이전종속 속성을 분리
37. 반정규화와 성능
가. 서능향상과 개발과 운영의 단순화를 위해 중복, 통합, 분리
나. 참조 무결성이 깨질 수 있는 것을 무릅쓰고 반정규화 진행이유
1) DISK I/O 량이 많아서 성능 저하
2) 경로가 너무 멀어 조인으로 인한 성능저하 예상될 경우
3) 칼럼을 계산하여 읽을 때 성능이 저하 될 것이 예상되는 경우
다. 테이블의 중복성, 칼럼의 중복성, 관계중복성 ▶ 조회성능 향상
BUT 입력, 수정, 삭제 성능은 떨어짐
*중복성의 원리를 활용하여 데이터 조회 시 성능을 향상시키는 역할
라. 반정규화 대상 조사 후 다른 방법 적용여부를 검토 후 반정규화 진행
마. 테이블 반정규화
테이블 병합
1:1 관계 테이블 병합
1:M 관계 테이블 병합
슈퍼/서브타입 테이블 병합
테이블 분할
*수직분할
(컬럼단위의 테이블을 DISK I/O 분산처리를 위해 테이블을 1:1로 분리)
*수평분할
(로우 단위의 발생 트랜잭션을 분석하여 DISK I/O 및 데이터 접근 효율을 높여 성능을 향상하기 위해 로우 단위로 테이블을 쪼갬)
테이블추가
중복 테이블 추가
통계 테이블 추가
(SUM, AVG 미리 수행하여 조회 시 성능 향상)
이력 테이블 추가
*부분 테이블 추가
(DISK I/O를 줄이기 위해 해당 컬럼들을 모아 좋은 별도의 반정규화된 테이블 생성)
바. 컬럼 반정규화
1) 중복 칼럼 추가
2) 파생 칼럼 추가
3) 이력 테이블 칼럼 추가
4) PK에 의한 칼럼추가
5) 응용시스템의 오작동을 위한 칼럼 추가
사. 컬럼 반정규화 : 중복관계 추가
38. 대량데이터에 따른 성능
가. 수직분할 : 컬럼단위로 분할하여 디스크 I/O 감소
1) 지나치게 많은 컬럼을 가진 테이블은 수직분할
나. 수평분할 : 로우단위로 분할하여 디스크 I/O 감소
1) RANGE PARTITION : 데이터 보관주기에 따른 테이블 관리 용이
EX) 한해 요금 데이터 → 월별 요금 데이터
2) LIST PARTITION : 특정값에 따라 분리저장할 수 있으나 RANGE PARTITION과 같이 데이터 보관 주기에 따라 쉽게 삭제하는 기능은 불가
EX) 지역을 나타내는 사업소코드별로 리스트테이블화
3) HASH PARTITION : HASH 조건에 따라 해싱 알고리즘이 적용되어 테이블이 분리됨. 테이블에 데이터가 정확하게 어떻게 들어갔는지 알 수 없음. 병목현상 발생시 사용. RANGE PARTITION과 같이 데이터 보관 주기에 따라 쉽게 삭제하는 기능은 불가.
다. 대용량 테이블 발생현상
1) 로우체이닝 : 로우 길이가 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고 두 개 이상의 블록에 걸쳐 하나의 로우가 저장되어 있는 형태
2) 로우 마이그레이션 : 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에서 저장하지 못하고 다른 블록의 빈 공간을 찾아 저장하는 방식
*테이블의 데이터는 BLOCK 단위로 디스크에 저장(하나의 행을 읽더라도
여러 개의 블록을 읽어야함) → BLOCK I/O 발생
*로우체이닝 + 로우마이그레이션이 많이 발생되면 절대적인 BLOCK I/O
횟수가 늘어남. BLOCK I/O가 자주 발생되면 DISK I/O도 자주 발생됨.
DISK I/O가 발생되면 성능이 급격히 저하됨
*데이터 모델링 완성 – 용량산정 – 트랜잭션 처리 패턴 분석 – 컬럼/로우 단위로 집중화된 처리가 발생되는지 분석 후 집중화된 단위로 테이블 분리 검토
39. 슈퍼 타입/서브타입 모델
가. 슈퍼타입 : SINGLE 타입 OR ALL IN ONE 타입이라고 부르기도함.
슈퍼타입 기준으로 처리하는데 개별타입으로 유지되어 UNION 연산에
의해 성능이 저하될 수 있음
EX) 고객 테이블 하나로 구성
나. 서브타입 : 각각의 서브타입으로 테이블 구성. PLUS OR SUPER SUB
라고도 부름. 서브타입을 기준으로 처리하는데 개별타입 혹은 슈퍼
타입으로 되어 있는 경우 성능이 저하되는 경우가 있음
EX) 개인고객, 법인고객 테이블로 구성
다. 개별타입 : ONE TO ONE 타입 기준 또는 1:1 타입 기준이라고도 부름.
개별타입 기준으로 처리하는데 테이블은 슈퍼타입으로 되어 있어 불필요하게
많은 양의 데이터가 집약되어 있어 성능이 저하되는 경우가 있음.
EX) 고객, 개인고객, 법인고객
40. PK/FK 칼럼 순서와 성능
가. PK가 여러 개의 속성으로 구성된 복합식별자 일 때 PK 순서에 대해
별로 고려하지 않고 모델링할 경우 성능이 저하됨(FULL TABLE SCAN)
나. 쿼리문에서 = 조건에 해당하는 필드를 먼저 앞에 지정함
다. 인덱스가 존재하지 않는 경우 조인 시 성능이 매우 안좋을 수 있음.
따라서 상속받은 FK 속성들은 SQL WHERE 절에서 조인으로 이용
되는 경우가 많이 있으므로 FK 인덱스를 생성해야 성능이 좋은 경우가
있음.
41. 절차형 SQL
가. 절차지향적인 프로그램 작성이 가능하도록 절차형 SQL 제공
나. 절차형 모듈 PL/SQL 이라고 부르며 종류는 아래와 같음
1) 프로시저
2) 사용자정의함수 : SUM, AVG, NVL 함수처럼 호출해서 사용. 프로시저처럼
SQL문을 LOGIC과 함께 데이터베이스에 저장해놓은 명령문 집합임
*프로시저와 차이점은 반드시 한건은 되돌려줘야함
3) 트리거 : INSERT, UPDATE, DELTE을 수행할 때 DBMS 내에서 자동으로
동작하도록 작성된 프로그램. DBMS가 자동적으로 수행.
프로시저
트리거
CREATE PROCEDURE 문법
CREATE TRIGGER
EXECUTE/EXEC 명령어
생성 후 자동으로 실행
내부에서 COMMIT/ROLLBACK 실행가능
내부에서 COMMIT/ROLLBACK 실행불가
다. PL/SQL 구조
1) BLOCK 구조 – 모듈화 가능
DECLARE(선언부)
필수
인수정의/데이터타입 선언
BEGIN(실행부)
필수
처리하고자하는 SQL문과 LOGIC 정의 실행부
*EXEPTION(예외 처리부)
선택
BEGIN~END에서 발생 에러 처리부
END
필수
-
2) BLOCK 내 SQL문, IF, LOOF 등이 존재
3) 네트워크 패킷 수를 감소시킴
42. 옵티마이저와 실행계획 EX)네비게이션
가. 옵티마이저 : 사용자가 질의한 SQL에 대한 최적의 실행방법을 결정하는
역할 수행
나. 당쟝한 실행방법들 중 최적의 실행방법을 결정
다. 비용기반 옵티마이저 : 비용(처리 예상시간과 자원)이 가장 적게드는
실행계획을 선택. 테이블 인덱스 등의 통계정보와 시스템 통계정보를
이용하여 최적 실행계획 도출
라. 옵티마이저 구성요소(질비대)
1) 질의 변환기
2) 비용 예측기
3) 대안계획생성기
*실행계획은 위에서 안쪽으로, 바깥쪽에서 안쪽으로 읽는다.
43. 인덱스 기본
가. 검색조건에 부합하는 데이터를 효과적으로(빠르게) 검색
EX) 책의 찾아보기
나. 한테이블에 0~N개 인덱스 가질 수 있으며, 과도하게 많은 인덱스는
INSERT, UPDATE, DELETE와 같은 DML 작업시 부하 발생
다. B*Tree 인덱스 (BALANCE TREE)(가장 일반적)
1) 루트 블록
2) 브랜치 블록
3) 리프 블록 – 키 값 순으로 정렬되어 있으며, 컬럼값과 행의 위치를 가리키는
레코드식별자(ROWID)로 구성,
라. 루프 & 브랜치 블록은 하위 블록에 대한 주소값을 가짐
마. 인덱스 스캔 비효율이 발생하면 덩달아 TABLE RANDOM ACCESS의
횟수도 많아짐(SQLD 튜닝 = RANDOM I/O와의 전쟁)
바. SLINGLE BLOCK I/O = 인덱스 / MULTI BLOCK I/0 = 테이블
44. 조인수행원리
가. NL조인 : 작은집합이 DRIVING되어야하고 INNER 테이블의 인덱스
스캔이 매우 중요
1) 인덱스 구성이 완벽해도 대량 데이터 조인시 불리
2) 한 레코드씩 순차 진행(부분 범위처리 유도해야 효율적 수행)
3) 소량 데이터를 처리하거나 부분범위처리가 가능한 OLTP 환경에 적합
4) RANDAOM ACCESS
나. 소트머지조인
1) 정렬 작업을 생략할 수 있는 인덱스가 존재하는 경우 사용
2) 양쪽 집합을 정렬한 다음에 NL 조인과 같은 오퍼레이션
3) SORT 부하
4) PGA 과다 사용 우려
다. 해시조인
1) 작은 집합을 Build Input으로 하고 큰 집합을 prode input으로 하는
것이 중요
2) OLAP 환경
3) 대량범위 처리 유리