Oracle - index란?

index란 무엇인가?

조회를 빠른 속도로 처리하기 위해서 사용됩니다.

SQL 명령문의 처리 속도를 향상시키기 위해서 컬럼에 대해서 생성하는 오라클 객체임.

---

--index 사용해야할 경우

1.테이블에 행의 수가 많을 때

2.조건절인 where문에 해당 컬럼이 많이 사용될 때

3.검색 결과가 데이터의 2% ~ 4% 정도일때(적은 양의 컬럼을 가져올 때)

4.join에 자주 사용되는 컬럼

5.NULL을 포함하는 컬럼이 많은 경우

---

--index 사용하지 말아야 하는 경우

테이블에 행의 수가 적을 때

where 문에 해당 컬럼이 자주 사용되지 않을 때

검색 결과가 전체 데이터의 10% ~ 15% 이상 높을 때(많은 양의 컬럼을 가져올 때)

테이블에 DML 작업이 많은 경우(입력,수정,삭제 등이 자주 발생할 때)

---

--index 딕션너리

USER_INDEXES :인덱스 이름 및 고유성을 포함

USER_IND_COLUMNS : 인덱스 이름, 테이블 이름, 열 이름 포함

---

--index 생성법

create index index명 on table명(index걸어줄 table에 colum명);

ex)create index idx_emp_ename on emp(ename);

---

--index 제거

drop index index명

ex)drop index idx_emp_ename;

---

--index 재생성

alter index index명 rebuild;

ex)alter index idx_emp_ename rebuild;

---

--index 조회(index를 모두 보여준다.)

select object_name, object_type

from user_objects

where object_type ='INDEX';

효율성이 떨어지면 인덱스를 재 생성해 주어야 한다.

컬럼의 데이터가 입력,수정,삭제될 경우 해당 컬럼에 의해 생성된 인덱스에 대해서 재구성 해야 된다.

---

--index 종류

Unique Index(고유 인덱스) - 유일한 값을 갖는 컬럼에 대해서 생성하는 인덱스

NonUnique Index(비고유 인덱스) - 중복된 데이터를 갖는 컬럼에 대해서 생성하는 인덱스

Single Index(단일 인덱스)

Composite Index(결합 인덱스)

 결합 인덱스 우선순위

 1순위 - 컬럼이 사용한 연산자에 의한 인덱스 컬럼 선정

 2순위 - 랜덤 액세스를 고려한 인덱스 컬럼 선정

 3순위 - 정렬 제거를 위한 인덱스 컬럼 선정

 4순위 - 단일 컬럼의 분포도를 고려한 인덱스 컬럼 선정

 위의 우선순위 구성 규칙의 이유는 디스크 I/O를 가장 적게 발생시키기 위해서다.

