MERGE (1) : 테이블 병합 기초 - 같은 구조를 가진 두 개의 테이블을 하나로 합치기
1. MERGE의 개념 이해와 표현
1) MERGE의 개념
MERGE는 DML(Data Manipulation Languae)에 속하는 명령어로서, 테이블A와 테이블B를 병합하는 데 사용하는 테이블 병합 명령어입니다.
2) MERGE의 표현
- 테이블A에 테이블B를 병합합니다.
- 병합 시, 기준이 되는 컬럼은X 입니다. using on () 부분에 이 병합 기준 조건을 지정하여 줍니다.
- 조건을 만족하면 update 또는 delete를 수행하고,
- 조건을 만족하지 못하면 테이블B의 내용이 테이블A에 새로이 추가됨으로서 테이블A로 병합되는 것입니다.
megre into 테이블이름A 약칭A
using 테이블이름B 약칭B
on (A.컬럼이름X=B.컬럼이름X)
when matched then
update set 업데이트 내용
when not matched then
insert values(B.컬럼이름P, B.컬럼이름Q, B.컬럼이름R, ...);
* 이하의 예제에서는 hr 연습계정의 employees 테이블을 복사한 employees_t, employees_t2 테이블을 사용합니다.
CREATE TABLE 구문을 사용하여 다음의 두 SQL 문장을 실행, employees_t와 employees_t2 테이블을 생성해 주세요.
create table employees_t
as select employee_id,
first_name||' '||last_name as fullname,
salary,
job_id
from employees
where salary between 2000 and 3000;create table employees_t2
as select employee_id,
first_name||' '||last_name as fullname,
salary,
job_id
from employees
where salary >=10000;
2. 예제 : MERGE의 사용
1) 예제 : 같은 구조를 가진 employees_t 테이블과 employees_t2 테이블을 employees_t 테이블로 병합하기
- 병합할 (합쳐질) 테이블 : employees_t
- 기준이 되는 컬럼 : employees_id
- 병합할 컬럼 : 전체(employees_id, fullname, salary, job_id)
위 조건에 따라 MERGE SQL 문장을 구성하면 다음과 같습니다.
merge into employees_t t1
using employees_t2 t2
on (t1.employee_id=t2.employee_id)
when matched then
update set t1.fullname=t2.fullname
when not matched then
insert values(t2.employee_id, t2.fullname, t2.salary, t2.job_id);위 문장을 워크시트에 입력 후 실행(Ctrl+Enter)합니다.
하단 스크립트 출력 창에 19개 행이(가) 병합되었다는 알림이 나타납니다.
테이블 employees_t(26개 행)에 테이블employees_t2(19개 행)를 병합하는 문장이므로, employees_t2의 19개 행이 employees_t 테이블에 추가된 것입니다.
SELECT FROM 구문을 사용하여, employees_t 테이블을 조회해 보면 본래 26개 행이었던 것이 45개 행으로 늘어나 있습니다. 두 개의 테이블 내용이 employees_t테이블로 병합된 것입니다.
* 물론, MERGE로 테이블 내용을 병합하더라도 employees_t2 테이블의 자료들은 그대로 보존되어 있습니다.
MERGE 문장은 JOIN, UPDATE, DELETE 등 다양한 명령어들과 함께 응용하여 사용할 수 있는 폭이 넓습니다.
NEXT_DAY, LAST_DAY : 주어진 날짜로부터 다음 ■요일의 날짜, 달의 마지막 날짜를 출력하는 날짜함수
1. NEXT_DAY, LAST_DAY의 개념과 표현
1) NEXT_DAY, LAST_DAY의 개념
NEXT_DAY와 LAST_DAY 함수는 각각 주어진 날짜를 기준으로 '다음 ■요일' 또는 '이달 마지막 날'을 출력해 주는 날짜함수입니다.
|
함수
|
의미
|
|
NEXT_DAY
|
주어진 날짜의 다음 ■요일 날짜를 출력
|
|
LAST_DAY
|
주어진 날짜가 속한 달의 마지막 날짜를 출력
|
2) NEXT_DAY, LAST_DAY의 표현
(1) 기본식 : NEXT_DAY
select next_day(날짜, '■요일')
from dual;
(2) 기본식 : LAST_DAY
select last_day(날짜)
from dual;
2. 예제 : NEXT_DAY, LAST_DAY 함수의 사용
1) 예제 : NEXT_DAY를 사용하여, 오늘 날짜를 기준으로 다음 월요일, 다음 화요일, 다음 토요일이 며칠인지 출력하기
NEXT_DAY에 SYSDATE(현재시각)를 함께 사용하여 SQL 문장을 구성하면 다음과 같습니다.
select sysdate as 오늘,
next_day(sysdate, '월요일') as 다음월요일,
next_day(sysdate, '화요일') as 다음화요일,
next_day(sysdate, '토요일') as 다음토요일
from dual;
2021년 1월 11일인 오늘은 월요일입니다.
- 다음 월요일 : 2021년 1월 18일(다음주 월요일)
- 다음 화요일 : 2021년 1월 12일(내일)
- 다음 토요일 : 2021년 1월 16일(이번주 토요일)
2) 예제 : LAST_DAY를 사용하여, 이번달 마지막 날짜를 출력하기
LAST_DAY 날짜함수에 오늘의 시스템 시각(SYSDATE)을 투입하면 이번달 마지막 날이 출력될 것입니다.
select sysdate as 오늘,
last_day(sysdate) as 이번달마지막날
from dual;
ADD_MONTHS : 주어진 날짜로부터 N달 뒤 날짜 출력하는 날짜함수
ADD_MONTHS는 주어진 날짜 A로부터 N달 뒤의 날짜를 출력해 주는 날짜함수입니다.
- 고객의 가입 유효기간, 멤버쉽 유효기간
- D-Day 설정
... 등 N개월의 기한/시한을 가진 데이터가 있을 경우, DATE(날짜) 유형의 컬럼, 자료들과 함께 응용하여 사용할 수 있을 것입니다.
select add_months(날짜A, 숫자N)
from dual;예컨대 1월 3일로부터 N을 더하게 된다면, 결과가 되는 날짜는 (1+N)월 3일이 될 것입니다.
(* N의 크기가 크다면, 해를 넘긴 결과가 출력됩니다.)
1) 예제 : 오늘로부터 3달 뒤, 6달 뒤 날짜 출력하기
시스템 현재시각(SYSDATE)으로부터 3달 뒤, 6달 뒤의 날짜를 ADD_MONTHS를 사용하여 출력합니다.
select sysdate as 오늘,
add_months(sysdate, 3) as 석달뒤,
add_months(sysdate, 6) as 여섯달뒤
from dual위 SQL 문장을 실행(Ctrl+Enter)한 결과는 다음과 같습니다.
오늘 : 2021년 1월 12일
- 3달 뒤 : 2021년 4월 12일
- 6달 뒤 : 2021년 7월 12일
2) 예제 : 오늘로부터 12달 뒤, 20달 뒤 날짜 출력하기
달 수(N)가 커져 해를 넘기더라도 마찬가지로 오늘 날짜로부터 N달을 더한 해당 연원일일 정상적으로 출력됩니다.
select sysdate as 오늘,
add_months(sysdate, 12) as 열두달뒤,
add_months(sysdate, 20) as 스무달뒤
from dual;
오늘 : 2021년 1월 12일
- 12달 뒤 : 2021년 4월 12일
- 20달 뒤 : 2021년 7월 12일
PIVOT : 테이블 행렬전환, 행과 열을 전환한 피벗 테이블 만들기, 집계함수 결과를 (X,Y)에 따라 2차원으로 나타내기
엑셀을 사용하다 보면 종종 '피벗테이블' 이라는 용어를 들을 수 있었습니다. 마찬가지로, 오라클 SQL 에서도 PIVOT 기능을 사용하여 집계 결과를 조금 더 잘 표현할 수 있습니다.
1. PIVOT의 이해와 표현
1) PIVOT 이해
피벗 테이블(pivot table)
표의 행과 열을 전환하는 등의 과정을 통하여 통계를 재정렬하고, 그 결과로 표 데이터를 요약하는 방법.
이에는 집계함수(합계, 평균 등)가 사용될 수 있습니다.
Wikipedia
- 2가지 컬럼(X, Y)에 따라 그룹화되어 1개의 컬럼으로 표현된 집계함수 정보(K)를
- X, Y 중 하나를 행렬전환하여, K값을 행과 열의 2차원적 정보로 조회할 수 있게 한다.
2) 오라클SQL에서 PIVOT 표현
(1) 기본식 : PIVOT 전
- 컬럼X와 Y를 기준으로 그룹화하여 집계된 집계함수 결과(K)의 일반적 표현
select 컬럼이름X,
컬럼이름Y,
집계함수(컬럼이름K) as 별명K
from 테이블이름A
group by 컬럼이름X,
컬럼이름Y;
(2) 기본식 : 위 '(1)'에의 PIVOT 적용
- 가로 : 컬럼X 기준 (그룹)
- 세로 : 컬럼Y 기준 (그룹)
- 레코드 : (X, Y) 조합에 따른 집계함수K값의 값
이 표현됩니다.
with temp as
(select 컬럼이름X,
컬럼이름Y,
컬럼이름K
from 테이블이름A)
select * from temp
pivot (
집계함수(컬럼이름K) as 별명K
for 컬럼이름Y in (컬럼X값1 as 별명X1,
컬럼X값2 as 별명X2,
...)
);
2. 예제 : PIVOT 미사용 / 사용 결과 비교
1) 예제 : PIVOT을 사용하지 않는 경우(UNPIVOT)
employees 테이블에서 직책(job_id) 및 부서(department_id) 별 직원 수를 출력하기
GROUP BY로 그룹화 하고, COUNT 함수를 사용하여 직원 수(employee_id 집계)를 출력하는 SQL 쿼리 문장은 다음과 같습니다.
select job_id,
department_id,
count(employee_id) as count
from employees
group by job_id,
department_id;
각 직책별, 부서별 직원 수가 1개의 컬럼(COUNT)에 표시되고 있습니다.
이처럼, 그룹별로 집계된 집계함수 결과값이 각각 1개의 컬럼에 표시되는 경우가 UNPIVOT 입니다.
2) 예제 : PIVOT을 사용
employees 테이블에서 직책(job_id) 및 부서(department_id) 별 직원 수를 출력하기
위 '예제1)'의 내용에 PIVOT을 적용하여 직원수(count(employee_id))를 2차원적으로 나타내려는 것입니다.
with temp as
(select job_id,
department_id,
employee_id
from employees)
select * from temp
pivot (
count(employee_id) as c
for department_id in (10 as d10,
20 as d20,
30 as d30,
40 as d40,
50 as d50,
60 as d60,
70 as d70,
80 as d80,
90 as d90,
100 as d100,
null as none)
);
각 직책별(X), 부서별(Y) 직원 수가 여러개의 컬럼으로 나뉘어 표시되고 있습니다.
부서 변수가 가로로 행렬전환되어 직원의 수가 2차원적으로 표현되었습니다.
이처럼 피벗테이블(PIVOT table)을 이용하여 다른 집계함수를 사용할 수도 있고(부서별-직급별 평균임금, 최대임금, 급여합계 등) 여러가지 집계함수를 조회할 수도 있습니다.
3) 비교 : UNPIVOT / PIVOT
UNPIVOT : 피벗 해제, 피벗테이블 해제하기, 행렬변환, 언피벗 (언피봇)
피벗(피봇), 언피벗(언피봇)은 엑셀 뿐만 아니라 오라클SQL에서도 데이터베이스 가공을 위해 사용되고 있습니다.
unpivot
[Verb] (transitive,computing,databases) To convert columns into rows, so as to undo a pivot operation.
네이버 영어사전
1. UNPIVOT의 이해와 표현
1) UNPIVOT의 이해
UNPIVOT은 PIVOT의 반대 역할을 수행합니다. 즉, 피벗 테이블을 해제하거나, 피벗 테이블 형식의 테이블을 그렇지 않은 형태로 만듭니다.
아래 그림과 같이, 행과 열로 나누어져 분포하는 집계함수 값을 다음과 같은 형태로 묶어 새로운 구조의 테이블로 변환해 주는 역할을 하는 것입니다.
2) 기본식 : UNPIVOT의 표현
테이블A에서 컬럼Y1_K, 컬럼Y2_K, 컬럼Y3_K,...(컬럼들)을 하나의 컬럼Y에 대한 값(row들)으로 나타내는 UNPIVOT SQL 쿼리의 기본적인 표현 방법은 다음과 같습니다.
select * from 테이블이름A
UNPIVOT (
컬럼이름K for 컬럼이름Y in (컬럼이름Y1_K as 컬럼Y값1,
컬럼이름Y2_K as 컬럼Y값2,
...)
);* 이 때, 반드시 테이블A의 컬럼 이름들(컬럼이름Y1_K, Y2_K,...)을 정확히 알고 있어야 함에 유의합니다. 만약 IN() 에서 틀린 컬럼이름을 하나라도 나열할 경우 오류가 발생합니다.
as 값 다음에 입력하는 값들(컬럼Y값1, 컬럼Y값2, ...)은 UNPIVOT된 테이블의 컬럼Y에 입력할 값들입니다.
2. 예제 : UNPIVOT 사용
* 이하에서는 hr 연습계정의 employees 테이블을 PIVOT 하여 만든 임의의 테이블 pivot_sample을 사용합니다. 먼저 다음의 SQL 쿼리를 실행하여, 예제 테이블을 생성해 주세요.
create table pivot_sample
as select * from (with temp as
(select job_id,
department_id,
employee_id
from employees)
select * from temp
pivot (
count(employee_id) as c
for department_id in (10 as d10,
20 as d20,
30 as d30,
40 as d40,
50 as d50,
60 as d60,
70 as d70,
80 as d80,
90 as d90,
100 as d100,
null as none)
)
);
select * from pivot_sample;
1) 예제 : pivot_sample 테이블을 UNPIVOT 하여,
직책(job_id) 및 부서(department_id)별 직원 수를 나타내는 테이블을 출력하기
select * from pivot_sample
UNPIVOT (
count_employee for department_id in (d10_c as 10,
d20_c as 20,
d30_c as 30,
d40_c as 40,
d50_c as 50,
d60_c as 60,
d70_c as 70,
d80_c as 80,
d90_c as 90,
d100_c as 100,
none_c as null)
);
UNPIVOT 된 결과가 나타났습니다.
ROWID : 인덱스 관리를 위한 데이터 주소 조회 방법
오라클 SQL 디벨로퍼에서 '인덱스'를 사용하고자 할 때 인덱스와 함께 알아야 할 개념이 ROWID 입니다.
인덱스(Index)
데이터베이스에서 데이터의 위치 정보를 가진 일종의 주소록. 데이터베이스의 객체이다.
인덱스를 생성하면 모든 블록을 다 읽지 않고 자료가 있는 블록으로 찾아가 해당 블록만 읽어들이므로, 일부 작업에서 쿼리(query)의 성능 향상을 기대할 수 있다.
Oracle Database 11g Manual(Oracle)
1. ROWID의 이해
1) ROWID의 개념과 구조
ROWID 란 오라클에서 '데이터 주소'를 의미합니다.
테이블 각 레코드(행)이 가지고 있는 고유의 주소(정확한 저장 위치)를 나타내므로,
주소록 역할을 하는 인덱스(index) 작성 시 ROWID를 이해하고 사용할 수 있어야 합니다.
2) 오라클 SQL에서 ROWID 조회 방법
SELECT - FROM 구문을 사용할 때, 컬럼 이름들과 함께 'ROWID"를 입력해 줍니다.
WHERE 조건절을 함께 사용하여 출력되는 레코드를 필터링할 수도 있습니다.
select ROWID, 컬럼이름X, 컬럼이름Y, ...
from 테이블이름
where 조건;
* 이하 예제에서는 hr 연습계정의 employees 테이블을 사용합니다.
2. 예제 : ROWID 조회
employees 테이블 전체 레코드에 대하여, ROWID와 사원번호(employee_id), 풀네임(first_name, last_name), 급여(salary)를 조회하기
각 컬럼들과 ROWID를 SELECT - FROM 구문으로 조회합니다.
select ROWID,
employee_id,
first_name||' '||last_name as fullname,
salary
from employees;
조건에 맞는 레코드의 테이블 정보와 함께, 각 레코드의 ROWID가 반환된 모습입니다.
INDEX(인덱스) 종류 요약 정리 : B-TREE, UNIQUE, Function based, Compisite, BITMAP
인덱스(INDEX)란, 오라클 SQL에서 데이터가 위치한 장소를 기록한 일종의 주소록(색인)입니다.
인덱스를 사용하면 테이블의 행에 직접 빠르게 액세스할 수 있습니다.
인덱스는 데이터의 특성에 따라 사용하는 것이 효율적일 수도 있고 효율적이지 않을 수도 있습니다.
또한, 인덱스를 사용하더라도 가장 적합한 형태(종류)의 인덱스가 자료에 따라 다를 수 있습니다.
오라클 SQL 인덱스의 특징을 주요 유형별로 요약하여 정리하면 다음과 같습니다.
|
오라클 인덱스 분류
|
특성
|
||
|
B-TREE
|
- 데이터 값의 종류가 많고, 동일한 데이터가 적을 경우
- 연속적인 숫자 타입 값(continuous number), 고유 문자열 등
- 인덱스 값이 다양할수록 효율적
|
||
|
UNIQUE
|
컬럼에 중복된 값이 없어야 함
|
||
|
NON-UNIQUE
|
컬럼의 레코드 값이 중복될 수 있음
|
||
|
FUNCTION BASED
|
함수를 바탕으로 컬럼의 값을 연산하여 인덱스 생성(함수기반)
|
||
|
COMPISITE
|
- 2개 이상의 컬럼을 합쳐 인덱스를 생성
- CREATE INDEX와 WHERE 조건절을 함께 사용
|
||
|
DESCENDING
|
인덱스 생성 시 내림차순으로 생성
|
||
|
BITMAP
|
- 범주형(factor, category), 그룹형 데이터가 적합
- 값의 종류(범주)가 적은 이산형(discrete) 컬럼
- 다양한 반복이 자주 일어나는 자료에서 자주 사용함
- 예 : 성별, 부서번호, 국가, 예/아니오, 등급, ...
|
||
테이블 인덱스(INDEX) 정보 조회 방법 : 데이터 딕셔너리(데이터 사전, data dictionary) 활용하기
1. 인덱스(INDEX) 정보 조회의 이해와 방법
1) INDEX 정보 조회 필요성
- 내 데이터베이스에 인덱스가 존재하는가?
- 어떤 인덱스가 있는가?
- 내 테이블 인덱스의 이름, 타입, 유형 조회
- 인덱스 정렬 방법 탐색
....
오라클 SQL 데이터베이스가 가지고 있는 인덱스의 속성을 한데 모아서 조회해야 하는 경우가 있습니다.
이 때에는 '데이터 딕셔너리(Data Dictionary, 데이터 사전)' 기능을 사용하여,
오라클DB가 가지고 있는 다양한 테이블 정보와 인덱스 정보를 일목요연하게 조회할 수 있습니다.
2) INDEX 정보 조회 방법
(1) 주요 데이터베이스 레퍼런스(Database Reference) :
인덱스 관련 다음과 같은 레퍼런스를 활용하여, SELECT - FROM 구문으로 인덱스 속성을 조회할 수 있습니다.
|
INDEX 관련 Database Reference
|
||
|
ALL_IND_COLUMNS
|
DBA_IND_COLUMNS
|
USER_IND_COLUMNS
|
|
ALL_IND_EXPRESSIONS
|
DBA_IND_EXPRESSIONS
|
USER_IND_EXPRESSIONS
|
|
ALL_IND_PARTITIONS
|
DBA_IND_PARTITIONS
|
USER_IND_PARTITIONS
|
|
ALL_IND_SUBPARTITIONS
|
DBA_IND_SUBPARTITIONS
|
USER_IND_SUBPARTITIONS
|
|
ALL_INDEXES
|
DBA_INDEXES
|
USER_INDEXES
|
|
ALL_INDEXTYPE_COMMENTS
|
DBA_INDEXTYPE_COMMENTS
|
USER_INDEXTYPE_COMMENTS
|
|
ALL_INDEXTYPE_OPERATORS
|
DBA_INDEXTYPE_OPERATORS
|
USER_INDEXTYPE_OPERATORS
|
|
ALL_INDEXTYPES
|
DBA_INDEXTYPES
|
USER_INDEXTYPES
|
- DBA_ : 데이터베이스 전체에 생성된 내역 조회
- ALL_ : 모든 내역 조회
- USER_ : 사용자 내역 조회
(1) 기본식 : 인덱스 관련 레퍼런스A의 모든 딕셔너리 조회
select * from 레퍼런스이름A;
select * from user_indexes;
select * from user_ind_columns;
(2) 기본식 : 인덱스 관련 레퍼런스A 중 조건을 충족하는 인덱스에 한하여
딕셔너리X, Y... 조회
select 딕셔너리X,
딕셔너리Y, ...
from 레퍼런스이름A
where 조건;
* 이하의 예제에서는 hr 연습계정을 사용하였습니다.
2. 예제 : 딕셔너리와 레퍼런스를 사용한 INDEX 속성 조회
1) 예제 : user_indexes의 전체 내용 조회하기
select * from user_indexes;
user_indexes에서 다음을 포함한 다양한 정보를 확인할 수 있습니다.
(* 조회하여 보면 딕셔너리의 수가 많아서 가로스크롤이 깁니다.)
- 인덱스 이름(index_name)
- 인덱스 유형(index_type)
- 사용자 계정(table_owner)
- 테이블 이름(table_name), 타입(table_type)
- UNIQUE 여부(uniqueness)
- COMPRESSION 여부(compression)
....
2) 예제 : user_indexes의 일부 내용만 조회하기
- 조건에 맞는 인덱스에 한하여 특정한 딕셔너리만 조회하기
일반적인 SQL SELECT-FROM 쿼리처럼 조회할 딕셔너리를 지정하고, 필터링할 인덱스 조건을 WHERE 조건절에 나타내 줍니다.
select table_name,
index_name
from user_indexes
where table_name='EMPLOYEES';
* INDEX를 검색하는 문장에서 '테이블 이름'은 대문자로 써야 조회가 됩니다.
일반적인 SQL 쿼리에서와 달리, 이 경우 테이블 이름으로 소문자를 쓰면 아무것도 나타나지 않았습니다.
조건을 충족하는 인덱스에 한하여, 원하는 딕셔너리들만 조회되었습니다.
3) 예제 : user_ind_columns의
- 인덱스와 인덱스가 지정된 컬럼 관련 정보 조회하기
select * from user_ind_columns;
이 경우, USER_INDEXES와 약간 다른 정보를 보여줍니다.
컬럼 정보에 집중된 모습입니다.
- 인덱스 이름(index_name)
- 테이블 이름(table_name)
- 컬럼 이름(column_name)
- 컬럼 포지션(column_position)
- 컬럼 길이(column_length)
- 정렬 순서(descend)
...
INDEX(인덱스) : 기본 개념, 인덱스 생성 조건 정리, 인덱스의 장점 및 단점 요약
1. 인덱스(INDEX) 개념
오라클 SQL 에서 '인덱스(INDEX)'는 데이터가 위치한 장소의 정보를 가진 일종의 주소록입니다.
데이터의 주소(ROWID)를 가지고 있습니다.
인덱스는 빠르게 데이터 작업을 하기 위한 한 방법으로 사용되고 있습니다.
2. 특성
1) 인덱스 사용의 목적 · 장점
ⓐ 일부 SQL 쿼리(query) 성능 향상 가능
ⓑ 포인터를 사용하여 행 검색 속도를 높일 수 있는 스키마 객체
- 테이블 행에 직접 빠르게 접속 가능
- 인덱스 경로를 사용한 신속한 자료 검색
- 데이터베이스의 모든 블록을 다 읽지 않고, 목표 데이터가 있는 블록 주소를 찾아 당해 블록만을 메모리에 복사하여 작업을 진행함
2) 그 외의 특성
ⓐ 생성 : 자동 생성 / 직접 생성 가능
ⓑ 컬럼에 인덱스가 없을 경우 : 전체 테이블을 스캔함
ⓒ 인덱스 유지·관리·사용 : 오라클 서버에서 진행
ⓓ 인덱스와 테이블은 독립적 : 인덱스 생성/삭제가 다른 인덱스나 대상 테이블에 영향을 미치지 않음
(* 단, 테이블 삭제 시 해당 테이블의 인덱스는 삭제됨)
ⓔ 인덱스 변경 불가 : 생성 혹은 삭제만 가능
ⓕ 인덱스가 많거나 / 있다고 하여 무조건 쿼리 속도가 빨라지지 않음.
- 테이블과 연관된 인덱스가 많을수록 오라클 서버 부담 증가
3. 인덱스 생성 지침
(* Oracle Database 11g Manual 재구성)
|
INDEX 생성 권장 O
|
INDEX 생성 권장 X
|
|
컬럼에 포함된 값이 광범위함
|
인덱스화된 컬럼이 표현식 일부로 참조됨
|
|
컬럼에 null 값이 많이 포함되어 있음
|
테이블이 자주 갱신(업데이트) 됨
|
|
1개 이상의 컬럼이 함께 WHERE, JOIN 조건으로 자주 사용되고 있음
|
컬럼이 조건문에 자주 사용되지 않음
|
|
테이블이 크고, 대부분의 쿼리가 전체의 2~4% 미만 레코드 검색일 것으로 전망
|
테이블이 작거나, 대부분의 쿼리가 전체 중 2~4% 이상의 행을 검색할 것으로 전망함
|
CREATE INDEX : 인덱스 생성 방법, 인덱스 만들기 (B-TREE, BITMAP, UNIQUE, ...)
앞서 인덱스(INDEX)의 기본 개념, 종류, 데이터의 고유 주소인 ROWID에 대하여 알아보았습니다. 본 페이지에서는 인덱스를 직접 생성하는 것을 목적으로 합니다.
인덱스는 데이터베이스의 모든 블록을 다 읽지 않고, 목적한 데이터가 위치해 있는 블록만을 읽어들이므로, 적절한 상황에서 사용하면 작업을 효율적으로 만들 수 있습니다.
1. 인덱스 생성 방식 및 방법
1) 인덱스 생성 방식의 유형
ⓐ 자동 생성 : 테이블의 컬럼이 PRIMARY KEY 또는 UNIQUE 제약 조건을 갖도록 정의할 경우,
Oracle 서버가 자동으로 고유 인덱스를 생성 (* 인덱스의 이름 = 제약 조건에 지정된 이름).
ⓑ 수동 생성 : 유저가 직접 인덱스를 생성
2) 인덱스 생성 방법
* 이미 인덱스가 존재하는 컬럼/옵션의 경우 오류(ORA-01408)가 발생할 수 있습니다.
ORA-01408: 열 목록에는 이미 인덱스가 작성되어 있습니다
유형별로 인덱스를 만드는 방법은 다음과 같이 정리할 수 있습니다.
1) 기본식 : B-TREE, NON UNIQUE
- 테이블 A의 컬럼X에 대한 인덱스K 생성하기
create index 인덱스이름K
on 테이블이름A(컬럼이름X);
2) 기본식 : B-TREE, NON UNIQUE, COMPISITE
CREATE INDEX 문을 실행하여 하나 이상의 열에 인덱스를 생성할 수 있습니다.
create index 인덱스이름K
on 테이블이름A(컬럼이름X, 컬럼이름Y);
3) 기본식 : UNIQUE
인덱스가 기반으로 하는 열의 값이 고유해야 함을 나타내려면 UNIQUE를 지정합니다.
create unique index 인덱스이름K
on 테이블이름A(컬럼이름X);
4) 기본식 : DESCENDING
- 테이블A의 컬럼X에 대하여, 인덱스에 내림차순을 적용하는 경우
큰 값들이 중요하고, 자주 찾을 때 내림차순 옵션을 적용할 수 있습니다.
물론 다른 UNIQUE 등 인덱스 유형과 함께 사용할 수 있습니다.
create index 인덱스이름K
on 테이블이름A(컬럼이름X desc);
5) 기본식 : Functin Based
- 테이블A의 컬럼X에 대한 산술식 바탕의 인덱스
이 산술식을 따르는 새로운 컬럼을 만들고 나서 그 열에 대해 인덱스를 만드는 것이 더 좋을 수도 있습니다.
create index 인덱스이름K
on 테이블이름A(컬럼이 포함된 산술식);
6) 기본식 : BITMAP
각 구분에 대해 비트맵(0,1)을 사용하여 인덱스를 생성합니다. discrete, binomial, 카테고리형, 범주형 변수인 컬럼의 경우에 BITMAP 유형이 적합할 수 있습니다.
create bitmap index 인덱스이름K
on 테이블이름A(컬럼이름X)
* 이하의 예제에서는 hr 연습계정의 employees 테이블을 사용합니다.
2. 예제 : CREATE INDEX 사용
1) 예제 : employees 테이블 last_name 컬럼에 대한 인덱스 생성
인덱스 이름은 임의로 sample_idx_employees_last_name으로 붙였습니다.
아래 SQL 문장을 실행하면, NON UNIQUE한 B-TREE 유형 인덱스가 생성될 것입니다.
create index sample_idx_employees_last_name
on employees(last_name);
2) 예제 : 생성한 인덱스 정보 조회하기
데이터베이스 레퍼런스, 딕셔너리를 사용합니다.
다음 2개의 문장을 각각 실행하면, 서버에 저장되어 있는 사용자 인덱스의 정보와 컬럼의 정보를 조회할 수 있습니다.
select * from user_indexes
where table_name='EMPLOYEES';select * from user_ind_columns
where table_name='EMPLOYEES';
sample_idx_employees_last_name 인덱스가 새로 생성되어 있는 모습을 확인할 수 있습니다.
DROP INDEX : 인덱스 삭제 방법, 데이터 딕셔너리에서 인덱스 제거
DROP INDEX 명령을 사용하면, 데이터베이스에 존재하는 인덱스를 제거할 수 있습니다.
drop index 인덱스이름K;※ 주의 : 이 때, 인덱스이름K는 반드시 대문자로 입력해야 합니다. 대문자로 입력하지 않을 경우 오류가 발생합니다.
오류 보고 -
ORA-00911: 문자가 부적합합니다
00911. 00000 - "invalid character"
*Cause: The identifier name started with an ASCII character other than a
letter or a number. After the first character of the identifier
name, ASCII characters are allowed including "$", "#" and "_".
Identifiers enclosed in double quotation marks may contain any
character other than a double quotation. Alternate quotation
marks (q'#...#') cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.
*Action: Check the Oracle identifier naming convention. If you are
attempting to provide a password in the IDENTIFIED BY clause of
a CREATE USER or ALTER USER statement, then it is recommended to
always enclose the password in double quotation marks because
characters other than the double quotation are then allowed.
* 권한 이슈 : 인덱스를 삭제하려면 인덱스의 소유자이거나 DROP ANY INDEX 권한이 있어야 함에 유의합니다.
* 인덱스가 연루된 테이블을 삭제하면 해당 테이블에 있었던 인덱스와 제약조건(constraints)도 자동으로 삭제됩니다. (cf. 테이블을 삭제하여도 뷰(view)와 시퀀스(sequence)는 남아있습니다.)
* 이하의 예제에서는 hr 연습계정에서 미리 만들어 놓은 임의의 인덱스를 사용합니다.
먼저 아래의 소스코드를 실행(Ctrl+Enter)하여, 새로운 인덱스를 만들어 주세요.
create index sample_idx_employees_last_name
on employees(last_name);
1) 예제 : 인덱스 sample_idx_employees_last_name 삭제하기
* 삭제할 인덱스의 이름을 반드시 대문자로 입력해 줍니다.
drop index SAMPLE_IDX_EMPLOYEES_LAST_NAME;
ALTER MONITORING : 인덱스(INDEX) 사용 여부 확인하기, 모니터링하기
오라클 SQL 디벨로퍼에서, 과연 만들어진 인덱스가 사용되고 있는지 궁금할 경우 ALTER MONITERING USAGE 를 사용하여 인덱스 사용 여부를 YES / NO 로 조회할 수 있습니다.
1. 어떤 인덱스(INDEX) A의 사용 여부 조회 방법
1) → 2) → 3) → 4) 순서로 진행합니다.
* 인덱스 이름을 사전에 알고 있어야 합니다.
1) 어떤 인덱스 A의 사용여부 모니터링 시작하기
* 인덱스 이름은 반드시 대문자로 입력해야 합니다.
alter index 인덱스이름A monitoring usage;
2) 인덱스 사용여부 조회하기
(1) 모니터링 중인 인덱스 전체의 사용여부를 살필 경우
select index_name, used
from v$object_usage;
(2) 모니터링 중인 특정 인덱스 A만 지정하여 사용여부를 확인하는 경우
* 인덱스 이름은 반드시 대문자로 입력해야 합니다.
select index_name, used
from v$object_usage
where index_name='인덱스이름A';
3) 어떤 인덱스 A의 사용여부 모니터링 중단하기
alter index EMP_EMP_ID_PK nomonitoring usage;
* 이하의 예제에서는 hr 연습계정을 사용합니다.
2. 예제 : hr 연습계정의 특정한 인덱스 사용여부 조회하기
1) 인덱스 이름 확인하기
인덱스 사용여부를 조회하려면, 먼저 인덱스의 이름부터 알아야 합니다.
다음의 SELECT FROM 구문과 데이터 딕셔너리를 사용하여, 사용자 인덱스 목록을 조회합니다.
select * from user_indexes;
위 SQL 문장을 실행(Ctrl+Enter)하면, 하단의 질의 결과에 인덱스와 그 정보들이 테이블 형태로 나타납니다.
상단 INDEX_NAME 컬럼이 각 인덱스의 이름입니다.
이 중, EMPLOYEES 테이블의 EMP_EMP_ID_PK 인덱스 사용여부를 조회하겠습니다.
2) ALTER INDEX MONITORING :
인덱스 모니터링 시작하기
ALTER INDEX MONITORING은 지정한 인덱스의 사용여부에 대해 모니터링을 시작하게끔 합니다.
alter index EMP_EMP_ID_PK monitoring usage;
지정한 인덱스가 변경되었다는 스크립트가 출력된다면, 당해 인덱스에 대한 모니터링이 시작된 것입니다.
3) 인덱스 사용여부 조회하기
select index_name, used
from v$object_usage위 문장을 그대로 실행하면, 인덱스 이름(index_name)과 사용여부(used)가 조회됩니다.
현재 모니터링 중인 EMP_EMP_ID_PK 인덱스가 사용되었음(YES)을 확인하게 되었습니다.
4) 인덱스 모니터링 중단하기
alter index EMP_EMP_ID_PK nomonitoring usage;사용여부를 조회했다면, 모니터링을 중단해도 됩니다. 위 문장을 실행하면 인덱스의 모니터링 상태가 변경됩니다.
'Structured Query Language > Oracle SQL' 카테고리의 다른 글
| Oracle SQL 기본_14 (0) | 2023.05.02 |
|---|---|
| Oracle SQL 기본_13 (0) | 2023.05.02 |
| Oracle SQL 기본_12 (1) | 2023.04.28 |
| Oracle SQL 기본_11 (0) | 2023.04.27 |
| Oracle SQL 기본_10 (1) | 2023.04.26 |
