02.고급 SQL 작성하기

인덱스 활용

테이블 조회 시간을 단축하기 위해 사용하는 인덱스의 개념을 이해하고, 인덱스를 생성하는 DDL(Data Definnition Language) 명령문을 작성할 수 있다

인덱스 개요

1. 인덱스 개념

인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주 는 자료구조를 일컫는다. 인덱스는 다음 그림과 같이 테이블의 특정 레코드 위치를 알려 주는 용도로 사용하는데, 이러한 인덱스는 자동으로 생성되지 않는다.

PK 컬럼은 PK를 생성할 때 자동으로 인덱스가 생성된다. 즉, PK 컬럼은 PK를 생성할 때 자동으로 PK 인덱스가 생성된다. 예를 들어 위의 그림과 같은 테이블에서 일련번호를 기본키(Primary Key)로 하는 경우, 일련번호에 대한 인덱스는 자동으로 생성되나, 생년월일이나 이름을 기준으로 하는 인 덱스는 자동으로 생성되지 않는다. 다음 질의문을 보자.

조건문 where 절에서 `이름'을 비교하고 있다. 이 경우 해당 테이블의 `이름' 컬럼에 인덱 스가 없는 경우, 테이블의 전체 내용을 검색하(Table full scan)게 된다. 반면, 인덱스가 생성되어 있다면 테이블의 일부분을 검색(Range scan)하여 데이터를 빠르 게 찾을 수 있다. 조건절에 `='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도 를 높일 수 있다. 하지만 자동으로 생성되지 않기 때문에 DB 사용자는 질의문을 분석하여 인덱스를 생성해야 한다.

인덱스 사용

1. 인덱스 사용 주체

[그림 2-1]에서 `이름' 컬럼에 대한 인덱스가 생성되어 있다면 데이터를 빠르게 찾을 수 있다. 이때 빠르게 찾는 행위의 주체는 DBMS이다. 즉, DBMS는 인덱스를 사용하여 빠른 검색을 수행한다. 이를 위해 DB 사용자는 DBMS가 인덱스를 사용할 수 있게 준비해 주어 야 한다. 따라서 DB 사용자 입장에서는 인덱스를 사용하는 개념보다는 준비하는 개념으로 접근해야 한다. 인덱스 준비 방법에 대해 알아보도록 하자.

2. 인덱스 준비

DB 사용자가 인덱스에 대해 조작할 수 있는 방법으로는 `생성, 삭제 그리고 변경' 조작이 있다. 참고로 인덱스 조작 명령은 SQL 표준화에 포함되지 않아 DBMS 제품 공급사마다 사용법이 약간씩 다르다. 여기서는 각 조작에 대한 개념을 익히도록 하자.

(1) 인덱스 생성

인덱스 생성 문법은 다음과 같다.

여기서 각각의 파라미터가 의미하는 내용은 다음과 같다.

(2) 인덱스 삭제

인덱스 삭제 명령 형식은 다음과 같다.

은 생성된 인덱스 이름을 의미한다. 인덱스 관련 명령어에 대한 SQL 표 준이 없기에 제품별 Drop 명령문의 사용법은 약간씩 다르다. 보통 인덱스를 테이블의 종속 구조로 생각하여 인덱스를 삭제하기 위해 테이블의 변경을 가하는 형식의 명령 을 사용한다. 즉, ALTER TABLE 명령 뒤에 DROP INDEX 명령이 추가되는 형태로 사 용된다.

은 생성된 인덱스 이름을 의미한다. 인덱스 관련 명령어에 대한 SQL 표 준이 없기에 제품별 Drop 명령문의 사용법은 약간씩 다르다. 보통 인덱스를 테이블의 종속 구조로 생각하여 인덱스를 삭제하기 위해 테이블의 변경을 가하는 형식의 명령 을 사용한다. 즉, ALTER TABLE 명령 뒤에 DROP INDEX 명령이 추가되는 형태로 사 용된다.

(3) 인덱스 변경

인덱스에 대한 정의를 변경하는 명령문 형식은 다음과 같다.