Function Based Index(함수 기반 인덱스)

 표현식을 기반으로 하는 인덱스

 인덱스 표현식은 테이블 열, 상수, SQL 함수 및 사용자가 정의한 함수로부터 생성

 1.컬럼의 중간 부분의 검색

 create index from_loc_idx on orders(sbstr(ship_id,5,3));

 create index repair_loc_idx on orders(substr(ship_id,3,2,),ord_date);

 2.조인 연결고리 컬럼이 대응하지 않는 경우의 해결

 ...

 from item_group x,items y

 where x.class1||x.class2||x.class3 = y.group_cd....

             ▽

 ...

 from item_group x, items y

 where x.class1 = substr(y.group_cd,1,2)

 and x.class2 = substr(y.group_cd,3,2)

 and x.class3 = substr(y.group_cd,5,3)

 ...

             ▽   

 create index group_cd_idx_ on item_group(class1||class2||class3);

 3.일자 컬럼이 분활된 경우의 해결

 where sal_yyyy >='2005' and sal_mm >='12' and sal_dd>='10' (x)

            ▽

 where sal_yyyy||sal_mm||sal_dd >='20051210' (o)

            ▽

 create index sal_date_idx on  sales(sal_yyyy||sal_mm||sal_dd);

 4.데이타 타입이 상이한 조인 컬럼

 create index deptno_idx on emp(to_number(deptno));

 5.조인 컬럼이 경우에 따라 달라지는 경우

 ...

 from sales s, department d

 where d.deptno = (case when sal_type = 1 then sal_dept else agent_no end)

 and d.location = 'BUSAN'

 '''

              ▽

 create index deptno_idx on sales(case when sal_type = 1 then sal_dept else agent_no end); 

 6.대/소문자나 공백이 혼재된 컬럼의 검색

 create index ename_upper_ix on employees(upper(ename));(대문자)

 create index ename_upper_ix on employees(upper(replace(ename,''));(대문자/공백)

 7.NULL 값을 치환하여 검색

 ...

 where :input_date between start_date and nvl(end_date,'99991231');

 ...

             ▽

 create index end_date_idx on account_history(nvl(end_date,'99991231'),start_date);

 8.복잡한 계산 결과의 검색

 create index order_amount_idx on order_items(item_cd,(order_price - nvl(order_discount,0)) * order_count));

 select/*+index_desc(x order_amount_idx)*/ *

 from ordero_items x

 where item_cd =:b1

 and rownum <= 100;

 9.기간, 컬럼 길이 검색

 create index item_idx on activities (expire_date - start_date); (기간)

 create index source_length_idx on print_media(text_length(source_text));(컬럼 길이)

 10.배타적 관계의 유일성 보장

 create unique index official_id_idx on customers

     (case when cust_type=1 then resident_id else business_id end);

 select * from customers

 where(case when cust_type=1 then resident_id else business_id end) =:b1;

 함수기반 인덱스는 버전에 따라 제약사항에 차이가 있으므로 관련 메뉴얼을 참조해야함.

ex)DATE TYPE 과 함수기반인덱스 활용 예

DATE TYPE으로 칼럼을 설계했을경우 일자만 비교하고 싶으면 TRUNC(DATE_COL)이렇게 하면됨

그런데 이런 함수를 쓰면 INDEX를 사용할 수 없다.(해결방법 Function-Based index)

create index ix_t on t(trunc(date_col)); (여기서 date_col은 date_type의 컬럼명)

쿼리문

select * from t

where trunc(date_col) = '20091001';

이렇게 하면 된다.

trunc(date_col)이 부분을 간편하게 이용하려면 뷰를 이용

create view v_t

as

select trunc(date_col) as date_col from t;

이러면 , 쿼리문이

select * from t

where date_col = '20091001';

---

[고유인덱스]-단일

 create unique index idx_dept_deptno on dept(deptno);

[비고유인덱스]-단일

 create index idx_dept_loc on dept(loc);

[결함 인덱스]

 create index ind_dept_com on dept(deptno,loc);

[함수기반 인덱스]

 create table emp as select * from emp;

 검색 조건으로 sal*12 = 3600 을 지정하는 경우, 이럴경우 sal*12를 인덱스를 탈 수 없을수 있다.

 이럴때 구성하는 인덱스가 함수기반 인덱스 입니다.

 create index idx_emp_annsal on emp(sal*12);(함수기반 인덱스 생성)

 select index_name, column_name

 from user_ind_columns

 where table_name='emp'; (인덱스 생성 확인)

--where 절의 사용에 따른 인덱스 활용

 점 조건 :IN,= -->해당 연산자는 하나의 점만을 의미

 선분 조건:LIKE,BETWEEN,<,> -->해당 조건을 만족하는 모든 실수를 의미

 조건에 사용된 연산자에 의해 액세스해야 하는 처리 범위 차이

 점 조건 + 점 조건 : 두 조건에 의해 처리 범위 감소

 점 조건 + 선분 조건 : 두 조건에 의해 처리 범위 감소

 선분 조건 + 선분 조건 : 앞의 선분 조건에 의해서만 처리 범위 감소

 선분 조건 + 점 조건 : 앞의 선분 조건에 의해서만 처리 범위 감소

 단일 인덱스보다는 결합 인덱스를 사용하고 점 조건을 우선적으로 인덱스 조건으로 해야 성향상을 기대할 수 있다.

---

--index 사용/불가 예

* 인덱스를 타지 않는 SQL

1. 인덱스 컬럼 절의 변형

2. 내부적인 데이터 변환

3. NULL 조건의 사용

4. 부정형 조건의 사용

5. LIKE 연산자 사용

6. 최적기가 판단

1 - 인덱스 컬럼 절의 변형

SQL> select ename from emp where sal * 2.1 > 950 --인덱스 사용불가

SQL> select ename from emp where sal > 950 /2.1  --인덱스 사용가능

SQL> select ename from emp where to_char(hiredate,'DDMMYY') = '250884' --인덱스 사용불가

SQL> select ename from emp where hiredate = to_date('250884','DDMMYY') --인덱스 사용가능

-> 인덱스 컬럼에 변형을 가하면은 사용할수 없습니다. 단 변형 가능하더라도 쓰고 싶다면은

    말리지는 않겠지만 create index .... on emp to_char(hiredate,'DDMMYY') 이렇게 하시면 됩니다.

2 - 내부적인 데이터 변환

SQL> select * from emp where hiredate ='14-JAN-85' --인덱스 사용불가

SQL> select * from emp hiredate = to_date('71-10-22','YY/DD/DD') --인덱스 사용가능

SQL> select * from emp where empno = '7936' --인덱스 사용불가

SQL> select * from emp where empno = to_number('7936') --인덱스 사용가능

-> 내부적인 데이터변환에서 가장 많이 실수하는 부분은 문자값 데이터타입을 갖는 컬럼에

    '값' -> 값 이렇게 하시는분이 많습니다. 딱맞는 데이터타입을 주세요 ^_^

3 - NULL 조건의 사용

SQL> select ename from emp where comm is null --인덱스 사용불가

SQL> select ename from emp where comm is not null --인덱스 사용불가

SQL> select ename from emp where ename > '' --인덱스 사용가능

SQL> select ename from emp where comm >= 0 --인덱스 사용가능

-> NULL조건으로 검색한다는 가정하에는 거의 인덱스 풀 스캔이 일어나겠죠. 적절히 사용합씨다.

4 - 부정형 조건의 사용

SQL> select ename from emp where deptno != 30 --인덱스 사용불가

SQL> select ename from emp where deptno < 30 and deptno > 30 --인덱스 사용가능

-> 논리적으로 부정형을 이용하여 인덱스를 사용하겠다는것은 말이 안되죠...

    이 쿼리문도 적절히 사용합씨다.

5 - Like 연산자 사용

SQL> select * from emp where ename like 'S%' --인덱스 사용가능

SQL> select * from emp where ename like '%S%' --인덱스 사용불가

-> %S% 부분을 꼭 쓰고싶다면은 이렇게 하세요 앞에부분을 다 넣는거죠. AS%, BS%...

    요즘 홈피를 보면 본문 찾기는 거의 없어져가고 있죠. 엔코아 경우 주제어를 검색을..

6 - 최적기가 판단

-> RBO경우 무조건 타죠 있으면은 이눔은 워낙 법을 좋아해서리..CBO는 통계값을 기준으로

    하기 때문에 DBA가 잘 해야겠죠. 그럼 우리가 판단하게 할라면은 HINT를 써서 이눔들을

    인도해야죠... 오늘도 전도를 ^____^

마지막으로 인덱스를 이용한 sort 회피 방법에 대해서 알아보겠습니다.

SQL> select empno, ename, job, comm from emp order by empno --sort 정렬

SQL> select empno, ename, job, comm from emp where empno >= 0 --sort 정렬제거

-> 인덱스는 값이 정렬되어 들어가있는 구조이기 때문에 처음부터 작은값 맨 끝값은

    최대값이 있겠죠. 잘 사용하면 최고의 튜닝이 되겠죠.

--Optimizer Hint Syntax(Hint 사용)

-----> SELECT -----> /*+ hint 절*/ ------->

DELETE

UPDATE

또는

-----> SELECT -----> --+ hint 절 ------->

DELETE

UPDATE

Hint 절에는 다음 조건들을 부여할 수 있다.

1. FULL(table_name)

: table을 full scan하길 원할 때 사용함.

2. INDEX(table_name index_name[index])

: 특정 index를 사용하도록 할 때 사용함.

3. INDEX_ASC(table_name index_name[index])

4. INDEX_DESC(table_name index_name[index])

5. AND_EQUALS(table_name index_name index_name[index])

6. ROWID(table_name)

7. CLUSTER(table_name)

8. HASH(table_name)

9. ORDERED

10. USE_MERGE(table_name)

11. USE_NL(table_name)

Hint를 사용하여 특정 index를 사용한 update의 예

(이 결과는 7.3 이상에서 제공되는 auto trace를 사용하는 방법이나

execution plan 또는 tkprof를 이용하여 확인할 수 있다.)

Update /*+ INDEX (e1 e_job) */

Emp e1

SET sal =

(SELECT --+ INDEX (e1 e_dept)

(e1.sal + AVG(e2.sal))/2

FROM emp e2

WHERE e2.deptno = e1.deptno)

WHERE job = 'CLERK'

AND deptno BETWEEN 10 AND 40;