INDEX

 

요약

INDEX란 자주 검색되는 컬럼의 이름과 ROWID를 묶어서 저장해 놓은 객체

생성 create index index이름 on table명(column명);

특징 분포가 고른 column에만 사용하도록 한다.

DB의 계산 추적 내용 보기

Index란?

자주 검색되는 컬럼의 이름과 row id를 묶어서 저장해 놓은 객체이다. 알아서 접근해 참고하여 정보를 출력해 준다.

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

*DB가 할 수있는 가장 빠르게 DATA를 찾는 방법

ROW ID로 찾기

"ROWID SEARCH"라고 한다.

ex)

SQL> SELECT ROWNUM,ROWID,EMPNO

2 FROM EMP;

ROWNUM ROWID EMPNO

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

1 AAAR3sAAEAAAACXAAA 7369

2 AAAR3sAAEAAAACXAAB 7499

3 AAAR3sAAEAAAACXAAC 7521

4 AAAR3sAAEAAAACXAAD 7566

5 AAAR3sAAEAAAACXAAE 7654

6 AAAR3sAAEAAAACXAAF 7698

7 AAAR3sAAEAAAACXAAH 7788 <-7788번의 ROWID

8 AAAR3sAAEAAAACXAAJ 7844

9 AAAR3sAAEAAAACXAAK 7876

10 AAAR3sAAEAAAACXAAL 7900

11 AAAR3sAAEAAAACXAAM 7902

11 개의 행이 선택되었습니다.

SQL> SELECT EMPNO,ENAME,SAL

2 FROM EMP

3 WHERE ROWID='AAAR3sAAEAAAACXAAH'; <-ROW ID

EMPNO ENAME SAL

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

7788 SCOTT 3000

*가장 느린 속도로 찾는것은 기존의

SELECT EMPNO,ENAME,SAL

FROM EMP; 즉 전부보여달라는 말이다.

"FULL TABLE SCAN" 방식이라고 한다.

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

Index 생성

create index index이름 on table명(column명);

create index idx_emp_ename

on emp (ename);

SELECT EMPNO,ENAME,SAL

FROM EMP

WHERE ENAME='SCOTT';

문장에 INDEX가 있던 없던 DB가 생성된 INDEX를 찾아 알아서 ROWID를 참고해 결과를 출력한다.

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

책과 DB INDEX 비교

책 DB

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

본문 테이블

빠른검색 INDEX INDEX

키워드 자주검색되는 컬럼의 VALUE

페이지 번호 ROWID

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

Index의 특징

테이블과는 별도의 저장장소를 이용한다.

db가 느려질 경우 튜닝을 하게 되면 index로 해결하려고 하는데

기존에 가지고 있던 데이터를 변화시킬 필요가 없기 때문이다.

모든 컬럼에 index를 만들면 select문의 속도가 빨라지긴한다.

대신에 dml의 속도는 떨어진다.

새롭게 데이터를 추가하면 index에도 따로 추가해야 하기 때문이다.

그래서 필요한 경우에만 사용해야 한다.

필요한경우

자주 검색되는 컬럼 EX)WHERE절 조건

JOIN 조건으로 사용되는 컬럼

PK(NULL이 아니다.중복이 아니다.), UK 제약이 걸려있는 컬럼 <-NULL과 중복이 안되게 되는 것은 UPDATE 용이하게 하기 위함.

분포가 고른 컬럼 그래서 자동으로 INDEX가 생성되어 있다.

분포가 고른 컬럼?

EX) 부서가 10,20,30,40이 있는데 10에 분포가 80%가 몰려잇다면 INDEX 안만드는게 낫다.

25%,25%,25%,25%등으로 고른경우에 사용

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

(고급 과정)

DB가 계산하는 과정을 추적해 어느부분에서 비효율적인지 알아내 바꿀수있도록 한다.

conn sys/oracle as sysdba <-sysdba는 집주인 권한

@= save된파일 로드할때 사용

@C:\app\JHTA\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql

SQL>

SQL> drop role plustrace;

drop role plustrace

*

1행에 오류:

ORA-01919: 롤 'PLUSTRACE'(이)가 존재하지 않습니다

SQL> create role plustrace;

롤이 생성되었습니다. <-각종 명령어

SQL>

SQL> grant select on v_$sesstat to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$statname to plustrace;

권한이 부여되었습니다.

SQL> grant select on v_$mystat to plustrace;

권한이 부여되었습니다.

SQL> grant plustrace to dba with admin option;

권한이 부여되었습니다.

SQL>

SQL> set echo off

SQL>grant plustrace to scott; <-scott한테 권한 부여

SQL>set autotrace on <-실행되는 명령어를 추적해 원리를 보여준다.

Execution Plan

---------------------------------------------------------- <-db가 정보 찾는 계획

Plan hash value: 3956160932

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 11 | 418 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 11 | 418 | 3 (0)| 00:00:01 | <-위에 써놓은 table full search방법

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

용도 > 원리를 보기 때문에 왜 느려지는지 원인을 알수가 있어 보완이 가능하다.(고급과정)

ex)

IDX_EMP_ENAME |

IDX_EMP_ENAME <-index 살펴본다

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

INDEX 이름

INDEX의 이름은 제약의 이름과 같은 이름을 가진다.

SELECT INDEX_NAME,INDEX_TYPE,TABLE_NAME,UNIQUENESS

FROM USER_INDEXES;

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES

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

EMP1_EMPNO_PK NORMAL EMP2 UNIQUE

PK_EMP NORMAL EMP UNIQUE

IDX_EMP_ENAME NORMAL EMP NONUNIQUE

SYS_C0011263 NORMAL DEPT2 UNIQUE <- INDEX이름 PK와 UK는 자동으로 만들어진다.

SYS_C0011264 NORMAL DEPT2 UNIQUE

SYS_C0011262 NORMAL DEPT1 UNIQUE

PK_DEPT NORMAL DEPT UNIQUE

PK와 UK는 왜 INDEX가 자동으로 만들어질까?

내부적으로도 사용하기 때문이다.

중복의 여부를 판단하기 위해서 .

굉장히 자주 검색된다.

*자동으로 생성된 INDEX는 지워질까?

안지워진다. 지우려면 제약조건을 지워야 한다.

DROP INDEX PK_DEPT

*

1행에 오류:

ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다

*index의 종류

12가지가 있다.

*index가 저장되는 구조?

B*Tree 구조로 저장된다.

BALANCE한 TREE구조를 가진다는 말.

ex)

인덱스를 생성

CREATE INDEX 이름

사용하는 지 확인

SET AUTOTRACE ON <-명령 추적기

SELECT ENAME

FROM EMP

WHERE JOB='SALESMAN';