다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Presentation on theme: "다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL"— Presentation transcript:

1 다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL
서진수 저

2 9장 인덱스를 배웁니다 1

3 9. 인덱스를 배웁니다 1. 인덱스란 무엇인가? 2

4 9. 인덱스를 배웁니다 - ROWID ( 주소 ) 조회하기 3 SCOTT>SELECT ROWID, empno, ename
2 FROM emp 3 WHERE empno=7902 ; ROWID EMPNO ENAME AAASHOAAEAAAACXAAM FORD 3

5 9. 인덱스를 배웁니다 AAASHOAAEAAAACXAAM - ROWID ( 주소 ) 란 무엇인가? 4 ROWID 입니다
데이터 오브젝트번호 파일 번호 BLOCK 번호 ROW 번호 4

6 9. 인덱스를 배웁니다 2. 인덱스의 생성 원리 대상 데이터 FULL SCAN SORT Block 에 기록 5

7 3. 인덱스 구조와 작동 원리 (B-TREE 인덱스 기준입니다)
9. 인덱스를 배웁니다 3. 인덱스 구조와 작동 원리 (B-TREE 인덱스 기준입니다) 6

8 9. 인덱스를 배웁니다 7

9 해당 블록을 복사하여 DB CACHE 로 로딩함
9. 인덱스를 배웁니다 SQL> SELECT * 2 FROM 사원 3 WHERE 이름=’홍길동’ ; 3번 줄의 where 조건의 칼럼으로 인덱스 검색 해당 블록을 복사하여 DB CACHE 로 로딩함 해당 데이터의 ROWID 를 찾음 8

10 9. 인덱스를 배웁니다 4. 인덱스의 종류 1) B-TREE 인덱스 9

11 UNIQUE Index 는 UNIQUE 제약조건과 동일합니다. 즉 중복되는 값을 입력할 수 없습니다.
9. 인덱스를 배웁니다 (1) UNIQUE INDEX SQL>CREATE UNIQUE INDEX 인덱스명 2 ON 테이블이름(컬럼명1 ASC | DESC , 컬럼명,……..); SCOTT>CREATE UNIQUE INDEX idx_dept2_dname 2 ON dept2(dname) ; SCOTT>INSERT INTO dept2 2 VALUES(9100,'임시매장',1006,'서울지사'); 1 row created. 2 VALUES(9101,'임시매장',1006,'부산지사'); INSERT INTO dept2 * ERROR at line 1: ORA-00001: UNIQUE constraint (SCOTT.IDX_DEPT2_DNAME) violated UNIQUE Index 는 UNIQUE 제약조건과 동일합니다. 즉 중복되는 값을 입력할 수 없습니다. 10

12 9. 인덱스를 배웁니다 (2) Non UNIQUE INDEX 11 SCOTT>CREATE INDEX 인덱스명
2 ON 테이블명(컬럼명1 ASC | DESC , 컬럼명2 , …….) ; SCOTT>CREATE INDEX idx_prof_position 2 ON professor(position DESC ) ; 11

13 9. 인덱스를 배웁니다 (3) Function Based INDEX(FBI – 함수기반 인덱스)
SCOTT>CREATE INDEX idx_prof_pay_fbi 2 ON professor(pay+100) ; - Index Suppressing Error SQL 을 작성 할 때 Where 절 등에 인덱스 검색 조건을 잘 못 주어서 인덱스를 사용 할 수 없는 경우를 뜻합니다. 인덱스가 만들어져 있는 칼럼에는 일반적으로는 산술 연산이나 함수 등을 사용하여 변형하면 안됩니다. 12

14 9. 인덱스를 배웁니다 (4) DESCENDING INDEX 큰 값을 먼저 조회 해야 할 경우에 주로 사용함.
주로 날짜의 경우 최근 날짜를 먼저 조회하는 경우가 많기 때문에 날짜 컬럼에 인덱스를 만들 경우에 자주 사용됨. SCOTT>CREATE INDEX idx_prof_pay 2 ON professor(pay DESC ); 13

