4-5강 SQL 활용-고급 SQL 작성(1)

과목4. 데이터베이스 구축, 5강. SQL 활용-고급 SQL작성(1)

 

[ 목차 ]

1. 뷰

2. 뷰의 특징

3. 뷰 사용법

4. 뷰 생성법

5. 뷰의 장점

6. 뷰의 단점

7. 뷰 삭제 및 변경

8. 뷰 내용 변경

9. 인덱스

10. 인덱스의 구조

11. 인덱스의 장점

12. 인덱스의 사용

13. 인덱스의 종류

14. 인덱스 적용 기준

15. 해시 인덱스

16. 해시 함수의 종류

17. 오버플로우 해결법

18. 인덱스 조작

19. 인덱스 스캔 방식

20. 집합연산

21. 집합연산의 유형

 

1. 뷰

1) 허용된 자료만을 제한적으로 보여주기 위해 하나 이상의 테이블로 부터 유도된 이름을 가지는 가상 테이블(물리적x)

2) 기본 테이블처럼 행과 열로 구성되지만 다른 테이블에 있는 데이터를 보여줄 뿐이며, 데이터를 직접 담고 있지 않음

3) 하나의 물리 테이블로부터 생성 가능하며 다수의 테이블 또는 다른 뷰를 이용해서도 만들 수 있음

4) 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용 가능

 

2. 뷰의 특징

1) 저장 장치내에 물리적으로 존재하지 않는 가상 테이블(논리적)

2) 데이터 보정 등 임시적인 작업을 위한 용도로 사용

3) 기본 테이블과 같은 형태의 구조로 조작이 거의 비슷

4) 삽입, 삭제, 갱신 연산이 가능하지만 제약적이며, 검색은 일반 테이블과 같음

5) 논리적 독립성을 제공

6) 독자적인 인덱스를 가질 수 없음

7) Create를 이용하여 뷰를 생성

8) 필요한 데이터만 골라 뷰를 이용해 처리

9) 다른 VIEW 정의에 기초

10) 하나의 VIEW를 삭제하면 그 VIEW를 기초로 만든 다른 VIEW 역시 자동 삭제

 

3. 뷰 사용법

SELECT * FROM 뷰명;

1) FORM 절에 있는 하나의 뷰를 통해 뷰를 구성하는 복수의 테이블을 대체하는 단순성에 의의가 있음

 

4. 뷰 생성법

1) 테이블 A를 그대로 만드는 경우

CARETE VIEW 뷰명 AS SELECT * FROM 테이블A

2) 테이블 A의 일부 칼럼으로 만드는 경우

CARETE VIEW 뷰명 AS SELECT 칼럼1, 칼럼2, 칼럼3 FROM 테이블A

3) 테이블 A와 B의 조인으로 만드는 경우

CARETE VIEW 뷰명 AS SELECT * FROM 테이블A AS a, 테이블B AS b WHERE a.칼럼1=b.칼럼1

4) SELECT문에는 UNION이나 ORDER BY 절을 사용할 수 없음

5) 칼럼명을 기술하지 않으면 SELECT문의 칼럼 이름이 자동으로 사용

1) SELECT ~ FROM ~ WHERE 문 : 서브쿼리 또는 부속질의

2) WITH CHECK OPTION; : 뷰에 대한 수정, 삽입 연산이 실행됬을 때 WHERE 절 조건에 위배될 경우 실행을 거부

3) AS : 테이블 이름에 별명을 지정하는 것으로, 생략 가능

5. 뷰의 장점

1) 논리적 독립성 제공 : 논리 테이블이기 때문에 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변하지 않아도 됨

2) 사용자 데이터 관리 용이 : 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능

3) 데이터 보안 용이 : 중요 보안 데이터를 저장 중인 테이블에는 접근 불허하고, 해당 테이블의 일부 정보만을 볼 수 있는 뷰에는 접근을 허용하는 방식으로 보안 데이터에 대한 접근 제어 가능

 

6. 뷰의 단점

1) 뷰 자체 인덱스 불가 : 인덱스는 물리적 저장 데이터를 대상으로 하기에 논리적 구성인 뷰는 인덱스를 가지지 못함

2) 뷰 정의 변경 불가 : 뷰의 정의를 변경하려면 뷰를 삭제하고 재생성해야 함

3) 데이터 변경 제약 존재 : 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음

 

7. 뷰 삭제 및 변경