한번생성된인덱스에대해변경이필요한경우는드물다. 또인덱스관련SQL문은 표준화가 안 되었으므로 인덱스 변경에 대한 명령문 지원 여부 및 방법은 벤더별로 다르다. 일부 제품은 인덱스에 대한 변경 SQL문이 없다. 이 경우 기존 인덱스를 삭제 하고 신규 인덱스를 생성하는 방식으로 사용이 권고되고 있다.

 

 

 

 

뷰 활용

먼저 생성된 테이블들을 이용하여 새로운 테이블과 뷰를 생성하는 DDL(Data Definition Language) 명령문을 작성할 수 있다.

뷰(View) 개요

뷰는 논리 테이블로서 사용자에게(생성 관점 아닌 사용 관점에서) 테이블과 동일하다. 아래 그림에서 `테이블A'와 `테이블B'는 물리 테이블을 의미하고, `뷰C'는 두 개의 테이블을 이용하여 생성한 뷰를 의미한다.

뷰는 `테이블A'와 같은 하나의 물리 테이블로부터 생성 가능하며, 다수의 테이블 또는 다 른 뷰를 이용해 만들 수 있다. 위 그림의 뷰와 같은 결과를 만들기 위해 다음 장에서 배 울 조인(Join) 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있다.

활용 상세

1. 뷰 사용

뷰를 사용하는 주된 이유는 다음과 같은 단순한 질의어를 사용할 수 있기 때문이다.

SELECT * FROM <View Name>;

즉, FROM 절에 있는 하나의 <뷰>를 통해 뷰를 구성하는 복수의 <테이블>을 대체하는 단 순성에 그 의의가 있다. 또 이러한 기능을 통해 테이블의 중요 데이터 일부만을 제공할 수 있는 등 다음과 같은 장점이 있다.

반면에 다음과 같은 단점이 있다.

뷰를 사용하기 위해서는 우선 뷰를 만들어야 한다. 뷰의 단순한 사용은 생성 과정에 의존적이다. 즉, 뷰를 어떻게 생성하였느냐에 따라 사용 방법이 달라진다. 뷰의 생성 방법에 대해 알아보도록 하자.

2. 뷰 생성

뷰 생성 명령의 일반 형태는 다음과 같다.

CREATE VIEW <뷰이름>(컬럼목록) AS <뷰를 통해 보여줄 데이터 조회용 쿼리문>

조회문의 다양한 변형에 따라 뷰의 내용이 달라진다. 즉, `테이블A'와 `테이블B'로부터 조회 가능한 형태 모두가 뷰로 치환될 수 있다.

3. 뷰 삭제 및 변경

뷰 정의 자체를 변경하는 것은 불가능하다. 일단 뷰를 정의하면, 뷰의 물리적 내용은 뷰의 이름과 데이터를 조회하기 위한 쿼리문뿐이다. 이때 뷰의 이름이나 쿼리문을 변경하는 수 단은제공되지않는다. 이경우뷰의삭제와재생성을통해뷰에대한정의변경이가능 하다. 뷰를 삭제하는 쿼리문은 다음과 같다.

DROP VIEW <View Name>;

4. 뷰 내용 변경

뷰를 통해 접근 가능한 데이터에 대한 변경이 가능하다. 하지만 모든 경우에 데이터의 변 경이 가능한 것이 아니라 일부 제약이 존재한다. 이러한 제약은 뷰 자체가 논리적 개념이 기에 물리적 상황에 의존적임을 의미한다. 예를 들어 PK에 해당하는 컬럼이 뷰에 정의되 어 있지 않은 경우 INSERT는 당연히 불가능하다.

 

 

 

 

다중 테이블 검색

조인, 서브쿼리, 집합 연산자를 사용하여 두 개 이상의 테이블로부터 데이터를 조회 하는 DML(Data Manipulation Language) 명령문을 작성할 수 있다.

다중 테이블 검색 방법

관계형 데이터베이스는 데이터의 중복을 최소화하기 위해 데이터를 분해하여 저장하고 통 합하여 사용한다. 데이터를 분해하는 방법으로 정규화 기법이 사용되며, 통합하는 기법으 로 다중 테이블에 대한 검색이 사용된다. 다중 테이블을 이용하는 보다 세부적인 기법은 다음과 같다.

