[SQLD] SQL 전문가 가이드 내용 정리

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

 

제1장 데이터 모델링의 이해

제1절 데이터모델의 이해

 

1. 모델링의 이해

가. 모델링의 정의

나. 모델링의 특징

다. 모델링의 세 가지 관점

 

2. 데이터 모델의 기본 개념의 이해

가. 데이터 모델링의 정의

나. 데이터 모델이 제공하는 기능

- 가시화, 명세화, 구조화, 문서화, 다양한 관점, 구체화된 상세 수준의 표현방법 제공

 

3. 데이터 모델링의 중요성 및 유의점

가. 파급효과(Leverage)

시스템 구축이 완성도어 가는 시점에 불가피한 데이터 구조의 변경으로 인한 일련의 변경작업은 시스템 구축에서 큰 위험요소

나. 복잡한 정보 요구사항의 간결한표현(Conciseness)

데이터 모델은 건축물로 비유하자면 설계 도면에 해당. 시스템을 구축하는 많은 관련자들이 설계자의 생각대로 정보요구사항을 이해하고 이를 운용할 수 있는 애플리케이션을 개발하고 데이터 정합성을 유지.

다. 데이터 품질(Data Quality)

오랜 기간 숙성된 데이터를 전략적으로 활요아려고 하는 시점에 문제ㅏㄱ 대두됨.

중복 데이터의 미정의, 데이터 구조의 비즈니스정의의 불충분, 동일한 성격의 데이터를 통합하지 않고 분리함으로써 나타나는 데이터 불일치 등의 데이터 구조의 문제로 인한 데이터 품질의 문제는 치유하기에 불가능한 경우가 대부분

 - 중복(Dulication)

 - 비유연성(Inflexibility) : 데이터 정의를 데이터의 사용 프로세스와 분리함으로써 데이터 모델링은 데이터 혹은 프로세스의 작은 변화가 애플리케이션과 데이터베이스에 중대한 변화를 일으킬 수 있는 가능성을 줄인다

 - 비일관성(Inconsistency) : 데이터의 중복이 없더라도 비일관성은 발생한다. 데이터 모델링을 할때 데이터와 데이터간 상호 연관 관계에 대한 명확한 정의는 이러한 위험을 사전에 예방할 수 있도록 해준다.

 

4. 데이터 모델링으 ㅣ3단계 진행

가. 개념적 데이터 모델링(Concptual Data Modeling)

나. 논리적 데이터 모델링(Logical Data Modeling)

다. 물리적 데이터모델링(Physical Data Modeling)

 

5. 프로젝트 생명주기에서 데이터모델링

 

6. 데이터 모델링에서 데이터독립성의 이해

가. 데이터독립성의 필요성

- 지속적으로 증가하는 유지보수 비용을 절감하고 데이터 복잡도를낮추며 중복된 데이터를 줄이기 위한 목적이 있다. 또한 끊임없이 요구되는 사용자 요구사항에 대해 화면과 데이터베이스 간에 서로 독립성을 유지하기 위한목적으로 데이터 독립성 개념이 출현했다고 할 수 있다.

- three-schema architecture

- 데이터 독립성을 확보하게 되면 다음과 같은 효과를 얻을 수 있다

   => 각 view의 독립성을 유지하고 계층별 view에 영향을 주지 않고 변경이 가는하다.

   => 단계별 스키마에 따라 데이터 정의어와 데이터 조작어가 다름을 제공한다.

나. 데이터베이스 3단계 구조

 - ANSI/SPARC의 3단계 구성의 데이터 독립성 모델

 - 외부 단계

 - 개념적 단계

 - 내부적 단계

다. 데이터 독립성 요소

 - 데이터베이스 스키마 구조는 3단계로구분되고 상호 독립저긴 의미를 가지고 고유한 기능을 가진다.

라. 두 영역의 데이터 독립성

 - 논리적 독립성

 - 물리적 독립성

 - 논리적인 데이터 독립성은 외부의 변경에도 개념 스키마가 변하지 않음. 새로운 요건이 추가되거나 삭제될 경우 컬럼이 변형될 수 있지만 그러한 변화가 개별 화면이나 프로세스에 의해 변화된다기 보다는 전체 업무적인 요건을 고려하여 종합적으로영향을 받음을의미

마. 사상(Mapping)

 - 상호 독립적인 개념을 연결 시켜줌

 - 논리적 사상(외부적/개념적 사상) : 외부적 뷰와 개념적 뷰의 상호 관련성을 정의함

 - 물리적 사상(개념적/내부적 사상) : 개념적 뷰와 저장된 데이터베이스의 상호관련성을 정의함

 

7. 데이터 모델링의 중요한 세 가지 개념

가. 데이터 모델링의 세 가지 요소

1) 업무가 관여하는어떤 것(Things)

