1) 인덱스 활용
1) 인덱스(index)
1) 인덱스 개념
인덱스란 데이터를 빠르게 찾을 수 있는 수단으로, 테이블에 대한 조회 속도를 높여주는 자료구조를 의미한다.
인덱스는 테이블의 특정 레코드 위치를 알려주는 용도로 사용한다.
테이블에서 기본키로 지정할 경우 자동으로 인덱스가 생성된다.
2) 인덱스 활용
① 인덱스 생성
CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column(s)>);
각각의 파라미터가 의미하는 내용은 다음과 같다.
| [UNIQUE] | 인덱스가 걸린 컬럼에 중복값을 허용하지 않음 (생략 가능) |
| <index_name> | 생성하고자 하는 인덱스 테이블 이름 |
| <table_name> | 인덱스 대상 테이블 이름 |
| <column(s)> | 인덱스 대상 테이블의 특정 컬럼 이름(들) |
② 인덱스 변경
ALTER [UNIQUE] INDEX <index_name> ON <table_name> (<column(s)>);
한번 생성된 인덱스에 대해 변경이 필요한 경우는 드물다.
일부 제품은 인덱스에 대한 변경 SQL문이 없다. 이 경우 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용이 권고되고 있다.
③ 인덱스 삭제
ALTER TABLE <table_name> DROP INDEX <index_name>;
<index_name> 은 생성된 인덱스 이름을 의미한다.
인덱스 관련 명령어에 대한 SQL 표준이 없기에 제품별 DROP 명령문의 사용법은 약간씩 다르다. 보통 인덱스를 테이블의 종속 구조로 생각하여 인덱스를 삭제하기 위해 테이블의 변경을 가하는 형식의 명령을 사용한다. 즉, ALTER TABLE 명령 뒤에 DROP INDEX 명령이 추가되는 형태로 사용된다.
2) 뷰 활용
1) 뷰(View)
1) 뷰 개념
뷰는 논리 테이블로서 사용자에게는(생성 관점이 아닌 사용 관점) 테이블과 동일하다.
뷰는 하나의 물리 테이블로부터 생성 가능하며, 다수의 테이블 또는 다른 뷰를 이용하여 만들 수 있다.
뷰는 같은 결과를 만들기 위해 조인(Join) 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있다.
2) 뷰 특징
뷰를 사용하는 주된 이유는 다음과 같은 단순한 질의어를 사용할 수 있기 때문이다.
SELECT * FROM <view_name>;
즉, FROM 절에 있는 하나의 <뷰>를 통해 뷰를 구성하는 복수의 <테이블>을 대체하는 단순성에 그 의의가 있다. 또 이러한 기능을 통해 테이블의 중요 데이터 일부만을 제공할 수 있다는 장점이 있다.
| 장점 | 논리적 독립성 제공 | 뷰는 논리 테이블이므로 테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다. |
| 사용자 데이터 관리 용이 | 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능하다. | |
| 데이터 보안 용이 | 중요 보안 데이터를 저장 중인 테이블에는 접근을 불허하고, 해당 테이블의 일부 정보만을 볼 수 있는 뷰에는 접근을 허용하는 방식으로 보안 데이터에 대한 접근 제어가 가능하다. | |
| 단점 | 뷰 자체 인덱스 불가 | 인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스를 가지지 못한다. |
| 뷰 정의 변경 불가 | 뷰의 정의를 변경하려면 뷰를 삭제하고 재생성하여야 한다. | |
| 데이터 변경 제약 존재 | 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있다. |
3) 뷰 생성
뷰 생성 명령의 일반 형태는 다음과 같다.
CREATE VIEW <뷰 이름>(컬럼목록) AS <뷰를 통해 보여줄 데이터 조회용 쿼리문>;
뷰 생성 방법
| 상황 | 뷰 생성 쿼리문 |
| 테이블A를 그대로 뷰 A로 만드는 상황 | CREATE VIEW 뷰A AS SELECT * FROM 테이블A; |
| 테이블A의 일부 컬럼은 뷰 X로 만드는 상황 | CREATE VIEW 뷰X AS SELECT 컬럼1, 컬럼2, ... FROM 테이블A; |
| 테이블A와 테이블B를 조인한 결과를 뷰 Y로 만드는 상황 | CREATE VIEW 뷰Y AS SELECT * FROM 테이블A, 테이블B WHERE 테이블A.컬럼 = 테이블B.컬럼; |
4) 뷰 삭제 및 변경
뷰 정의 자체를 변경하는 것은 불가능하다. 일단 뷰를 정의하면, 뷰의 물리적 내용은 뷰의 이름과 데이터를 조회하기 위한 쿼리문일뿐이다. 이 때 뷰의 이름이나 쿼리문을 변경하는 수단은 제공되지 않는다. 이 경우 뷰의 삭제와 재생성을 통해 뷰에 대한 정의 변경이 가능하다.
뷰를 삭제하는 쿼리문은 다음과 같다.
DROP VIEW <view_name>;
뷰를 통해 접근 가능한 데이터에 대한 변경이 가능하다. 하지만 모든 경우에 데이터의 변경이 가능한 것이 아니라 일부 제약이 존재한다. 이러한 제약은 뷰 자체가 논리적 개념이기에 물리적 상황에 의존적임을 의미한다. 예를 들어 기본키에 해당하는 컬럼이 뷰에 정의되어 있지 않은 경우 INSERT는 불가능하다.
3) 다중 테이블
1) 다중 테이블
다중 테이블 검색 방법
- 관계형 데이터베이스는 데이터의 중복을 최소화하기 위해 데이터를 분해하여 저장하고 통합하여 사용한다.
- 데이터를 분해하는 방법으로 정규화 기법이 사용되며, 통합하는 기법으로 다중 테이블에 대한 검색이 사용된다.
다중 테이블을 이용하는 보다 세부적인 기법은 다음과 같다.
| 조인 | 두 개의 테이블을 결합하여 데이터를 추출하는 기법 |
| 서브쿼리 | SQL문 안에 포함된 SQL문 형태의 사용 기법 |
| 집합 연산 | 테이블을 집합 개념으로 조작하는 기법 |
2) 조인(Join)
1) 조인 개념
조인은 결합을 의미하며, 관계형 데이터베이스에서의 조인은 교집합 결과를 가지는 결합 방법을 의미한다.
조인으 두 테이블의 공통값을 이용하여 컬럼을 조합하는 수단이다. 보통 기본키와 후보키값을 결합하여 사용하는 것이 일반적이다. 보다 엄밀하게 말하자면 기본키, 후보키와 관계없이 논리적인 값들의 연관을 사용한다.
2) 조인 유형
조인은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능이다.
크게 논리적 조인과 물리적 조인으로 구분할 수 있다.
| 논리적 조인 | 사용자의 SQL문에 포함되는 테이블 결합 방식 두 테이블에 공통으로 존재하는 컬럼을 이용하는 방식(공통 컬럼 기반) 종류 - 내부 조인(Inner Join) - 외부 조인(Outer Join) |
| 물리적 조인 | 데이터베이스의 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식 특정 테이블의 모든 데이터를 기준으로 다른 테이블의 정보를 추출(다른 테이블에 값이 없어도 출력됨) 종류 - 중첩 반복 조인(Nested Loop Join) - 정렬 합병 조인(Sort-Marge Join) - 해시 조인(Hash Join) |
① 논리적 조인
| 내부 조인 (Inner Join) |
동등 조인(Equl Join) | 공통 존재 컬럼의 값이 같은 경우를 추출 |
| 자연 조인(Natual Join) | 두 테이블의 모든 컬럼을 비교하여 같은 컬럼명을 가진 모든 컬럼 값이 같은 경우를 추출 | |
| 교차 조인(Cross Join) | 조인 조건이 없는 모든 데이터의 조합을 추출 | |
| 외부 조인 (Outer Join) |
왼쪽 외부 조인(Left Outer Join) | 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출 |
| 오른쪽 외부 조인(Right Outer Join) | 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출 | |
| 완전 외부 조인(Full Outer Join) | 양쪽 모든 데이터를 추출 |
② 물리적 조인
| 중첩 반복 조인(Nested Loop Join) | 2개 이상의 테이블에서 하나의 집합을 기준으로 바깥 테이블의 처리 범위를 하나씩 엑세스하면서 그 추출된 값으로 안쪽 테이블을 조인하는 방식 |
| 정렬 합병 조인(Sort-Marge Join) | 적당한 인덱스가 없을 때 사용되며 양쪽 테이블의 처리 범위를 각자 실행하여 정렬된 결과를 차례로 스캔하고, 연결고리 조건으로 합병하는 방식 |
| 해시 조인(Hash Join) | 조인할 테이블에 대해아 해시 함수를 생성하고 해시 함수의 순서대로 결과를 출력하는 방식 |
3) 서브쿼리(Sub Query)
1) 서브쿼리의 개념
서브쿼리는 SQL문 안에 포함된 또 다른 SQL문을 의미한다. 서브쿼리는 알려지지 않은 기준을 검색하기 위해 사용한다.
메인쿼리와 서브쿼리의 관계는 주종 관계이기 때문에, 서브쿼리에 사용되는 컬럼 정보는 메인쿼리의 컬럼처럼 정보를 사용할 수 있으나 역으로는 성립하지 않는다.
2) 서브쿼리의 유형
서브쿼리는 동작하는 방식이나 반환되는 데이터의 형태에 따라 분류할 수 있다.
① 동작 방식에 따른 서브쿼리
| 비연관(Un-Correlated) 서브쿼리 | 서브쿼리가 메인쿼리의 컬럼을 가지고 있지 않은 형태 메인쿼리에 서브쿼리에서 실행된 결과값의 제공 용도 |
| 연관(Correlated) 서브쿼리 | 서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태 메인쿼리가 먼저 수행되어 얻은 데이터를 서브쿼리의 조건에 맞는지 확인하고자 할 경우에 사용 |
② 데이터 형태에 따른 서브쿼리
| Single Row(단일 행) | 서브쿼리의 결과가 항상 1건 이하인 서브쿼리 단일 행 비교 연산자(=, <, >, <=, >=)가 사용된다. |
| Multiple Row(다중 행) | 서브쿼리 실행 결과가 여러 건인 서브쿼리 다중 행 비교 연산자(IN, ANY, SOME, EXISTS)가 사용됨 |
| Multiple Column(다중 컬럼) | 서브쿼리 결과가 여러 컬럼으로 반환되는 서브쿼리 메인쿼리의 조건절에 여러 컬럼을 동시에 비교할 때, 서브쿼리와 메인쿼리에서 비교하는 컬럼 개수와 위치가 동일해야 함 |
4) 집합 연산
1) 집합 연산의 개념
테이블을 집합 개념으로 보고, 두 테이블 연산에 집합 연산자를 사용하는 방식이다.
집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어 준다. 일반적으로 집합 연산자를 사용하는 상황은 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할때와 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때 사용할 수 있다.
2) 집협 연산의 유형
| UNION | 여러 SQL문의 결과에 대한 합집합(중복 행 제거함) |
| UNION ALL | 여러 SQL문의 결과에 대한 합집합(중복 행 제거하지 않음) |
| INTERESCTION | 여러 SQL문의 결과에 대한 교집합(중복 행 제거) |
| MINUS | 앞의 SQL문의 결과와 뒤의 SQL문의 결과 사이에 차집합(중복 행 제거함, 일부 제품의 경우 EXCEPT 사용) |
