:: 작업장

9월 5일에 시험을 본 SQLD 합격했습니다.

(네이버 블로그에는 포스팅을 했는데 티스토리는 몰아서올렸네요..ㅎㅎㅎ 티스토리로 정착하고싶은데 아직 네이버 블로그 접속수가 많아서 ㅠㅠ)

회사를 다니며 2~3주가량 준비해서 시험을 준비했는데요. 첫시험에 재응시없이 합격해서 기분이 더 좋네요.

저의 공부방법은 책을보고 블로그에 포스팅하는 방식으로 했습니다. 두번 세번은 못봤고 한번할때 실습까지 하면서 제대로 보려고노력했습니다. 사실 블로그 포스팅하다가 중간단계에서는 너무 시간이 많이걸리는것같아서... 밑줄치며 공부를 했습니다.

음 팁이아닌 팁을 드리자면 연습문제를 무조건 다 풀어보시고 인터넷에 공유되어있는 SQLD/P 기출문제를 보시는것을 추천드립니다. 연습문제랑 거의 똑같은 문제도 나오기 때문이죠...

연습문제랑 기출 꼭 풀어보고가세요!

아무쪼록 시험준비하시는분들은 건승하시길 바랍니다!

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 3절 조인 수행 원리

- 조인이란 두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다.

- SQL문에서 FROM 절에 두 개 이상의 테이블이 나열된 경우 조인이 수행된다.

- 조인 연산은 두 테이블 사이에 수행된다.

- 테이블을 조인할 때는 조인 단계별로 다른 조인 기법을 사용할 수 있다.

( 테이블 A와 테이블 B를 조인할 때는 NL Join 기법을 수행하고, 해당 조인결과를 테이블 C와 조인할 때는 Hash Join 기법을 사용할 수 있다.)

1. NL Join

- 중첩된 반복문과 유사한 방식으로 조인을 수행함.

- 반복문의 외부에 있는 테이블을 선행테이블 또는 Outer table이라고 하고, 반복문 내부에 있는 테이블을 후행 테이블 Inner 테이블 이라고한다.

FOR 선행테이블 읽음 -> 외부테이블

    FOR 후행테이블 읽음 -> 내부테이블

선행 테이블과 후행 테이블을 조인

- 먼저 선행 테이블의 조건에 맞는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다.

(선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행한다)

- NL Join에서는 선행 테이블의 조건을 만족하는 행의 수가 많으면, 그 만큼 후행 테이블의 조인 작업은 반복 수행된다.

 그러므로 테이블 결과 행의 수가 적은 테이블을 조인 순서상 선행 테이블로 선택하는 것이 전체 일량을 줄일 수 있다.

- NL Join의 작업 방법

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음.

2) 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행

3) 선행 테이블의 조건을 만족하는 모든 행에 대해 1번 작업 반복 수행

ex) NL Join 수행 방식

1) 선행 테이블에서 조건에 맞는 첫번째 행을 찾음.

- 이경우 선행테이블에 주어진 조건을 만족하지 않을 경우 해당 데이터는 필터링됨.

2) 선행테이블의 조인 키를 가지고 후행 테이블에 조인 키가 존재하는지 찾으러감

- 조인시도

3) 후행 테이블의 인덱스에 선행 테이블의 조인키가 존재하는지 확인

- 선행 테이블의 조인 값이 후행 테이블에 존재하지 않으면 선행 테이블 데이터는 필터링됨

(더이상 조인 작업을 진행할 필요가 없음)

4) 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 엑세스

- 인덱스 스캔을 통한 테이블 엑세스

후행 테이블에 주어진 조건까지 모두 만족하면 해당 행을 출력버퍼에 넣음.

추출버퍼는 SQL문의 실행결과를 보관하는 버퍼로서 일정 크기를 설정하여 추출버퍼에 결과가 모두 차거나 더 이상 결과가 없어서 추출버퍼를 채울것이 없으면 결과를 반환한다.

추출버퍼는 운반단위, Array Size, Prefetch size라고도 한다.

NL Join 기법은 조인이 성공하면 바로 조인결과를 사용자에게 보여줄 수 있다. 그래서 결과를 가능한 빠르게 보여줘야하는 온라인 프로그램에 적당한 조인 기법이다.

2. Sort Merge Join

- 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행한다.

- NL Join은 랜덤엑세스 방식으로 데이터를 읽는 반면 Sort Merge 조인은 주로 스캔 방식으로 데이터를 읽는다.

- Sort Merge Join은 랜덤엑세스로 NL Join에서 부담이 되던 넓은 범위의 데이터를 처리할 때 이용되는 조인기법이다.  그러나 Sort Merge Join은 정렬할 데이터가 많아 메모리에서 모든 정렬을 수행하기 어려운경우 임시영역을 사용하기 때문에 성능이 떨어질 수 있다.

- 일반적으로 대량의 데이터 조인 작업에서 정렬작업을 필요로 하는 Sort merge join 보다는 CPU 작업 위주로 처리하는 Hash Join이 성능상 유리하다.

- Sort Merge Join은 Hash Join과는 달리 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다는 장점이 있다.

- Sort Merge Join은 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인기법. 

- 조인할 테이블 중에서 이미 앞 단계의 작업을 수행하는 도중에 정렬 작업이 미리 수행되었다면 조인을 위한 정렬 작업은 발생하지 않을 수 있다.

- Sort Merge Join 동작

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음.

2) 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행.

1 ~ 2번 작업을 선행 테이블 조건에 만족하는 모든 행에 대해 반복 수행

3) 후행 테이블에서 주어진 조건에 맞는 행을 찾음.

4) 후행 테이블의 조인 키를 기준으로 정렬 작업 수행

3~4번 작업을 후행 테이블의 조건에 만족하는 모든 행에 대해 반복 수행

5) 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출 버퍼에 넣음.

3. Hash Join

- 해슁 기법을 이용하여 조인을 수행한다.

- 조인을 수행할 테이블의 조인칼럼을 기준으로 해쉬 함수를 수행하여 소로 딩알한 해쉬값을 갖는 것들 사이에 실제 값이 같은지 비교하면서 조인을 수행한다.

- Hash Join은 NL Join의 랜덤 액세스 문제점과 Sort Merge Join의 문제점인 정렬작업의 부담을 해결 위한 대안으로 등장하였다.

- HASH Join은 칼럼의 인덱스를 사용하지 않기 때문에 조인칼럼의 인덱스가 존재하지 않아도 사행 가능하다.

- Hash Join은 해쉬함수를 사용해서 Join을 수행하기 때문에 '='로 수행하는 조인 즉 동든 조인에서만 사용할 수 있다.

- 해쉬함수가 적용될 때 동일한 값은 항상 같은 값으로 해슁됨이 보장된다.

- 해쉬함수를 적용할 때 보다 큰 값이 항상 더 큰 값으로 되고 작은값이 항상 작은 값으로 된다는 보장이 없으므로 동등 조인에만 적용가능하다.

- 해쉬조인은 조인작업을 수행하기 위해 해쉬테이블을 메모리에 생성해야 한다.

- 해쉬조인을 할 때는 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다. (선행테이블의 결과를 완전히 메모리에 저장할 수 있다면 임시 영역에 저장하는 작업이 발생하지 않기 때문)