2) 어떤 것이 가지는 성격(Attributes)

3) 업무가 관여하는 어떤 것 간의 관계(Relationships)

나. 단수와 집합(복수)의 명명

 

8. 데이터 모델링의 이해관계자

가. 이해관계자의 데이터 모델링 중요성 인식

나. 데이터모델링의 이해관계자

 

9. 데이터 모델의 표기법인 ERD의 이해

가. 데이터 모델 표기법

- 바커(Barker) 표기법

- IE(Information Engineering) 표기법

나. ERD(Entity Relationship Diagram) 표기법을 이용하여 모델링하는 방법

1) ERD작업 순서

엔티티를 그린다 -> 엔티티를 적절하게 배치한다 -> ㅇ티티간 관계를 설정한다 -> 관계명을 기술한다 -> 관계의 필수여부를 기술한다

2) 엔티티 배치

3) ERD 관계의 연결

4) ERD 관계명의 표시

5) ERD 관계차수와 선택성 표시

- 일대다등등 표시하는 것

 

10. 좋은 데이터 모델의 요소

가. 완전성(Completeness)

- 어무에서 필요로 하는 모든 데이터가 데이터 모델에 정의되어 있어야 함.

나. 중복배제(Non-Redundancy)

- 나이, 생년월일은 데이터 중복이라 볼 수 있음

다. 업무규칙(Business Rule)

라. 데이터 재사용(Data Reusability)

마. 의사소통(Communication)

라. 통합성(Integration)

 

제2절 엔터티(Entity)

 

1. 엔티티의 개념

- 실체, 실체

- 업무에 필요하고 유용한 정보를 저장하고 관리하기 위한 집합적인 것(Thing)

- 엔티티는 업무 활동상 지속적인 관심을 가지고 있어야하는 대상, 그 대상들 간에 동질성을 지닌 인스턴스 들이나 그들이 행하는 행위의 집합이다.

- 엔티티는 그 집합에 속하는 개체들의특성인 속성(Attribute)를 가짐

- 과목 : 엔티티, 수학영어과학 : 인스턴스

 

2. 엔티티와 인스턴스에 대한 내용과 표기법

 

3. 엔티티의 특징

가. 업무에서 필요로하는 정보

나. 식별 가능

다. 인스턴스의 집합

- 엔티티의 특징 중 한개가 아니라 두 개 이상.

라. 업무프로세스에 의해 이용

마. 속성을 포함

- 엔티티에는 속성이 포함되어야 한다.

바. 최소 한개이상의 관계가 존재

 

4. 엔티티의 분류

가. 유무형에 따른 분류

- 유형엔티티, 개념엔티티, 사건 엔티티

나. 발생시점에 따른 분류

- 기본/키 엔티티, 중심엔티티, 행위엔티티

다. 엔티티 분류 방법의 예

 

5. 엔티티의 명명

- 현업에서 사용하는 용어 사용

- 약어 사용하지 않음

- 단수명사 사용

- 모든 엔티티에 유일하게 이름 부여

- 엔티티의 생성 의미대로 이름 부여

 

제3절 속성(Attribute)

1. 속성(Attribute)의 개념

2. 엔티티, 인스턴스와 속성, 속성값에 대한 내용과 표기법

가. 엔티티, ㅣㄴ스턴스, 속성, 속성값의 관계

나. 속성의 표기버

3. 속성의 특징

4. 속성의 분류

가. 속성의 특성에 따른 분류

1) 기본 속성

2) 설계 속성

3) 파생 속성

나. 엔티티 구성방식에 따른 분류

5. 도메인

6. 속성의 명명

 

제4절 관계(Relationship)

1. 관계으 개념

가. 관계의 정의

나. 관계의패어링

 

2. 관계의 분류

- 연관관계 : 항상 이용하는 관계

- 의존관계 : 상대방 클래스의 행위에 의해 관계가 형성될 때 구분하여 표현

 

3. 관계의 표기법

가. 관계명 : 관계의 이름

나. 관계차수(Cardinality) : 1:1, 1:M, N:M

다. 관계선택사양(Optionality) : 필수관계, 선택관계

 

4. 관계의 정의 및 읽는 방법

가. 관계 체크사항

나. 관계 읽기

[그림 I-1-39] 관계 읽는 방법

[eunhye] 그림 1-39 봐두면 편리할듯

 

제5절 식별자

1.식별자 계념

- 하나의 엔티티에 구성되어 있는 여러 개의 속성 중에 엔티티를 대표할 수 있는 속성

- 보통 식별자와 키(Key)를 동일하게 생각하는 경우가 있는데 식별자라는 용어는 업무적으로 구분이 되는 정보로 생각할 수 있으므로 논리 데이터 모델링 단계에서 사용하고, 키는 데이터베이스 테이블에 접근을 위한 매개체로서 물리 데이터 모델링 단계에서 사용한다.

 

