쌍용강북교육센터 국비 학원 Day 34일차 Oracle (Oracle INDEX, Plan)

 

쌍용 강북 교육센터 국비학원 Day 34일차

 

INDEX

index (색인)는 예를 들어 설명하면 두꺼운 책 뒤에 나오는 '찾아보기' 와 같은 기능을 하는 것이다.
'찾아보기' 의 특징은 정렬되어 있는 것인데 index에 저장된 데이터도 정렬되어 저장되어 있다는 것이 특징이다.
index는 where 절이 있는 select 명령문의 속도를 향상 시키기 위하여 사용한다.

 

 

INDEX 생성

 

1. where 절에서 자주 사용되어진 컬럼에 만들어야 한다.
2. 선택도 (Selectivity)가 높은 컬럼에 만들어야 한다.
    ※ 선택도란 - 선택도가 높을수록 고유하다는 뜻이다.     
3. 카디널리티 (Cardinality)가 높은 컬럼에 만들어야 한다.
    ※ 카디널리티의 사전적인 뜻은 집합원의 갯수를 뜻하는 것으로서, 상대적인 개념이다.
카디널리티가 높다라는 것은 중복도가 낮아 고유한 데이터일수록 카디널리티가 상대적으로 높다 라는 것이다.
카디널리티가 낮다라는 것은 중복도가 높아 중복된 데이터가 많을수록 카디널리티가 상대적으로 낮다 라는 것이다.

 

어떤 컬럼에 unique 한 index를 생성하면 그 컬럼에 들어오는 값은 중복된 값은 들어올 수 없으며 오로지 고유한 값만 들어오게 된다. 반대로 non-unique는 중복이 허용된다. unique 한 index는 non-unique 한 index 보다 검색속도가 조금 더 빠르다.

 

[문법]
create [unique] index 인덱스명
on 해당테이블명 (컬럼명 asc | dsce) ;
    

컬럼에 primary key 제약 또는 unique 제약을 주면 자동적으로 그 컬럼에는 unique 한 index가 생성되어진다. 생성된 인덱스명은 제약조건명이 된다. (또한 index의 삭제는 alter를 이용한 제약조건을 삭제해야 한다)

 

※ 또한 컬럼명에 함수를 넣어 함수기반 인덱스(Function based index) 를 생성할 수도 있다.

 

index 확인 방법

 

 

 

SQL Developer 에서 Plan(실행계획) 확인하는 방법

 

select 문이 실행될 때 인덱스를 사용하여 데이터를 얻어오는지 인덱스를 사용하지 않고 Table Full Scan 하여 얻어오는지 알아보는것을 SQL Plan (실행계획)이라고 한다.

Plan(실행계획) 과 Trace(자동추적) 가 있는데 이 둘의 차이점은 Plan은 SQL을 실행하기 전에 Oracle Optimizer (옵티마이저, 최적화기)가 SQL을 어떻게 실행할지를 미리 알려주는 것이고, Trace 는 SQL을 실행해보고, Oracle Optimizer가 SQL을 어떻게 실행했는지 그 결과를 알려주는 것이다.

그러므로 정확도로 말하자면 Trace가 Plan보다 훨씬 정확한 것이다. Plan은 말그대로 계획이라서 Oracle Optimizer가 실제 실행할때는 여러가지 이유로 다르게 실행할 수도 있기 때문이다. 그래서 Trace가 정확 하기는하나 시간이 오래 걸리는 SQL인 경우에는 한참 기다려야 하는 단점이 있기는 하다. ( COST(비용)의 값이 적을 수록 속도가 빠른 것이다)

 

Trace에서 LAST_CR_BUFFER_GETS 와 LAST_ELAPSED_TIME 컬럼이 나오는데 LAST_CR_BUFFER_GETS 는 SQL을 실행하면서 각 단계에서 읽어온 블록(Block) 갯수를 말하는 것이고, LAST_ELAPSED_TIME 은 경과시간 정보이다.
즉, 이 정보를 통해서 어느 구간에서 시간이 많이 걸렸는지를 확인할 수 있으므로, 이 부분의 값이 적게 나오도록 SQL 튜닝을 하게 된다.


DML (insert, update, delete)이 빈번하게 발생하는 테이블에 index가 생성되어 있으면 DML작업으로 인해 속도도 저하되고, Index Split(인덱스 쪼개짐) 이 발생하므로 index 가 많다고 해서 결코 좋은 것이 아니기에 테이블당 index 의 개수는 최소한의 개수로 만드는 것이 좋다.
    
※ Index Split 이란 Index 의 block(블럭) 들이 1개에서 2개로 나뉘어지는 현상을 말한다. 이러한 현상은 Index 는 정렬이 되어 저장되기 때문에 중간에 끼워들어가는 경우 발생한다.

 

※ Trace 를 하기 위해서는 SYS 또는 SYSTEM 으로 부터 권한을 부여 받은 후 재접속을 해야 한다.

grant select_catalog_role to 사용자명 ;
grant select any dictionary to 사용자명 ;

 

 

 

 

Rebuild

 

index 가 생성되어진 테이블에 delete 를 하면 테이블의 데이터는 삭제가 되어지지만 Index 자리에 데이터는 삭제되지 않고서 사용을 안한다는 표시만 하게 된다. 그래서 사용하지 않는 Index 정보로 인해서 index를 사용해서 select를 해올 때 index 검색속도가 떨어지게 된다. 이러한 경우 Index Rebuild 작업을 해주어야 검색속도가 빨라지게 된다. 
    
    
update 또한 Index 에는 '수정' 이라는 작업은 없고 index 를 delete 를 하고 새로이 insert 를 해준다. 그러므로 index 를 delete 할 때 발생하는 단점 및 index 를 insert 를 할 때 발생하는 Index Split가 발생하므로 update를 빈번하게 발생시켜 버리면 이러한 경우에도 Index Rebuild 작업을 해주어야 한다.

 

[문법]    
alter index 인덱스명 validate structure ;
    
INDEX 삭제하기
drop index 인덱스명 ;

   

 

 

INDEX 상태 (Balance) 확인

[문법]
analyze index 인덱스명 validate structure ; (분석하기)

select (del_lf_rows_len / lf_rows_len) * 100
from index_stats
where name =  ' 인덱스명 ' ;

 

※ 값이 0에 가까울수록 인덱스 상태가 좋은 것이다. (밸런스가 깨지지 않았다) 값을 보기전에 항상 analyze(분석) 를 먼저 실행하고 봐야한다.

 

 

Composite Index (복합 인덱스) 생성하기

 

2개이상의 컬럼으로 묶어진 인덱스를 말하는 것으로써 where 절에 2개의 컬럼이 사용될 경우 각각 인덱스를 만들어서 사용하는 것보다 2개의 컬럼을 묶어서 하나의 인덱스로 만들어 사용하는 것이 속도 향상에 유리하다. (복합인덱스를 잘 사용할 경우 select문의 속도가 올라간다)

복합인덱스 생성시 선행컬럼을 정하는 것이 중요한데 선행컬럼은 맨처음에 지정하는 컬럼으로 선정하는 기준은 where 절에 가장 많이 사용되는 것과 선택도가 높은 컬럼으로 선정되어야 한다.