4장 SQL 고급(2)
데이터베이스 연구실
1
[목 차]
4.2 뷰
4.2.1 뷰의 생성
4.2.2 뷰의 수정 삭제 4.3 인덱스
4.3.1 인덱스와 B-TREE 4.3.2 인덱스의 생성 4.3.3 인덱스의 관리
2
[목 차]
4.2 뷰
4.2.1 뷰의 생성
4.2.2 뷰의 수정 삭제 4.3 인덱스
4.3.1 인덱스와 B-TREE
4.3.2 인덱스의 생성
4.3.3 인덱스의 관리
} 정의
} 하나 이상의 테이블에서 원하는 데이터를 가져와서 하나의 테이블처럼 사용할 수 있도록 만든 가상의 테이블
} 실제 존재하는 테이블이 아닌 여러 테이블에서 파생(가공)된 정보를 하나의 테이블처럼 볼 수 있게 만든 SQL문
4.2 뷰
고객판매현황 뷰
고객테이블
고객명, 연락처
판매테이블
고객명,주문번호, 주문액
고객판매현황 뷰
고객명,연락처,주문번호,주문액
3
} 목적
} 편리성
} 사용자에게 편의성을 제공
} 사용자가 필요한 정보만을 요구에 맞게 제공
} 일반 사용자들은 일반 테이블처럼 사용 가능
} 재사용성
} 자주 사용되는 쿼리를 미리 정의
} 보안성
} 각 사용자 별로 필요한 데이터만 선별하여 보여줄 수 있음
} 목적
} 편리성
} 사용자에게 편의성을 제공
} 사용자가 필요한 정보만을 요구에 맞게 제공
} 일반 사용자들은 일반 테이블처럼 사용 가능
} 재사용성
} 자주 사용되는 쿼리를 미리 정의
} 보안성
} 각 사용자 별로 필요한 데이터만 선별하여 보여줄 수 있음
4
4.2.1 뷰의 생성
CREATE VIEW 뷰이름 (컬럼) [WITH ENCRYPTION]
AS
SELECT …
FROM … WHERE … [WITH CHECK OPTION]
} 뷰이름 : 테이블과 마찬가지로 데이터베이스내의 유일한 이름지정
} 컬럼 : 뷰에서 보여질 컬럼이며 내부 SELECT 문과 1:1, SQL에 정의가 되어있으면 생략가능
} WITH ENCRYPTION : 내부 SQL문의 암호화, 함부로 정의를 볼 수 없음
} WITH CHECK OPTION : 뷰를 통한 자료 변경시 지켜야 할 조건 지정
5
} 뷰이름 : 테이블과 마찬가지로 데이터베이스내의 유일한 이름지정
} 컬럼 : 뷰에서 보여질 컬럼이며 내부 SELECT 문과 1:1, SQL에 정의가 되어있으면 생략가능
} WITH ENCRYPTION : 내부 SQL문의 암호화, 함부로 정의를 볼 수 없음
} WITH CHECK OPTION : 뷰를 통한 자료 변경시 지켜야 할 조건 지정
} 생성조건
} 사용자는 뷰의 정의에서 사용되는 객체들에 대한 SELECT 권한을 가지고 있어야만 함
} 정의에서 사용되는 SELECT문은 SELECT 구문에 top이 사용될 경우 ORDER BY사용가능하며 그 외 SELECT INTO, ORDER BY, COMPUTE, COMPUTE BY 문을 사용할 수 없음
} 임시 테이블을 대상으로 생성할 수 없음
} 생성조건
} 사용자는 뷰의 정의에서 사용되는 객체들에 대한 SELECT 권한을 가지고 있어야만 함
} 정의에서 사용되는 SELECT문은 SELECT 구문에 top이 사용될 경우 ORDER BY사용가능하며 그 외 SELECT INTO, ORDER BY, COMPUTE, COMPUTE BY 문을 사용할 수 없음
} 임시 테이블을 대상으로 생성할 수 없음
6
생성
7
테이블처럼 사용
8
} 뷰의 사용 중 컬럼의 변경, 추가나 SQL문장의 변경이 생길 경우 수정이 필요하며 이때는 ALTER 명령을 사용
4.2.2 뷰의 수정 삭제
ALTER VIEW 뷰이름 AS
SELECT 문 ...
9
ALTER VIEW 뷰이름 AS
SELECT 문 ...
수정 후
주문(OCNT) 수량 컬럼으로 조회가능
10
} 뷰를 더 이상 사용할 필요가 없을 경우는 DROP 명령으로 삭제
DROP VIEW 뷰이름
11
4.3 인덱스
} 인덱스(index)
} 테이블에 대한 검색 시 속도를 높여주기 위해 사용되는 자료 구조
} 일반적으로 B-TREE 의 구조를 가짐
인덱스 테이블
12
검색
SELECT 이름 FROM 고객 WHERE 전화번호=‘1004’
김철수 1002 심철수 1005 나미인 1004 이영수 1001 김철수 1002 심철수 1005 나미인 1004 이영수 1001 1001
1002 1003 1004
….
인덱스 테이블
4.3.1 인덱스와 B-TREE
} B-트리
} 데이터베이스와 파일 시스템에서 널리 사용되는 트리자료구조의 일종
} 자료를 정렬된 상태로 보관
} 루돌프 바이어에 의해 창시
13
} 인덱스의 특징
} 테이블의 컬럼 (한 개, 또는 여러 개)을 이용하여 생성
} 빠른 검색과 함께 효율적인 레코드 접근이 가능하게 함
} 테이블의 저장 공간에 비해 작은 공간 차지 (순서대로 정리된 자료와 물리적 위치만 보유)
} 테이블의 부분집합
} 일반적으로 B-TREE 형태의 구조를 가짐
} 데이터 정렬된 형태로 저장됨 4.3.2 인덱스의 생성
} 인덱스의 특징
} 테이블의 컬럼 (한 개, 또는 여러 개)을 이용하여 생성
} 빠른 검색과 함께 효율적인 레코드 접근이 가능하게 함
} 테이블의 저장 공간에 비해 작은 공간 차지 (순서대로 정리된 자료와 물리적 위치만 보유)
} 테이블의 부분집합
} 일반적으로 B-TREE 형태의 구조를 가짐
} 데이터 정렬된 형태로 저장됨
14
} 생성시 고려사항
} WHERE 절에 자주 나오는 컬럼을 대상
} 조인이 자주 일어나는 경우 조인되는 컬럼을 대상
} 인덱스에 NULL은 저장되지 않으므로 NULL 많은 컬럼 유리
} 하나의 테이블의 인덱스가 많으면 속도가 느려질 수 있음
} 보통 테이블당 4~5개 정도 권장
(시스템의 HW성능 등에 따라 유동적)
} 생성시 고려사항
} WHERE 절에 자주 나오는 컬럼을 대상
} 조인이 자주 일어나는 경우 조인되는 컬럼을 대상
} 인덱스에 NULL은 저장되지 않으므로 NULL 많은 컬럼 유리
} 하나의 테이블의 인덱스가 많으면 속도가 느려질 수 있음
} 보통 테이블당 4~5개 정도 권장
(시스템의 HW성능 등에 따라 유동적)
15
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n]) [WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]
} 검색형태
} 포인트 검색
} 특정한 자료를 대상으로 조회
} 범위 검색
} 자료의 범위를 대상으로 조회
16
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n]) [WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]
} 클러스터드 인덱스(clustered index)
} 한 테이블에 하나만 허용
} 데이터가 키 값에 따라 정렬
} 포인터 및 범위 검색 모두에 유리
} Primary Key 생성시 자동 생성
} 책의 목차
17
생성방법
CREATE CLUSTERED INDEX 인덱스명 ON 테이블명 ( 컬럼 ASC )
Customer 테이블의 Primary Key 인 custid의 경우 테이블생성시 clustered index가 생성 PK가 없을경우 정상수행
자동 생성된 경우 삭제할 수 없음
18
19
Clustered index를 사용하여 결과탐색을 확인할 수 있음
20
Clustered index를 사용하여 결과탐색을 확인할 수 있음
} 넌클러스터드 인덱스(nonclustered index)
} 테이블당 여러 개 생성가능 (약 240개)
} 데이터는 들어가 있는 순서대로 있음
} 대상 컬럼을 기준의 별도의 인덱스 페이지 생성
} 책의 색인 페이지
21
생성방법
create nonclustered index 인덱스명 on 테이블명(컬럼명 asc)
22
nonClustered index를 사용하여 결과탐색
Index Seek를 확인할 수 있음
23
nonClustered index를 사용하여 결과탐색
Index Seek를 확인할 수 있음
4.3.3 인덱스의 관리
} 인덱스 삭제
} 인덱스가 많을 경우 DB 성능에 영향을 미치므로 사용하지 않는 경우 삭제하여 주어야 함
24