2. 식별자의 특징

- [표 I-1-7]주식별자의 특징

- 유일성, 최소성, 불변성, 존재성

 

3. 식별자 분류및 표기법

가. 식별자 분류

- [표 I-1-8] 식별자의 분류체계

- 대표성 여부 : 주식별자, 보조식별자

- 스스로 생성 여부 : 내부식별자/외부식별자

- 속성의 수 : 단일식별자/복합식별자

- 대체여부 : 본질식별자/인조식별자

나. 식별자 표기법

 

4. 주식별자 도출기준

가. 해당 업무에서 자주 이용되는 속성을 주식별자로지정하도록 함

나. 명칭, 내역등과 같이 이름으로 기술되는 것은 피함

- 한 회사에 부서이름이 100개가 있다고 할 때, 각각의 부서이름은 유일하게 구별될 수 있다고하여 부서이름을 주식별자로 지정하지 않도록 한다. 부서이름을 주식별자로 선정하면 실제 데이터를 읽을 때 항상 부서이름이 where절에 기술되는 현상이 발생된다. 부서이름은 많은 경우 20자 이상이 될 수 있으므로 조건절에 정확한 부서이름을 기술하기는 쉬운 일이아니다. 이와 같이 명칭이나 내역이 있고 인스턴스들을 식별할 수 있는 다른 구분자가 존재하지 않을 경우는 새로운 식별자를 생성하도록 한다. 보통 일련번호와 코드를많이 사용한다.

다. 속성의 수가 많아지지 않도록 함

 

5. 식별자 관계와 비식별자 관계에 따른 식별자

가. 실별자관계와 비식별자 관계의 설정

나. 식별자관계

자식엔터티의 주식별자로 부모의 주식별자가 상속이 되는 경우

다. 비식별자관계

- 자식엔티티에서 받은 속성이 반드시 필수가 아니어도 무방하기 때문에 부모 없는 자식이 생성될 ㅅ ㅜ있는 경우

- 엔티티별로 데이터의 생명주기를 다르게 관리할 경우, 부모엔티티에인스턴스가 자식의 엔티티와 관계를 가지고 있었지만 자식만 남겨두고 먼저 소멸될 수 있는 경우

- 여러 개의 엔티티가 하나의 엔티티로 통합되어 표현되었는데 각각의 엔티티가 별도의 관계를 가질 때

- 자식 엔티티에 주식별자로 사용하여도 되지만 자식 엔티티에서 별도의 주식별자를 생성하는 것이 더 유리할때

라. 식별자 관계로만 설정할 경우의 문제점

- 부모에서 자식으로 식별자 관계로연결되므로 인해 주식별자의 속성 수가 많아지게 된다.

- 여러 테이블과 관계가 있는 조회 SQL을 작성하는 경우 개발자 복잡성과 오류가능성을 유발

마. 비식별자 관계로만 설정할 경우의 문제점

- 자식 엔티티로 상속이 되지 않아 자식엔티티에서 데이터를 처리할 때 쓸데없이 부모엔티티까지 찾아가야 함

바. 식별자 관계와 비식별자관계 모델링

1) 비식별자관계 선택 프로세스

관계분석 -> 관계의 강/약 분석(약한 관계) -> 자식테이블 독랍 PK필요(독립 PK구성) -> SQL 복잡도 증가, 개발생산성저하 (PK속성 단순화)

2) 식별자와 비식별자관계 비교

[표 I-1-10]

3) 식별자와 비식별자를 적용한 데이터 모델

 

제2장 데이터 모델과 성능

제1절 성능 데이터 모델링의 개요

제2절 정규화와 성능

제3절 반정규화와 성능

제4절 대량 데이터에 따른 성능

제5절 데이터베이스 구조와 성능

제6절 분산 데이터베이스와 성능

 

 

과목2 SQL 기본 및 활용

 

제1절 SQL 기본

 

제1절 관계형 데이터베이스 개요

제2절 DDL(DATA DEFINITION LANGUAGE)

제3절 DML(DATA MANIPULATION LANGUAGE)

제4절 TCL(TRANSACTION CONTROL LANGUAGE)

제5절 WHERE 절

제6절 함수(FUNCTION)

제7절 GROUP BY, HAVING 절

제8절 ORDER BY 절

제9절 조인(JOIN)

 

제2절 SQL 활용

제1절 표준 조인(STANDARD JOIN)

 

1. STANDARD SQL 개요

가. 일반 집합 연산자

<일반 집합 연산자와 현재의 SQL 비교>

1) UNION 연산은 UNION 기능으로

2) INTERSECTION 연산은 INTERSECT 기능으로

3) DEFFERENCE 연산은 EXCEPT(Oracle은 MINUS) 기능으로