15 9. 인덱스를 배웁니다 (5) 결합 인덱스 ( Composite INDEX ) 두 개 이상의 칼럼을 결합하여 생성하는 인덱스.
주로 두 개 이상의 칼럼이 AND 조건으로 검색 될 경우 많이 사용됨. SQL> SELECT 이름, 성별 2 FROM 사원 3 WHERE 성별 = ‘여자’ 4 AND 이름 = ‘유관순’ ; * 결합 인덱스 생성 구문 예 : SQL> CREATE INDEX idx_사원_성별_이름 2 ON 사원(성별,이름) ; 14

16 9. 인덱스를 배웁니다 - 칼럼 순서의 중요성 15

17 9. 인덱스를 배웁니다 2) BITMAP INDEX 16
SCOTT> CREATE BITMAP INDEX idx_사원_성별_bit 2 ON 사원(성별) ; 16

18 9. 인덱스를 배웁니다 - 성별 칼럼으로 만들어진 Bitmap Bitmap 은 칼럼에서 데이터의 종류만큼
신규 데이터가 입력될 경우에 모든 Map 이 Update 되어야 합니다. - 지역 칼럼으로 만들어진 Bitmap 17

19 9. 인덱스를 배웁니다 5. 인덱스의 주의사항 1) DML에 취약하다 (1) Insert - Index Split 현상
(2) Delete - 인덱스 내용이 삭제가 안됨 (3) Update – Delete + Insert 작업이 발생함 2) 타 SQL 실행에 악영향을 줄 수 있습니다. 18

20 9. 인덱스를 배웁니다 6. 인덱스 관리 방법 1) 인덱스 조회하기 19
SCOTT>SELECT table_name, index_name 2 FROM user_indexes 3 WHERE table_name='DEPT2'; TABLE_NAME INDEX_NAME DEPT IDX_DEPT2_DNAME DEPT SYS_C 19

21 9. 인덱스를 배웁니다 2) 사용 여부 모니터링 하기 Monitoring 기간동안 사용되었는지 확인 20
SCOTT>ALTER INDEX idx_dept2_dname MONITORING USAGE ; SCOTT>ALTER INDEX idx_dept2_dname NOMONITORING USAGE ; SCOTT>SELECT index_name, used 2 FROM v$object_usage 3 WHERE index_name='IDX_DEPT2_DNAME'; INDEX_NAME USED IDX_DEPT2_DNAME NO Monitoring 기간동안 사용되었는지 확인 20

22 9. 인덱스를 배웁니다 3) INDEX Rebuild 하기 Index 는 생성 후 오라클이 자동으로 관리를 합니다.
그러나 앞에서 살펴본 바와 같이 데이터가 삭제되거나 update 될 경우 인덱스 내부의 상태가 흐트러지는 현상이 발생합니다. 이럴 경우 인덱스를 Rebuild 해 주면 성능이 개선됩니다. 단, 흐트러져있는 정도에 따라 Rebuild 보다는 Recreate 가 더 좋을 경우도 있을 수 있습니다. 실습은 교재 321 – 323 페이지를 참고하세요 21

23 9. 인덱스를 배웁니다 7. 인덱스 활용 예제 1) 인덱스를 활용하여 정렬한 효과를 내는 방법
실습은 교재 325 – 326 페이지를 참고하세요 22

24 9. 인덱스를 배웁니다 7. 인덱스 활용 예제 2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)을 구하는 방법 23

25 9. 인덱스를 배웁니다 7. 인덱스 활용 예제 2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)을 구하는 방법 24

26 9. 인덱스를 배웁니다 7. 인덱스 활용 예제 2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)을 구하는 방법 25

27 Index 사용 인덱스를 통해서 정렬된 데이터를 보고자 할때 where절에 주는 조건 3가지 (order by절을 안써도 인덱스를 통해서 정렬된 결과를 볼수있음) 1. 문자 > ' ' 2. 숫자 > 0 3. 날짜 < to_date('9999/12/31','rrrr/mm/dd')

