[오라클 Index 란, 생성, 삭제]오라클 인덱스에 대해서(Oracle Index)
인덱스관리
- 인덱스는 Table과 Cluster에 관련된 선택적 구조이며 Table에서 명시적으로 생성되어 SQL 문의 실행속도를 향상 시킬수 있다. 인덱스의 존재여부는 SQL문법등에는 변화를 주지않으며 단지 실행속도에만 영향을 준다.
- Oracle은 인덱스가 생성되면 자동으로 유지 관리하며 , 사용자가 더 이상 추가작업을 수행하지 않아도 새행 추가,갱신,삭제등의 일연의 작업을 모든 인덱스에 자동으로 반영한다.
- 인덱스가 없는 Table에 SQL Loader나 Impoer등으로 Data를 삽입한후 인덱스를 생성하는 것이 효율적 이다. Data가 Load되기전에 인덱스를 생성하면 모든 인덱스는 Data가 삽입될때마다 갱신되어야 하므로 OverHead가 발생할수 있다.
- Oracle은 인덱스 생성자(sort_area_size parameter)에 할당된 메모리 정렬영역을 사용하면서 인덱스 생성에 할당된 임시 시그먼트에서 정렬정보를 교환해야 한다.
- 적용기준
컬럼의 분포도가 10 ~ 15 % 이내인 경우 적용
분포도가 범위이내더라도 절대량이 많은 경우에는 단일 테이블 클러스터링을 검토할 것
분포도가 범위 이상이더라도 부분범위처리를 목적으로 하는 경우에는 적용
인덱스만을 사용하여 요구를 해결하는 경우는 분포도가 나쁘더라도 적용할 수 있음(손익분기점)
- 인덱스 컬럼선정
분포도가 좋은 컬럼은 단독적으로 생성하여 활용도 향상
자주 조합되어 사용되는 경우는 결합인덱스 생성
엑세스 경우의 수를 만족할 수 있도록 각 인덱스간의 역할 분담 (Application 별이 아닌 사용형태별)
가능한 수정이 빈번하지 않는 컬럼
가능한 한 컬럼이 여러 인덱스에 포함되지 않게 할 것
기본키 및 외부키 (조인의 연결고리가 되는 컬럼)
결합 인덱스의 컬럼순서 선정에 주의 (사용빈도,유일성,SORT유형,부분범위처리등을 고려)
반복수행(loop 내) 되는 조건은 가장 빠른 수행속도를 낼 수 있도록 하는데 초점을 맞출 것
실제 조사된 엑세스 종류를 토대로 선정 및 검증
- 고려사항
새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수가 있음
지나치게 많은 인덱스는 많은 오버헤드를 발생시킴
넓은 범위를 인덱스로 처리시 많은 오버헤드 발생
옵티마이져를 위한 통계데이타를 주기적으로 갱신 (ANALYZE)
인덱스를 위한 추가적인 저장공간이 필요해 짐
인덱스의 개수는 테이블의 사용형태에 따라 다름
(검색위주형, 동시 다량처리형, 단일 처리형, 배치처리형)
분포도가 양호한 컬럼도 처리 조건(범위)에 따라 분포도가 나빠 질 수 있음
NULL 값을 가지는 컬럼의 인덱스 ENTRY는 생성되지 않음
인덱스 사용원칙을 준수해야 인덱스가 사용되어짐
조인(join)시에 인덱스가 사용되지 않으면 엑세스 경로는 무조건 특정형태로 고정되어짐
- 효율적인 활용
인덱스와 테이블은 서로 다른 디스크에 저장
적절한 STORAGE parameter 부여(initial, next)
PCTFREE 는 테이블보다 적게 부여할 것
부분범위처리 개념을 활용하면 조건이 부여되는 컬럼이 반드시 인덱스를 가지지 않아도 만족한 수행속도를 보장할 수 있음
인덱스만으로 처리되도록 SQL 구사
옵티마이져의 엑세스 경로 생성원칙을 이해
EXPLAIN PLAN, SQL TRACE 활용
최적의 엑세스 경로가 생성되도록 SQL 작성 (사용 인덱스의 분포도 감안)
필요시 힌트(HINT)를 이용한 엑세스 경로 지정
특정 경우 강제로 인덱스를 사용하지 못하게 하여 튜닝
조인(join) 시의 엑세스 경로에 주의, 가능한 조인을 많이 활용
엑세스 경로에 확신이 없을 시 SQL 실행 테스트
응용 프로그램의 핵심 SQL은 반드시 테스트하는 습관이 중요
EXPLAIN PLAN 을 통한 엑세스 경로의 확인 및 SIMULATION
SQL TRACE 를 이용한 악성 엑세스 추출 및 원인 분석. 해결
인덱스 생성 및 삭제 시점의 효율적 조절(로드,배치처리등)
주기적인 튜닝 필요
인덱스관리(2)
1. 인덱스 생성
- 제약조건과 관련된 인덱스생성
Table 생성시 using index option을 가진 enable절을 사용하여 unique key와 primary key제약조건
과 관련된 인넥스에 저장영역 옵션을 설정할수 있다.
create table test (name varchar2(10) primary key,.....)
enable primaey key
using index
tablespace users
pctfree 10
pctused 70;
물론 아래와 같이 사용도 가능하다.
create table test (name varchar2(10), addr varchar2(20),
constraint pk_test primary key (name)
using index storage (initial 10k next 10k) tablespace users_index
)
pctfree 10
pctused 70
storage
(
initial 20k
next 20k
)tablespace users;
Table및 인덱스에대한 저장영역을 명시적으로 지정
- 명시적으로 인덱스 생성
create index emp_name on emp(ename)
tablespace users_index
storage
(
initial 10k
next 10k
pctincrease 0)
pctfree 10
pctused 70;
인덱스에대한 저장영역을 명시적으로 지정
- 기존 인덱스 재생성
데이타 원본으로부터 기존의 인덱스를 재생성
저장영역 설정을 변경가능
인덱스를 삭제하고 create index를 하는것보다 기존 인덱스를 재생성하는것이 낫다.
alter index index_name rebuild;
- 인덱스의 변경
alter index emp_ename
initrans 5
maxtrans 10
storage
(
initial 200k
next 200k
pctincrease 10);
- 인덱스의 삭제
Table이 삭제되면 관련 인덱스도 삭제된다.
Unique Key나 Primary Key제약조건에 설정된 인덱스만 삭제할수 없다.
그렇게 하려면 제약조건 자체를 삭제해야 한다.
drop index emp_ename;
인덱스관리(3)
-인덱스는 테이블의 로우(row)와 하나씩 대응되는 별도의
저장체(object)를 말하며 인덱스를 생성시킨 컬럼(column)
들과 테이블의 로우의 논리적인 주소(rowid)로 구성되고
이들간에 서로 정렬(sort)되어 있다. 인덱스는 하나의 테이블에
여러개를 지정할 수 있으며 하나의 컬럼은 여러개의 인덱스에
포함될 수도 있다. 또한 테이블과는 무관하여 생성 및 삭제가
독립적이다. 경우에 따라 테이블은 전혀 인덱스를 가지지 않을
수도 있으며 인덱스의 변화에 따라 실행결과는 결코 달라지지
않고 다만 처리 경로에 영향을 미칠 따름이다.
- 에플리케이션의 수정없이 인덱스의 조정만으로도 처리경로는
저절로 바뀌게 되므로 실제 실무상에서 적용해 보면 인덱스의
적절한 지정만으로도 60% 이상의 엑세스 효율성을 향상시킬 수
있다. 인덱스는 결코 특정 에플리케이션을 위해 지정하는 것이
아니라 몇 개의 인덱스가 수십, 수백가지의 처리경우를 모두
만족할 수 있도록 지정해야 한다. 가장 최적의 인덱스를 어떻게
구성할 것인가는 수 많은 에플리케이션을 잘 작성하는 것 보다
훨씬 중요하다는 사실을 반드시 명심해야 한다.
- 인덱스를 생성시킨 컬럼으로 정렬(sort)되어 있다. 만약 동일한
값이 여러개가 있다면 같은 값의 인덱스 로우는 다시 ROWID로
정렬되어 저장된다.인덱스에 있는 ROWID는 테이블에 있는 로우의
물리적인 주소(Address)가 아니다. 즉, 테이블에는 ROWID가
실제로는 존재하지 않으며 인덱스에 있는 ROWID는 다만 테이블에
있는 해당 로우를 찾기위해 사용되는 논리적인 정보일 뿐이다.
ROWID는 문자형식으로 볼 때는 18자리로 나타나지만 실제로는
6 바이트를 가진 ROWID 타입(Type)으로 저장된다.
-생성된 인덱스는 경우에 따라 하나 이상 사용되기도 하고 전혀
사용되지 않을 수도 있다. 옵티마이져(Optimizer)에 의해 판단되는
엑세스 경로는 주어진 조건, 인덱스의 구성, 통계정보, 클러스터링,
SELECT LIST, 사용자의 코딩(Coding), 힌트(Hint), 옵티마이져
모드(Mode) 등에 따라 다양하게 나타나며 이 결정된 엑세스 경로에
따라 수행속도가 좌우된다.
그러므로 양호한 엑세스 경로를 보장받으려면 옵티마이져의 엑세스
생성원리에 대한 이해를 바탕으로 좋은 엑세스 경로가 생성될 수
있도록 테이블을 설계하고, 적절하고 종합적인 경우를 대비한
인덱스의 지정, 넓은 범위의 처리나 조인(Join)의 효율성을 향상하기
위한 클러스터링, 인덱스 적용원칙에 맞는 SQL의 코딩(Coding),
효율적인 SQL의 구사, 통계자료의 주기적인 재생성, 힌트나
사용제한기능(Suppressing)을 활용한 옵티마이저를 제어하는
등의 노력이 필요하다.
-부정형(Not, <>)으로 조건을 기술한 경우에도 인덱스는 사용지 않는다.
인덱스 컬럼은 비교되는 상수값의 스트링(String)과 B*_Tree 방식으로
비교하여 찾는 것이므로 주어진 값이 아닌 값을 찾는 부정형 조건에는
직접 비교해야 할 값이 존재하지 않으므로 논리적으로 볼 때 이미 비교할
방법이 없기 때문이다. 물론 부정형으로 작성된 SQL도 상당 부분
긍정형으로 바꾸어 인덱스를 사용하게 할 수 있다.
-인덱스 컬럼이 NULL로 비교되면 사용될 수 없다. 그것은 컬럼의 값이
NULL인 로우는 인덱스에 저장이 되지 않기 때문이다. 물론 여러 컬럼으로
구성된 결합 인덱스의 첫번째 가 아닌 컬럼의 값을 NULL로 비교하는
경우에는 인덱스가 사용된다.
-옵티마이져가 필요에 따라 상기 적용원칙을 준수했음에도 불구하고
특정 인덱스의 사용을 취사 선택함으로써 사용되지 않는 인덱스가
생길 수 있다. 이는 인덱스 머지(Merge)를 피하기 위해 조건의
랭킹(Ranking)에 차이가 있을 때의 취사 선택, 사용자의 힌트,
통계정보에 의거하여 산출된 엑세스 비용(Cost)의 차이 등의
이유로 발생되며 옵티마이져 모드에 따라 서로 다를 수도 있다.
1. 인덱스 컬럼의 변형(Suppressing)
인덱스 컬럼의 변형은 사용자가 펑션(Function), 연산자(Operator) 등에 의해
컬럼에 변형을 가하는 '외부적 변형'과 DBMS에 의해 자동적으로 일어나는
'내부적 변형'이 있다. 이러한 컬럼의 변형이 엑세스에 미치는 영향이 매우
크므로 먼저 사용자의 코딩에 의해 일어나는 외부적 변형의 유형과 그 해결방법을
찾아보자.
-----------------------
* 외부적(External) 변형
-----------------------
인덱스 컬럼에 변형이 일어나면 상대값과 비교되기 전에 먼저 가공이 된 후에
비교가 된다. 인덱스는 가공되기 전의 값으로 생성되어 있는 것이지 가공된
후의 값을 가지고 있는 것이 아니므로 인덱스는 사용될 수 없게 되는 것이다.
외부적 변형이란 사용자가 인덱스를 가진 컬럼을 어떤 펑션(Function)이나
연산, 결합(||) 등으로 가공을 시킨 후 비교할 때 발생되는 것이며 이러한
거의 모든 경우는 변형이 일어나지 않도록 다시 기술할 수 있다. 인덱스
컬럼의 변형은 주로 상대 비교값과의 대응을 위해 사용되지만 다음의 여러
예에서 볼 수 있듯이 비교되는 상대값을 변형시킴으로써 동일한 결과를 얻을 수 있다.
SELECT dept, ename, sal
FROM EMP
WHERE SUBSTR(job,1,4) = 'SALE'
이 SQL은 다음과 같이 바꿀 수 있으며 위의 예와는 다르게 JOB으로 생성된 인덱스를
사용할 수 있다.
SELECT dept, ename, job
FROM emp
WHERE job LIKE 'SALE%'
유사한 몇 가지 예를 좀 더 살펴보기로 하자.
SELECT empno,ename,job FROM emp
WHERE TO_CHAR(hiredate,'YYMMDD') TO_DATE('950101',YYMMDD')
SELECTempno,ename,job FROM emp
WHERE hiredate = '950101' (O)
어떤 사용자들은 NULL 값에 대한 막연한 두려움 때문에 다음과 같은 실수를 한다.
뒤에서 NULL 공포증을 이겨내는 방법을 자세히 설명하겠지만 비교되는 컬럼에 NULL
값이 있으면 처리대상에서 아예 제외되므로 많은 사용자들은 억지로라도 NULL 값을
없앤 후 비교해야 한다고 착각하는 경우가 있다.
SELECT empno, ename, job
FROM emp
WHERE NVL(job,'X') = 'CLERK';
SELECT empno, ename, job
From emp
Where job = ‘CLERK’; (O)
결합 인덱스를 비교하는 경우 비교할 컬럼이 여러개이므로 다음과 같은 실수를 범한다.
SELECT empno, ename, job
FROM emp
WHERE job || dept = 'CLERK10'
SELECT empno, ename, job
From emp
WHERE job = 'CLERK'
AND dept = '10'; (O)
SELECT *
FROM tab1
WHERE col1 || col2 = :FLD
SELECT *
FROM tab1
WHERE col1 = SUBSTR(:FLD,1,3)
AND col2 = SUBSTR(:FLD,4,2) (O)
--------------------------
* 내부적(Internal) 변형
---------------------------
인덱스 컬럼의 변형은 사용자가 직접 컬럼을 가공시켜 코딩하지 않더라도
서로 다른 데이타 타입을 비교하고자 할 때 DBMS가 어느 한 쪽을 기준으로
하여 동일한 타입이 되도록 내부적인 변형을 일으키게 하여 자신도 모르게
사용제한(Suppressing)이 발생된다. 이러한 내부적인 변형은 원하지 않은
엑세스 경로를 만들게 되어 수행속도에 막대한 영향을 미치는 경우가 많이
발생되므로 정확히 알고 사용해야 한다.
여러가지 경우를 설명하기 위해 먼저 다양한 컬럼 타입을 가진 간단한
테이블을 생성해 보기로 한다.
CREATE TABLE SAMPLET
( chr CHAR(10),
num NUMBER(12,3),
var VARCHAR2(20),
dat DATE )
먼저 문자 타입과 숫자 타입이 서로 비교되는 경우를 살펴보자.
SELECT chr, num, var, dat
FROM SAMPLET
WHERE chr = 10
상수 부분은 숫자이고 비교되는 'chr' 컬럼은 문자 타입일 때는 숫자를
기준으로 문자 타입이 숫자 타입으로 변한다. 물론 이러한 법칙은
가변길이(varchar2)에서도 동일하게 적용된다. 위의 SQL은
SELECT chr, num, var, dat
FROM SAMPLET
WHERE TO_NUMBER(chr) = 10
로 코딩한 것과 동일한 변형이 DBMS 내부에서 수행된다. 이러한 기준은
비교값이 상수가 아니라 숫자 타입으로 지정된 테이블의 컬럼값일 때도
동일하게 적용된다. 그러므로 컬럼의 데이타 타입을 결정할 때 같이
비교해야 하는 컬럼의 타입은 반드시 일치시켜야 한다. 이러한 내부적인
컬럼의 변형을 방지하기 위해 아래와 같이 상수값의 타입을 문자 타입으로
기술하는 습관을 들여야 한다.
SELECT chr, num, var, dat
FROM SAMPLET
WHERE chr = '10'
이와 유사하게 숫자 타입으로 지정된 컬럼을 다른값과 비교한다면
SELECT chr, num, var, dat SELECT chr, num, var, dat
FROM SAMPLET FROM SAMPLET
WHERE num = 10 WHERE num = '10'
상수값이 문자 타입이든 숫자 타입이든 컬럼의 타입이 숫자인 경우는
내부적 변형이 일어나지 않는다. 그렇다면 일견 가능한 한 숫자 타입
으로 지정해야 안전할 것처럼 보이지만 다음의 예를 살펴보면 오히려
문자 타입으로 지정하는 것이 바람직하다는 것을 알게 된다. 만약
다음과 같이 우리가 숫자 타입으로 지정된 컬럼을 'LIKE'로 비교한다면
SELECT chr, num, var, dat
FROM SAMPLET
WHERE num like '10%'
원칙대로라면 'num' 컬럼이 숫자 타입이므로 '10%' 쪽이 숫자로 변해야
하나 '%'라는 문자값이 있으므로 숫자 타입으로 바꿀 수 없다. 그렇다고
에러를 낼 수 없기 때문에 DBMS는 이번에는 상수값을 기준으로 숫자 타입을
문자 타입으로 바꾸게 되어 'num' 컬럼은 인덱스를 사용할 수 없게 된다.
실무상에서는 항상 '='로만 사용되는 경우는 그리 많지 않으며 사용자의
요구에 따라 다양한 범위의 데이타가 요구된다. 또한 수행속도에 문제를
일으키는 대개의 경우는 넓은 범위의 처리에서 발생되므로 인덱스 지정이
예상되는 컬럼은 문자타입으로 하는 것이 좋다.
물론 일련번호와 같이 'LIKE'로 사용할 의미가 없는 것은 숫자 타입으로
하는 것은 당연하다.
SQL 코딩시 반드시 유의할 사항은 비교하는 상수값을 함부로 숫자 타입으로
하지 않도록 해야 하며(물론 숫자 타입인 경우가 확실하다면 필요없지만)
특히 조인(Join)의 연결고리가 되는 컬럼들의 데이타 타입이 서로 다르지
않도록 해야 한다. 다음의 예를 통해 조인 컬럼들의 타입이 다를 때 발생하는
문제점을 알아 본다.
SELECT x.ordno, x.orddate, y.item, y.ordqty
FROM ORDER1T X, ORDER2T Y
WHERE x.ordno = y.ordno
AND x.ord_date = '950201'
이 조인 문장은 연결키가 정상이라면 ORDER1T 테이블의 x.ord_date' 인덱스의
'950201' 데이타를 찾아 테이블을 엑세스한 후 그 'ordno'에 대응되는 ORDER2T
테이블의 로우들만 찾게 된다. 만약 x.ordno 가 숫자 타입이고 y.ordno가
문자 타입이라면 y.ordno는 숫자 타입으로 변경되어 인덱스를 사용할 수 없는
상태가 되므로 ORDER2T 테이블이 먼저 전체 로우를 엑세스하여 각 로우마다
그 'ordno'에 대응되는 ORDER1T의 로우를 찾아 x.ord_date 가 '950201'
인 지를 검증한다.
이 두가지 경우의 엑세스 량은 아주 큰 차이가 나게 되어 엄청난 수행속도의
저하를 가져 오게 되므로 테이블 설계 시에 반드시 유의해야 한다.
 | |||||
|---|---|---|---|---|---|
[기업100%환급]SQL기초에서
Schema Object까지  |
총 | 5일 | 40시간 | 01-22 | |
[기업100%환급]PL/SQL,ORACLE
HINT,TUNING  |
총 | 5일 | 40시간 | 02-17 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 18일 | 54시간 | 02-11 | |
| 빅데이터
하둡 실무과정 |
총 | 12일 | 36시간 | 02-18 | |
 | |||||
| SQL초보에서실전전문가까지
|
총 | 8일 | 56시간 | 01-18 | |
| 빅데이터
하둡 실무과정 |
총 | 5일 | 35시간 | 02-15 | |
댓글 없음:
댓글 쓰기