4) PRODUCT 연산은 CROSS JOIN 기능으로 구현

나. 순수 관계 연산자

<순수 관계 연산자와 현재의 SQL 비교>

1) SELECT 연산은 WHERE 절로

2) PROJECT 연산은 SELECT 절로

3). (NATURAL) JOIN 연산은 다양한 JOIN으로

4) DIVIDE 연산은 혅 사용되지 않음

 

2. FROM 절 JOIN 형태

<ANSI/ISO SQL에서 표시하는 FROM절의 JOIN 형태>

- INNER JOIN

- NATURAL JOIN

-USING 조건절

-ON 조건절

-CROSS JOIN

-OUTER JOIN

 

3. INNER JOIN

- USING 조건절이나 ON 조건절을 필수적으로 사용

 

4. NAUTAL JOIN

- 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN 수행

- USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.

- JOIN에 사용된 컬럼들은 같은 데이터 유형이여야 하며, ALIAS나 테이블 명과 같은 접수다를 붙일 수 없다.

- NATURAL JOIN은 JOIN에 사용된 같은 이름의 컬럼을 하나로 처리하지만, INNER JOIN의 경우는 2개의 칼럼으로 표시된다.

 

5. USING 조건절

- 원하는 컬럼에 대해서만 선택적으로 EQUI JOIN

- USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 컬럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

 

6. ON 조건절

- ON 조건절을 사용한 JOIN의 경우는 ALIAS나 테이블 명과 같은 접두사를 사용하여 SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 한다.

가. WHERE 절과의 혼용

나. ON 조건절 + 데이터 검증 조건 추가

- ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수는 있으나, 검색 조건 목적인 경우는 WHERE 절을 사용할 것을 권고한다. 다만, OUTER JOIN에서 조인의 대상을 제한하기 위한 목적으로 사용되는 추가 조건의 경우는 ON 절에 표기되어야 한다.

다. ON 조건절 예제

라. 다중 테이블 JOIN

 

7. CROSS JOIN

- 모든 데이터의 조합

- NATURAL JOIN의 경우 WHERE 절에서 JOIN 조건을 추가할 수 없지만, CROSS JOIN의 경우 WHERE 절에 JOIN 조건을 추가할 수 있다. 그러나, 이 경우는 INNER JOIN과 같은 결과를 얻기 때문에 CROSS JOIN을 사용하는 의미가 없어지므로 권고하지 않는다.

 

8 OUTER JOIN

- JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용

가. LEFT OUTER JOIN

나. RIGHT OUTER JOIN

다. FULL OUTER JOIN

- RIGHT OUTER JOIN 과 LEFT OUTER JOIN의 합집합

- UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제

 

9. INNER vs OUTER vs CROSS JOIN 비교

 

제2절 집합 연산자

 

<집합 연산자의 종류>

1) UNION : 중복제거된 합집합

2) UNION ALL : 합집합

3) INTERSECT : 교집합

4) EXCEPT : 차집합

 

- 어떤 형태의 SELECT 문이라도 집합 연산자 사용 가능

- 여러 개의 SELECT문을연결

- ORDER BY는 집합 연산을 적용한 최종 결과에 대한 정렬

 

제3절 계층형 질의와 셀프 조인

제4절 서브쿼리

* 서브쿼리란 하나의 SQL문안에 포함되어있는 또 다른 SQL문

* 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만, 메인쿼리는 서브쿼리의 컬럼을 사용할 수 없다.

* 서브쿼리에서는 ORDER BY 절을 사용할수 없다.

<동작 방식에 따른 서브 쿼리>

- Un-Correlated(비연관) 서브쿼리 : 서브쿼리가 메인퀄컬럼을 가지고 있지 않는 형태의 서브쿼리. 메인쿼리에 값(서브쿼리가 실행된 결과)를 제공하기 위한 목적으로 주로 사용된다.

- Correlated(연관) 서브쿼리 : 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태의 서브쿼리. 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할때 주로 사용.

<반환되는 데이터의 형태에따른 서브쿼리>

- Single Row 서브쿼리

- Multi Row 서브쿼리

- Multi Column (다중 컬럼)서브쿼리

1. 단일 행 서브쿼리

2. 다중 행 서브쿼리

- 다중 행 비교 연산자와 함께 사용 : IN, ALL, ANY, EXISTS

3. 다중 칼럼 서브쿼리

- 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수가 있다. 서브쿼리와메인쿼리에서 비교하고자 하는컬럼 개수와 컬럼의 위치가 동일해야한다.

4. 연관 서브쿼리

5. 그밖에 위치에서 사용하는 서브쿼리

가. SELECT 절에 서브쿼리 사용하기

- Scalar Subquery : 한 행, 한 칼럼만 반환하는 서브쿼리

