/*
인덱스
INDEX - 인덱스는 "값 + rowid" 로 구성되어 있다.
- 동일한 값을 같은 경우 ROWID순으로 소트되어 있기 때문에 블럭에서 테이블 엑세스가 이루어 진다.
인덱스 컬럼
+ 검색조건에 빈빈히 이용되는 컬럼 혹은 컬럼의 집합(SQL문의 WHERE절)
+ ORDER BY, GROUP BY, UNION, DISTINCT (TEMP TABLESPACE) 등 정렬이 필요한 구문에 빈번히 이용되는 컬럼 혹은 컬럼의 집합
+ 집계함수의 이용에 사용되는 컬럼 혹은 컬럼의 집합
+ 수정이 잦은 컬럼은 인텍스 지정 억제(오히려 부하를 줄수 있다)
+ 외부키(FOREIG KEY)는 클러스터인텍스 지정
클러스터 테이블
= 클러스터는 하나 이상의 테이블이 물리적으로 같이 저장되어 있는 그룹을 말한다.
관련된 행들이 같이 저장되어 있다면 디스크 엑세스는 시간을 개선시킨다.
= 클러스터 안에 있는 관련된 테이블의 컬럼을 CLUSTER KEY라고 말한다.
인덱스 적용 대상
+ 중,대규모 테이블
+ 대상 데이터의 10% 이내의 데이터 요구인 경우(분포도)
+ 스캔의 범위를 줄이고자 할때
+ 정렬을 필요로 하는 경우
+ 테이블에 접근하지 않고 인덱스만으로 데이터 요구를 해결하고자 하는 경우
인덱스 적용시 고려사항
+ 인덱스를 위한 추가적인 기억공간을 필요로 함(INDEX TABLESPACE)
+ 인덱스와 테이블 I/O수의 합계가 전체 테이블을 스캔하는 I/O수보다 적은 경우에만 성능향상에 기여한다고 볼 수 있음(손익분기점: 10~15%)
+ 여러 컬럼을 결합하여 인덱스를 구성하는 경우 컬럼의 순서 중요
+ 입력, 수정 혹은 인덱스 컬럼의 값을 수정하는 경우 인덱스 데이터도 변경되어야 하므로 그에 따른 오버헤드도 고려할 것
- 테이블당 인덱스의 수는 4개 정도가 적당
- 무분별한 인덱스는 등록, 수정, 삭제작업시 부하
+ 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음(RANK 등)
인텍스 대상테이블 선정
+ 테이블의 크기가 5~6블럭 이상
+ 무작위접근(RANDOM ACCESS)이 빈번한 경우
+ 특정범위/순서의 데이터조회가 필요한 경우
+ JOIN의 연결고리가 되는 칼럼
+ 참조 무결성을 지정한 경우 연결고리 되는 컬럼
+ NESTED JOIN이 일어나는 테이블
+ 단순보관용이거나 전체조회용일 경우에는 인덱스를 생성하지 않는다.
+ 여러 개의 인덱스가 있는 경우 동시에 많은 양의 입력/수정/삭제가 이루어지면 부하가 발생하므로 인덱스의 수나 생성시점 또한 중요하다.
분포도와 손익분기점
+ 인덱스를 생성하고자 하는 컬럼의 분포도는 10~15%를 넘지 않아야 한다.
분포도 = 1/컬럼값의 종류 * 100
= 데이터별 평균 로우 수 / 테이블의 총 로우 수 * 100
+ 인덱스의 선택성(SELECTIVITY)
테이블의 전체 레코드중에서 구분값(DISTINCT VALUE)이 차지하는 비율.
선택성 = (구분값의 갯수 / 전체 ROW 갯수) * 100
선택성이 높을 수록 인덱스의 분포도는 양호 - UNIQUE INDEX의 선택성은 1.00
예) 테이블의 레코드수: 1000, 구분값:950 => 선택성: 950/1000 (0.95)
인덱스의 활용[적용기준]
+ 6블럭 이상의 테이블에 적용(6블럭이하는 연결고리만)
+ 컬럼의 분포도가 10~15% 이내인 경우 적용
+ 분포도가 범위이내더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 검토할 것
+ 분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 적용
+ 인덱스만을 사용하여 요구를 해결하고자 하는 경우는 분포도가 나쁘더라도 적용할 수 있음(손익분기점)
분포도 1/컬럼값의 종류 * 100 = 컬럼값의 평균 로우수/테이블의 총 로우수 * 100
인덱스의 효율적 이용
+ 일반적으로 가장 좋은 하나의 인덱스만 사용하는 것이 여러 개의 인덱스를 사용하는 것보다 유리하다.
-> 인덱스 머지(INDEX MERGE)의 회피
+ 인덱스 테이블과 데이터 테이블은 각기 다른 디스크에 배치
+ 인덱스 테이블에 대한 적절한 기억공간 할당
+ 테이블 접근없이 인덱스만으로 데이터 요구 해결
+ 인덱스 생성 및 삭제 시점의 효율적 운용 검토
+ 데이터 로드나 일시에 많은 데이터를 입력할 경우 인덱스에 대한 부하를 줄이기 위해
인덱스를 삭제,입력 및 로드가 끝난 이후 재생성
+ 인덱스의 영영은 생성목적과 의미를 내포
데이터 검색 방식
1. FULL-TABLE SCAN
데이터 BLOCK만을 읽는다.
다중 BLOCK I/O를 한다(DB_FILE_MULTIBLOCK_READ_COUNT)
PARALLEL QUERY OPTION 기능을 사용할 수 있다.
SELECT 되는 ROW의 수가 많은 경우에 유리하다.
2. INDEX SCAN
INDEX BLOCK 과 데이터 BLOCK을 읽는다.
다중 BLOCK I/O를 할 수 없다.
SELECT 되는 ROW의 수가 적은 경우에 유리하다.(10~15% 이내)
3. FAST-FULL INDEXED SCAN
SELECT 에 사용된 모든 컬럼이 INDEX 내에 포함이 되는 경우
즉 INDEX에만 접근하면 원하는 데이터가 전부 있는 경우에 사용한다.
FULL-TABLE SCAN 을 해야 하는 경우에 FULL-TABLE SCAN에 대한 대체방법.
FULL-TABLE SCAN 보다 가볍다.
FULL-TABLE SCAN의 장점인 다중 BLOCK I/O와 PARELLEL QUERY를 사용한다.
옵티마이저의 실행계획 작성 우선순위[RULE BASED]
1. ROWID에 의한 1 로우 엑세스
2. 클러스터 조인에 의한 1 로우 엑세스
3. UNIQUE KEY 또는 PRIMARY KEY 를 사용하는 HASH CLUSTER KEY에 의한 1 로우 엑세스
4. UNIQUE KEY 또는 PRIMARY KEY 에 의한 1 로우 엑세스
5. 클러스터 조인
6. 해시 클러스터 키
7. 인덱스 클러스터 키
8. 복합 키
9. 단일 컬럼 인덱스
10. 인덱스 컬럼에서의 바운드 범위 조회
11. 인덱스 컬럼에서의 언바운드 범위 조회
12. 소트 병합 조인
13. 인덱스 컬럼의 최대 또는 최소값
14. 인덱스 컬럼에서의 ORDER BY 사용
15. 테이블의 전체 검색
INDEX를 사용하지 않는 경우
+ 좌변을 가공하지 마라.(인덱스컬럼의 연산, 결합 등의 변형)
-> SAL * 12 = 2000 => SAL = 2000/12로
+ NULL은 가급적 사용하지 마라.(NULL 비교)
+ 부정형 비교( <>, NOT )
+ 함수를 사용하는 경우(SUBSTR, NVL 등)
+ LIKE 문에서 '%AB%',''%AB는 사용목함. ''AB% 가능.
+ WHERE 절에 인덱스가 2개 있다면 인덱스의 우선순위에 의해 LIKE 보다는 = 조건이 작동한다.(취사선택)
-- INDEX COLUMN 의 변형
-- SELECT * FROM DEPT WHERE SUBSTR(DNAME, 1, 2) = 'AC'
-- NOT OPERATOR
-- SELECT * FROM EMP WHERE JOB <> 'SALES'
-- NULL, NOT NULL
-- SELECT * FROM EMP WHERE ENAME IS NOT NULL
-- OPTIMIZER 의 취사선택
-- SELECT * FROM EMP WHERE JOB LIKE 'AB%' AND EMPNO = '7891'
INDEX COLUMN의 변형[EXTERNAL]
+ 모든 내용을 읽어서 서브스트링을 돌리지 말고, INDEX가 잡혀있다면 'AC'로 시작하는 것을 찾아서 그것만 테이블 엑세스
SELECT * FROM DEPT WHERE SUBSTR(DNAME, 1, 2) = 'AC'
-> SELECT * FROM DEPT WHERE DNAME LIKE 'AC%'
+ 모두를 연산한 후 비교하지 말고, 한번만 연산후 그 값인 것을 찾는다.
SELECT * FROM EMPTEST WHERE SAL * 12 = 12000000
-> SELECT * FROM EMPTEST WHERE SAL = 12000000 / 12
+ 1차 인덱스 컬럼은 CHAR타입으로 변경한다.
SELECT * FROM EMPTEST WHERE TO_CHAR(HIRE_DATE,'YYYYMMDD') = '19811203'
-> SELECT * FROM EMPTEST WHERE HIRE_DATE = ''19811203
+ 테이블 설계시에 NULL 을 예방하라.
SELECT * FROM EMP WHERE NVL(COMM, 0) < 100
SELECT * FROM EMP WHERE NVL(JOB,'') = 'CLERK'
-> SELECT * FROM EMP WHERE JOB LIKE 'CLERK%'
+ INDEX가 설정되있는 것을 각각 검색
SELECT * FROM EMP WHERE DEPTNO||JOB = '30MANAGER'
-> SELECT * FROM EMP WHERE DEPTNO = 30 AND JOB = 'MANAGER'
의도적인 SUPPRESSING(억제)
: 의도적인 SUPPRESSING은 타입에 관계없이 RTRIM()을 사용하자.
- 인덱스는 분포도 10~15% 이하의 경우 효율적.
- 분포도가 90% 이면 엄청 속도가 느려진다. 따라서 옵티마이저가 인덱스를 타지 않도록 의도적으로 SUPPRESSING한다.
SELECT * FROM EMP WHERE JOB = 'MANAGER'
-> SELECT * FROM RTRIM(JOB) = 'MANAGER'
-> SELECT * FROM EMP WHERE JOB||'' = 'MANAGER'
SELECT * FROM EMP WHERE EMPNO = 8978
-> SELECT * FROM EMP WHERE EMPNO + 0 = 8978 (NUMBER 형일 경우에만)
- 값이 틀려지면 더 작은 ROWID가 있을 구 있으므로 인덱스 머지 방식은 절대로 사용할 수 없다.
둘중 하나의 인덱스를 선정합니다.랭크를 비교.
둘 다 LIKE 이므로 만들어진 시점을 비교하여 최근에 만들어진 인덱스를 사용합니다. 전부다 비기면 나중에
쓰여진 인덱스를 사용. STATUS인덱스를 사용했다면 엄청 느려짐.
SELECT * FROM AAA WHERE CUST LIKE 'DN%' ABD STATUS LIKE '9%'
-> SELECT * FROM AAA WHERE CUST LIKE 'DN%' AND RTRIM(STATUS) LIKE '9%' (일부러 안쪽의 인덱스를 안쓰게 만들었다.)
CREATE TABLE SAMPLET(
CHA CHAR(10),
NUM NUMBER(12,3),
VAR VARCHAR2(20),
DAT DATE
)
INDEX COLUMN 의 변형(INTERNAL)
SELECT * FROM SAMPLET WHERE CHA = 10
-> SELECT * FROM SAMPLET WHERE TO_NUMBER(CHA) = 10
SELECT * FROM SAMPLET WHERE NUM LIKE '9410%'
-> SELECT * FROM SAMPLET WHERE TO_CHAR(NUM) LIKE '9410%'
SELECT * FROM SAMPLET WHERE DAT = '01-JAN-94'
-> SELECT * FROM SAMPLET WHERE DAT = TO_DATE('01-JAN-94')
+ 서로 다른 DATA TYPE이 비교시의 INTERNAL CONVERSION(옵티마이저의 인공지능은 사람을 따라올 수 없다)
-- EMAIL 은 현재 VARCHAR2형
SELECT * FROM EMPLOYEES WHERE EMAIL = 100 -- (문자열을 숫자형으로 비교, FULL SCAN)
-> SELECT * FROM EMPLOYEES WHERE EMAIL = '100' -- (TO_CHAR(100) 로 변화하여 검색)
-- DATE형: FULL SCAN -> 옵티마이저가 TO_DATE()로 변환한다.
SELECT * FROM EMPLOYEES WHERE HIRE_DATE = '99/06/21'
-> SELECT * FROM EMPLOYEES WHERE HIRE_DATE = TO_DATE('99/06/21')
NOT OPERATOR
+ 부정을 긍정으로 만들어서 사용한다.
SELECT * FROM EMP WHERE JOB <> 'SALESMAN'
-> SELECT * FROM EMP E WHERE E.EMPNO > 0 AND NOT EXISTS(SELECT * FROM EMP B WHERE B.EMPNO = E.EMPNO AND B.JOB = 'SALESMAN')
SELECT * FROM EMP WHERE EMPNO > 0 MINUS SELECT * FROM EMP B WHERE B.JOB = 'SALESMAN'
NULL, NOT NULL
+ NULL, NOT NULL 비교인 경우 인덱스 사용 안함.
+ NULL 이 정말 필요한지를 고민해봐야 한다. 테이블 설계시 디폴트값 셋팅이 필요한지 검토
+ NULL 이 필요한 경우는 정말 극히 드물다.
SELECT * FROM EMPLOYEES WHERE JOB_ID IS NOT NULL;
-> SELECT * FROM EMPLOYEES WHERE JOB_ID > ' ';
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IS NOT NULL;
-> SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID >= 0;
SELECT * FROM EMPLOYEES WHERE JOB_ID > ' ' AND EMPLOYEE_ID IS NULL;
OPTIMIZER 의 취사선택
+ 랭킹의 차이
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID = 'SH_CLERK'
(ENAME INDEX만 사용, LIKE 보다 = 이 우선임.)
+ 인덱스머지의 회피
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID LIKE 'S%';
(ENAME or JOB INDEX중 하나만 사용.)
+ 힌트에 의한 선택
SELECT /*+INDEX(EMP_JOB_IDX)* / * FROM EMPLOYEES WHERE LAST_NAME LIKE 'S%' AND JOB_ID LIKE 'S%'
(같은 조건일때 힌트를 사용)
RULE_based vs COST_based - 차이점 예제
+ SqlPlus 에서 SHOW PARAMETER OPT 하면... optimizer_mode string CHOOSE
SQL> conn system
암호 입력: ********
SQL> show parameter opt;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options string
object_cache_optimal_size integer 102400
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
plsql_optimize_level integer 2
현재 설정은 CHOOSE.
즉 오라클이 알아서 CBO 나 RBO를 선택하겠다는 ㅤㅆㅡㅅ이다.(Analyze작업이 되면 cost방식으로 해석 - dept테이블로 확인)
+ CBO를 적용하기 위해선 ANALYZE작업이 선행되어야 한다.
+ ANALYZE작업에 의한 통계값이 없다면 오라클은 정확하게 CBO를 수행할 수 없다.
+ 일단 analyze를 하게 되면 옵티마이저는 choose일 경우 cost를 선택하게 된다.
이때 통계치에 의해 기존 rbo로 수행되면 sql문의 실행계획이 달라지게 된다.
-> Cost를 사용하기 위해서느 ㄴ전체 app의 sql문을 수정할 필요가 있다.
+ RULE based
-- INDEX merge(and_equel)
SELECT * FROM EMPLOYEES WHERE JOB_ID = 'MK_REP' AND LAST_NAME = 'Fay';
-- 항상 EMPLOYEES_ID index만 사용
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND EMPLOYEE_ID = '202';
-- 항상 나중에 생성된 Index만 사용(job_id)
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID BETWEEN '202' AND '9000' AND JOB_ID LIKE 'MK%';
+ Cost based
-- INDEX merge(and_equel), 특정 index
SELECT * FROM EMPLOYEES WHERE JOB_ID = 'MK_REP' AND LAST_NAME = 'Fay';
-- 분포도에 따라 LAST_NAME index도 사용
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND EMPLOYEE_ID = '202';
-- 분포도에 따라 index 사용, 혹은 Full-Scan
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'Fa%' AND JOB_ID LIKE 'MK%';
ANALYZE
-- 명령은 다음과 같다.
ANALYZE TABLE HR.EMPLOYEES COMPUTE STATISTICS;
+ 옵티마이저가 CBO로 수행되기 위해서는 ANALYZE에 의한 통계 정보가 있어야만 하며...
다음과 같이 확인해 볼 수 있다.
CONN SYSTEM
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS AS EMPTY, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
FROM DBA_TABLES
WHERE OWNER = 'HR'
AND TABLE_NAME = 'EMPLOYEES';
+ 인덱스 Rebuild(Index Reorganization)를 위한 정보도 얻을 수 있다.
ANALYZE 명령어
+ Cost-Based 방식에 사용할 통계데이터를 모아 준다.
+ Analyze 명령어는 수행될 때마다 다음의 정보를 수정
- table
테이블의 행수, 사용된 블록 수, 한번도 사용되지 않은 블록 수,
사용가능한 Space의 평균, Chained Row수, 컬럼당 Distinct Value의 수,
컬럼당 두 번째로 가장 작은 값, 컬럼당 두번째로 가장 큰 값.
- INDEX
-> Index level, Level 블록 수, Distinct Key 수, 키당 leaf 블록 수의평균,
키당 Data 블록 수의 평균, Clustering Factor, 최소키 값, 최대키 값.
*/
Index Merge
select employee_id, hire_date from employees where salary between 8600 and 10000 and hire_date like '94%';
-- 결합 인덱스 생성
-> create index hire_date_salary_idx on employees(hire_date, salary);
select salary, hire_date from employees where salary between 8600 and 10000 and hire_date like '94%';
select * from employees where hire_date like '95/%';
-> select * from employees where hire_date in ( '95/10/17','95/07/14','95/05/18','95/05/01');
-> select * from employees where hire_date in ( select hire_date from employees where hire_date like '95/%' );
결합인덱스의 조건 : equal이 결합인덱스에 미치는 영향
- 결합인덱스(COL1, COL2)
+ 결합 인덱스가 WHERE 조건에 나올 경우 순서에 의해 효율의 차이가 많이 날수 있다.
결합인덱스 선택에서 EQUAL로 나오게 할 수 있느냐가 중요하다.
: 결합인덱스 컬럼 결정 순서
a. 항상 조건이 될 수 있는 컬럼이 제일 앞에 와야 한다.
b. 항상 = 로 나올수 있는가?
-> = 로 만들수 있는가 까지 볼수 있어야 겠다.
c. 분포도가 좋은 컬럼을 우선으로 한다.
d. sort 순서는?
결합인덱스 구성 컬럼 중 sort을 해야 하는 컬럼에 대한 결정.
e. 어떤 컬럼을 추가? (후보선수)
인덱스 선정 절차
+ 해당 테이블의 엑세스 유형 조사
+ 대상 컬럼의 선정 및 분포도 분석
+ 반복 수행되는 엑세스 경로의 해결
+ 클러스터링 검토(인덱스만으로도 안되는 경우가 있다.)
+ 인덱스 컬럼의 조합 및 순서의 결정
+ 시험생성 및 테스트
+ 수정이 필요한 애플리케이션 조사 및 수정
엑세스 유형이 조사(설계단계)
+ 반복 수행되는 엑세스 형태를 찾는다.
+ 분포도가 아주 양호한 컬럼을 찾아 인덱스 유형을 찾는다.
+ 자주 넓은 범위의 조건이 부여되는 경우를 찾는다.
+ 자주 조건절에 사용되는 컬럼들의 엑세스 유형을 찾는다.
+ 자주 결합되는 사용되는 경우를 찾는다.
+ 소트의 유형을 조사한다.
+ 일련번호를 부여하는 경우를 찾는다.(PK)
+ 통계자료 추출을 위한 엑세스 유형을 조사항다.(거의 대용량일 경우 대량의
데이터를 읽어서 GROUP BY 등을 하는 경우 클러스터링 고려)
인덱스의 선정기준
+ 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상
+ 자주 조합되어 사용되는 경우는 결합인덱스 생성
+ 각종 액세스 경우의 수를 만족할 수 있도록 인덱스간의 역할 분담
+ 가능한 수정이 빈번하지 않는 컬럼(발란스트리 깨짐)
+ 기본키 및 외부키(조인 연결고리가 되는 컬럼)
+ 결합 인덱스의 컬럼 순서 선정에 주의
+ 반복수행 되는 조건은 가장 빠른 수행 속도를 내게 할 것
+ 실제 조사된 액세스 종류를 토대로 선정 및 검증
인덱스의 선정기준
+ 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상
+ 자주 조합되어 사용되는 경우는 결합인덱스 생성
+ 각종 엑세스 경우의 수를 만족할 수 있도록 인덱스 간의 역활 분담
+ 가능한 수정이 빈번하지 않는 컬럼(발란스트리 깨짐)
+ 기본키 및 외부키(조인 연결고리가 되는 컬럼)
+ 결합 인덱스의 컬럼 순서 선정에 주의
+ 반복수행되는 조건은 가장 빠른 수행 속도를 내게 할 것
+ 실제 조사된 엑세스 종류를 토대로 선정 및 검증
인덱스 고려사항
+ 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수 있음
(실행 계획이 바뀔 수 있다.)
+ 지나치게 많은 인덱스는 오버헤드 발생
+ 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생
랜덤엑세스(rowid에 의한 direct엑세스가 다량 발생)
+ 옵티마이저를 위한 통계데이터를 주기적으로 갱신(CBO)
+ 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
+ 인덱스의 개수는 테이블의 사용형태에 따라 적절히 생성
트랜잭션이 많은 경우(OLTP) 최소로 인덱스를 만들어야 한다.
+ 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음
+ 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐
+ 조인시에 인덱스가 사용여부에 주의
/*
부분범위 처리
= 처리할 내용이 아무리 많더라도 일단 운반단위에 차게 되면 데이터를 RETURN시키고,
사용자가 다음 ACTION을 취할 때까지 기다리므로 처리범위의 크기에 영향을 받지 않도록 하는 기법
- 조건을 만족하는 전체 집합이 아닌 일부분만 엑세스
- 데이터량이 많아도 퍼포먼스에 지정이 없고, 오히려 향상
- 인덱스나 클러스터를 적절히 활용한 소트의 대체(ORDER BY 를 안하도록 한다.)
그 외..
- 테이블을 엑세스하지 않고 인덱스만 사용하도록 유도
- EXIST의 활용(IN 보다 효율적, EXISTS 는 옵티마이저가 부분범위처리한다.)
- ORDER BY 생략(불필요한 정렬작업 생략)
- MIN / MAX
- ROWNUM의 활용 -> STOP KEY 를 이용
- QUERY를 이원화 하여 일부분씩 스캔 하도록 유도(소트의 범위를 줄임)
등의 활용예를 통해 부분범위 처리를 확인해 볼 수 있다.
옵티마이저 힌트는 적대적인 것은 아니다. 힌트를 줘 놓고 힌트를 쓸수 있는 요소가 없으면 힌트가 사용되지 않는다.
때문에 멍텅구리 조건을 주는 경우가 있다.
- 인덱스가 있는 컬럼을 조건절에 넣는다.(단, 추출하려는 데이터에 영향을 미치지 않는 한도내에서.)
* 전체범위처리 : FULL RANGE SCAN 후 가공하여 ARRAY SIZE 만큼 추출
* 조건을 만족하는 ROW 수가 ARRAY SIZE에 도달되면 멈춤(STOP KEY)
*/
/*
SORT 를 대신하는 인덱스
*/
-- 싱글 인덱스
-- SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%' ORDER BY YMD, ITEM;
-- 결합 인덱스
-- SELECT * FROM PRODUCT WHERE YMD = '951023' AND ITEM LIKE 'AB%';
/*
INDEX 생성
*/
-- create index HIREDATE_IDX ON EMPLOYEES(HIRE_DATE)
/*
MAX 처리
*/
-- SELECT MAX(HIRE_DATE) FROM EMPLOYEES;
-- SELECT /*+INDEX_DESC(EMPLOYEES HIREDATE_IDX) */ HIRE_DATE FROM EMPLOYEES WHERE HIRE_DATE < '20/12/30' AND ROWNUM = 1;
-- SELECT MAX(EMPLOYEE_ID)+ 1 FROM EMPLOYEES WHERE DEPARTMENT_ID = 100;
-- SELECT /*+INDEX_DESC(EMPLOYEE_ID) */ EMPLOYEE_ID + 1 FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 AND ROWNUM = 1;
/*
EXISTS
*/
-- SELECT COUNT(*) FROM hr.EMPLOYEES WHERE DEPARTMENT_ID = 100 AND EMPLOYEE_ID > 10 ;
-- SELECT 1 FROM DUAL WHERE EXISTS(SELECT 'X' FROM EMPLOYEES WHERE DEPARTMENT_ID = 100 AND EMPLOYEE_ID < 10);
/*
범위가 좁은것 부터 큰 것으로 조건을 지정
*/
-- select * from employees where employee_id between 10 and 10000 and department_id = '100';
-- select /*+index_asc(department_id, employee_id)*/ * from employees
-- where department_id = '100' and employee_id between 10 and 10000 ;
/*
인덱스를 최대한 활용하라.
*/
-- select min(salary) from employees;
-- select min(salary) from employees where salary > 0 and rownum = 1;
/*
부분범위 처리가능시 최초운반단위를 빨리 채우도록 한다.
*/
-- select last_name, salary * 100 from employees where hire_date like '9%' order by last_name;
-- select last_name, salary * 100 from employees where hire_date like '9%' and last_name > ' ' ;
/*
UNION 대신 UNION ALL을 사용하라.
*/
-- select last_name, salary * 100 from employees where hire_date like '96%'
-- union
-- select last_name, salary * 100 from employees where hire_date not like '96%' ;
-- select last_name, salary * 100 from employees where hire_date like '96%'
-- union all
-- select last_name, salary * 100 from employees where hire_date not like '96%' ;
Hash Function을 이용해서 메모리와 CPU를 많이 사용해서 일반적으로 배치작업에서 주로 사용됨
-. /*+ use_hash(테이블) */
-. 적은테이블과 큰테이블의 조인시에 유리
-. Equal 조인에서만 가능
-. Driving Table에 인덱스를 필요로 하지 않고 각 테이블을 한번만 읽음
-. 다른조인방법보다 CPU자원을 많이 소비하며 양쪽 테이블의 scan이 동시에 일어남
해시 조인(Hash-Join)은 두 테이블 중 하나를 기준으로 비트맵 해시 테이블을 메모리에 올린 후 나머지 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 비교하여 매칭되는 데이터를 추출하는 방식 입니다.
성능을 위해서는 당연히 사이즈가 작은 테이블이 메모리에 올라가는 것이 좋으며 이때 이 테이블을 드라이빙 테이블(driving/outer table) 이라고 합니다. 특히 이 해시 테이블이 메모리에 생성되면 성능은 좋으며(메모리에 생성되지 않으면 내부적으로 임시 테이블이 만들어 져야 합니다.) 두 테이블의 크기 차이가 클수록 성능은 좋아집니다.
또한 해시 조인은 안티 조인과 병렬처리와 잘 맞으며 범위 검색(Range scan)이 아닌 동등 비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합 합니다.
일반적으로 배치작업에서 주로 사용되며, 각 테이블을 Sort한 후 Merge 하는 조인을 말한다
-. /*+ use_merge(테이블) */
-. 동시에 각각의 테이블이 자신의 처리범위를 액세스하여 정렬해둠
-. 각 테이블은 어떠한 상수값도 서로 영향을 주지 않으며, 주어진 상수값에 의해서만 각자 범위를 줄이게됨
-. 전체범위처리를하며 부분범위처리를 할수 없음
-. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우에만 Random Access이고, Merge작업은 Scan방식
-. 선택적으로 연결고리가 되는 컬럼은 인덱스를 사용하지 않음
-. 조인의 방향과는 상관없음
-. Equal 조인에서만 가능
-. 처리량이 많은 경우로 Random Access를 하지 않음으로 전체범위처리에 유리
-. 자신의 처리범위를 인덱스를 통해 어떻게 줄이느냐가 관건
-. 상수값을 받아 줄여진 범위가 30%이상이면 Sort Merge가 유리
- 두 개 컬럼 모두 같은 조건( 인덱스가 둘 다 없거나, 둘 다 있는 경우)인 경우에는 FROM절의
가장 오른쪽에 정의된 테이블이 구동테이블이 됨.
SQL>select a.empno , a.ename , a.sal, b.deptno, b.dname, b.loc
from big_emp a , big_dept b
where a.deptno = b.deptno
and b.deptno between 10 and 40
and b.loc = 'DALLAS' ;
테이블을 조인 하는 경우 중첩 루프 조인(Nested Loop Join)이 일어나도록 하는 힌트 문장 입니다. 중첩 루프 조인은 중첩 반복이라고도 하는데 하나의 테이블(outer/driving table)에서 추출된 로우를 가지고 일일이 다른 테이블(inner/probed table)을 반복해서 조회하여 찾아지는 레코드를 최종 데이터로 간주하는 방법 입니다.
즉 조인 입력 한 개를 외부 입력 테이블로 사용하고, 한 개는 내부(최하위) 입력 테이블로 사용하고 외부 루프는 외부 입력 테이블을 행 단위로 사용하고 각 외부 행에 대해 실행되는 내부 루프는 내부 입력 테이블에서 일치되는 행을 검색 하는거죠… 이것을 원시 중첩 루프 조인이라고 하는데 검색에서 인덱스를 사용하는 경우에는 인덱스 중첩 루프 조인이라고 합니다.
예를 들어 EMP 테이블과 DEPT 테이블을 조인하는 경우 dept 테이블이 건수가 작다면 우선 이 테이블을 외부 루프로 해서 하나씩 읽으면서 이에 대응하는 emp 테이블의 데이터를 추출 하는 경우라면 중첩 루프 조인에 해당 합니다. 이때 emp 테이블의 경우 건수가 많다고 가정을 하면 대부분 인덱스를 이용하도록 emp 테이블의 외래키인 deptno 컬럼은 대부분 인덱스를 걸게 되죠^^
중첩 루프 조인은 테이블중 적어도 하나의 조인 컬럼에 대해 인덱스(or Hash Index)가 존재할 때 연관되는 방식으로 이 중첩 루프 조인에서 테이블중 하나의 테이블 또는 중간 결과 셋을 대상으로 FULL SCAN이 일어나게 됩니다. 이 테이블이 드라이빙 테이블이 되는데… 이 테이블의 데이터 건마다 나머지 테이블에서 원하는 데이터를 추출하기 위해 대부분 인덱스를 사용하게 되는 겁니다.
보통 USE_NL 힌트 구문은 ORDERED 힌트 구문과 같이 사용되는데 USE_NL이 취하는 인자는 FROM절에서 두번째 나오는 테이블(비드라이빙 테이블, inner/probed table)을 명시해 주어야 합니다. 안수로 사용되지 않은 첫 번째 테이블은 outer/driving table이 되는 것입니다.
선행적 특징을 작는데 먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정됨
Driving Table에 의해 범위가 결정되며 Driving Table의 범위가 적을수록 수행속도는 빨라진다
고로 Driving Table을 어던 테이블로 결정하느냐가 중요하다
-. /*+ use_nl (테이블) */
-. 나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스하게됨, 즉 값을 받아서 처리범위가 정해짐
-. Driving Table의 인덱스 액세스는 첫번 로우만 Random Access이고, 나머지는 Scan, 연결작업은 Random Access임
-. 연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음
-. 연결고리 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음
-. 연결작업 수행 후 체크되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐
-. 전체가 아닌 부분범위 처리를 하는 경우 유리함
-. 조인되는 테이블중 어느 한쪽의 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리함
-. Driving Table의 처리량이 많거나 연결 테이블의 Random Access량이 많을 경우에는 분리함
-. 일반적으로 처리량이 적은 경우로서 Random Access를 많이 하므로, 온라인 어플리에서 유리함
-. Driving Table의 선택이 관건임
■ Nested-Loop Join 의 경우
- 한쪽 컬럼에만 인덱스가 생성되어 있는 경우에는 FROM절에 열거된 테이블의 순서와는 상관없
이 인덱스 없는 테이블이 우선순위가 됨
(Join 문장에는 WHERE 조건절이 없기 때문에 어떤 경우라도 하나의 테이블이 전체스캔 되어야
하는데 인덱스가 있는 테이블을 구동테이블로 선택시 인덱스를 전체 스캔하고 다시 테이블을 전
체 스캔을 시켜야 하기 때문에 오히려 인덱스가 없는 테이블을 전체 스캔하는 경우보다 더 많은
액세스를 할 수 있기 때문)
SQL>create index i_dept_deptno_loc On big_dept(deptno,loc) ;
SQL>select a.empno , a.ename , a.sal, b.deptno, b.dname, b.loc
from big_emp a , big_dept b
where a.deptno = b.deptno
and b.deptno between 10 and 40
and b.loc = 'DALLAS' ;
Rows Row Source Operation
------- ---------------------------------------------------
14728 NESTED LOOPS
28956 TABLE ACCESS FULL BIG_EMP <= InnerTable
14728 TABLE ACCESS BY INDEX ROWID BIG_DEPT <= Driving Table
43683 INDEX RANGE SCAN (object id 27502)
- 조인에 참여되는 공통컬럼 중 한쪽만 인덱스가 생성되어 있고 하나의 비조인 컬럼에 유일 인덱스
가 생성되어 있는 경우 유일 인덱스를 가진 테이블이 구동 테이블이 된다.
(Unique Index는 검색방법 중 대단히 좋은 성능을 보장해 주는 Index Type 임)
SQL>create unique index i_emp_empno on big_emp(empno);
SQL>create index i_emp_deptno on big_dept(deptno);
SQL>select big_emp.ename , big_dept.dname
from big_emp,big_dept
where big_emp.deptno = big_dept.deptno
and big_emp.empno = 4000;
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
2 TABLE ACCESS BY INDEX ROWID BIG_EMP <= driving table
2 INDEX UNIQUE SCAN (object id 27505)
1 TABLE ACCESS BY INDEX ROWID BIG_DEPT <= inner table
2 INDEX RANGE SCAN (object id 27506)
- 조인에 참여하는 공통컬럼이 모두 인덱스가 생성되어 있는 경우 FROM절로 부터 가장 오른쪽에 정
의된 테이블이 구동테이블.
SQL>create index i_emp_deptnoon on big_emp(deptno);
SQL>create index i_dept_deptno on big_dept(deptno);
SQL>select big_emp.ename , big_dept.dname
from big_emp,big_dept
where big_emp.deptno = big_dept.deptno
and big_emp.empno = 4000 ;
Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS
290 TABLE ACCESS FULL BIG_DEPT <= driving table
1 TABLE ACCESS BY INDEX ROWID BIG_EMP <= inner table
18806 INDEX RANGE SCAN (object id 27508)
/*
** Shared_pool의 hit ratio보는 스크립트.. <박제용>
**
** 이 영역은 SQL 쿼리문이 저장되고, 유저별 사용 영역과, 데이터 딕셔너리등이 저장된다.
** 만일 적게 할당되면 유저의 접속이 많아질수록 throughput에 큰 영향을 준다.
** hit ratio는 95% 이상을 유지시켜야 한다.
**
*/
select sum(gets) "Gets", sum(getmisses) "Misses",
(1-(sum(getmisses) / (sum(gets)+sum(getmisses))))*100
"HitRate"
from v$rowcache;
SGA에 큰 영향을 주는 파라미터들의 설정을 표시해주는 SQL
/*
** DB의 주요 메모리 사용 조회 <박제용>
**
** DB의 주요 메모리 사용을 보여준다. DB가 사용하는 메모리는
** v7.3의 경우 OS메모리의 2/5 를, v8.x 버젼의 경우 1/2 정도를
** 할당해 주는 것이 좋다.
**
*/
select name, value
from v$parameter
where name in('db_block_buffers','db_block_size','shared_pool_size','sort_area_size');
SGA중에 Block buffer의 현재 사용량과 빈공간을 보고싶을 때
/*
** DB_BLOCK_BUFFERS의 현재 사용 현황을 보여줌. <박제용>
**
** block_buffer를 튜닝하기 전에 현재의 사용현황을 보여준다.
** 이 데이터를 주기적으로 보관하여 분석한다.
*/
select decode(state, 0, 'FREE',
1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state)
"BLOCK STATUS", count(*)
from x$bh
group by decode(state,0, 'FREE',
1, decode(lrba_seq,0,'AVAILABLE','BEING USED'),
3, 'BEING USED', state);
SGA의 Block buffer의 hitratio를 연산해주는 SQL
/*
** DB_BLOCK_BUFFERS의 hit ratio보는 스크립트.. <박제용>
**
** 이 영역은 유저의 쿼리 내용이 버퍼링 되는 공간으로 크기가 적으면
** 유저별로 과도한 disk read를 발생시킨다.
** hit ratio는 90~95% 이상을 유지시켜야 한다.
**
*/
select 1-(sum(decode(name, 'physical reads', value,0))/
(sum(decode(name, 'db block gets', value,0)) +
(sum(decode(name, 'consistent gets', value,0))))) * 100
"Read Hit Ratio"
from v$sysstat;
한 유저 스키마의 모든 객체를 Analyze 해주는 SQL
/*
** 테이블 analyze 스크립트 2.. <박제용>
** 한 유저에 속한 모든 객체를 analyze한다.
**
** 사용방법 SQL>@analyze0 [유저ID]
** 유저ID는 반드시 대문자로.
*/
exec dbms_utility.analyze_schema('&1','DELETE');
exec dbms_utility.analyze_schema('&1','COMPUTE');
지정한 테이블을 다시 Analyze 해주는 SQL
/*
** 테이블 analyze 스크립트.. <박제용>
** Query를 파싱하는 Optimizer로 하여금 더욱 정확하고, 빠른 파싱을 유도하기위해 Analyze를 한다.
**
** 사용방법 SQL>@analyze1 [테이블명]
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;
- Predefined Oracle Server
; 오라클에서 미리 정한 ERROR...(NO_DATA_FOUND등...)
- Non-predefined Oracle Server
; 자주일어나는 error가 아니어서 미리 define되어 있지 않은 error
- User-defined
; 오라클 서버 error가 아니라 user가 업무상 rule에 위배되는 사항을 exception처리 하는 것.
2. Predefined Exception Names
ACCESS_INTO_NULL ORA-06530 초기화되지 않은 object의 속성에 값을 assign하라.
ACCESS_INTO_NULL ORA-06530 초기화되지 않은 object의 속성에 값을 assign하라.
COLLECTION_IS_NULL ORA-06531 초기화되지 않은 nested table에 EXISTS와 다른 method를 적용시켜라.
CURSOR_ALREADY_OPEN ORA-06511 OPEN CURSOR가 이미 open되어 있다.
DUP_VAL_ON_INDEX ORA-00001 중복된 값을 insert하려고 한다.
INVALID_CURSOR ORA-01001 잘못된 cursor연산자를 발생시켰다.
INVALID_NUMBER ORA-01722 문자열을 number type으로 convertion 실패
LOGIN_DENIED ORA-01017 오라클 login시 username or password를 잘못 입력했다.
NO_DATA_FOUND ORA-01403 single row SELECT된 데이터가 없다.
NOT_LOGGED_ON ORA-01012 PL/SQL이 오라클에 connect되지 않은 상태에서 database를 호출하려고 한다.
PROGRAM_ERROR ORA-06501 PL/SQL이 내부적인 문제가 있다.
ROWTYPE_MISMATCH ORA-06504 Host cursor변수와 PL/SQL cursor변수가 return type과 일치하지 않는다.
BEGIN SELeCT ... COMMIT ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
statement ;
WHEN TOO_MANY_ROWS THEN
statement ;
WHEN OTHERS THEN
statement ;
END ;
4. Non-Predefined Error
DECLARE
e_products_invalid EXCEPTION ;
PRAGMA EXCEPTION_INIT (e_products_invalid, -2292 ) ;
v_message VARCHAR2(50) ;
BEGIN
...
EXCEPTION
WHEN e_products_invalid THEN
:g_message := 'Product code specified is not valid.' ;
...
END ;
--> -2292 error code를 e_products_invalid 라는 이름으로 정의 하겠다.
5. User-Defined Exception
DECLARE
e_amount_remaining EXCEPTION ;
...
BEGIN
...
RAISE e_amount_remaining ; -- 여기서부터 e_amount_remaining을 발생 시킨다.
...
EXCEPTION
WHEN e_amount_remaining THEN
:g_message := 'There is still an amount in stock.' ;
...
END ;
Why Join ? DB를 설계할 때 정규화(Normalization)가 기본 개념이듯이, SQL에서는 서로 다른 테이블들에 흩어져 있는 데이터를 주어진 조건에 의해 연결하는 조인(Join)이 기본 개념이라 할 수 있다.
예를 들어, 서울에 위치한 부서명과 소속된 사원명을 출력하라는 쿼리가 있을 때에 부서명은 DEPT 테이블에 있고, 사원명은 EMP 테이블에 있다. 따라서 조인이 필요할 것이고, 조인에 사용되는 연결조건은 소속관계이므로 DEPT 테이블의 부서번호와 EMP 테이블의 부서번호가 동일하다는 조건이 될 것이다.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno ß 조인조건
Join Order 조인은 두개 이상의 테이블을 주어진 조인조건으로 연결한다. 이때 연결은 순차적으로 수행되므로 만약 2개의 테이블을 조인하면 2종류의 순서가 가능하고, 3개의 테이블을 조인하면 6종류의 순서가 가능하다. 이때 항상 성능상 유리한 순서 하나로 수행되도록 할 필요가 있다.
RBO에서는 FROM 절의 가장 오른쪽부터 왼쪽으로 풀어나간다. 그러나 CBO를 사용하는 것이 권장된다.
1. 많이 걸러지는 순서대로
테이블에 주어지는 조건에 따라 많이 걸러지는 테이블을 먼저 처리하도록 해야 한다. 많이 걸러진다는 것은 조인조건으로 연결할 소스데이터가 그만큼 줄어든다는 것을 뜻하는 것이므로 성능상 유리하기 때문이다. 많이 걸러지는 것은 대개 그 테이블에 주어지는 WHERE 조건으로 판단하며, 많이 걸러질 수 있는 것은 데이터 상황으로 파악해야 할 것이다.
예를 들면, 위의 첫번째 SQL예제에서 DEPT 테이블을 먼저 처리하는 것이 좋은지, EMP 테이블을 먼저 처리하는 것이 좋은지 살펴보자. 먼저 DEPT 테이블에 대해서 loc 컬럼에 대한 조건이 있는 반면에, EMP 테이블에 대해서는 조인조건 이외의 조건은 없으므로 DEPT 테이블이 먼저 처리되는 것이 좋을 것이다. 만약 EMP 테이블에 다른 조건이 있고, DEPT 테이블에도 조건이 있는 경우에는 데이터 상황으로 비추어 많이 걸러지는 것을 우선적으로 처리하는 것이 좋다.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno
AND e.job = ‘MANAGER’
2. ORDERED 힌트
ORDERED라는 힌트가 주어지면 FROM 절의 왼쪽부터 오른쪽으로 순서대로 조인을 하라는 것이다. 흔히 SQL개발자가 데이터 상황을 보았을 때 가장 유리한 순서를 알고 있다면 FROM 절에 왼쪽부터 오른쪽으로 테이블들을 배치하고 SELECT에 ORDERED 힌트를 준다.
예를 들면 위의 예에서 d.loc = ‘서울’ 이라는 조건이 e.job = ’MANAGER’라는 조건보다 더 많이 걸러진다고 판단되면 아래와 같이 FROM절에 dept 다음에 emp를 기술하고 ORDERED 힌트를 명시한다.
SELECT /*+ ORDERED */ d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno
AND e.job = ‘MANAGER’
그러나 항상 조인조건에 사용될 액세스경로를 항상 주의하여야 하고, 만약 중간에 적합치 않은 액세스 경로가 있다면 원하는 순서대로 풀리지 않을 경우가 많으므로 인덱스생성등의 조치를 통해서 액세스경로를 정상으로 만들어야 한다.
예를 들면, 위의 예에서 e.deptno에 인덱스가 없고 d.deptno에는 인덱스가 있는 경우에는 어쩔 수 없이 emp 테이블이 먼저 풀린다. 이때에는 e.deptno에 인덱스를 만드는 것을 고려해야 한다.
Join Operations 조인은 테이블에 존재하는 데이터들을 주어진 조인조건에 만족되는 데이터들끼리 연결하는 것을 의미하는데, 그 조인조건을 만족하는 로우들을 매핑하기 위한 물리적인 방식에는 세가지가 존재한다. 이 방식은 옵티마이저가 인덱스의 존재 유무라든지, 데이터의 분포도를 고려해서 플랜을 작성하지만, SQL 개발자가 힌트를 제공함으로서 원하는 조인방식으로 유도할 수도 있다.
1. NESTED-LOOPS
대부분의 조인에서 많이 사용되는 방식이다. 한 테이블이 먼저 풀리고, 풀린 결과의 각 건에 대해서 다른 테이블에 조인조건이 만족되는 건이 있는지를 검사하는 방식이다. 먼저 풀리는 테이블을 드라이빙(driving) 테이블이라 하고, 나중에 조인조건을 검사하는 테이블을 이너(inner) 테이블이라 한다.
NESTED-LOOPS 조인의 수행성능을 좋게 하기 위해서는 반복적으로 발생하는 이너 테이블에 대한 조인조건 검사 수행이 최적화되어야 한다. 다시말해서, 인덱스가 생성되어 있어야 한다. 인덱스가 없는 상태에서 NESTED-LOOPS 방식으로 플랜이 생성되지 않을 가능성이 크지만, 만약 플랜이 NESTED-LOOPS 조인이면서 이너테이블이 FULL TABLE SCAN이라면 조인성능은 매우 느리다.
만약 SQL 개발자가 조인을 NESTED-LOOPS 방식으로 유도하기 위해서는 USE_NL 힌트를 사용할 수 있으나, 이너 테이블에 조인조건을 검사하기 위한 인덱스가 존재하는지 확인해야 하고, 없다면 생성해야 한다.
SELECT /*+ USE_NL(e) */ ß emp 테이블을 이너 테이블로 하는 NL 조인
d.dname, e.ename
FROM dept d, emp e
WHERE d.loc = ‘서울’
AND d.deptno = e.deptno ß emp 테이블의 deptno 컬럼에 인덱스 필요
2. HASH
한쪽 테이블에 대해서 조인조건에 사용된 컬럼값들을 이용하여 해쉬 테이블을 만들고 다른 쪽 테이블에 대해서 그 해쉬함수를 적용하여 조인조건을 만족하는 건들을 찾아가는 방법이다.
해쉬테이블을 만드는데 오버헤드가 많이 들 수 있으므로, 이 방법은 주로 한쪽 테이블의 풀린 결과가 매우 적은 경우에 사용된다. 그리고 다른 쪽 테이블이 매우 큰 대용량의 경우에 해쉬방식이 NESTED-LOOPS 방식보다 빠르게 수행되는 편이다.
이 방법은 Equi-Join (조인조건이 =로 되어 있는 경우)에서만 사용될 수 있으며, 힌트로는 USE_HASH가 있다.
3. SORT-MERGE
양쪽 테이블을 풀은 결과를 조인컬럼에 의해서 정렬한 이후에 순서대로 내려가면서 조인조건을 비교하는 방식이다. 인덱스가 없어서 NESTED-LOOPS 방식을 사용하기 어려우면서 양쪽 테이블의 풀은 결과가 작지 않아서 HASH 방식도 어려운 경우에 사용될 수 있다.
이 방식도 equijoin에서만 사용할 수 있고, 힌트로는 USE_MERGE가 있다.
4. Cluster
조인하고자 하는 두 테이블이 조인 연결조건에 의해서 클러스터링 되어 있는 경우에 단순히 클러스터만 읽어서 조인을 할 수 있는 방법이다. 이 방식도 equijoin에서만 사용 가능하다.
Join Methods 1. Outer-Join 두 테이블을 조인할 때 비록 조인조건을 만족하지 않더라도 조인결과에 나오고 싶게 하고 싶은 경우가 있다. 이럴때 아우터조인을 사용할 수 있는데, 항상 기준 테이블을 주고, 기준 테이블에 있는 건들은 조인조건에 의해 버려지지 않도록 한다. 아우터조인의 대상이 되는 테이블의 조건에는 (+) 기호를 붙인다. 그러면 아우터조인의 대상이 되는 테이블의 컬럼값들은 조인조건에 만족되면 있는 값들이 출력되고, 만족되지 않는 경우에는 NULL로 출력된다.
예를 들어, 부서명과 사원명을 출력하되 사원이 하나도 없는 부서명도 출력하라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
만약, 아우터조인의 대상이 테이블에 조건을 주어야 하는 경우에는, 그 조건에 있는 컬럼에도 (+) 기호를 붙여야 하며, 이 의미는 조인조건을 만족치 못하지만 아우터조인에 의해서 살아난 경우에는 그 조건을 검사하지 말라는 의미이다. 그 조건에 (+) 기호를 붙이지 않는 경우에는 그 조건에 의해서 아우터조인의 효과가 무력화된다.
예를 들어, 모든 부서를 나열하되, 매니저가 있는 경우에 매니저이름을 보여라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
AND e.job(+) = ‘MANAGER’
그러나 아우터조인의 대상 테이블에 OR 또는 IN의 조건을 적용해야 하는 경우에는 (+) 기호를 붙이지 못한다.
예를 들어, 모든 부서를 나열하되, 매니저 또는 ANALYST가 있는 경우에는 그 이름을 출력하라.
SELECT d.dname, e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
AND e.job(+) IN (‘MANAGER’, ‘ANALYST’)
위의 예를 수행하면 “ORA-01719: outer join operator(+) not allowed in operand of OR or IN” 에러가 발생한다. 이러한 경우에는 인라인뷰를 사용하는 등의 방법으로 우회해야 한다.
아우터조인의 경우, 조인의 순서는 항상 기준 테이블이 먼저 풀린다.
2. Self-Join 자기 자신 테이블과 조인하는 것을 말하는 것으로, 대개 Self Relationship을 가지는 설계에서 많이 사용된다.
예를 들어, 사원번호 100번의 사원의 이름과 그의 관리자명을 구하라는 쿼리에서
SELECT e.ename, m.ename
FROM emp e, emp m
WHERE e.empno = 100
AND e.mgr = m.empno
만약 위의 예제에서 관리자들을 최상위까지 나열하라. 단, 최상위까지는 3명의 관리자가 있을 수 있다. 라고 한다면 아래와 같이 할 수 있다.
SELECT e.ename, m1.ename, m2.ename, m3,ename
FROM emp e, emp m1, emp m2, emp m3
WHERE e.empno = 100
AND e.mgr = m1.empno
AND m1.mgr = m2.empno
AND m2.mgr = m3.empno
그러나 몇 명의 관리자가 있는지 SQL 개발단계에서 알 수 없다면 셀프조인으로 해결할 수는 없고, CONNECT BY로서 해결해야 한다.
SELECT LEVEL, e.ename
FROM emp
START WITH e.empno = 100
CONNECT BY PRIOR mgr = empno
3. Non-equijoin
조인은 항상 FK에 의해 참조되는 컬럼과의 = 로서만 조인되는 것은 아니다. 때로는 크기비교 연산자나 LIKE 등에 의해 데이터가 연결될 수도 있다.
예를 들어, 사원들의 직급과 연봉수준을 출력하라는 쿼리가 있을때에 SALGRADE 테이블과 EMP 테이블은 BETWEEN으로 연결될 것이다.
SELECT e.ename, e.job, g.level
FROM emp e, salgrade g
WHERE e.sal BETWEEN g.losal AND g.hisal
때로 조인을 하다보면 조인조건이 SUBSTR()에 의해 조인되는 컬럼의 변형(Suppress)를 가하게 되어 인덱스를 사용하지 못하는 경우가 생길 수 있다. 이러한 경우에는 더 유리한 플랜으로 유도하기 위해서는 SUBSTR()으로 잘라서 비교하는 것이 아니고 반대편을 ‘%’를 붙여서 LIKE로 비교하면 변형이 발생하지 않고 인덱스를 사용하는 조인으로 유도할 수 있다.
4. Semi-Join EXISTS를 사용한 서브쿼리에서 SEMI-JOIN 형식으로 풀린다.
5. Anti-Join NOT IN을 사용한 서브쿼리에서 ANTI-JOIN 형식으로 풀린다.
To Prevent Join 1. 비정규화(Denormalization)
설계 당시에 자주 발생할 소지가 있는 조인에 대해서 미리 조인한 형태의 설계를 하는 것을 말한다. 그러나 데이터 관리 및 정합성을 보장하기 어려울 수 있다.
예, 추천종목 테이블에서 종목코드와 더불어 종목명과 시장구분을 가지고 있다.
2. PL/SQL Function 사용 비정규화는 데이터의 생성시에 부담을 줄 수 있다. 따라서 비정규화가 어려운 경우에는 Function을 사용해서 조인을 피할 수 있다.
예, 주문내역테이블의 경우 주문이 발생할 때마다 종목명과 시장구분을 세팅하기가 어려우므로 쿼리시에 get_stockname(stkcd)이라는 함수를 이용한다.