- Hash Join에서는 선행 테이블을 이용하여 먼저 해쉬 테이블을 생성한다고 해서 선행테이블을 Build Input 이라고도 하며, 후행 테이블은 만들어진 해쉬테이블에 대해 해쉬값의 존재여부를 검사한다고 해서 Prove Input 이라고도 한다.

- Hash Join 동작

1) 선행 테이블에서 주어진 조건을 만족하는 행을 찾음

2) 선행 테이블의 조인키를 기준으로 해쉬 함수를 적용하여 해쉬테이블 생성.

-> 조인칼럼과 SELECT  절에서 필요로 하는 칼럼도 함꼐 저장됨.

1 ~ 2번 작업을 선행테이블의 조건을 만족하는 모든 행에 대해 반복 수행

3) 후행 테이블에서  주어진 조건에 맞는 행을 찾음.

4) 후행 테이블의 조인키를 기준으로 해쉬함수를 적용하여 해당 버킷을 찾음.

-> 조인키를 이용해서 실제 조인될 데이터를 찾음

5) 조인에 성공하면 추출 버퍼에 넣음

3~5번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해서 반복 수행

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 2절 인덱스 기본

1.인덱스 특징과 종류

- 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념.

- 테이블을 기반으로 선택적으로 생성할 수 있는 구조.

- 인덱스의 기본적인 목적은 검색 기능의 최적화이다.

가. 트리기반 인덱스

- DBMS에서 가장 일반적인 인덱스는 B-Tree 인덱스이다.

* B Tree 인덱스

- B Tree 인덱스는 브랜치블록과 리프블록으로 구성된다. 브랜치 블록 중에서 가장 상위에서 있는 블록을 루트 블록 이라고 한다. 브랜치 블록은 분기를 목적으로 하는 블록이다.

- 브랜치 블록은 다음 단계의 블록을 가리키는 포인터를 가지고 있다.

- 리프 블록은 인덱스를 구성하는 칼럼의 데이터와 데이터를 가지고있는 행의 위치를 가리키는 레코드 식별자(RID, Record Identifier/Rowid)로 구성되어 있다.

- 인덱스 데이터는 인덱스를 구성하는 칼럼의 값으로 정렬된다.(데이터의 값이 동일하면 레코드 식별자의 순서로 저장됨)

- 리프 블록은 양방향 링크를 가지고 있다.  이것을 통해 오름 차순과 내림 차순 검색을 쉽게 겁색할 수 있다.

- B Tree 인덱스는 =로 검색하는 일치 검색과 BETWEEN, >, 등과 같은 범위 검색에 모두 적합한 구조이다.

인덱스에서 원하는 값을 찾는 과정.

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

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

3단계: 오른쪽에 있는 값보다 크면 오른쪽 포인터로 이동

이과정을 리프 블록을 찾을 때까지 반복한다. 리프 블록에서 찾고자 하는 값이 존재하면 해당 값을 찾을 것이고, 없으면 해당 값은 존재하지 않아 검색에 실패하게된다.

인덱스를 생성할 때 동일 칼럼으로 구성된 인덱스를 중복해서 생성할 수 없다. 

인덱스 구성칼럼은 동일하지만 순서가 서로 다르면 생성가능하다 ( JOB + SAL, SAL + JOB 가능)

오라클에서 트리 기반 인덱스에는 B-Tree 인덱스 이외에도 비트맵 인덱스, 리버스키 인덱스, 함수기반 인덱스 등이 존재한다.

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

- SQL Server의 인덱스 종류는 저장 구조에 따라 클러스터형 인덱스와 비클러스터형 인덱스로 나뉜다.

클러스터형 인덱스는 두 가지 중요한 특징이있다.

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

- 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없다

(인덱스 키 칼럼과 나머지 칼럼을 리프페이지에 같이 저장하기 때문에 테이블을 랜덤 엑세스할 필요가 없다.)

- 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있다.

2. 리프 페이지의 모든 로우는 인덱스 키 칼럼순으로 물리적으로 정렬되어 저장된다.

- 테이블 로우는 물리적으로 한 가지 순서로만 정렬될 수 있다. 그러므로 클러스터형 인덱스는 테이블당 한 개만 생성할 수 있다.

- 리프블록에 인덱스 키 칼럼 외에도 테이블의 나머지 칼럼이 모두 함께 있다.

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

가. 전체 테이블 스캔

- 전체 테이블 스캔 방식으로 데이터를 검색하는 것은 테이블에 존재하는 모든 데이터를 읽어 가면서 조건에 맞으면 결과로 추출하고 조건에 맞지 않으면 버리는 방식으로 검색한다.

- Oracle의 경우 검색조건에 맞는 데이터를 찾기 위해 테이블의 고수위마크(HWM) 아래의 모든 데이터를 읽는다.

- 고수위 마크는 테이블에 데이터가 쓰여졌던 블록 상의 최상위 위치를 의미한다.

- 전체 테이블 스캔 방식으로 데이터를 검색할 때 고수위 마크까지의 블록 내 모든 데이터를 읽어야 하기 때문에 모든 결과를 찾을 때까지 시간이 오래 걸릴 수 있다.

옵티마이저가 Full table scan을 선택하는 이유는 일반적으로 다음과 같다.

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

- SQL문에 조건이 존재하지 않는다는 것은 테이블에 존재하는 모든데이터가 답이 된다는 의미이므로 테이블의 모든 블록을 읽으면서 무조건 결과로서 반환한다.

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

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

- 또한 사용가능한 인덱스는 존재하나 함수를 사용하여 인덱스 칼럼을 변경한 경우에도 인덱스를 사용할 수 없다.

3) 옵티마이저의 취사 선택

조건을 만족하는 데이터가 많을 경우 결과 데이터를 추출하기위해 테이블의 대부분 블록을 엑세스 한다고 옵티마이저가 판단하면 조건에 맞는 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있다.

4) 그 밖의 경우

- 병렬 처리 방식으로 처리할 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우 전체 테이블 스캔 방식으로 데이터를 읽을 수 있다.

나. 인덱스 스캔

- 인덱스 스캔은 인덱스를 구성하는 칼럼의 값을 기반으로 데이터를 추출하는 엑세스 기법.

- 인덱스는 인덱스 구성 칼럼의 순서로 정렬되어 있다. (A+B 구성의 인덱스일 경우 A 칼럼으로 정렬되고 A칼럼 값이 동일할 경우 B 칼럼으로 정렬된다. B 칼럼까지 동일한경우 RID로 정렬된다.)

- 인덱스 순서와 동일한 정렬 순서를 사용자가 원하는 경우 정렬작업을 수행하지 않을 수 있다.

1) Index Unique Scan (인덱스 유일 스캔)

유일 인덱스를 사용하여 단 하나의 데이터를 추출하는 방식. 유일 인덱스는 중복을 허락하지 않는 인덱스로 유일인덱스 구성 칼럼에 모두 '='로 값이 주어지면 결과는 최대 1건이 된다.

인덱스 유일 스캔은 유일 인덱스 구성칼럼에 대해 모두 '='로 값이 주어진 경우에만 가능한 인덱스 스캔방식이다.

2) Index Range Scan

- 인덱스 범위 스캔은 인덱스를 이용하여 한 건 이상의 데이터를 추출하는 방식

