SQL - INDEX 데이터검색속도 높이기

index 는 책의 '목차' 와 비슷하다.

• 테이블의 검색속도를 높이기 위한 database object 이다.
  ex) 일반쿼리 검색 속도가 너무 느리다면 실행계획을 먼저 살펴 본 후 index 가 없으면 index 를 생성해 다시 실행 해 준다.
  (검색할 컬럼 = where 절 에 사용할 컬럼 ) !
• index 의 구조 :  컬럼명과 rowid 로 구성되어있다.
• 인덱스(목차)를 생성하려는 컬럼의 데이터가 자동으로 asc 하게 정렬되어 구성됨
• 정말 필요할때만 인덱스를 생성해야한다.
• 불필요하게 인덱스를 많이 생성한다면 insert, update, delete 속도가 느려진다.

인덱스를 생성하는 방법 2 가지

1. 수동으로

create index 인덱스이름
on 테이블명(컬럼명)

1. 자동으로
   primary key 제약이나 unique 제약을 걸명 자동적으로 index 가 생성된다.

---

index의 구조를 확인하고 싶다면

select ename, rowid
from emp
where ename > ' ';

---

→ 인덱스 안 모든 데이터를 검색하고 싶다면

1. 숫자 > 0
2. 문자 > ' '
3. 날짜 < to_date ('9999/12/31', 'RRRR/MM/DD')
   로 검색 하면 된다.

• 검색해서 확인해보면 컬럼이 asc 하게 abc 순으로 자동으로 정렬되어 검색된다.
  → index 는 자동적으로 정렬되어 만들어준다.
• index 의 rowid 를 통해 table 의 rowid 로 바로 검색한다.
  같은 rowid 이기 때문에 index 에서 먼저 rowid 찾은후 그다음 table로 rowid 를조회하기 때문에 속도가 빠르다. 
  → full scan 할 필요도없다.
  → 인덱스가 없다면 테이블을 처음부터 끝까지 다 읽게 된다
  ( 중간에 1600 을 찾는다 하더라도 또 있을지 모르니 끝까지 읽게 (full scan) 되는것 )
• → 인덱스가 있다면 asc 된 월급을 찾는것이기때문에 찾게되면 더이상 그 밑으로 찾지 않는다.
• rowid = 행의주소, 책으로 치면 page 번호

지금까지 만든 index 조회하기

select  index_name
from user_indexes;

특정 테이블에 걸린 인덱스 확인하기

select index_name
from user_indexes
where table_name ='EMP';

인덱스 구조 확인

select 컬럼명, rowid
from 테이블명 
where 컬럼명  > ' ';

→ 컬럼이 문자인 경우 구조 검색 방법 이다.

인덱스 삭제하기

drop index 인덱스명 

 

예제

만약 인덱스가 없는 평범한 쿼리로 sal 을 검색한다면

select ename, sal
from emp
where sal = 1600 ; 

→ emp table 을 full scan 해서 월급이 1600 인 사원의 데이터를 찾아 출력한다.

그러나 인덱스가 존재한다면

create index emp_sal (= 인덱스 이름) 
on emp(sal);  → 테이블명(컬럼명)

→ 인덱스 생성 후

select ename, sal
from emp
where sal =1600;

→ 쿼리문을 작성할때 where 절에 sal 로 검색을 하면 오라클에서 자동으로 emp_sal 인덱스를 먼저 검색하여
sal 의 1600인 rowid 를 알아내어 emp 의rowid 의 행을 찾아 이름과 월급을 조회합니다.

문제 515 emp테이블의 이름 컬럼에 인덱스 거시오

create index emp_ename
on emp(ename);

select ename, sal, job
from emp
where ename = 'SCOTT';

 

→ 이름을 빨리 찾기 위해 이름에 대한 index 를 만든다.
지금 만든 index 의 구조는 ename과 rowid 로 구성되어있다.

문제 516 emp_sal 의 인덱스 구조를 살펴보시오 ( 인덱스가 NUmber 인 경우) 

select sal, rowid
from emp
where sal >= 0;

 

' ' 공백은 문자 일때 해당되며 숫자는 0 으로 해야한다.

 

문제 517 이름이 제임스인 사원 이름,월급, 부서번호 출력 해라

select ename, sal, deptno
from emp
where ename = 'JAMES';

 

지금 문제 sql 을 실행하면서 아까 ename 의 index 를 만들었는데 위의 sql 은 ename 의 index 를 사용해서 검색했는지?
아니면 table 을 full scan 했는지 알아보려면 ?
쿼리 실행 계획을 볼 수 있어야 한다.

explain plan for
select ename, sal, deptno
from emp
where ename = 'JAMES';

select * from table (dbms_xplan.display);

→ 안쪽에서 밖으로 읽으면 된다.
인덱스를 거쳐 ename 이 검색 됐음을 확인 할 수 있다. 

그런데 만약 index 를 DROP 하고 난뒤 ENAme 을 검색하게 되면 어떻게 되는지 실행 계획을 확인 해보자 

DROP INDEX EMP_ENAME;  → 삭제 
explain plan for
select ename, sal, deptno
from emp
where ename = 'JAMES';

select * from table (dbms_xplan.display);

 

→ 실행 계획을 확인 해보면 table access full 로 테이블을 full scan 한것을 확인 할 수 있다.
→ 느려진다!

문제 81년11월17 일날 입사한 사원의 이름과 입사일을 출력 하시오 (인덱스사용)

create index emp_hiredate
on emp(hiredate);

→ 인덱스 생성

