CREATE INDEX ["HR"]."EMP_DEPARTMENT_IX" ON ["HR"]."EMPLOYEES" ("DEPARTMENT_ID");
CREATE INDEX ["HR"]."EMP_NAME_IX" ON ["HR"]."EMPLOYEES" ("LAST_NAME",["FIRST_NAME"]);
자. 이것이 인덱스를 생성하는 구문이다. 보면 [] 에 들어가는 것들은 제외를 할수 있다. 제외가 되는 구문은 UNIQUE와 스키마명, 다중컬럼을 생성하는 구문이 될 수 있겠다.
1번과 2번은 단일 인덱스(Single Index) 를 생성하는 구문이고 3번은 복합 인덱스(composite index 혹은 concatenated Index) 라고 할 수 있겠다.
또한 B-트리의 인덱스의 경우 최대 32개까지 생성할 수 있고 비트맵인 경우 최대 30개 까지 가능하다.
복합 인덱스를 UNIQUE 인덱스로 구성할 경우 인덱스에 해당되는 개별 컬럼의 값은 중복이 되어도 상관이 없지만 인덱스를 구성하는 컬럼의 조합 값은 중복값을 허용하지 않는다.
또한 일반적으로 SELECT 문의 WHERE 절에서 사용되는 컬럼 순으로 인덱스를 구성 해야 한다. 미국의 경우 이름보다는 성을 많이 사용하기 때문에 인덱스를 구성할 때 FIRST_NAME 컬럼 앞에 LAST_NAME 컬럼이 오게 한것이다.
인덱스 삭제
- 인덱스 삭제도 다른 오브젝트들처럼 DROP문을 사용하여 삭제가 가능하다.
인덱스의 종류
- 유일성 여부에 따라 : UNIQUE 인덱스 와 NON-UNIQUE 인덱스
- 인덱스 구성 컬럼의 개수에 따라 : 단일 인덱스와 복합 인덱스
- 인덱스 생성자에 따라 : 수동 인덱스와 자동 인ㅔ덱스
이 외에도 인덱스 성격, 구조에 따라 다음과 같이 구분할 수 있다.
- B-트리 인덱스, 비트맵인덱스, 해쉬 클러스터 인덱스, 리버스 키 인덱스. 비트맵 조인 인덱스, 함수기반 인덱스,
도메인 인덱스, 분할 인덱스
이중에서도 가장 많이 사용되는 인덱스는 B-트리 인덱스이다. CREATE INDEX 문이나 오라클이 생성하는 자동 인덱스는 모두 B-트리인덱스 이다.
B-트리 인덱스
- B-트리 인덱스(Balanced-Tree Index)는 b-트리 알고리즘을 사용한 인덱스로써 가장 일반적인 형태의 인덱스 이다. 이것은 실제로 ROWID를 가지고 있따.
비트맵 인덱스
- 이것은 ROWID 값 대신 각 키에 대한 비트맵 값(0과1로 구성됨) 이 사용된다. 인덱스의 카디널리티가 낮은 컬럼의 경우 비트맵 인덱스에 적당한 컬럼이라 할 수 있다.
카디널리티란 ?
- 컬럼값의 종류의 척도를 카디널리티라 한다. 예를들어 성별을 구분하는 GENDER 컬럼이 있다면 이 컬럼에 올 수 있는 값은 남성 혹은 여성 두가지 종류의 값 뿐이다. 이러한 GENDER 컬럼의 경우 카디널리티가 낮다라고 할 수 있다.
인덱스 생성시 고려해야할 사항
1. 자주 조회되는 컬럼을 인덱스 컬럼으로 선택
2. 참조 제약조건이 있는 컬럼에 생성
3. 전체 데이터의 15% 이하의 데이터를 조회할 경우. 항상 그렇다고 할순 없지만 15%를 넘어갈 경우 인덱스를 통해
조회하는 것보다 테이블 전체를 검색하여 조회하는것이 더 빠를수도 있다.
4. 테이블 간 조인에 사용된 컬럼을 인덱스 컬럼으로 선택하면 조인 성능이 향상된다.
5. 전체 로우가 적은 경우에는 굳이 인덱스를 만들 필요가 없다.
6. 복합 인덱스를 구성하는 경우 컬럼의 순서는 SELECT문의 WHERE절에서 좀더 자주 사용되는 컬럼을 먼저 오게
한다.
7. LONG과 LONG RAW 타입 컬럼은 인덱스로 만들수 없다.
8. 복합 인덱스로 만들수 있는 컬럼은 32개 이다.
뷰의 생성
SELECT 문장
여기서 특이한 문장을 볼수가 있다. 바로 REPLACE인데 기존에 있는 뷰를 다시 생성하고자 한다면 해당 뷰를 제거하고 다시 생성해야 하지만 REPLACE를 쓰면 OR REPLACE 구문을 쓰면 DROP을 할 필요가 없다.
SELECT first_name, last_name, email, hire_date
FROM employees
WHERE department_id = 20;
뷰의 특성?
1. 데이터 보안
2 . 복잡한 쿼리를 저장하여 사용
- 여러 테이블을 조인하는 복잡한 쿼리가 있는데 그 사용빈도가 높다면 이를 뷰로 만들어 사용하는것
편리하다.
3. 뷰를 사용한 데이터의 변경
- 데이터 변경이 가능한 뷰가 있고 그렇지 않은 뷰가 있다. 데이터를 보기만 하는 뷰를 Read-Only 뷰라하고 변
경이 가능한 뷰를 Updatable 뷰라 한다.
Read-Only 뷰
SELECT 문장
WITH READ ONLY;
Updatable 뷰
SELECT 문장
Read-only 뷰의 경우 마지막에 'WITH READ ONLY'구문을 명시해주면 된다. 이구문을 명시하지 않으면 디폴트로 updatable 뷰가 생성 된다.
Updatable뷰는 insert, update, delete가 가능하다 하지만 기준이 되는 테이블에 not null 속성이 있는데 이속성들을 뷰 테이블이 가지가지고 있지않고 insert를 하려고 한다면 에러가 발생한다. 따라서 기준이 되는 테이블의 not null 속성들은 view 테이블에서도 모두 가지고 있어야 insert가 정상적으로 이루어 진다.
하지만 다음의 내용을 포함한 UPDATABLE 뷰를 생성했다 하더라도 데이터를 갱신할 수 없다.
- UNION, UNION ALL을 사용한 경우
- DISTINCT를 사용한 경우
- 집계 및 분석 함수를 사용한 경우(SUM,AVG 등)
- GROUP BY, ORDER BY, MODEL, CONNECT BY, START WITH절을 포함한 경우
- SELECT 리스트에 서브쿼리가 포함된 경우
- SELECT 리스트에 의사컬럼을 사용한 경우 (ROWID나 ROWNUM )등 은 UDATE 할수 없다.
어떤 컬럼들이 INSERT,UPDATE,DELETE 가능한 지 알아보려면 USER_UPDATABLE_VIEW라는 시스템 뷰를 조회하면 된다.