- 유일 인덱스의 구성 컬럼 모두에대해  '='로 값이 주어지지 않는 경우와 비유일 인덱스를 이용하여 모든 엑세스 방식은 인덱스 범위 스캔 방식으로 데이터를 엑세스 하는것이다.

3) 인덱스 역순 범위스캔은 리프블록의 양방향 링크를 통해 내림차순으로 데이터를 읽는 방식. 이 방식을 이용하여 Max Value를 쉽게 찾을 수 있다.

다. Full Table scan과 Index Scan 방식의 비용.

- 인덱스 스캔 방식은 사용가능한 적절한 인덱스가 있을 경우에만 이용할 수 있는 스캔방식 이지만 전체 테이블 스캔 방식은 인덱스의 존재 유무와 상관없이 항상 이용가능한 스캔방식.

- 인덱스 스캔은 인덱스에 존재하는 레코드 식별자를 이용해서 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽는다. 그러므로 불필요하게 다른 블록을 더 읽을 필요가 없다.

 따라서 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

- 전체 테이블 스캔은 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽는다.

* 데이터를 찾을 때 여러 대부분의 데이터를 읽을 거라면 한번에 여러 블록씩 읽는 전체 테이블 스캔 방식이 유리할 수 있다.

[SQLD] 2과목 SQL 기본및 활용 - 3장 SQL 최적화 기본 원리 1절SQL 옵티마이져와 실행계획

1.옵티마이져

- 옵티마이저는 사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할을 함

- 최적의 실행방법을 실행계획이라고함.

- 다양한 실행 방법들 중 최적의 실행 방법을 결정하는 것이 바로 옵티마이져의 역할.

- 실행방법을 결정하는 방법에 따라 비용기반 옵티마이져와 규칙기반 옵티마이져로 나눌 수 있다.

가. 규칙기반 옵티마이져.

- 규칙기반 옵티마이져는 규칙(우선순위)을 가지고 실행계획을 생성함.

- 규칙기반 옵티마이져가 실행계획을 생성할 때 참조하는 정보에는 SQL문을 실행하기위해 이용가능한 인덱스의 종류, SQL 문에서 사용하는 연산자의 종류, SQL문에서 사용하는 객체의 종류 등이 있다. 이러한 정보에 따라 우선순위가 정해져 있고, 이 우선순위를 기반으로 실행계획을 생성한다.

규칙1. Single row by rowid : 

ROWID를 통해서 테이블에서 하나의 행을 엑세스 하는 방법. 하나의 행을 엑세스하는 가장 빠른 방법.

규칙4. Single row by unique or primary key

유일 인덱스를 통해서 하나의 행을 엑세스 하는 방식. 인덱스를 먼져 엑세스 하고 인덱스에 존재하는 ROWID를 추출하여 테이블의 행을 엑세스한다.

규칙8. Composite index

복합인덱스에 동등 연산자 조건으로 검색하는 경우.

예를들어 A+B 칼럼으로 복합 인덱스가 새엇ㅇ되어 있을 때 WHERE A=1 AND B=1 형태로 검색하는 방식.

복합인덱스 사이의 우선순위 규칙은 인덱스 구성 칼럼의 개수가 더 많고 해당 인덱스의 모든 구성 칼럼에 대해 '='로 값이 주어질 수록 우선순위가 더 높다.

ex)A+B 와 A+B+C로 구성된 인덱스가 각각 존재할 때 a,b,c 칼럼에 대해 모두 =로 값이 주어진다면 A+B+C인덱스의 우선순위가 높으며, A,B 칼럼에만 = 로 값이 주어진다면 A+B+C 인덱스가 우선순위가 높다.

규칙9. Single Column Index

단일 칼럼 인덱스에 = 조건으로 검색하는 경우.

규칙 10. BOUNDED RANGE SEARCH ON INDEXED COLUMNS

인덱스가 생성되어 있는 칼럼에 양쪽 범위를 한정하는 형태로 검색하는 방식. BETWEEN, LIKE 등의 연산자가 있다.

규칙11. UNBOUNDED RANGE SEARCH ON INDEXED COLUMNS

인덱스가 생성되어 있는 칼럼에 한쪽 버뮈만 한정하는 형태로 검색하는 방식. >, >=,<,<= 등이 있다. 만약 A 칼럼에 인덱스가 생성되어 있고, A > '10' 또는 A <'20' 형태로 검색하는 방식.

규칙 15. FULL TABLE SCAN

전체 테이블을 엑세스 하면서 조건절에 주어진 조건을 만족하는 행만을 결과로 추출.

* 규칙기반 옵티마이저는 인덱스를 이용한 엑세스 방식이 전체 테이블 엑세스 방식보다 우선순위가 높다.

* 규칙기반 옵티마이저가 조인 순서를 결정할 때는 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준이다.

1) 조인 칼럼에 대한 인덱스가 양쪽 테이블에 모두 존재한다면 설명한 규칙에 따라 우선순위가 높은 테이블을 선행테이블로 선택한다. 

2)한쪽 조인칼럼에만 인덱스가 존재하는 경우에는 인덱스가 없는 테이블을 선행 테이블로 선택해서 조인을 수행한다. 

3) 조인 칼럼에 모두 인덱스가 존재하지 않는 경우에는 FROM 절의 뒤에 나열된 테이블을 선행 테이블로 선택한다.

4) 만약 조인테이블의 우선순위가 동일할 경우 FROM 절에 나열된 테이블의 역순으로 선행 테이블을 선택한다.

* 규칙 옵티마이저의 조인 기법

양쪽 조인 칼럼에 모두 인덱스가 없는 경우 Sort Merge Join을 사용하고.

둘 중 하나라도 조인 칼럼에 인덱스가 있을 경우 일반적으로 NL Join을 사용한다.

나. 비용기반 옵티마이져

- 비용기반 옵티마이저는 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다.

- 비용기반 옵티마이저는 비용을 예측하기 위해서 규칙기반 옵티마이저가 사용하지않는 테이블, 인덱스, 칼럼등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다.

- 통계정보가 없는 경우 비용기반 옵티마이저는 정확한 비용예측이 불가능해서 비효율적인 실행계획을 생성할 수 있다.

- 비용기반 옵티마이저는 FULL TABLE SCAN이 INDEX SCAN 보다 유리하다고 판단할 경우 FULL TABLE SCAN을 수행한다.

* 비용기반 옵티마이저 구성

비용기만 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예축기 등의 모듈로 구성되어있다.

1) 질의변환기

사용자가 작성한 SQL 문을 처리하기에 보다 용이한 형태로 변환하는 모듈.

2)대안 계획 생성기

돌일한 결과를 생성하는 다양한 대한 계획을 생성하는 모듈이다.

대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해서 생성된다.

( 많은 대안계획의 생성은 성능에 저하를 유발시킬 수 있으므로 대안 계획의 수를 제약하는 다양한 방법을 사용한다.)

3) 비용예측기

대안 계획 생성기에 의해서 생성된 대안 계획의 비용을 예측하는 모듈이다.

대안계획의 정확한 비용을 예측하기 위해서 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다.

2. 실행계획

- SQL이 요구한 사항을 처리하기 위한 처리와 방법을 의미한다.