나. FROM 절에 서브쿼리 사용하기

- Inline View : SQL문이 실행될때만 임시적으로 생성되는 동적인 뷰, 데이터베이스에 해당 정보가 저장되지 않는다.

- 인라인 뷰는 동적으로 생성된 테이블이다. 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다. 서브쿼리의 칼럼은 메인쿼리에서 사용할 수 없지만인라인 뷰의 컬럼은 sql문 자유롭게 참조할 수 있다.

다. HAVING 절에 서브쿼리 사용하기

라. UPDATE문의 SET 절에서 사용하기

마. INSERT문의 VALUES절에서 사용하기

 

6. 뷰(View)

* 뷰는 실제 데이터를 가지고 있지 않으며 뷰 정의(View Definition)만을 가지고 있다. 질으에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성하여 질의를 수행한다.

* 뷰 사용의 장접 : 독립성, 편리성, 보안성

제5절 그룹 함수

1. 데이터분석 개요

- AGGREGATE FUNCTION

- GROUP FUNCTION

- WINDOW FUCTION

2. ROLLUP 함수

3. CUBE 함수

4. GROUPING SETS 함수

제6절 윈도우 함수

1. WINDOW FUNCTION 개요

2. 그룹 내 순위함수

가. RANK 함수

나. DESNSE_RANK 함수

다. ROW_NUMBER 함수

3. 일반 집계 함수

가. SUM 함수

나. MAX 함수

다. MIN 함수

라. AVG 함수

마. COUNT 함수

4. 그룹 내 행 순서 함수

가. FIRST_VALUE 함수

나. LAST_VALUE 함수

다. LAG 함수 : 이전 몇 번째 행

라. LEAD 함수 : 이후 몇 번째 행

5. 그룹 내 비율 함수

가. RATIO_TO_REPORT 함수 : 파티션 내 전체 SUM 값에 대한 행렬 칼럼 값의 백분율을 소수점으로 나타냄

나. PERCENT_RANK 함수 : 값이 아닌 행의 순서별 백분율

다. CUME_DIST 함수 : 누적백분율

라. NTILE 함수 : 전체 건수를 N 등분한 결과

제7절 DCL(DATA CONTROL LANGUAGE)

제8절 절차형 SQL

 

 

제3절 SQL 최적화 기본 원리

제1절 옵티마이저와 실행계획

1. 옵티마이저

* 사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정

* 실행 계획 = 최적의 실행 방법 

* 규칙기반 옵티마이저(RBO, Rule Based Optimizer), 비용기반 옵티마이저(CBO, Cost Based Optimizer)

가. 규칙기반 옵티마이저

 <규칙기반 옵티마이저가 실행계획을 생성할 때 참조하는 정보>

  - 이용가능한 인덱스 유무 종류 : 유일, 비유일, 단일, 복합 인덱스

  - 연산자 종류 : =, <,<>,LIKE,BETWEEN

  - 참조하는 객체 : 힙 테이블, 클러스터 테이블

 <규칙기반 옵티마이저의 실행계획 우선 순위>

  1순위. Single row by row id

   - row id를 통해서 테이블에서 하나의 행을 엑세스

  2순위. Single row by cluster join

  3순위. Single row by hash cluster key with unique or primary key

  4순위. Single row by unique or primary key

   - 유일 인덱스(Unique Index)를 통해서 하나의 행을 엑세스.

     인덱스를 먼저 엑세스하고, 인덱스에 존재하는 row id를 추출하여 테이블의 행을 엑세스

  5순위. Cluster join

  6순위. Hash Cluster key

  7순위. Indexed Cluster key

  8순위. Composite index

   - 복합 인덱스에 동등 조건으로 검색

     A+B 컬럼으로 복합 인덱스, A=10 and B=1로 검색하는 경우

  9순위. Single column index

    - 단일 컬럼 인덱스에 동등 조건으로 검색

     A 컬럼 인덱스, A=10으로 검색하는 경우

  10순위. Bouned range search on indexed columns

    - 인덱스가 생성되어 있는 컬럼ㅇ 양쪽 범위를 한정하는 형태

     A컬럼 인덱스, A BETWEEN 10 AND 20의 경우

  11순위. Unbounded range search on indexed columns

    - 인덱스가 생성되어 있는 컬럼에 한쪽 범위만 한정하는 형태

     A컬럼 인덱스, A>10 (>,>=,<,<=)

  12순위. Sort merge join

  13순위. Max or Min of indexed column

  14순위. ORDER BY on indexed column

  15순위. Full table scan

* 규칙 기반 옵티마이저는 해당 SQL문에서 이용 가능한 인덱스가 존재한다면 전체 테이블 엑세스 방식보다는 항상 인덱스를 사용하는 실행계획을 생성

두테이블을 조인할때

