13-1. DATABASE OBJECT(INDEX)

■ 데이터베이스 오브젝트 5가지

1. table

2. view

3. sequence

4. index  -------->데이터 검색속도를 높이는

                   object

                  (책의 목차같은 느낌)

5. synonym

 

 

■INDEX

데이터 검색속도를 높이는 OBJECT

책의 목차와 같은 역할을 한다.

 

문제421.

사원 테이블에 이름에 인덱스를 거시오.

 

create index emp_ename

 on emp(ename);

 

create index 테이블명_컬럼명

on테이블명(컬럼명)

 

※설명

사원 이름을 조회할 때 검색속도를 높이기 위한 인덱스(목차)가 만들어졌음.

emp_ename 인덱스는 abcd... 순으로 정렬이 되어서 만들어졌음.

 

 

select ename, sal

 from emp

 where ename = 'SCOTT';

 

 

문제422.

emp_ename 이 abcd 순으로 정렬이 되어서 사원이름을 저장했는지 확인하시오

 

select ename, rowid

 from emp

 where ename > '    ';

 

rowid 가 책의 페이지수 라고 생각하면 된다.

 

F7 누르면 index 쓴거 확인할 수 있다.

 

select ename, rowid

 from emp;

이대로 돌리면 (에프7)

table access full 이라고 나온다

 

 

인덱스의 구조:

 1. 컬럼값 + rowid 로 구성

 2. 컬럼값이 내림차순으로 정렬이 되어 있다. 엥 오름차순 아님??

 

튜닝전: select ename, sal

         from emp

          order by ename asc;

 

튜닝후: select ename, sal

           from emp

          where ename > '   ' ;

왜 where 절에 공백을 주고 부등호를 쓸까?

  모든 글자를 다 뽑기 위해

  공백문자 보다 큰 거 뽑으라고 조건 건다.

 이미 index 를 해줬기 때문에

order by 안쓰고

where 절에 저런 조건만 걸어도

오름차순으로 정렬된다.

부하 안걸리고 좋다.

정렬은 무리가 많이 가는 명령이다

order by를 가급적 안 쓰는게 좋다.

 

 

문제423.(오늘의 마지막 문제)

월급에 인덱스를 걸고

아래의 sql 을 튜닝하시오.

 

튜닝전: select ename, sal

        from emp

        order by sal asc;

 

 

create index emp_sal

on emp(sal);

 

튜닝후: select ename, sal

          from emp

          where  sal > -1 ;

 

 

create index 테이블명_컬럼명

on테이블명(컬럼명)

 

 

 

*인덱스의 구조

1. 컬럼값 + rowid

2. 컬럼값이 ascending 하게 정렬이 되어있다.

 

튜닝전: select ename, sal

            from emp

           where emp

           order by sal asc; <<--성능을 떨군다.

 

 

튜닝후: create index emp_sal

           on emp(sal);

 

           select ename, sal

           from emp

          where sal >= 0;

 

 

 

문제424.

아래의 SQL 을 튜닝하시오.

 

튜닝전: select ename, sal

           from emp

            order by sal desc;

 

튜닝후: create index emp_sal

          on emp(sal);

 

select /*+ index_desc(emp emp_sal) */

        ename, sal

 from emp

 where sal > 0;

 

힌트를 써준다!

*힌트(hint) ?

 오라클에게 어떠한 데이터를 보여달라고 하는 것은  select 문이고

 오라클에게 지금 수행하는 selcet 문을

어떻게 실행해달라고 하는 것이 힌트이다.

(알아서 하지 않게, 실행하는 방법까지 알려주는 거임.)