- 실행계획 생성한다는 것은 SQL을 어떤 순서로 처리할 지 결정하는 작업이다.

- 옵티마이저는 다양한 처리 방법들 중에서 가장 효율적인 방법을 찾아준다.

- 실행계획을 구성하는 요소에는 조인순서, 조인기법, 엑세스기법, 최적화정보, 연산 등이 있다.

1) 조인순서: 조인 작업을 수행할 때 참조하는 테이블의 순서이다.

2) 조인기법: 두 개의 테이블을 조인할 때 사용하는 방법으로 NL Join, Hash Join, Sort Merge Join 등이 있다.

3) 엑세스 기법: 인덱스를 이용하여 테이블을 엑세스하는 인덱스 스캔과 테이블 전체를 모두 읽으면서 조건을 만족하는 행을 찾는 풀 테이블 스캔이 있다.

4) 최적화정보: 옵티마이저가 실행계획의 각 단계마다 예상되는 비용을 표시한것.

cost: 상대적인 비용 정보

card: 주어진 조건을 만족한 결과 집합 혹은 조인 조건을 만족한 결과 집합 건 수

bytes: 결과집합이 차지하는 메모리 양을 바이트로 표시한 것.

이러한 정보들은 통계정보를 바탕으로 옵티마이저가 계산한 예상치이다.

5) 연산: 여러 가지 조작을 통해서 원하는 결과를 얻어내는 일련의 작업이다.

연산에는 조인 기법, 엑세스기법, 필터, 정렬, 집계, 뷰 등 다양한 종류가 존재한다.

3. SQL 처리 흐름도.

- SQL의 내부적인 처리절차를 시각적으로 표현한 도표이다.

- 실행계획을 시각화한 것이다.

조인순서는 TAB1 -> TAB2 일 때 TAB1을 Outer Table 또는 Driving Table 이라고 하고, TAB2를 Inner Table 또는 Lookup Table 이라고 한다.

[SQLD] 2과목 SQL 기본및 활용 - 1장 9절 JOIN

1. 조인의 개요

- 두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것을 JOIN이라고 한다.

- 일반적인 경우 행들은 PK나 FK값의 연관만으로 JOIN 성립이 가능하다.

- 선수라는 테이블과 팀이라는 테이블이 있는 경우 선수 테이블을 기준으로 필요한 데이터를 검색하고 이 데이터와 연관된 팀 테이블의 특정 행을 찾아오는 과정이 JOIN을 이용하여 데이터를 검색하는 과정으로 볼 수 있다.

- FROM 절에 여러개의 테이블을 나열하더라도 SQL에서 처리할 때는 단 두 개의 집합 간에만 조인이 일어난다. 

이때 테이블의 조인 순서는 옵티마이저에 의해 결정된다.

(FROM 절에 A, B, C 세 테이블이 나열되었 더라도 특정 2개의 테이블만 먼저 조인 처리되고, 나머지 두개의 테이블이 조인되어 처리된 새로운 데이터 집합과 남은 한 개의 테이블이 다음 차례로 조인된다.)

2. EQUI 조인

- EQUI 조인은 두 개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우에 사용되는 방법으로

PK - FK 관계를 기반으로 한다. 

(일반적으로 테이블 설계시에 나타난 PK - FK의 관계를 이용하는 것이지 반드시 PK - FK 의 관계로만 EQUI JOIN이 발생하는 것은 아니다.)

- 이 기능은 계층형이나 망형 데이터베이스와 비교하여 관계형 데이터베이스의 큰 장점이다.

- JOIN 조건은 WHERE 절에 기술하게 되는데 " =  " 연산자를 사용해서 표현한다.

- 조인조건에 맞는 데이터만 출력하는 INNER JOIN에 참여하는 대상 테이블이 N개라고 했을 때 N개의 테이블로부터 필요한 데이터를 조회하기 위해 필요한 JOIN 조건은 대상 테이블의 개수에서 하나를 뺀 N-1개 이상 필요하다.

(EX. 나열된 테이블이 2개일 경우 1개이상 조건 필요하며, 3개일 경우 2개이상 조건이 필요함.)

EX) 선수 테이블과 팀 테이블에서 선수 이름과 소속된 팀의 이름을 출력하시오.

A. 

SELECT PLAYER.PLAYERNAME, TEAM.TEAMNAME

FROM PLAYER, TEAM

WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID

* ANSI/ISO SQL 표준방법 표기가능

SELECT PLAYER.PLAYERNAME, TEAM.TEAMNAME

FROM PLAYER INNER JOIN TEAM

ON PLAYER.TEAM_ID INNSER JOIN TEAM.TEAM_ID

[SQLD] 2과목 SQL 기본및 활용 - 1장 8절 ORDER BY 절

1. ORDER BY 절

- SQL 문장으로 조회된 테이블을 다양한 목적에 맞게 특정 칼럼을 기준으로 정렬하여 출력하는데 사용한다.

- ORDER BY 절에 칼럼명 대신에 SELECT 절에서 사용한 ALIAS명이나 칼럼 순서를 나타내는 정수도 사용한다.

* ASC : 오름차순정렬( DEFAULT)

* DESC : 내림차순 정렬

* ORDER BY 절 사용 특징.

- 기본적인 정렬 순서는 오름차순(ASC) 이다.

- 숫자형 데이터 타입은 오르차순으로 정렬했을 때 가장 작은 값부터 출력된다.

- 날짜형 데이터 타입은 오름차순으로 정렬했을 때 날짜 값이 가장 빠른 값이 먼저 출력된다.

(2015/01/01 -> 2015/01/02 ->2015/01/03 ....)

- 오라클은 NULL값을 사용할 경우 가장 큰 값으로 간주하며, SQL server는 가장 작은값으로 간주한다.

- ORDER BY 절에 칼럼의 순서로 명시할 수 있다

(EX. ORDER BY 3 DESC, 2,1)

EX1.) 컬럼기준 정렬

SELECT DEPTNO, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING MAX(SAL) >=3000

ORDER BY DEPTNO ;

결과
DEPTNO   AVG(SAL)

---------- ----------

10         2916.66667

20         2175

* 정렬안한 결과

DEPTNO   AVG(SAL)

---------- ----------

20          2175

10          2916.66667

EX2.) ALIAS 기준 정렬

SELECT DEPTNO DEP_AS, AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING MAX(SAL) >=3000

ORDER BY DEP_AS;

* 오라클에서는 NULL 값을 가장 큰 값으로 취급한다.

* SQL Server에서는 NULL 값을 가장 작은 값으로 취급.

ENAME COMM

---------- ----------

TURNER     0

ALLEN   300

WARD   500

MARTIN 1400

SCOTT

KING

ADAMS

JAMES

FORD

MILLER

BLAKE

JONES

GOODBOY

CLARK

EX4)여러가지 컬럼을 기준으로 정렬

연봉이 높은 순서대로, 연봉이 같은경우 커미션이 높은 순서대로 정렬

SELECT ENAME, SAL, NVL(COMM,0) NVL_COMM

FROM EMP

ORDER BY SAL, NVL_COMM;

EX5) 칼럼 순서로 정렬.

SELECT ENAME, SAL, NVL(COMM,0) NVL_COMM

FROM EMP

ORDER BY 2,3,1;