* 모두 인덱스가 존재하지 않으면, FROM절의 뒤에 나열된 테이블을 선행 테이블로 선택     => Sort Merge Join

* 한쪽 조인 컬럼에만 인덱스가 존재하는 경우, 인덱스가 없는 테이블을 선행 테이블로 선택해서 조인  => NL Join

* 양쪽 테이블에 모두 존재하면, 우선 순위가 높은 테이블을 선행테이블로 선택     => NL Join

* 선행 테이블(=Driving Table = Outer Table) <=> Lookup Table(=Inner Table)

나. 비용 기반 옵티마이저

* 질의 변환기 : 사용자가 작성한 SQL문을 처리하기에 용이한 형태로 변환

* 대안 계획 생성기 : 동일한 결과를 생성하는 다양한 대안 계획을 생성

   - 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경

* 비용 예측기 : 대안 계획 생성기에 의해 생성된 대안 계획의 비용 예측

* 비용기반 옵티마이저는 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체테이블 스캔을 수행하는 방법으로 실행 계획을 생성할 수도 있다. (인덱스를 무조건 사용하지 않음)

2. 실행 계획

* SQL에서 요구한 사항을 처리하기 위한 절차와 방법

* 실행 계획을 생성한다 = SQL을 어떤 순서로어떻게 실행할지를 결정한다.

<실행 계획을 구성하는 요소>

  1) Join Order (조인 순서)

    - 조인을 수행할 때 참조하는 테이블의순서

    - From 절에 n개의 테이블이 존재할때 조인순서는 n!만큼 가능

  2) Join Method(조인 기법)

    - 조인할 때 사용할 수 있는 기법

    - NL Join, Hash Join, Sort Merge Join

  3) Access Method(액세스 기법)

    - 하나의 테이블을액세스할 때 사용할 수 있는 방법

    - 인덱스 스캔, 전체테이블 스캔

  4) Optimazation Information

    - 실행 계획의 각 단계마다 예상되는 비용사항 표시

    - cost : 상대적인 비용 정보

    - card : cardinality, 조건을 만족한 결과 혹은 조건을 만족한 결과 집합 건수

    - bytes : 결과 집합이 차지하는 메모리 양(Byte)

  5) Operation(연산)

    - 여러가지 조작을통해서 원하는 결과를 얻어내는 일련의 작업

    - 조인기법, 액세스기법, 필터, 정렬, 집계, 뷰 등

3. SQL 처리 흐름도

[그림 2-3-5] SQL 처리 흐름도 참고

SELECT ...

  FROM TAB1 A, TAB2 B

 WHERE A.COL1 = :condition1

    AND B.COL2 = :condition2

[그림 2-3-5]의 처리 흐름도가 위의 SQL문을 나타낸 것이라고 할떄

1) 엑세스 건수 = TAB1 테이블의 총 건수

   TAB1의 A.COL1 컬럼에 이용가능한 인덱스가 존재하지않아 전체 테이블 스캔을 수행했음.

2) 조인시도 건수 = TAB1에서 A.COL1 =:condition1을 만족하는 건수

   TAB1을 액세스한 후 테이블에서 읽은 해당 건에 대해 A.COL1=:condition1 조건을 만족한 건만이 TAB2와 조인을 시도한다.

3) 테이블 액세스 건수 = 조인시도 건수 중 B.KEY = A.KEY를 만족한 건수

   B.KEY 컬럼만으로 구성된 인덱스인 I01_TAB2에서 B.KEY=A.KEY를 만족한 건만이 TAB2 테이블에 엑세스

   TAB2는 이미 읽혀졌기 때문에 A.KEY의 값은 상수

4) 성공 건수 = SQL 실행을 통해 사용자에게 답으로 보여지는 결과 건수

제2절 인덱스 기본

1. 인덱스 특징과 종류

* 인덱스는 테이블을 기반으로 선택적으로 생성

* 목적 : 검색 성능의 최적화

* insert, update, delete 등과 같은 DML 작업은 테이블과 인덱스를 함께 변경해야하기 때문에 오히려 느려질 수 있다.

가. 트리기반 인덱스

- DBMS에서 가장 일반적인 인덱스

- Roof Block : 브랜치 블록 중에서 가장 상위에 있는 블록

- Branch Block : 분기를 목적으로 하는 블록, 다음 단계의 블록을 가리키는 포인터를 가짐

- Leaf Block : 트리의 가장 아래에 존재, 인덱스를 구성하는 컬럼의 데이터

인덱스 데이터는 인덱스를 구성하는 컬럼의 값으로 정렬, 인덱스 값이 동일하면 레코드 식별자 순서로 저장

양방향 링크(Double Link) : 정렬을 쉽게 할 수 있음

- B-트리 인덱스는 일치검색(exact match)과 범위검색 모두에 적합