28 Index 사용 1.index : 인덱스를 통해서 테이블의 데이터를 찾고자 할때
2. index_asc : 인덱스의 컬럼의 데이터를 ascending하게 읽겠다 3. index_desc:인덱스의 컬럼의 데이터를 descending하게 읽겠다 4. index_ss : index skip scan을 하겠다. 결합컬럼 인덱스의 첫번째 컬럼이 where절에 존재하지 않을 때 사용

29 Index 사용 사원테이블의 sal에 인덱스를 생성하고 월급이 3000인 사원의 이름,월급을 조회하고 인덱스를 통해서 데이터를 찾는지 확인하라 create index emp_sal on emp(sal); select /*+ index(emp emp_sal) */ ename,sal from emp where sal=3000;

30 Index 사용 이름,월급을 출력하는데 이름을 ABCD순서대로 출력(order by 쓰지말고)
select /*+ index_asc(emp emp_ename) */ ename, sal from emp where ename>' ‘;

31 9. 인덱스를 배웁니다 7. 인덱스 활용 예제 2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)을 구하는 방법 26
SCOTT>SELECT /*+ index_desc(s idx_사원_name) */ name 2 FROM 사원 s 3 WHERE name >='0' 4 AND rownum=1 ; NAME 홍길동 26

32 FIRST_ROW (MAX/MIN) 방법
9. 인덱스를 배웁니다 7. 인덱스 활용 예제 2) 인덱스를 활용하여 최소값(MIN) / 최대값(MAX)을 구하는 방법 SCOTT>select /*+ index_desc (s idx_사원_name) */ max(name) 2 from 사원 s 3 where name > '0' ; MAX(name) 홍길동 FIRST_ROW (MAX/MIN) 방법 27

33 9. 인덱스를 배웁니다 8. Invisible Index (인비저블 인덱스) - 11g New Feature
- 인덱스는 사용하지 않을 경우 삭제를 해야 성능향상에 도움이 됨. 사용 여부를 알기가 어렵다는 단점이 있음. 인비져블 인덱스는 인덱스가 삭제 된 상태처럼 만들어서 테스트를 할 수 있음 SCOTT>CREATE INDEX idx_emp_ename ON emp(ename) ; Index created. SCOTT>SELECT table_name,index_name,visibility 2 FROM user_indexes 3 WHERE table_name = 'EMP' ; TABLE_NAME INDEX_NAME VISIBILIT EMP IDX_EMP_ENAME VISIBLE EMP PK_EMP VISIBLE 28

34 9. 인덱스를 배웁니다 SCOTT>ALTER INDEX IDX_EMP_ENAME INVISIBLE ;
Index altered. SCOTT>SELECT table_name, index_name, visibility 2 FROM user_indexes 3 WHERE table_name = 'EMP' ; TABLE_NAME INDEX_NAME VISIBILITY EMP IDX_EMP_ENAME INVISIBLE EMP PK_EMP VISIBLE 29

35 9. 인덱스를 배웁니다 1. 다시 상태를 VISIBLE 로 변경하기
SCOTT>ALTER INDEX idx_emp_ename VISIBLE ; Index altered. SCOTT>SELECT table_name, index_name, visibility 2 FROM user_indexes 3 WHERE table_name = 'EMP' ; TABLE_NAME INDEX_NAME VISIBILIT EMP IDX_EMP_ENAME VISIBLE <- 변경되었습니다. EMP PK_EMP VISIBLE 30

36 9. 인덱스를 배웁니다 2. SQL 힌트 구문에서 해당 인덱스를 사용하게 하기
SCOTT>SELECT /*+ index (emp idx_emp_ename) */ ename 2 FROM emp 3 WHERE ename >'0'; 31

37 9. 인덱스를 배웁니다 DROP INDEX 인덱스명