다양한예제로쉽게배우는 오라클 SQL 과 PL/SQL 서진수저
9 장인덱스를배웁니다 1
1. 인덱스란무엇인가? 2
- ROWID ( 주소 ) 조회하기 SCOTT>SELECT ROWID, empno, ename 2 FROM emp 3 WHERE empno=7902 ; ROWID EMPNO ENAME --------------------------------- ---------- ------------- AAASHOAAEAAAACXAAM 7902 FORD 3
- ROWID ( 주소 ) 란무엇인가? AAASHOAAEAAAACXAAM ROWID 입니다 AAASHO AAE AAAACX AAM 데이터오브젝트번호 파일번호 BLOCK 번호 ROW 번호 4
2. 인덱스의생성원리 대상데이터 FULL SCAN SORT Block 에기록 5
3. 인덱스구조와작동원리 (B-TREE 인덱스기준입니다 ) 6
7
SQL> SELECT * 2 FROM 사원 3 WHERE 이름 = 홍길동 ; 3 번줄의 where 조건의칼럼으로인덱스검색 해당데이터의 ROWID 를찾음 해당블록을복사하여 DB CACHE 로로딩함 8
4. 인덱스의종류 1) B-TREE 인덱스 9
(1) UNIQUE INDEX SQL>CREATE UNIQUE INDEX 인덱스명 2 ON 테이블이름 ( 컬럼명1 ASC DESC, 컬럼명,..); SCOTT>CREATE UNIQUE 2 ON dept2(dname) ; INDEX idx_dept2_dname SCOTT>INSERT INTO dept2 2 VALUES(9100,' 임시매장 ',1006,' 서울지사 '); 1 row created. SCOTT>INSERT INTO dept2 2 VALUES(9101,' 임시매장 ',1006,' 부산지사 '); INSERT INTO dept2 * ERROR at line 1: ORA-00001: UNIQUE constraint (SCOTT.IDX_DEPT2_DNAME) violated UNIQUE Index 는 UNIQUE 제약조건과동일합니다. 즉중복되는값을입력할수없습니다. 10
(2) Non UNIQUE INDEX SCOTT>CREATE INDEX 인덱스명 2 ON 테이블명 ( 컬럼명 1 ASC DESC, 컬럼명 2,.) ; SCOTT>CREATE INDEX idx_prof_position 2 ON professor(position DESC ) ; 11
(3) Function Based INDEX(FBI 함수기반인덱스 ) SCOTT>CREATE INDEX idx_prof_pay_fbi 2 ON professor(pay+100) ; - Index Suppressing Error SQL 을작성할때 Where 절등에인덱스검색조건을잘못주어서인덱스를사용할수없는경우를뜻합니다. 인덱스가만들어져있는칼럼에는일반적으로는산술연산이나함수등을사용하여변형하면안됩니다. 12
(4) DESCENDING INDEX 큰값을먼저조회해야할경우에주로사용함. 주로날짜의경우최근날짜를먼저조회하는경우가많기때문에날짜컬럼에인덱스를만들경우에자주사용됨. SCOTT>CREATE INDEX idx_prof_pay 2 ON professor(pay DESC ); 13
(5) 결합인덱스 ( Composite INDEX ) 두개이상의칼럼을결합하여생성하는인덱스. 주로두개이상의칼럼이 AND 조건으로검색될경우많이사용됨. SQL> SELECT 이름, 성별 2 FROM 사원 3 WHERE 성별 = 여자 4 AND 이름 = 유관순 ; * 결합인덱스생성구문예 : SQL> CREATE INDEX idx_ 사원 _ 성별 _ 이름 2 ON 사원 ( 성별, 이름 ) ; 14
- 칼럼순서의중요성 15
2) BITMAP INDEX SCOTT> CREATE BITMAP INDEX idx_ 사원 _ 성별 _bit 2 ON 사원 ( 성별 ) ; 16
- 성별칼럼으로만들어진 Bitmap - 지역칼럼으로만들어진 Bitmap Bitmap 은칼럼에서데이터의종류만큼 Map 이생성됩니다. 신규데이터가입력될경우에모든 Map 이 Update 되어야합니다. 17
5. 인덱스의주의사항 1) DML에취약하다 (1) Insert - Index Split 현상 (2) Delete - 인덱스내용이삭제가안됨 (3) Update Delete + Insert 작업이발생함 2) 타 SQL 실행에악영향을줄수있습니다. 18
6. 인덱스관리방법 1) 인덱스조회하기 SCOTT>SELECT table_name, index_name 2 FROM user_indexes 3 WHERE table_name='dept2'; TABLE_NAME INDEX_NAME -------------------------- ----------------------------- DEPT2 IDX_DEPT2_DNAME DEPT2 SYS_C0014275 19
2) 사용여부모니터링하기 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 20
3) INDEX Rebuild 하기 Index 는생성후오라클이자동으로관리를합니다. 그러나앞에서살펴본바와같이데이터가삭제되거나 update 될경우인덱스내부의상태가흐트러지는현상이발생합니다. 이럴경우인덱스를 Rebuild 해주면성능이개선됩니다. 단, 흐트러져있는정도에따라 Rebuild 보다는 Recreate 가더좋을경우도있을수있습니다. 실습은교재 321 323 페이지를참고하세요 21
7. 인덱스활용예제 1) 인덱스를활용하여정렬한효과를내는방법 실습은교재 325 326 페이지를참고하세요 22
7. 인덱스활용예제 2) 인덱스를활용하여최소값 (MIN) / 최대값 (MAX) 을구하는방법 23
7. 인덱스활용예제 2) 인덱스를활용하여최소값 (MIN) / 최대값 (MAX) 을구하는방법 24
7. 인덱스활용예제 2) 인덱스를활용하여최소값 (MIN) / 최대값 (MAX) 을구하는방법 25
7. 인덱스활용예제 2) 인덱스를활용하여최소값 (MIN) / 최대값 (MAX) 을구하는방법 SCOTT>SELECT /*+ index_desc(s idx_ 사원 _name) */ name 2 FROM 사원 s 3 WHERE name >='0' 4 AND rownum=1 ; NAME ---------- 홍길동 26
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
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
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
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
2. SQL 힌트구문에서해당인덱스를사용하게하기 SCOTT>SELECT /*+ index (emp idx_emp_ename) */ ename 2 FROM emp 3 WHERE ename >'0'; 31