<인덱스에서 값을 찾는 방법>

1단계. 브랜치 블록의 가장 왼쪽 값이 찾고자하는 값보다 작거나 같으면 왼쪽 포인터로 이동

2단계. 찾고자하는 값이 브랜치 블록의 값 사이에 있으면 가운데 포인터로 이동

3단계. 찾고자하는 값이오른쪽에 있는 값보다 크면 오른쪽으로 이동

- 인덱스 구성컬럼은 동일하지만 컬럼 순서가 다르면 서로 다른 인덱스 생성 (JOB+SAL != SAL+JOB)

- ORACLE의 트리기반 인덱스 : B-트리 인덱스, 비트맵 인덱스, 리버스키 인덱스, 함수기반 인덱스

나. SQL Server의 클러스터형 인덱스

* SQL Server의 인덱스 종류는 저장 구조에 따라 클러스터형 인덱스와 비클러스터형 인덱스로 구분

<클러스터형 인덱스>  [그림 2-3-8]

1) 인덱스의 리프 페이지가 데이터 페이지다. 

  - 사전 : 알파벳 순으로 정렬된 사전에서 알파벳으로 단어를 찾으면 단어와 설명이 함께 있다.

  - 트리구조 인덱스 예시 : 전문서적 끝부분의 색인은 내용의 위치를 알려주고, 위치를 직접 찾아서 내용을 봐야한다.

2) 리프 페이지의 모든 로우는 키 컬럼 순으로정렬되어 물리적으로 저장된다. 테이블 로우는 물리적으로 한가지 순서로만 정렬될 수 있다. 클러스터형 인덱스는 테이블당 한개만 생성할 수있다.

  - 전화번호부 한권을 상호와 번호로 동시에 정렬할 수 없음.

2. 전체 테이블 스캔과 인덱스 스캔

가. 전체 테이블 스캔

 - 테이블에 존재하는 모든 데이터를 읽어가면서 조건에 맞으면 결과로서 추출하고 맞지 않으며 버리는 방식

 - 오라클의 경우 고수위 마크(HWM, High Water Mark) 아래의 모든 블록을 읽는다.

 - 고수위 마크 : 테이블에 데이터가 쓰여졌던 블록상의 최상위 위치 (현재는 지워져서 데이터가 존재하지 않을 수도 있음.)

<옵티마이저가 연산으로서 전체 테이블 스캔 방식을 선택하는 경우>

1) SQL문에 조건이 존재하지 않는 경우

2) SQL문의 주어진 조건에 사ㅛㅇ가능한 인덱스가 존재하지 않는 경우

  - 사용가능한 인덱스가 존재하지 않는다면 데이터를 엑세스 할 수 있는 방법은 텡블의 모든 데이터를 읽으면서 주어진 조건을 만족하는지ㅡㄹ 검사하는 방법 뿐

  - 주어진 조건에 사용가능한 인덱스는 존재하나 함수를 사용하여 인덱스 컬럼을 변형한 경우에도 인덳 사용 불가

3) 그밖의 경우

  - 병렬 처리 방식

  - 전체 테이블 스캔 방식의힌트를 사용한 경우

나. 인덱스 스캔 

- 인덱스를 구성하는 컬럼의 값을 기반으로 데이터를추출

- 인덱의 리프 블록을 읽으면 인덱스 구성 컬럼의 값과 테이블 레코드 식별자를 알 수 있다.

- SQL문에서 필요한 모든 컬럼이 인덱스 구성 컬럼에포함된 경우 테이블에 대한 액세스는 발생하지 않는다.

- 인덱스를 경유하여 데이터ㅡㄹ 읽으면 사용자가 원하는 정렬 순서와 인덱스의 순서가 동일한 경우 정렬 작업을 하지 않아도 된다.

<인덱스 스캔 방식>

1) 인덱스 유일 스캔

  - 유일 인덱스를 사용하여 단 하나의 데이터를 추출

2) 인덱스 범위 스캔, 인덱스 역순 범위 스캔

  - 인덱스를 이용하여 한건이상의 데이터를 추출하ㅡㄴ 방식

  - 유일 인덱스의 구성컬럼 모두에 대해 =로 값이 주어지지 않은 경우, 비유일 인덱스를 이용하는 모든 경우의 액세스 방식

3) 기타 방식

  - 인덱스 전체 스캐, 인덱스 고속 전체 스캔, 인덱스 스킵 스캔

다. 전체 테이블 스캔과 인덱스 스캔 방식의 비교

- 인덱스 스캔 : 인덱스에 존재하는 레코드 식별자를이용해서 검색하는 데이터의 정확한 위치를 읽어 데이터를 일근다. 불필요하게 다른 블록을 읽을 필요 없음. => 한번의 I/O에 한 블록을 읽음