예제. DEPT 테이블 정보를 부서명, 지역별, 부서번호 내림차순으로 정렬하여 출력. EX1) 칼럼명사용. SELECT DNAME, LOC, DEPTNO FROM DEPT ORDER BY DNAME, LOC, DEPTNO; EX2) 칼럼명 + ALIAS 사용  SELECT DNAME, LOC LOC_AS, DEPTNO FROM DEPT ORDER BY DNAME, LOC_AS, DEPTNO; EX3) 칼럼순서번호 + ALIAS 사용 SELECT DNAME, LOC LOC_AS, DEPTNO FROM DEPT ORDER BY 1, LOC_AS, 3; -결과는 모두 같다.

 

2. SELECT 문장 실행 순서.

- GROUP BY 절과 ORDER BY 절이 같이 사용될 때 SELECT 문장은 6개의 절로 구성된다.

1. SELECT 2. FROM 3. WHERE 4. GROUP BY 5. HAVING 6. ORDER BY

=> 문장 실행 순서.(옵티마이저가 SYNTAX 및 SEMANTIC 에러를 점검하는 순서) 

1. 발췌대상 테이블 참조.( FROM )

2. 발췌대상 데이터가 아닌것은 제거 ( WHERE)

3. 행들을 소그룹화 (GROUP BY)

4. 그룹핑된 값의 조건에 맞는 것만을 출력 (HAVING)

5. 데이터값을 출력 및 계산 (SELECT)

6. 데이터를 정렬(ORDER BY)

* ORDER BY절에는 SELECT 목록에 없는 문자형 항목이 포함될 수 있다.

단, SELET DISTINCT를 지정하거나 SQL 문장에 GROUP BY 절이 있거나 또는 SELECT문에 UNION 연산자가 있으면 열정의가 SELECT 목록에 표시되어야 한다.

(관계형 DB에서 데이터를 메모리로 올릴 때 행단위로 모든 컬럼을 가져오게되므로 SELECT절에서는 일부만 선택하더라도 ORDER BY 절에서 메모리에 올라온 다른 컬럼데이터를 사용할 수 있다.)

ex) SELECT 절에 없는 EMP 칼럼을 ORDER BY 절에 사용.

SELECT EMPNO, ENAME

FROM EMP

ORDER BY MGR ;

     EMPNO ENAME

---------- ----------

      7902 FORD

      7788 SCOTT

      7900 JAMES

      7499 ALLEN

      7521 WARD

      7844 TURNER

      7654 MARTIN

      7934 MILLER

      7876 ADAMS

      7698 BLAKE

      7566 JONES

      7782 CLARK

      7369 GOODBOY

      7839 KING

ex) 인라인 뷰에 정의된 SELECT 칼럼을 메인쿼리에서 사용.

SELECT EMPNO

FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);

     EMPNO

----------

      7902

      7788

      7900

      7499

      7521

      7844

      7654

      7934

      7876

      7698

      7566

      7782

      7369

      7839

ex) 인라인 뷰에 미정의된 SELECT 칼럼을 메인쿼리에서 사용.

SELECT MGR

FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR;)

결과

ERROR at line 1:

ORA-00904: "MGR": invalid identifier

=> MGR이 인라인뷰를 통해 조회되지않음.

- 서브쿼리절에서 선택되지 않은 컬럼들은 계속 유지가 되는것이 아니라 서브쿼리 범위를 벗어나면 더이상 하용할 수 없다.

-GROUP BY 절에서 그룹핑 기준을 정의하게 되면 데이터베이스는 일반적인 SELECT 문장처럼 FROM 절에 정의된 테이블 구조를 그대로 가지고 가는 것이 아니라 GROUP BY 절의 그룹핑 기준에 사용된 칼럼과 집계 함수에

사용될 수 있는 숫자형 데이터 칼럼들의 집합을 새로 만든다.

- GROUP BY 절을 사용하게 되면 그룹핑 기준에 사용된 칼럼과 집계함수에 사용될 수 있는 숫자형 데이터 컬럼들의 집합을 새로 마드는데 개별 데이터는 필요 없으므로 저장되지 않는다. GROUP BY 이후 수행 절인 SELECT 절이나 ORDER BY 절에서 개별 데이터를 사용할 경우 에러가 발생한다.

* SELECT 절에서는 그룹핑 기준과 숫자 형식 칼럼의 집계함수를 사용할 수 있지만 그룹핑 기준 외의 문자 형식 칼럼은 정할 수 없다.

- GROUP BY 절 사용 시 SELECT 절에 일반 칼럼을 사용

SELECT JOB, SAL

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY SAL;

 

ERROR at line 1:

ORA-00979: not a GROUP BY expression

 

=> 에러발생.

- GROUP BY 절 사용시 ORDER BY 절에 일반 칼럼을 사용해본다.

SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY SAL;

ORA-00979: not a GROUP BY expression

 

=> 에러발생

- GROUP BY 절 사용시 ORDER BY 절에 집계 칼럼 사용.

SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

JOB

---------

singer

people

actor

MANAGER

PRESIDENT

SALESMAN

ANALYST

CLERK

 

3. TOP N 쿼리

- ROWNUM

오라클에서 우선순위가 높은 N개의 로우를 추출하기 위해 ORDER BY 절과 WHERE 절의 ROWNUM조건을 같이 사용하는 경우가 있는데 이 두조건으로는 원하는 값을 얻을 수 없다.

오라클의 경우 정렬이 완료된 후 데이터의 일부가 출력되는 것이 아니라 데이터의 일부가 추출된 후 데이터에 대한 정렬작업을 수행한다.

예제. 사원테이블에서 급여가 높은 3명만 내림차순으로 출력

SELECT ENAME, SAL

FROM EMP

WHERE ROWNUM < = 3

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

ALLEN 1600

WARD 1250

 

GOODBOY   800

=> 해당 결과는 급여에 대한 오름차순 정렬이 아니라 무작위로 추출된 3명에 대해 급여를 내림차순으로 정렬한 결과이므로 원하는 결과가 출력된것이아니다.

( WHERE 절이 ORDER BY 절보다 먼저 수행 되기 때문이다.)

A. 정답. 인라인 뷰를 통해 데이터 정렬을 먼저 수행 후 메인쿼리에서 ROWNUM 사용.

SELECT  ENAME, SAL

FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC)

WHERE ROWNUM < = 3 ;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

 

FORD 3000

* 인라인뷰를 사용하여 추출하고자 하는 집합을 정렬한 후 ROWNUM을 적용시킴으로써 결과에 참여하는 순서와 추출된는 로우 순서를 일치 시킴으로써 TOP N 쿼리의 결과를 만들었다.

- TOP

반면에, SQL server는 조건을 사용하게 되면 별도의 처리 없이 관련 ORDER BY 절의 데이터 정렬 후 원하는 일부 데이터만 쉽게 출력할 수 있다.

TOP (EXPRESSION) [PERCENT] [WITH TIES]

 

* TOP 절을 사용해서 결과 집합으로 반환되는 행의 수를 제한할 수 있다. 

- WITH TIES 옵션은 ORDER BY 절의 조건 기준으로 TOP N 의 마지막 행으로 표시되는 추가 행의 데이터가 같을경우 N+ 동일정렬 순서 데이터를 추가 반환하도록 지정하는 옵션이다.

