인덱스
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) 최소로 인덱스를 만들어야 한다.
+ 분포도가 양호한 컬럼도 처리범위에 따라 분포도가 나빠질 수 있음
+ 인덱스 사용원칙을 준수해야 인덱스가 사용되어짐
+ 조인시에 인덱스가 사용여부에 주의
Posted by 홍반장