조인(JOIN)

1. 조인 개념

조인은 결합을 의미하며, 관계형 데이터베이스에서의 조인은 교집합 결과를 가지는 결합 방법을 의미한다. 교집합이 되는 공통점은 다양한 관점에서 정의될 수 있다. 여기서 그 관 점을 정의하는 것이 바로 조인의 조건이 된다. 조인은 두 테이블의 공통값을 이용하여 컬럼을 조합하는 수단이다. 보통 PK와 FK 값을 결합하여 사용하는 것이 일반적이다. 보다 엄밀하게 말하자면 PK, FK와 관계없이 논리적 인 값들의 연관을 사용한다.

세 개 이상의 테이블에 대한 결합은 두 개의 테이블을 우선 결합하고 그 결과와 나머지 한 개의 테이블을 다시 결합한다.

2. 조인 유형

조인은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능이라고 할 수 있 다. 조인은 크게 물리적 조인과 논리적 조인으로 구분할 수 있으며, 물리적 조인은 데이터 베이스의 성능을 높이기 위한 튜닝 관점에서 다루는 주제로서, 본 학습에서는 사용자가 직접 제어할 수 있는 논리적 조인에 대해 알아본다.

논리적 조인은 크게 내부 조인과 외부 조인으로 구분할 수 있으며, 각각의 세부 유형은 다음과 같이 구분할 수 있다.

내부 조인에서 내부의 의미는 외부 조인에 대비하기 위해 사용 되었으므로 수식어가 없는 조인은 내부 조인을 의미한다. 내부 조인의 세부 유형은 조인의 조건에 따라 세분된다. 명시적으로 지정하지 않고, 두 테 이블의 컬럼 이름이 같은 값을 기준으로 하면 자연 조인이고, 특정 컬럼을 지정하면서 같 은 값을 비교하면 동등 조인이며, 값이 다른 것을 비교하면 비동등 조인이 된다. 외부 조인은 기준 테이블이 참조 테이블과 조인하되, 참조 테이블에 조인할 데이터가 있 는 경우는 참조 테이블의 데이터를 함께 출력하고, 참조 테이블의 조인 데이터가 없는 경 우에도 기준 테이블의 모든 데이터를 표시하고 싶은 경우에 사용한다.

보통 기준 테이블의 모든 값에 대해 참조 테이블의 데이터가 반드시 존재한다는 보장이 없는 경우 외부 조 인을 사용한다. 또 내부 조인에서 조인의 대상이 되는 컬럼을 명시적으로 선언하기 위하여 USING 조건절 이나 ON 조건절이 사용된다.

서브쿼리(Sub Query

1. 서브쿼리 개념

서브쿼리는 다음 그림과 같이 SQL문 안에 포함된 또 다른 SQL문을 의미한다. 서브쿼리의 용도는 알려지지 않은 기준을 위한 검색을 위해 사용한다.메인쿼리와 서브쿼리 관계는 주종 관계로서, 서브쿼리에 사용되는 컬럼 정보는 메인쿼리 의 컬럼 정보를 사용할 수 있으나 역으로는 성립하지 않는다.

2. 서브쿼리 유형

서브쿼리는 동작하는 방식이나 반환되는 데이터의 형태에 따라 분류할 수 있다. 동작하는 방식에 따른 서브쿼리의 종류는 다음과 같다.

반환되는 데이터 형태에 따른 서브쿼리의 종류는 다음과 같다.

집합 연산

1. 집합 연산 개념

테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식이다. 집합 연산자는 여러 질의 결과를 연결하여 하나로 결합하는 방식을 사용한다. 즉, 집합 연산자 는 2개 이상의 질의 결과를 하나의 결과로 만들어 준다. 일반적으로 집합 연산자를 사용 하는 상황은 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할 때와 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때 사 용할 수 있다.

2. 집합 연산 유형

테이블을 집합의 개념으로 보고 두 테이블 사이에 가능한 집합 연산은 다음과 같은 종류 가 있다.

 

출처 : NCS 학습모듈