1장. 인덱스의 원리와 활용

(1) 인덱스의 구조

  - 인덱스 키 컬럼 + rowid 

  -  null 값에는 index가 생성 되지 않는다.
  -  인덱스의 구조를 보고 싶은데 where 절에 적절하게 이용할 조건이 없다면 ?
    (인덱스를 통하려면 무조건 where 절에 해당 인덱스 컬럼이 존재해야 한다.)
    1) 숫자 > 0
    2) 문자 > ' '
    3) where ename < to_date('9999/12/31','RRRR/MM/DD')

 * 해당 index에서 ename is not null 로 걸면 index full table scan 이 된다.
 
(2) index를 사용하지 못하는 경우
   1) 인덱스 컬럼이 가공이 되면 인덱스 엑세스가 안된다.
       ( where 조건절에 인덱스 컬럼이 가공이 되면 정상적으로 인덱스를 엑세스 할 수 없다.)
  
         - 명시적 형변환
         - 묵시적(암시적) 형변환 
               - 숫자가 우선 순위에 있다.
               - 비교하는값이 문자라면 컬럼 값이 숫자여도 문자로 변환된다.     
               - 묵시적 형변환으로 인하여 함수 기반 인덱스를 생성할 경우도 있다.
                 (그러나 그렇게 추천할 만한 사항은 아니다.)  

     2) 부정형 비교시

       * 오라클 옵티마이저가 제대로 작동하지 않는다면 ( 정렬시 컬럼 값이 정렬되어 있지 않다면) SQL 문에 힌트를 사용한다.
          (힌트 (hint) - 실행계획에 영향을 미치는 명령어)
       * 와일드 카드를 앞에 사용하면 index range scan 이 안된다.

(3) 다양한 인덱스 스캔 방식

     1) Data를 엑세스 하는 방법
            ⑴ 테이블 엑세스 방법
                - rowid 에 의한 data 검색
                - full table scan
        
           ⑵ 인덱스 엑세스 방법
                - index range scan
                - index full scan
                - index unique scan
                - index skip scan
                - index fast full scan
                - index merge scan
                - index bitmap merge scan
                - index join

    2) rowid 에 의한  data 검색
         - 하나의 row를 검색하는 가장 빠른 방법

            ⑴ rowid란
                - row의 물리적 주소 (file#+block#+row#)

            ⑵ rowid hint

              - /*+ rowid(테이블명) */

            예시)
              SELECT ename ,sal
                FROM EMP
             WHERE ROWID = 'AAASlIAAEAAAALuAAL';


 * SQL_실행계획 읽는 법

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     2 |    40 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     2 |    40 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_SAL |     2 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
  -> 가장 안 쪽으로 들어가 있는 부분부터 읽으면서 위로 올라간다.

문제 1. 부서번호의 인덱스를 생성하고 부서번호에 인덱스의 구조를 확인하시오!

SQL> create index emp_deptno
            on emp(deptno) ;

SQL> select deptno, rowid
           from emp
         where deptno > 0 ;

문제 2. 아래의  SQL 의 실행계획을 확인해서 SQL을 처리할 때 테이블을 엑세스 하지 않고 인덱스만 엑세스 했다는 것을 확인하시오!

SQL> select deptno, rowid
          from emp
       where deptno > 0 ;

문제 3. 사원 테이블에 월급에 인덱스르 걸고 월급에 걸린 인덱스의 구조를 확인 하시오 !

SQL> create index emp_sal
             on emp(sal) ;

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

* null 값에는 index가 생성 되지 않는다.

문제 4. 사원테이블에 ename 에 인덱스를 걸고 ename에 걸린 인덱스의 구조를 확인하시오!

SQL> create index emp_ename
             on emp(ename);

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

* 인덱스의 구조를 보고 싶은데 where 절에 적절하게 이용할 조건이 없다면 ?
    (인덱스를 통하려면 무조건 where 절에 해당 인덱스 컬럼이 존재해야 한다.)

1) 숫자 > 0
2) 문자 > ' '
3) where ename < to_date('9999/12/31','RRRR/MM/DD')

* 해당 index에서 ename is not null 로 걸면 index full table scan 이 걸린다.

문제 5. 입사일에 인덱스를 걸고 입사일에 걸린 인덱스의 구조를 확인하시오!

SQL> create index emp_hiredate
            on emp(hiredate) ;

SQL> SELECT /*+ index_asc(emp emp_hiredate) */ hiredate, rowid
            from emp
        WHERE hiredate < TO_DATE('9999/12/31','RRRR/MM/DD') ;

* 오라클 옵티마이저가 제대로 작동하지 않는다면 ( 정렬시 컬럼 값이 정렬되어 있지 않다면) SQL 문에 힌트를 사용한다.
* 힌트 (hint) - 실행계획에 영향을 미치는 명령어

문제 6. 월급이 1000에서 3000 사이인 사원들의 이름과 월급을 출력하는데 월급이 높은사원부터 출력하시오!

SQL> SELECT /*+ index_desc(emp emp_sal) */ ename, sal
            from emp
        WHERE sal BETWEEN 1000 AND 3000  ;

*  order by 절은 성능을 느리게 한다.
 
문제 7. 1981년도에 입사한 사원들의 이름과 입사일을 출력하는데 최근에 입사한 사원부터 출력하시오!

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

* 위의 SQL이 Full table scan 이유
    - where  조건절에 인덱스 컬럼이 가공이 되면 정상적으로 인덱스를 엑세스 할 수 없다.