EX) 사원테이블에서 급여가 높은 2명을 내림차순으로 출력.

SELECT TOP(2) ENAME, SAL

FROM EMP

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

 

EX2) 사원테이블에서 급여가 높은 2명을 내림차순으로 출력하는데, 같은 급여를 받는 사원이 있으면 같이 출력

SELECT TOP(2) WITH TIES ENAME, SAL

FROM EMP

ORDER BY SAL DESC;

ENAME   SAL

---------- ----------

KING 5000

SCOTT 3000

 

FORD 3000

 

[SQLD] 2과목 SQL 기본및 활용 - 1장 6절 함수

1. 내장함수 개요

- 함수는 벤더에서 제공하는 함수인 내장함수와 사용자가 정의할 수 있는 사용자 정의함수로 구분할 수 있다.

- 내장함수는 함수의 입력값이 단일행 값이 되는 단일행 함수와 여러개의 값이 입력되는 다중행 함수로 구분할 수 있다.

- 다중행 함수는 집계함수, 그룹함수, 윈도우함수로 나눌 수 있다.

* 함수는 입력되는 값이 아무리 많아도 출력은 하나만 된다는 M:1관계라는 특징을 가지고있다.

1) 단일행 함수의 종류 ( 오라클 / SQL server)

* 단일행 함수의 중요한 특징.

- SELECT, WHERE, ORDER BY 절에 사용 가능하다.

- 각 행들에 대해 개별적으로 작용하여 데이터 값들을 조작하고, 각각의 행에 대한 조작 결과를 리턴한다.

- 여러 인자를 입력해도 단 하나의 결과만 리턴한다.

- 함수의 인자로 상수, 변수, 표현식이 가능하며 하나의 인수를 가지는 경우도 있지만 여려개의 인자를 가질 수 있다.

- 특별한 경우가 아니면 함수의 인자로 함수를 사용하는 함수의 중첩이 가능하다.

2. 문자형 함수.

- 문자 데이터를 매개변수로 받아들여서 문자나 숫자값의 결과를 돌려주는 함수이다.

몇몇 문자형 함수의 경우는 결과를 숫자로 리턴하는 함수도 있다.

* 문자형 함수 적용 시 리턴되는 결과

3. 숫자형 함수

- 숫자데이터를 입력받아 처리하고 숫자를 리턴한다.

- 숫자형 함수 적용되었을 때 리턴되는 값

4. 날짜형 함수

- 날짜형 함수는 DATE 타입의 값을 연산하는 함수

- 단일행 날짜형 데이터 연산

ex) 오라클

SELECT ENAME, HIREDATE,

TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) YYYY,

TO_NUMBER(TO_CHAR(HIREDATE,'MM')) MM,

TO_NUMBER(TO_CHAR(HIREDATE,'DD')) DD

FROM EMP;

ENAME   HIREDATE   YYYY MM   DD

---------- --------- ---------- ---------- ----------

seungho    17-DEC-80   1980 12   17

ALLEN   20-FEB-81   1981  2   20

WARD   22-FEB-81   1981  2   22

JONES   02-APR-81   1981  4     2

ex) SQL server 함수

SELECT ENAME, HIREDATE,

DATEPART(YEAR, HIREDATE) 입사년도,

DATEPART(MONTH, HIREDATE) 입사월,

DATEPART(DAY, HIREDATE) 입사일

FROM EMP;

ex) SQL server 함수2

SELECT ENAME, HIREDATE

YEAR(HIREDATE) 입사년도,

MONTH(HIREDATE) 입사월,

DAY(HIREDATE) 입사일

FROM EMP;

5. 변환형 함수

- 특정 데이터 타입을 다양한 형식으로 출력하고 싶을 경우에 사용되는 함수

* 암시적 데이터 유형변환의 경우 성능 저하가 발생할 수 있으며 자동적으로 데이터베이스가 알아서 계산하지 않는 경우가 있어서 에러를 발생할 수 있으므로 명시적인 데이터 유형 변환방법을 사용하는것이 바람직함.

EX) 오라클

EX) SQL server

EX) 금액을 달러와 원화로 표기

6. CASE 표현

- IF -THAN ELSE 논리와 유사한 방식

- ANSI/ISO SQL 표준에서는 CASE Expression이라고 표시되어 있는데 함수와 같은 성격을 갖는다.

- ORACLE의 Decode 함수와 같은 기능을 한다.

SELECT ENAME

               CASE WHEN SAL > 2000

                          THEN SAL

                          ELSE 2000

               END REVISED_SALARY

FROM EMP;

- SIMPLE_CASE_EXPRESSION

 * CASE 조건 뒤에 바로 조건에 사용되는 칼럼이나 표현식을 표시하고 WHEN 절에서 앞에서 정의한 칼럼이나 표현식과 같은지 판단하는 문장으로 (=) 조건만 사용한다면 SEARCHED_CASE_EXPRESSION보다 간단하게 사용할 수 있는 장점이 있다.

CASE 

   EXPR WHEN COMPARISON_EXPR THEN RETURN_EXPR

   ELSE 표현절

END

- SEARCHED_CASE_EXPRESSION

*SEARCHED_CASE_EXPRESSION 다음에 칼럼이나 표현식을 표시하지않고 다음 WHEN 절에서 EQUI(=), >, >= , <, <= 을 이용한 조건절을 사용하여 SIMPLE_CASE_EXPRESSION보다 훨씬 다양한 조건을 저장할 수 있는 장점이 있다.

CASE 

    WHEN CONDITION THEN RETURN_EXPR

    ELSE  표현절

END

7. NULL 관련 함수.

가. NVL / ISNULL 함수.

* NULL 특성

- 넓값은 아직 정의되지 않은 값으로 0 또는 공백과 다르다.(0은 숫자이며 공백은 문자이다.)

-테이블을 생성할 때 NOT NULL 조건 또는 PRIMARY KEY로 정의되지 않은 모든 데이터 유형은 널값을 표현할 수 있다.

- 널 값을 포함하는 연산의 경우 결과값도 널 값이다. 모르는 데이터에 숫자를 더하거나 빼도 결과는 마찬가지로 모르는것과 같은 이치.

- 결과값을 NULL이 아닌 다른 값을 얻고자 할 때 NVL/ISNULL함수를 사용한다. NULL 값의 대상이 숫자 유형 데이터인 경우는 주로 0으로 문자유형 데이터인 경우 블랭크보다는 'x'같이 해당 시스템에서 의미없는 문자로 바꾸는 경우가 많다.

- NVL(NULL 판단대상컬럼, 'NULL 일 때 대체값')

EX)

SELECT PLAYER_NAME 선수명, POSITION, NVL(POSITION,'없음')

FROM PLAYER

WHERE TEAM_ID='K08';

- ISNULL(SQL server)

EX)

SELET PLAYER_NAME 선수명, POSITION, ISNULL(POSITION,'없음')

FROM PLAYER;

- CASE를 이용한 표현

EX)

SELECT PLAYER_NAME 선수명, POSITION

              CASE WHEN POSITION IS NULL THEN '없음'

              ELSE POSITION

              END AS 포지션

FROM PLAYER;

*  NULL값의 비교연선은 IS NULL!!!!