DROP VIEW 뷰명 {RESTRICT | CASECADE};

1) 뷰 정의 자체를 변경하는 것은 불가능

2) 뷰의 물리적 내용은 뷰의 이름과 데이터를 조회하기 위한 쿼리문뿐

3) 뷰의 이름이나 쿼리문을 변경하는 수단은 제공되지 않음

4) 뷰의 삭제와 재생성을 통해 뷰에 대한 정의 변경이 가능

5) RESTRICT : 뷰를 다른 곳에서 참조하고 있으면 삭제가 취소

6) CASCADE : 뷰를 참조하는 다른 뷰, 제약 조건까지 모두 삭제

 

8. 뷰 내용 변경

1) 뷰를 통해 접근 가능한 데이터에 대한 변경이 가능하지만 일부 제약이 존재

2) 뷰 자체가 논리적 개념이기에 물리적 상황에 의존적임을 의미

 

9. 인덱스

1) 데이터를 빠르게 찾을 수 있는 수단으로, 테이블에 대한 조회 속도를 높여주는 자료구조

2) 테이블에서 자주 사용하는 칼럼 값을 빠르게 검색할 수 있도록 색인을 만든 것

3) 과도한 인덱스는 성능 저하를 유발

4) 필요없는 인덱스는 DB에 차지하는 공간이 많아져 인덱스를 사용하지 않는 기본 table scan보다 느려질 수 있음

 

10. 인덱스의 구조

1) 가장 많이 사용하는 검색 트리는 b-tree 구조

2) db의 성능향상을 위한 db튜닝 수단으로 가장 일반적인 방법

b-tree

1) 인덱스 파일에서 인덱스를 구성하는 방법 중 하나


2) 한 노드 안에 잇는 키값은 오름차순을 유지


3) 모든 리프노드(단노드, 단말노드, 최하단)는 같은 레벨에 위치하여 균형을 유지


4) 루트노드(최상단)는 리프가 아닌 이상 적어도 두 개의 서브트리를 보유


5) 탐색, 추가, 삭제는 루트로부터 시작

11. 인덱스의 장점

1) sql문을 변경하지 않아도 성능 개선이 가능

2) 테이블의 데이터에 영향을 미치지 않음

3) 일정한 효과 기대 가능

 

12. 인덱스의 사용

1) 특정 레코드 위치를 알려주는 용도로 사용

2) 자동으로 생성되지 않음

3) pk(기본키) 칼럼은 pk를 생성할 때 자동으로 인덱스가 생성

 

13. 인덱스의 종류

1) 순서 인덱스 ordered

  1] 정렬된 순서로 인덱스를 생성, 관리

  2] b-tree 알고리즘을 이용하여 오름차순, 내림차순을 지정

2) 해시 인덱스 hash

  1] 해시함수에 의해 직접 데이터에 키값으로 접근

  2] 데이터에 접근 비용이 균일하며 레코드 양과 무관

  3] 데이터를 해시 테이블이라는 배열에 저장하고 해싱 함수를 이용해 데이터가 위치한 곳의 주소를 찾아 자료를 검색

  4] 버킷 : 하나의 주소를 갖는 파일의 한 구역

  5] 슬롯 : n개의 슬롯이 모여 하나의 버킷을 형성

  6] 충돌 : 서로 다른 2개 이상의 레코드가 같은 주소를 갖는 현상

  7] 시노임 : 같은 주소를 갖는 레코드의 집합

  8] 오버플로 : 버킷 내에 기억 공간이 없는 현상

3) 비트맵 인덱스 bitmap

  1] 칼럼에 적은 개수의 값이 저장된 경우 사용, 즉 분포고다 좋은 칼럼에 적합(ex. OLAP)

  2] 수정변경이 적은 경우에 유용 

4) 함수기반 인덱스 functional

  1] 함수 기반으로 사전에 인덱스를 설정하면 인덱스 기능 및 속도 향상

5) 단일 인덱스 singled

  1] 하나의 칼럼으로만 인덱스를 지정

  2] 업무적 특성에 의해 주로 사용하는 칼럼이 하나인 경우

6) 결합 인덱스 concatenated

  1] 복수개의 칼럼을 이용하여 인덱스를 지정

  2] 동시에 where 조건으로 사요되는 빈도가 많으 경우

7) 클러스터드 인덱스 clustered

  1] 저장된 데이터의 물리적 순서에 따라 인덱스 생성

  2] 특정 범위의 검색시 유리