- 전체 테이블 스캔 : 한번의 I/O 요청에 여러 블록을 한꺼번에 읽음

- 어짜피 테이블의 모든 데이터를 읽을 것이라면 전체 테이블 스캔 방식이 유리할 수 있음

제3절 조인 수행 원리

 

 

 

 

 

 

 

---

* 1차 정규형

 - 모든 속성은 반드시 하나의 값을 가져야 한다. 즉, 반복 형태가 있어서는 안된다.

 - 각 속성의 모든 값은 동일한 형태이어야 한다.

 - 각 속성들은 유일한 이름을 가져야한다.

 - 레코드들은 서로 간에 식별 가능해야한다.

* 2차 정규형

 - 식별자가 아닌 모든 속성들은 식별자 전체 속성에 완전 종속되어야한다.

 - 기본키가 아닌 모든컬럼들이 기본키에 종속적이어야 2차 정규형을 만족할 수 있다.

 - 부분 함수종속성이 있으면안된다.

* 3차 정규형

 - 2차 정규형을 만족하고, 식별자를 제외한 나머지 속성들 간의 종속이 존재하면 안된다.

* ANSI/ISO 표준 SQL에서 표시하는 FROM 절의 JOIN 형태

 - INNER JOIN, NATURAL JOIN, CROSS JOIN, OUTER JOIN, USING 조건절, ON 조건절

* WINDOW FUNCTION : OVER, PARTITION BY COL

* 모델링의 세가지 관점 : 데이터 관점, 프로세스 관점, 상관관점

* 데이터베이스 3단계 구조

 - 외부스키마 : 응용 프로그래머가 접근하는 DB 정의

 - 개념스키마 : DB에 저장된 데이터와 관계 정의

 - 내부스키마 : DB가 물리적으로 저장된 형식

 - 물리적 독립성, 논리적 독립성

* 속성의 특성에 따른 분류

 - 기본속성, 설계속성, 파생속성

* 엔티ㅣ 구성 방식에 따른 분류

 - PK 속성, FK 속성, 일반 속성

* 식별자의 특징 : 유일성, 최소성, 불변성, 존재성

 - 최소성 : 주식별자를 구성하는 속성의 수ㅡㄴ 유일성을 만족하ㅡㄴ 최소의 수가 되어야함

 - 존재성 : 주식별자가 지정되면 반드시 데이터 값이 존재함(NULL 안됨)

* 식별자의 분류체계

대표성 여부	주식별자
	보조식별자
스스로 생성여부	내부 식별자	엔티티내부에서 스스로 만들어지는 식별자
	외부 식별자	타 엔티티와의 관계를 통해 타 엔티티로부터 받아오는 식별자
속성의 수	단일 식별자
	복합 식별자
대체 여부	본질 식별자	업무에 의해 만들어지는 식별자
	인조 식별자	업무적으로만들어지지는 않지만 원조 식별자가 복잡한 구성을 가지고 있기 때문에 인위적으로 만든 식별자

* 분산 데이터베이스의 투명성

 - 분할투명성(단편화) : 하나으 ㅣ논리적 RELATION이 여러 단편으로 분할되어 각 단편의 사본이 여러 SITE에 저장

 - 위치 ㅜ명성 : 사용하려는 데이터으 ㅣ저장 장소 명시 불필요

 - 지역사상 투명성 : 지역 DBMS와 물리적 DB 사이의 MAPPING 보장

 - 중복 투명성

 - 장애 투명성장애와 무관한 TRANSACTION의 원자성 유지

 - 병행투명성: 다수 TRANSACTION 동시 수형 결과의 일관성 유지

 

* 트랜잭션의 특성

 - 원자성 (atomicity): 트랜잭션에 정의된 연산들을 모두 성공적으로 실행되던지 아니면 전혀 실행되지 않은 상ㅌ로 남아이써야한다. (ALL OR NOTHING)

 - 일관성 (consistency) : 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이휴에도 데이터베이스의 내용에 잘못이 있으면 안된다.

 - 고립성(isolation) : 트랜잭션이 실행되는 도중에 다른트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다

 - 지속성 : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의내용은영구적으로 저장된다ㅣ

* 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

 

 

과목	장	절	읽기	블로그	예제	연습문제	기출문제
과목1	제1장	1	2회	O
		2	2회	O
		3	2회	O
		4	2회	O
		5	2회	O
	제2장	1	2회
		2	2회
		3	2회
		4	2회
		5	2회
		6	2회
과목2	제1장	1	1회
		2	1회
		3	1회
		4	1회
		5	1회
		6	1회
		7	1회
		8	1회
		9	1회
	제2장	1	1회	O
		2	1회	O
		3	1회
		4	1회	O
		5	1회	O
		6	1회	O
		7	1회
		8	1회
	제3장	1	1회	O
		2	1회	O
		3	1회