Q. 급여와 커미션을 포함한 연봉을 계산하라.

A.

SELECT ENAME, SAL, COMM, (SAL*12)+COMM 연봉A, SAL*12+NVL(COMM,0) 연봉B

FROM EMP;

* NVL 함수를 사용하지 않은 연봉A의 결과값은 NULL과 더하기 연산을 하기 때문에 NULL이 나온다.

나. NULL과 공집합.

- 일반적인 NULL / ISNULL 함수 사용.

1) 정상적으로 매니저 정보를 가지고 있는 SCOTT의 매니저를 출력한다.

SELECT MGR FROM EMP WHERE ENAME='SCOTT';

       MGR

------------------

      7566

2 )매니저에 NULL이 있는 KING의 매니저를 출력.

SELECT MGR FROM EMP WHERE ENAME='KING';

       MGR

------------------

3) 매니저가 NULL인 경우 빈칸이 아닌 9999로 표현

SELECT MGR, NVL(MGR,9999) FROM EMP WHERE ENAME='KING';

 MGR     NVL(MGR,9999)

---------- -------------

              9999

- 공집합의 NVL / ISNULLL 함수 사용

* SELECT 1 FROM DUAL WHERE 1=2;   (공집합 발생시키는 쿼리.)

1) 공집합을 발생시키기 위해 사원 테이블에 존재하지않는 'HANI' 라는 이름으로 데이터 검색

SELECT MGR FROM EMP WHERE ENAME='HANI';

no rows selected

2) NVL / ISNULL 함수를 이용하여 9999로 변경 시도

SELECT NVL(MGR,9999) FROM EMP WHERE ENAME='HANI';

no rows selected

* 인수의 값이 공집합인 경우에는 NVL, ISNULL을 사용해도 역시 공집합이 출력됨.

* NVL 함수는 NULL값을 다른 대상으로 바꾸는 함수지 공집합을 대상으로 하지 않는다.

3) 적절한 집계함수를 찾아서 NVL 함수 대신 적용.

SELECT MAX(MGR) MGR FROM EMP WHERE ENAME='HANI';

       MGR

----------

* NULL값이 출력됨.

* 다른 함수와 달리 집계함수와 Scalar Subquery의 경우 인수의 결과값이 공집합이 null 인 경우에도 null을 출력한다.

4) 집계 함수를 인수로 한 NVL/ISNULL 함수를 이용해서 공집합인 경우에도 9999를 출력하게한다.

SELECT NVL(MAX(MGR),9999) MGR FROM EMP WHERE ENAME='HANI';

       MGR

----------

      9999

* 공집합의 경우 NVL 을 사용해도 공집합이 출력되므로 그룹함수와 NVL 함수를 같이 사용해서 처리한다.

다. NULLIF

- NULLIF 함수는 EXPR1이 EXPR2와 같으면 NULL 출력 다르면 EXPR1 리턴한다.

- 특정 값을 NULL로 대채하는 경우에 유용함.

1) NULLIF 함수 사용

SELECT ENAME, EMPNO, MGR, NULLIF(MGR,7698) NULLIF

FROM EMP;

2) CASE문 사용

SELECT ENAME, EMPNO, MGR, 

              CASE WHEN MGR=7698 THEN NULL

              ELSE MGR

              END NULL_CASE

FROM EMP;

라. 기타 NULL 관련함수 COALESCE

- COALESCE 함수는 인수의 숫자가 한정되어 있지 않으며, 임의의 개수 EXPR에서 NULL이 아닌 최초의 EXPR를 나타낸다. 만약 모든 EXPR이 NULL이라면 NULL을 리턴한다.

SELECT ENAME, COMM, SAL, COALESCE(COMM,SAL) COAL

FROM EMP;

* CASE로 표현

SELECT ENAME, COMM, SAL,

CASE WHEN COMM IS NOT NULL THEN COMM

           ELSE  (CASE WHEN SAL IS NOT NULL THEN SAL

                                                                              ELSE NULL

                        END)

            END COAL

FROM EMP; 

[SQLD] 2과목 SQL 기본및 활용 - 1장 5절 WHERE

1. WHERE 조건절 개요

- 사용자들은 자신이 원하는 자료만을 검색하기 위해서 SQL문장에 WHERE 절을 이용하여 자료들에 대하여 제한할 수 있다.

- WHERE절에는 두 개 이상의 테이블에 대한 조인 조건을 기술하거나 결과를 제한하기위한 조건을 기술할 수도 있다.

- WHERE절에 조건이 없는 경우 FULL TABLE SCAN이 발생하므로 SQL튜닝의 1차적인 검토대상이 된다.

2. 연산자의 종류

* 연산자 우선순위 (처리순서)

1) 괄호를 묶은 연산이 가장 먼저 처리된다.

2) 연산자들 중에는 부정 연산자가 먼저 처리되고

3) 비교연산자( =, > ,>=, <, <=), SQL 비교연산자 (BETWEEN a AND b, IN (list), LIKE, IS NULL) 처리되고

4) 논리연산자 중에는 AND, OR 순으로 처리된다.

Q. K 리그 일부 선수들의 이름과 포지션, 백넘버를 알고싶다.

조건은 소속팀이 삼성블루윙즈이거나 전남드래곤즈에 소속된 선수들 중에 포지션이 미드필드이면서 키는 170이상 180 이하여야한다.

A.

SELECT PLAYER_NAME, POSITION, BACKNO

FROM PLAYER

WHERE (TEAMNAME ='삼성블루윙즈' 

        OR TEAMNAME='전남드래곤즈')

      AND POSITION = 'MF'

      AND HEIGHT 170 BETWEEN 180 ;

3. 비교연산자.

* 문자 유형 비교 방법

4. SQL 연산자

1) IN 연산자 사용

SELECT PLAYER_NAME 

FROM PLAYER

WHERE TEAM_ID IN ('K02', 'K07');

Q. 사원테이블에서 JOB이 MANAGER 이면서 20번 부서에 속하거나 JOB이 CLERK 이면서 30번 부서에 속하는 사원의 정보를 IN 연산자를 이용하여 출력하라

A1.

   SELECT * 

   FROM EMP

   WHERE (JOB,DEPTNO) IN (('MANAGER',20),('CLERK',30));

     EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7566 JONES      MANAGER       7839 02-APR-81   2975    20

      7900 JAMES      CLERK       7698 03-DEC-81     950    30

* 이렇게 조회하면 결과가 다르므로 용도에 맞춰 사용한다.

SELECT * 

FROM EMP

WHERE JOB IN ('MANAGER','CLERK')

      AND  DEPTNO IN (20,30);

EMPNO ENAME      JOB       MGR HIREDATE     SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      7566 JONES      MANAGER       7839 02-APR-81   2975    20

      7698 BLAKE      MANAGER       7839 01-MAY-81   2850    30

      7876 ADAMS      CLERK       7788 23-MAY-87   1100    20

      7900 JAMES      CLERK       7698 03-DEC-81     950    30

2) LIKE 연산자 사용

-  LIKE 연산자에서는 와일드카드 사용이 가능하다.

3) IS NULL연산자

* NULL값의 특성

- NULL값과 수치연산은 NULL을 리턴한다.

- NULL과 비교 연산은 FALSE를 리턴한다.