OLAP 온라인 분석 처리
1) 데이터 웨어하우스를 다차원적으로 분석하고 시각화하는 과정

14. 인덱스 적용 기준

1) 인덱스 적용 대상 칼럼은 전체 로우 값대비 분포도가 10~15% 이내인 경우가 적합(ex. 학점)

2) 인덱스로 지정한 카럶에 의해 검색했을 때 검색 결과가 전체 튜플의 10~15%미만일 때 인덱스의 효과 높음

3) 좋은 분포도를 가진 인덱스는 인덱스 키 당 하나 또는 소수의 값을 가짐

 

15. 해시 인덱스

1) 신속한 검색을 위해 키 값에 해시 함수를 적용하여 주소 값을 빠르게 계산하고 레코드가 저장된 위치를 직접 접근

2) <키값, 주소>의 쌍으로 저장하여 특정 키 값에 대한 검색 방법 중 가장 빠름

3) 버킷 : 동일한 해시 주소를 같은 레코드들이 저장된 메모리 블록

4) 슬롯 : 1개의 해시 레코드를 저장할 수 있는 공간으로, 슬롯이 모여 버킷을 이룸

5) 충돌 : 해시 레코드를 삽입할 때 서로 다른 2개 이상의 데이터가 같은 해시 주소를 갖는 현상

6) 동거자 : 해시 함수가 같은 주소로 변환시키는 모든 레코드

7) 오버플로우 : 버킷 내에 저장할 공간이 없는 상태

 

16. 해시 함수의 종류

1) 계수 분석 digit analysis : 숫자의 분포

2) 제산법 division : 나눈 나머지

3) 제곱법 mid-sqare : 제곱한 값의 중간 부분 값

4) 폴딩 folding : 키를 여러 부분으로 나누고 각 부분의 값을 모두 더하거나 보수, 더하거나 XOR(서로 다르면 참)하여 주소를 계산

5) 기수 변환 radix transformation : 다른 진법으로 변환

 

17. 오버플로우 해결법

1) 선형 검색법 = 개방 주소법 : 충돌이 발생한 다음 위치에서 차례로 검색

2) 체인법 : 별도의 버킷으로 연결하여 저장

3) 다중 해싱법 = 확장 해싱 : 처음 비트를 이용, 디렉토리

 

18. 인덱스 조작

1) db 사용자가 인덱스에 대해 조작 가능한 방법은 생성, 삭제 변경

2) dbms 공급사마자 사용법이 다름

3) 생성 : CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명 칼럼명(ASC or DESC) CLUSTER;

UNIQUE : 중복값 허용하지 않음

CLUSTER : 물리적으로 데이터를 정렬하여 좀 더 빠르게 검색 가능하게 설정

4) 삭제 : DROP INDEX 인덱스명;

5) 변경 : ALTER [UNIQUE] 인덱스 인덱스명 ON 테이블명 칼럼명;

 

19. 인덱스 스캔 방식

1) index range scan : 루트에서 리프까지 수직적으로 탐색 후 리프 블록을 필요한 범위만 스캔

2) index full scan : 리프 블록을 처음부터 끝가지 수평적으로 탐색

3) index unique scan : 수직적 탐색만으로 스캔

4) index skip scan : 인덱스 선두 칼럼이 조건절에 빠졌어도 스캔

5) index fast full scan : full scan보다 빠른 스캔 방시으로 multiblock read 방식

6) index range scan descending : 내림차순으로 정렬

 

20. 집합연산

1) 테이블을 집합 개념으로 보고 두 테이블 연산에 집합 연산자를 사용하는 방식

2) 여러 질의 결과를 연결하여 하나로 결합하는 방식, 즉 2개 이상의 질의 결과를 하나의 결과로 생성

3) 집합연산을 적용하기 위해 두 테이블이 호환성을 지녀야 함(칼럼의 개수, 데이터 타입의 일치)

 

21. 집합연산의 유형

SELECT --- FROM --- WHERE --- UNION(UNION ALL|INTERSECTION|EXCEPT) SELECT --- FROM --- WHERE ---

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

2) UNION ALL : 합집합(중복 제거 X)

3) INTERSECTION : 교집합(중복 제거)

4) EXCEPT( =MINUS) : 차집합(중복 행 제거, 선행되는 것 중 후행되는 것을 제외한 것들)