29강[인덱스 작성과 삭제], 30강[뷰 작성과 삭제]

인덱스 작성과 삭제

 

1. 인덱스 작성

 

인덱스는 CREATE INDEX 명령으로 만든다. (DDL)

 

Oracle이나 DB2 등에서 인덱스는 스키마 객체로 취급한다. 따라서 스키마 내에 이름이 중복되지 않도록 해야한다.

SQL Server나 MySQL에서 인덱스는 테이블 내의 객체가 된다. 따라서 테이블 내에 이름이 중복되지 않도록 해야한다.

 

인덱스를 작성할 때는 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정할 필요가 있다.

이때 열은 복수로도 지정할 수 있다.

 

SYNTAX)

CREATE INDEX 인덱스명 ON 테이블명 (열명1, 열명2, ... )

 

ex)

CREATE INDEX isample65 ON sample62(no);

 

2. 인덱스 삭제

SYNTAX) 스키마 객체의 경우

DROP INDEX 인덱스명

 

SYNTAX) 테이블 내 객체의 경우

DROP INDEX 인덱스명 ON 테이블명

 

인덱스는 테이블에 의존하는 객체이므로 DROP TABLE로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제된다. 인덱스만 삭제하는 경우에는 DROP INDEX를 사용한다.

 

인덱스를 작성해두면 검색이 빨라진다. 작성한 인덱스의 열을 WHERE 구로 조건을 지정하여 SELECT 명령으로 검색하면 처리속도가 향상된다.

한편, INSERT 명령의 경우에는 인덱스를 최신 상태로 갱신하는 처리가 늘어나므로 처리속도가 조금 떨어지게 된다.

 

 

다음과 같은 명령으로 인덱스를 작성해보자.

CREATE INDEX isample651 ON sample62(a);

 

WHERE 구에 a 열에 대한 조건식을 지정한 겨우 SELECT 명령은 인덱스를 사용해 빠르게 검색할 수 있다.

SELECT * FROM sample62 WHERE a = 'a';

 

3. EXPLAIN

실제로 인덱스를 사용해 검색하는지 확인하려면 EXPLAIN 명령을 사용하면 된다.

 

SYNTAX)

EXPLAIN SQL명령

 

EXPLAIN 뒤에 확인하고 싶은 SQL명령을 지정하면 된다.

하지만 실제로 SQL명령이 실행되는 것은 아니고 어떤 상태로 실행되는지를 데이터베이스가 설명해줄 뿐이다.

EXPAIN은 표준 SQL에는 존재하지 않는 데이터베이스 제품 의존형 명령이다.

하지만 대부분 이와 비슷한 명령을 지원한다.

possible_keys는 사용할 수 있는 인덱스가 표시되며, key는 사용된 인덱스가 표시된다.

 

인덱스를 작성하지 않은 WHERE 조건을 사용하면 possible_keys와 key가 NULL이 된다.

 

4. 최적화

데이터베이스 내부의 최적화에 의해, SQL 명령을 실행하기에 앞서 실행계획을 세운다.

실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단한다. 이때 판단 기준으로 인덱스의 품질도 고려한다.

EXPLAIN 명령은 이 실행계획을 확인하는 명령이다.

 

예를 들어 '예' 또는 '아니오' 라는 값만 가지는 열이 있다면 해당열에 인덱스를 지정해도 다음과 같은 이진트리가 되어 좋은 구조를 가지지 못한다.

단순한 리스트와 별 다른 차이가 없는 구조로, 이진탐색에 의한 효율화를 기대할 수 없다.

 

데이터의 종류가 적으면 적을수록 인덱스의 효율도 떨어진다.

반대로 서로 다른 값으로 여러 종류의 데이터가 존재하면 그만큼 효율은 좋아진다.

이런식으로 인덱스의 품질을 고려해 실행계획이 세워지게 된다.

 

 

뷰 작성과 삭제

뷰는 테이블과 같은 부류의 데이터베이스 객체 중 하나이다.

 

서브쿼리는 FROM 구에서도 기술할 수 있다는 것을 알 것이다.

여기서 FROM 구에 기술된 서브쿼리에 이름을 붙이고 데이터베이스 객체화하여 쓰기 쉽게 한 것을 뷰라고 한다.

 

 