explain plan for
select ename, hiredate
from emp
where hiredate = to_date('81/11/17', 'RR/MM/DD');

select * from table (dbms_xplan.display);

 

→ 실행 계획 살펴보면 인덱스 먼저 실행된뒤 검색 되었다.

index 가 존재하는데 불구하고 full table scan 된 경우! (튜닝전sql )
where 절 컬럼이 가공된경우

select ename, hiredate
from emp
where to_char(hiredate, 'RR/MM/DD') = '81/11/17';

 

해당 쿼리의 실행 계획을 보면

explain plan for
select ename, hiredate
from emp
where to_char(hiredate, 'RR/MM/DD') = '81/11/17';

select * from table (dbms_xplan.display);

→ where 절 hierdate 에 to_char 로 가공이 되었기 때문에 full table scan 이 된것이다.

---

문제 522 아래의 sql 을 튜닝하시오

튜닝전

select ename, sal
from emp
where sal * 12 = 36000;

튜닝 후

select  ename, sal
from emp
where sal = 36000/12;

 

튜닝전 where 절에 가공된 sal 의 *12 를 = 로 넘겨 가공되지 않은 sal 로 튜닝해준다.
sal 컬럼이 index 되어있다면
튜닝전 sql 은 가공되어있기 때문에 index 되지 않고 FULL SCAN 하는 반면에
튜닝 후 SQL 은 가공되어있지 않은 SAL 이기 때문에 INDEX 를 이용해 실행 된다.

---

문제 523 아래의 SQL 의 검색속도를 높이기 위한 알맞은 인덱스를 생성하시오

create index emp_job
on emp(job);

EXPAIN PLAN FOR
select ename, sal, job
from emp
where job = 'ANALYST';

select * from table (dbms_xplan.display);

---

문제 525 튜닝전 SQL 을 튜닝하시오

튜닝전 SQL

select ename, sal, job
from emp
where substr(job,1,5) = 'SALES';

→ job 에 index 가 되어있다해도 가공된 job 이여서 index 를 적용시키지 못하고full table scan 을 하게됩니다.

튜닝후

1. index 생성 (where 절의 job)

create index emp_job
on emp(job) ;

1. JOB 에 중첩되어있는 함수(SUBSTR)를 LIKE 으로 풀어 튜닝.

select ename, sal, job
from emp
where job like 'SALES%';

1. 실행 계획 확인

explain plan for 
select ename, sal, job
from emp
where job like 'SALES%';

select * from table (dbms_xplan.display);

1. INDEX 실행 확인 완료
2. 튜닝 완료

문제 526 입사일에 인덱스 거시오 

create hiredate_idx
on emp(hiredate);

→ 인덱스 생성

explain plan for
select ename, hiredate
from emp
where hiredate = to_date('1981/11/17','RR/MM/DD');

select * from table (dbms_xplan.display);

→ 실행결과를 확인해 보면 index range scan 되었다.
→ 내가 작성한 SQL 의 실행계획을 만드는 오라클의 옵티마이저가 인덱스를 통해서 데이터를 검색했다.

---

인덱스를 만들었는데도 불구하고 FULL TABLE 스캔할때 ( 옵티마이저가 INDEX 를 잊은것 같을때 )

만약 HIREDATE_IDX 를 만들었는데도 불구하고 옵티마이저가 인덱스를 통해 실행계획을 세우지 않았다면
우리가 옵티마이저에게 인덱스를 통해서 실행계획을 세우라고 알려주어야한다.
알려주는 방법?

select /*+ index(emp hierdate_idx) */ ename, hiredate
from emp
where hiredate = to_date ('1981/11/17','RR/MM/DD');

1. */*+ 힌트 / 의 의미? 힌트라는 의미이다.
2. 옵티마이저에게 이렇게 실행계획을 세우면 좋겠다고 권고를 하는것이다 ( 권고? 왜냐면 옵티마이저도 힌트를 무시할때도있기때문)
3. /*+ 를 입력한후 꼭 한칸의 공백을 주어야한다.
4. index (테이블명 인덱스이름) : 이 테이블의 인덱스를 사용해라

만약 full table scan 을 원한다면 ?

/*+ full(테이블명) */

→ 이테이블을 full table sacn 하여라

explain plan for
select /*+ full(emp) */ ename, hiredate
from emp
where hiredate = to_date ('1981/11/17','RR/MM/DD');

select * from table(dbms_xplan.display);

→ 옵티마이저가 full scan 하였다.

---

문제 529 튜닝하시오

튜닝전

select ename, hiredate
from emp
where to_char(hiredate,'RRRR') = '1981';

→ where 절에 hiredate 가 to_date 로 중첩되어있다.

튜닝후

expLain plan for
select  ename, hiredate
from emp
where hiredate between to_date('1981/01/01','RRRR/MM/DD')and
to_date ('1981/12/31','RRRR/MM/DD')+1;

select * from table (dbms_xplan.display);

→ 확인해보면 INDEX 실행되었다.

- 그리고 웬만하면 날짜 관련 데이터를 추출할 때에는 to_date 함수를 사용하여 출력하는게 정확하다. 

530 아래 emp14 테이블에 age컬럼에 인덱스를 만든후, 인덱스를 통한 age 조건절검색이 되었는지 실행계획을 통해 확인하시오

create index emp14_age_idx
on emp14(age);

explain plan for
select ename, age, telecom
from emp14
where age = 27;

select * from table (dbms_xplan.display);

 

 

다음에는 힌트를 사용하여 인덱스를 asc 정렬이 아닌 DESC 하는 시간을 배워보겠다.