(예를 들면

 1. 카페라떼 주세요. ->SQL

 2. 카페라떼 주는데, 원두는 하우스 블랜드로 갈아주시고 우유는 120도로 데워주시고 시럽을 2번 반 넣어주세요 ---------> HINT

 

select /*+ 힌트 */ 컬럼명, ...

만약 + 를 빼면 주석처리가 된다(실행안됨)

+를 넣어줘야 힌트가 된다.

 

 

select /*+ index_desc(테이블명 컬럼명) */

        ename, sal

 from emp

 where sal > 0;

 

**index에서 슬래쉬 안의 컬럼명은

 인덱스에서 정한 컬럼명: emp_sal

 

 

*힌트의 종류

1. index_desc ---> 인덱스를 descending (역순) 으로 읽으시오

2. index_asc ----> 인덱스를 ascending (정순)으로 읽으시오

 

*emp_sal 의 인덱스의 모습(컬럼값+rowid)

select sal, rowid

 from emp

 where sal > 0;

 

  컬럼값 +  ROWID    

 

 

 

문제425.

이름과 월급을 출력하는 아래의 SQL 을 튜닝하시오

튜닝전: select ename, sal

             from emp

           order by ename desc;

 

튜닝후:  create index emp_ename

                on emp(ename);

 

select /*+  index_desc (emp emp_ename)  */

                        ename, sal

              from emp

              where ename >' ';

            

 

 

문제426.

아래의 sQL 을 튜닝하시오!

(인덱스 알아서 걸고 하시오)

튜닝전: select ename, job

           from emp

           order by job asc;

 

 

create index emp_job

 on emp(job);

 

select /*+ index asc (emp emp_job) */

       ename, job

         from emp

        where job > ' ';

 

 

*인덱스의 구조 (책의 목차와 같다)

1. 컬럼값 + rowid

   (소제목)  (페이지 번호)

 

2. 컬럼값이 ascending 하게 정렬이 되어 있다.

 

 

 

문제427.

이름이 SCOTT 인 사원의 이름,월급을

인덱스를 통해서 테이블을 조회하는

과정을 기술하시오

 

select ename, sal

from emp

 where ename = 'SCOTT';

   

     인덱스 -------------------------->테이블

  (emp_sal)                               (emp)

 

select ename, rowid

 from emp

 where ename  >= ' ';

 

 

 

화면 캡처: 2018-10-31 오전 10:22

인덱스는 rowid 를 순서대로 안 훑어도

목차보고 찾듯이

바로바로 꺼내온다.

 

 

 

인덱스 없이

select rowid, ename

 from emp;

 

인덱스 없이 출력한

rowid 를 보면

ABCD....부터 차례대로 훑는다.

즉 페이지1 부터 일일이 훑는다는 것이다.

 

왜 빨라지는가에 대한

원리를 이해 해야 한다.

 

 

문제428.

아래의 쿼리문이 어떻게 인덱스를 통해서

테이블의 데이터를 조회하는지

기술하시오 !

 

select ename, sal

 from emp

 where sal = 3000;

 

 

emp_sal 인덱스 ------------------->emp 테이블

1.                                                           2.

select sal, rowid                                 select rowid, e.*

  from emp                                           from emp e;

where sal > 0;

 

 

 

1.

 

 

2.

 

select ename, sal

 from emp

 where sal = 3000;

 

이 쿼리문의

내면수행과정을 뜯어보면

index 걸어줬을 땐 바로 3000을 찾아가고,

index 안 걸어줬을 땐 rowid 순차적으로 검색해서              

                                        찾아온다.

F7 누르면 순서도가 나오는데

아래서부터 실행된다

 

인덱스 레인지 스캔

-> 인덱스 로우아이디로 접근한다

->그리고 출력

 

 

문제429.

아래의 sql 의 블럭의 갯수를 비교하시오

(실행계획과 블럭의 갯수를 확인하시오)

 

튜닝전:

select /*+ full(emp) */ ename, sal

 from emp

 where ename = 'SCOTT';

 

※설명:  full 힌트는 emp 를 full scan 해라

 

 

튜닝후:

select /*+ index(emp emp_ename) */

       ename, sal

 from emp

 where ename = 'SCOTT';

 

※설명: emp 테이블의 emp_ename 인덱스를

         이용해서 검색해라

 

블럭 수: 튜닝전 4건/ 튜닝후 3건

 

 

선생님 정리:

 

문제429. 아래의 SQL 의 블럭의 갯수를 비교하시오 !

         (실행계획과 블럭의 갯수를 확인 하시오 )

 

튜닝전 :  select  /*+ full(emp) */  ename, sal

             from  emp

             where  ename ='SCOTT';

 

※ 설명: full 힌트는 emp 를 full table scan 해라 ~

---------------------------------------------

| Id  | Operation         | Name | Rows  | By

---------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |

|*  1 |  TABLE ACCESS FULL| EMP  |     1 |

---------------------------------------------

 

   0  db block gets

   4  consistent gets

   0  physical reads

 

튜닝후 :  select  /*+  index(emp  emp_ename) */ ename,sal

            from  emp

            where  ename='SCOTT';

 

※ 설명 :  emp 테이블의 emp_ename 인덱스를 이용해서 검색해라 ~

---------------------------------------------

| Id  | Operation                   | Name

---------------------------------------------

|   0 | SELECT STATEMENT            |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP

|*  2 |   INDEX RANGE SCAN          | EMP_ENA

---------------------------------------------

  0  db block gets

  3  consistent gets

  0  physical reads

 

 

 

문제430.

아래의 SQL 을 튜닝하시오

(튜닝전과 튜닝후의 실행계획을 각각 보시오)

 

튜닝전:

select ename, sal

 from emp

 where sal * 12 = 36000;

              ↑

      인덱스 컬럼

 

 

 

 

튜닝후:

select  ename, sal

 from emp

 where sal = 36000/12;

 

*좌변 가공 안 하는게 좋다.

index 발동 조건:

  where 절을 꼭 쓰고

  where 절의 좌변을 가공하지 않는다.

 

 

 

문제431.

아래 sql 을 튜닝하시오

(F7 확인)

 

튜닝전:

select ename, job, sal

from emp

 where substr(job, 1,5) = 'SALES';

 

튜닝후:

select ename, job, sal

from emp

where job like 'SALES%';

 

'%SALES%' 로 하면 FULL SCAN 탐.

결국 전체 잡을 조회하는 거랑 같으니까.

 

 

 

문제432.

아래의 sQL 을 튜닝하시오

 

튜닝전:

select ename, sal, hiredate

 from emp

 where to_char(hiredate, 'rr/mm/dd') = '81/11/17';

 

 

 

튜닝후:

 select ename, sal, hiredate

  from emp

   where hiredate = to_date('81/11/17', 'rr/mm/dd');

 

 

 

 

 

문제.433

아래의 sql 을 튜닝하시오

 

튜닝전:

select ename, sal, job

 from emp

 where ename||sal ='SCOTT3000';

 

 

튜닝후:

select ename, sal, job

 from emp

 where ename ='SCOTT'   and    sal=3000;

 

 

둘 다 index 걸은건데

오라클이 ename 에 index 탔다.

더 성능좋은 인덱스를 타고 작업한다

좋은성능기준은 유니크함이다.

 

만약

월급에 인덱스 타게 하고 싶으면

 

select /*+ index (emp emp_sal) */

      ename, sal, job

 from emp

 where ename ='SCOTT'   and    sal=3000;

 

실행계획 지정해줘서 월급으로 index 탄 걸 알 수 있다.

 

 

문제434.

아래의 sql 을 튜닝하시오

 

튜닝전:

select ename, sal

 from emp

 where sal like '30%' ;

 

**핵심은

where 절에서 엮을 때

숫자&숫자

문자& 문자

날짜&날짜

 

좌변 가공 안했는데

왜 인덱스 안 타냐

이유는

 

 

문자보다

숫자가 우선순위가 높기 때문에

(우선순위:   숫자 >  문자)

문자 - > 숫자로 변경 해줘야 하는데

% 를 숫자로 못 바꾸니까

오라클이

숫자를 문자로 변경 해준거다.

 

쿼리문에서 like 를 자주 사용 할 것 같은 컬럼은

처음부터 문자로 만들었어야 한다.

(모델링 작업: 데이터베이스 모델러들의 일)

 

근데 인설트값 다 넣고

숫자를 문자로 다시 바꾸는건

건물 다 짓고 설계도 작성부터 다시 하라는거랑 똑같다.

 

위의 경우 해결방법은

함수기반 인덱스를 생성하는 것이다.

 

create index emp_sal_func

 on emp( to_char(sal) );

 

답

 select ename, sal

 from emp

 where to_char(sal) like '30%' ;

 

 

 

문제435.

아래의 데이터를 입력하고

아래의 데이터를 조회하는 쿼리를

튜닝된 sql 로 작성하시오

 

insert into emp(empno, ename, sal)

 values(2912, '  Biff  ', 3000);

 

commit ;

 

공백들어간 이름이 Biff 라는 사원을 조회해보시옹.

 튜닝전:

select empno, ename, sal

from emp

where trim(ename) = 'Biff';

이렇게 하면 좌변 가공

 

 

 

인덱스생성 할 때 !!!!!!!!!!!!

 

create index emp_ename_func

 on emp(trim(ename) );

이거 안먹히는데

>>cmd 창에서 작업중이여서(커밋 안해서)

*******주의하시오

 

 

 

select empno, ename, sal

from emp

where trim(ename) = 'Biff';

함수인덱스 먹이고 이렇게 ㅇㅇ

 

 

현업에서 자료 입력 할 때

아예 저런 실수가 없게 해주는게 짱이다.

테이블을 고급스럽게....

 

 

문제436.(점심시간 문제)

아래의 SQL을 튜닝하시오!

 

튜닝전:

select ename, hiredate

 from emp

 order by hiredate desc;

 

 

 

create index emp_hiredate_func

on emp( to_char(hiredate, 'rr/mm/dd') );

 

 

튜닝후:

select /*+ index desc (emp emp_hiredate_func) */

ename, hiredate

 from emp

  where hiredate > to_date('1900/01/01', 'RRRR/MM/DD');

 

 

 

 

틀린답

select /*+ index desc (emp emp_hiredate_func) */

ename, hiredate

 from emp

  where hiredate is not null;

왜??????????

1. hiredate 는 날짜 타입이고

is not null 은 문자타입이라서 full scan 이다

 >> index 에 to_char(hiredate) 걸고

where to_char(hiredate) 를 같이 걸어준다.

**그래도 안나온다

 

 

※order by 절을 사용 안하고

인덱스를 통해서 정렬 될 결과를 보려면

반드시 where 절에 해당 인덱스 컬럼이

존재해야 한다.

 

 

 

문제437.

아래의 sql 을 튜닝하시오

 

튜닝전: select ename, sal, job

 from emp

 where job = 'SALESMAN'

 order by sal desc;

 

튜닝후:

select /*+ index desc (emp emp_sal) */

ename, sal, job

 from emp

 where job = 'SALESMAN'

 and sal >= 0;

 

 

 

문제438.

아래의 sql 을 튜닝하시오

(그룹함수 사용하지 말고 결과를 출력하시오)

 

튜닝전:

select max(sal)

 from emp;

 

 

튜닝후:

select /*+ index_desc (emp emp_sal) */

   sal

 from emp

 where sal > -1

 and rownum = 1;

 

 

 

select /*+ index_desc (emp emp_sal) */

        sal

 from emp

  where  sal>0    and   rownum <= 2;

이건 안된다

왜냐하면

rownum 특성상

1부터 연속 된 숫자를 뽑기 때문이다.

 

 

 

 

문제439.

아래의 sql 을 튜닝하시오

 

튜닝전:

select max(Sal)

 from emp

 where job = 'SALESMAN';

 

튜닝후:

select /*+ index_desc (emp emp_sal) */

        sal

 from emp

 where

  job= 'SALESMAN'

and

sal > -1

 and

       rownum=1;

     

튜닝 전 후가 답이 다름

언더바 안쓰셔서....

 

 

 

 

문제440.

아래의 sql 을 튜닝하시오

튜닝전:

select ename, hiredate

 from emp

 where hiredate = (select max(hiredate)

                                   from emp );

 

 

튜닝후:

 

select /*+ index_desc (emp emp_hiredate) */

         ename, hiredate

from emp

 where hiredate  < to_date('9999/12/12', 'rrrr/mm/dd')

and

rownum = 1;

 

 

문제441.

아래의 sql 을 튜닝하시오

 

튜닝전:

select ename, sal

 from emp

 where sal = (select max(Sal)

                  from emp);

 

튜닝후:

select /*+ index_desc (emp emp_sal) */

        ename, sal

 from emp

 where sal > 0

 and

 rownum = 1;

 

 

 

문제442.

우리반 테이블에서

이름, 나이, 전공을 출력하는데

order by 절 이용하지 않고

index 를 통해서 정렬되게

인덱스를 걸고 sql 을 작성하시오

(나이 높은순서로)

 

create index emp2_age

 on emp2(age);

 

select /*+ index_desc (emp2 emp2_age) */

       ename, age, major

 from emp2

 where

 age > 0;

 

 

 

문제443.

scott 이 가지고 있는 인덱스 리스트를 조회하시오!

select index_name

 from user_indexes;

 

 

 

문제444.

사원테이블에 월급에 걸린 sal 인덱스를

삭제하시오

drop index emp_sal;

 

 

■INDEX 삭제

문제445.

scott 이 가지고 있는 모든 인덱스를

다 삭제하시오

 

select 'drop index '||INDEX_NAME||';'

from user_indexes;

 

drop index EMP_ENAME;

drop index EMP_JOB;

drop index EMP_HIRE_FUNC;

drop index EMP_SAL_FUNC;

drop index EMP_ENAME_FUNC;

drop index EMP_HIREDATE;

drop index SYS_IL0000020432C00001$$;

drop index EMP2_AGE;

drop index DEPT_DEPTNO_PK;

 

이 중 sys 랑

제약 걸은 인덱스는

지울 수 없다.