1. 뷰

데이트베이스 객체란 테이블이나 인덱스 등 데이터베이스 안에 정의하는 모든 것을 말한다.

뷰 역시 데이터베이스 객체 중 하나이다.

 

본래 데이터베이스 객체로 등록할 수 없는 SELECT 명령을, 객체로서 이름을 붙여 관리할 수 있도록 한 것이 뷰이다.

SELECT 명령을 실행했을 때 테이블에 저장된 데이터를 결과값으로 반환한다.

따라서 뷰를 참조하면 그에 정의된 SELECT 명령의 실행결과를 테이블처럼 사용할 수 있다.

 

=> 뷰는 SELECT 명령을 기록하는 데이터베이스 객체다.

=> 뷰를 사용함으로써 자주 사용하거나 복잡한 SELECT 명령을 뷰로 만들어 편리하게 사용할 수 있는 것이다.

 

뷰는 테이블처럼 취급할 수 있지만 실체가 존재하지 않는다는 의미로 '가상 테이블' 이라 불리기도 한다.

SELECT 명령으로 이루어지는 뷰는 테이블처럼 데이터를 쓰거나 지울 수 있는 저장공간을 가지지 않는다.

 

 

2. 뷰 작성과 삭제

뷰는 데이터베이스 객체이기 때문에 DDL로 작성하거나 삭제한다.

 

- 뷰의 작성

 

SYNTAX)

CREATE VIEW 뷰명 AS SELECT 명령

(여기서 쓰는 AS 키워드는 별명을 붙일 때 사용하는 AS와 달리 생략할 수 없다)

 

뷰는 필요에 따라 열을 지정할 수도 있는데, 이 경우에는 이름 뒤에 괄호로 묶어 열을 나열한다.

 

SYNTAX)

CREATE VIEW 뷰명 (열명1, 열명2, ... ) AS SELECT 명령

 

ex)

 

- 뷰 삭제

DROP VIEW 뷰명

 

 

3. 뷰의 약점

뷰는 데이트베이스 객체로서 저장장치에 저장된다.

하지만 테이블과 달리 대량의 저장공간을 필요로 하지 않는다.

데이터베이스에 저장되는 것은 SELECT 명령뿐이기 때문이다.

다만 저장공간을 소비하지 않는 대신 CPU 자원을 사용한다.

 

뷰의 근원이 되는 테이블에 보관하는 데이터양이 많은 경우, 집계처리를 할 때도 뷰가 사용된다면 처리속도가 많이 떨어질 수 밖에 없다. 뷰를 중첩해서 사용하는 경우에도 처리 속도가 떨어지기 쉽다.

이같은 상황을 회피하기 위해 사용할 수 있는 것이 머티리얼라이즈드 뷰이다.

 

- 머티리얼라이즈드 뷰(Materialized View)

일반적으로 뷰는 데이터를 일시적으로 저장했다가 쿼리가 실행 종료될 때 함께 삭제된다.

그에 비해 머티리얼라이즈드 뷰는 데이터를 테이블처럼 저장장치에 저장해두고 사용한다.

그러므로 일반적인 뷰처럼 매번 SELECT 명령을 실행할 필요가 없다.

다만 뷰에 지정된 테이블의 데이터가 변경된 경우에는 SELECT 명령을 재실행하여 데이터를 다시 저장한다.

 

뷰에 지정된 테이블의 데이터가 자주 변경되지 않는 경우라면 머티리얼라이즈드 뷰를 사용하여 뷰의 약점을 어느정도 보완할 수 있다.

(머티리얼라이즈드 뷰는 Oracle과 DB2에서만 사용할 수 있는 데이터베이스 객체이다)

 

- 함수 테이블

부모쿼리와 어떤식으로든 연관된 서브쿼리의 경우에는 뷰의 SELECT 명령으로 사용할 수 없다.

대신 함수테이블을 사용하면 된다.

함수는 인수를 지정할 수 있기 때문에 인수의 값에 따라 WHERE 조건을 붙여 결과값을 바꿀 수 있다.

그에 따라 상관 서브쿼리처럼 동작할 수 있다.