sql중급 / DDL문 (index)

ㅁ인덱스 복습

1. 인덱스의 구조 : 인덱스를 생성하려는 컬럼의 데이터 + rowid(행의 물리적 주소)

                        컬럼의 데이터가 asc하게 정렬되어있음

2. 빅데이터를 검색할 때 검색속도를 향상시키기 위해서는 인덱스를 생성하고 인덱스를 통해서 데이터를 검색하는게 필수

 

문제526. 아래의 sql의 검색속도를 향상 시키시오(인덱스 생성)

select ename, hiredate

 from emp

where hiredate = to_date('1981/11/17','RRRR/MM/DD');

create index emp_hiredate
 on emp(hiredate);
 
-- 인덱스 생성 후 다시 검색하면 성능 빨라짐

select ename, hiredate
 from emp
 where hiredate = to_date('1981/11/17','RRRR/MM/DD');

문제527. 위의 sql의 실행계획이 인덱스를 access하는지 확인하시오.

explain plan for
select ename, hiredate
 from emp
 where hiredate = to_date('1981/11/17','RRRR/MM/DD');
 
select * from table(dbms_xplan.display);

내가 던진 sql의 실행계획을 만드는 오라클의 옵티마이저가 인덱스를 통해 데이터 검색했음을 확인할 수 있음.

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

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

select 절에  /*+힌트*/ 를 줘서 옵티마이저에게 이렇게 실행계획을 세우면 좋겠다고 권고하는 것.(옵티마이저는 힌트를 무시할 수도 있음)

 /*+ index(emp emp_hiredate) */ = index(테이블명 인덱스이름) 을 사용해라~ 라는 뜻

 /*+ full(테이블명) */ = full table scan을 하라는 뜻

문제528. 위의 sql을 full scan하시오. 이를 실행계획으로 확인하시오

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

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

select ename, hiredate

 from emp

where to_char(hiredate,'RRRR') = '1981';

select ename, hiredate
 from emp
where hiredate between to_date('81/01/01','RR/MM/DD') 
                   and to_date('81/12/31','RR/MM/DD')+1;

문제530. 아래의 sql의 검색속도를 높이기 위해서 인덱스를 생성하시오

select ename, age, telecom

 from emp14

 where age = 27;

create index emp14_age_idx
 on emp14(age);
 
 --인덱스명 작성시 '_idx'붙이면 좀 더 명확하게 인덱스라는 걸 알 수 있음.

인덱스를 이용한 튜닝방법 중 정렬을 일으키는 sql튜닝

order by절이 대용량 데이터를 정렬하게 되면 성능이 아주 느려지기 때문에 order by절 사용을 가급적 자제하는게 바람직.

order by절을 쓰지 않고 데이터를 정렬하는 방법 => 인덱스 활용 

인덱스는 생성할 때만 정렬하고 그 이후에는 정렬된 데이터가 인덱스의 구조로 저장되어 있으므로 인덱스에서 데이터를 읽어오면 정렬된 결과를 볼 수 있음. 

-- 튜닝 전
select ename, sal
 from emp
 order by sal asc;

-- 튜닝 후 : 인덱스에서 데이터를 가져옴
select ename, sal
 from emp
 where sal >= 0 ;
 /* 인덱스의 모든 데이터를 가져오는 것 = 정렬된 데이터를 가져오는 것 
                                    = order by 절을 대체할 수 있음 */

문제531. 아래의 sql을 order by 절 없이 정렬되게 튜닝하시오

-- 튜닝 전
select ename, sal
 from emp
 order by sal desc;
 
-- 튜닝 후
select /*+ index_desc(emp emp_sal) */ ename, sal      --인덱스 힌트
 from emp
 where sal >= 0 ;                                     --where절에 인덱스조건

index_asc 힌트는 asc정렬로 스캔

index_desc힌트는 desc 정렬로 스캔

기본조건이 asc이기 때문에 desc로 정렬시 select절 힌트에 /*+ index_desc(테이블명 인데스명) */ 작성 후 where절에 어느 컬럼에 인덱스를 사용하는지에 대한 검색조건이 있어야함.

asc가 기본조건이라해도 옵티마이저가 환경에 따라 다를 수 있으니 index_asc로 작성하길 권함.

 

ㅁ인덱스 리스트 확인하는 방법

select index_name
 from user_indexes;

 

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

select ename, sal, job

 from emp

 where job = 'SALESMAN'

 order by sal desc;

select /*+ index_desc(emp emp_sal) */ ename, sal, job     --인덱스 desc
 from emp
 where job = 'SALESMAN' and sal >= 0;                     --sal에 대한 인덱스 조건 넣어야함

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

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')

 order by hiredate desc;

select /*+ index_desc(emp emp_hiredate) */ename, hiredate
 from emp
 where hiredate between to_date('1981/01/01','RRRR/MM/DD') 
                 and to_date('1981/12/31','RRRR/MM/DD') ;
    /* where절에 hiredate 검색조건을 설정해줬기 때문에 굳이 인덱스조건 설명하지 않아도 됨.             
       만약 where절에 hiredate 검색조건이 없었더라면,
       인덱스조건 hiredate < to_date('9999/12/31','RRRR/MM/DD')을 넣어줘야 함 */

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

select ename, job, sal

 from emp

 where job like 'SALES%'

 order by sal desc;

select /*+ index_desc(emp emp_sal) */ ename, job, sal
 from emp
 where job like 'SALES%' 
   and sal >= 0;

인덱스는 정말 필요한 컬럼에만 생성해야함. 불필요하게 인덱스를 많이 생성하게 되면 insert, update, delete속도가 느려짐.

책으로 예를 들면, 책 개정시 목차(인덱스)까지 구성해야함. 따라서 시간이 더 걸림. 

 

ㅁ인덱스 삭제하기

1. 인덱스 리스트 확인

select *
 from user_indexes;

2. 리스트에서 확인 후 특정 리스트 삭제하기

drop index emp_sal;

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

select *
 from user_indexes
 where table_name = 'EMP';

 

문제 535. emp14테이블에 걸려있는 인덱스를 모두 삭제하시오

select index_name
 from user_indexes
 where table_name = 'EMP14';
 
drop index EMP14_AGE_IDX;