문제 8. 월급이 3000인 사원들의 이름과 월급을 출력하시오!

explain plan for
  SELECT ename, sal
  FROM emp
  WHERE sal=3000;

select * from table(dbms_xplan.display);

문제 9. 이름이 SCOTT 인 사원의 이름과 월급과 직업을 출력하는 실행계획을 출력하시오 !

SQL> explain plan for
    2   select ename, sal
    3  from emp
    4  where ename='SCOTT';

SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 106684950

-----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows    | Bytes | Cost (%CPU)| Time    |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |        |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | EMP_ENAME |     1 |    |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME"='SCOTT')

Note
-----
   - dynamic sampling used for this statement (level=2)

문제 10. 아래의 SQL 을 튜닝하시오 !

튜닝 전 :

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

튜닝 후 :

SELECT /*index_desc(emp emp_sal)*/ename, sal*12 연봉
FROM EMP
WHERE sal = 36000/12;

문제 11. 아래의 SQL을 튜닝하시오!

create index emp_job
on emp(job);

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

튜닝 후:
SELECT /*+ index(emp emp_job) */ ename, job, sal
  FROM EMP
  WHERE job LIKE 'SALES%' ;

※ 와일드 카드를 앞에 사용하면 index range scan 이 안된다.

문제 12. 아래의 데이터를 입력하고 오늘 입사한 사원의 이름과 입사일을 출력하시오!

SELECT ename, hiredate
FROM EMP
WHERE hiredate >= to_date('16/01/08','RR/MM/DD')
AND hiredate < TO_DATE('16/01/08','RR/MM/DD')+1;

문제 13. 아래의 SQL 을 튜닝하시오 !

- 조건
CREATE TABLE EMP700
  (ename VARCHAR2(20),
   SAL VARCHAR2(20));


CREATE INDEX emp700_sal
ON emp700(sal);


튜닝전 :

select ename, sal
from emp700
where sal = 3000;

* 위의 경우 실제 실행 방법

Plan hash value: 3880125747

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP700 |     1 |    24 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("SAL")=:SYS_B_0)

Note
-----
   - dynamic sampling used for this statement (level=2)

튜닝후:

SELECT /*+ index(emp700 emp700_sal) */ ename, sal
from emp700
where sal = '3000';

* SQL*plus 에서 실제 실행계획을 보는 명령어

-  select *
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +alias +outline +predicate'))
/

문제 14. 아래의 SQL의 결과가 출력되겠는가?

튜닝 전 :
select ename, sal
from emp
where sal like '30%';

* 해당 경우는 모델이 잘 못 된 경우로 해당 상태는 프로그램 수정으로는 튜닝이 안된다.
    So. 프로그램 수정이 아닌 다른 방법을 사용해야 한다. 

튜닝 후 : 
( 함수 기반 인덱스를 생성한다. )
create index emp_sal_func
on emp(to_char(sal));

select /*+ index(emp emp_sal_func) */ename, sal
from emp
where sal like '30%';

select /*+ index(emp emp_sal_func) */ename, sal  
from emp 
where sal like :"SYS_B_0"

( 바인드 변수 사용 )

Plan hash value: 3983152063

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP          |    16 |   672 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_SAL_FUNC |    16 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
문제 15. 아래의 data를 입력하고 이름이  JANE 인 사원의 이름과 월급과 직업을 출력하시오!

튜닝 전:

insert into emp(empno, ename, sal)
valuse(2345, '   JANE  ', 4600);

튜닝 후

create index emp_ename_func
on emp(TRIM(ename));

SELECT /*+ index(emp emp_ename_func)*/ ename, sal, job
  FROM EMP
  WHERE TRIM(ename) = 'JANE';

문제 16. 이름이 EN 또는 IN을 포함하고 있는 사원들의 이름과 월급, 직업과 입사일을 출력하시오!

-- 튜닝 전

SELECT ename, sal, job, hiredate
FROM EMP
WHERE ename LIKE '%EN%'
or ename LIKE '%IN%';

OR

SELECT ename, sal, job, hiredate
FROM EMP
WHERE regexp_like(ename,'(EM|IN)');

-- 튜닝 후

SELECT ename, sal, job, hiredate
FROM EMP
WHERE ROWID in (SELECT /*+ index(emp emp_ename) */ rowid
                  from emp
                where ename like '%EN%' or ename like '%IN%') ;

or

SELECT /*+ no_query_transformation rowid(emp) */ ename, sal, job, hiredate
  FROM EMP e,(SELECT /*+ index(emp emp_ename)   */ rowid
                  from EMP
                where ename like '%EN%' or ename like '%IN%') b
WHERE e.ROWID = b.rowid  ;

* 둘의 실행 계획이 다르다. 
  - 바로 검색보다는 서브쿼리르 사용해서 rowid를 먼저 조회하고 검색하는 것이 빠를 수 있다.
  - 어떤 실행계획이 달라서 실행계획을 보고 계산할 수 있을 때까지는 어느것이 더 성능이 좋을 지 모른다.


'ORACLE > SQL 튜닝' 카테고리의 다른 글

SQL튜닝(4) - IOT (index organizaion table) 구성, 클러스터 테이블 구성, 다중 클러스터 테이블 구성  (0) 2016.04.11
SQL튜닝(3) - index(index merge scan, index bitmap merge scan, index join, index uniqe scan), table random access 부하  (0) 2016.04.06
SQL튜닝(2)(1.10) - index ( index range scan, index full scan, index fast full scan, index skip scan )  (0) 2016.04.01

+ Recent posts

티스토리툴바