- 어떤 값과 비교할 수도 없으며 특정 값보다 크다, 적다라고 표현할 수 없다.

 * NULL값의 비교연산은 IS NULL, IS NOT NULL을 사용해야 원하는 결과를 얻을 수 있다.

4) ROWNUM, TOP 사용

- 오라클 ROWNUM 사용

SQL 처리 결과 집합과 각 행에 대해 임시로 부여되는 일련번호로 테이블이나 집합에서 원하는 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다.

- SQL server TOP 사용

TOP (Expression) [PERCENT] [WITH TIES]

- Expression : 반환할 행의 수를 지정하는 숫자.

- PERCENT : 쿼리 결과 집합에서 처음 Expresseion%의 행만 반환됨.

- WITH TIES : ORDER BY 절이 지정된 경우에만 사용할 수 있으며, TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.

* SQL 문장에서 ORDER BY 절이 사용되지않으면 ROWNUM과 TOP절은 같은 기능을 하지만 ORDER BY 절이 사용되면 기능상 차이를 보인다.

[SQLD] 2과목 SQL 기본및 활용 - 1장 4절 TCL

1. 트랜잭션 개요.

- 트랜잭션은 데이터베이스의 논리적인 연산단위이다.

- 트랜잭션이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.

(분할할 수 없는 최소단위)

- 전부 적용하거나 전부 취소한다. ( ALL OR NOTHING)

- 올바르게 반영된 데이터를 데이터베이스에 반영시키는 것을 COMMIT, 트랜잭션 시작 이전 상태로 되돌리는 것을 ROLLBACK 이라고한다. SAVEPOINT 기능과 함께 TCL로 분류한다.

- 트랜잭션의 대상이 되는 SQL 문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML문이다.

- SELECT 문은 트랜잭션 대상은 아니지만 SELECT FOR UPDATE 등 배타적 LOCK을 요구하는 SELECT 문장은 트랜잭션의 대상이 될 수 있다.

* 트랜잭션의 특성 ACID

2. COMMIT

- 입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해 트랜잭션을 완료할 수 있다.

* COMMIT이나 ROLLBACK 이전의 데이터 상태

- 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.

- 현재 사용자는 SELECT문으로 결과를 확인할 수 있다.

- 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.

- 변경된 행은 잠금이 설정되어 다른사용자가 변경할 수 없다.

*COMMIT 이후의 데이터 상태

- 데이터에 대한 변경사항이 데이터베이스에 반영된다.

- 이전 데이터는 영원히 잃어버리게된다.

- 모든 사용자는 결과를 볼 수 있다.

- 관련된 행에 대한 잠금이 풀리고 다른 사용자들이 행을 조작할 수 있다.

* SQL server의 COMMIT

- 오라클은 DML을 수행할 경우 사용자가 트랜잭션을 종료하기 위해 COMMIT 혹은 ROLLBACK을 수행해야 종료된다.

하지만 SQL server경우에는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행이후 사용자가 COMMIT이나 ROLLBACK 을 처리할 필요가 없다.

DML 구문이 성공적이면 자동으로 COMMIT되고 오류가 발생할 경우 자동으로 ROLLBACK  처리된다.

* SQL server의 트랜잭션 방식

1) AUTO COMMIT

- SQL server의 기본 방식이며 DML, DDL 수행할 때마다 DBMS 가 트랜잭션을 컨트롤 하는방식이다.

명령어가 성공적으로 수행되면 COMMIT을 수행하고 오류가 발생하면 자동으로 ROLLBACK을 수행한다.

2) 암시적 트랜잭션

- 오라클과 같은방식으로 처리된다. 트랜잭션의 시작은 DBMS가 처리하고 트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK으로 처리한다. 인스턴스 단위 또는 세션 단위로 설정할 수 있다.

3) 명시적 트랜잭션

- 트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다.

BEGIN TRANSACTION (BEGIN TRAN 구문도 가능)으로 시작

COMMIT TRANSACTION (TRANSACTION 생략가능)

ROLLBACK TRANSACTION (TRANSACTION 생략가능) 으로 종료한다.

ROLLBACK 구문을 만나면 최초 BEGIN TRANSACTION 까지 ROLLBACK이 수행된다.

3. ROLLBACK

- 테이블에 입력한 데이터나 수정한데이터 삭제한 데이터에 대하여 COMMIT 이전에는 변경사항을 취소할 수있다.

- 변경사항이 취소되어 데이터의 이전상태로 복구하며 관련된 행에대해 LOCK이 풀리고 다른 사용자들이 데이터를 변경할 수 있다.

* SQL server의 ROLLBACK

- AUTOCOMMIT이 기본방식이므로 임의적으로 ROLLBACK을 수행하기 위해서는 명시적으로 트랜잭션을 선언해야한다.

*COMMIT과 ROLLBACK을 수행하며 얻는 효과

- 데이터 무결성 보장

- 영구적인 변경을 하기 전 데이터의 변경사항 확인 가능

- 논리적으로 연관된 작업을 그루핑하여 처리가능.

4. SAVEPOINT

- ROLLBACK할 때 트랜잭션에 포함된 전체 작업을 롤백 하는것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.

- 복수의 저장점을 지원할 수 있으며 동일한 이름으로 저장할 경우 나중에 정의한 저장점이 유효하다.

- 오라클

- SQL server

- ROLLBACK 원리 (오라클)

 - A  저장점으로 돌린 후 B 저장점으로 되돌릴 수 없다. 특정 지점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효화되기 때문

(ROLLBACK TO A를 실행한 시점에서 A 이후에 저장한 B는 존재하지않는다.)

* ROLLBACK TO B 후 ROLLBACK TO A는 가능하다.

***트랜잭션 정리

- 해당테이블에 데이터의 변경을 발생시키는 입력, 수정, 삭제 수행시 그 변경되는 데이터의 무결성을 보장하는것이 COMMIT과 ROLLBACK의 목적이다.

- COMMIT은 변경된 데이터를 테이블이 영구적으로 반영해라 라는 의미를 갖음.

- ROLLBACK 변경된 데이터가 문제가있으니 변경 전 데이터로 복귀하라는 의미.

- 저장점은 데이터 변경을 저장점까지만 롤백하라는 의미.

- 오라클의 트랜잭션은 SQL문이 시작되면 자동으로 시작되고 COMMIT 또는 ROLLBACK을 수행할 때 종료된다.

- 다음의 경우에는 COMMIT과 ROLLBACK을 수행하지 않아도 자동으로 트랜잭션이 종료됨.

* CREATE, INSERT, DROP, RENAME, TRUNCATE TABLE 등 DDL이 수행되면 그 전후 시점에 자동으로 커밋된다.

* DML문장 이후 커밋없이 DDL을 수행하면 DDL 수행 전에 자동 커밋된다.

* 데이터메이스를 정상종료하면 자동으로 커밋된다.

* 애플리캐이션의 이상 종료로 데이터베이스와 접속이 단절되었을 때는 트랜잭션이 자동 롤백된다.

* SQL Server의 트랜잭션은 AUTO COMMIT이 기본방식이다.

(애플리케이션의 이상종료로 데이터베이스와 접속이 단절되었을 경우에는 오라클처럼 자동 ROLLBACK된다.)