[오라클/SQL] INDEX 생성/조회/삭제/통계정보

 

INDEX  란?

    Index는 데이터 베이스에서 데이터를 빠르게 조회하고 위한 데이터베이스 검색 기술이다.

    보통 전체에서 2%에 해당되는 정보에 접근할 때에는 인덱스가 유리하다. (즉, 소규모 데이터를 쿼리할 경우)

    특정 항목에 대한 인덱스를 생성한 뒤 해당 항목으로 쿼리하면 인덱스가 적용되어 속도가 향상된다.

 

 

INDEX 생성/조회/삭제

생성
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3, .......)

조회
SELECT * FROM USER_INDEXES

삭제
DROP [인덱스 명]

 

 

INDEX 가 불필요한 경우는 ?

데이터가 수천 건 미만인 경우에는 FULL SCAN의 시간이 그리 많이 소요되지 않기 때문에 인덱스를 생성하면 소요시간이 증가 된다. 조회보다 삽입, 수정, 삭제 처리가 더 많은 테이블이 있는 경우 조회 결과가 전체 데이터의 15% 이상 읽어 들일 경우

 

 

INDEX  사용 여부를 위한 통계정보 

    통계정보는 Oracle Optimizer가 SQL 쿼리에 대해 가장 최적의 데이터를 추출할 수 있는 경로를 결정해주는  Oracle의 내부구조이다.

    규칙_기반과 비용_기반이 있다..

    일반적으로 비용_기반이 더 나은 성능을 가지고 있고, 관리자는 대부분 비용_기반으로 작업한다.

    이 비용을 알아내기 위해 통계정보가 필요하다.

DBMS_STATS 패키지
gather_database_ stats	데이터베이스의 모든 Object에 대한 통계 정보 생성
gather_schema_ stats	해당 스키마의 모든 Object에 대한 통계 정보 생성
gather_table_stats	테이블과 그 테이블과 연관된 인덱스에 대한 대해 통계 정보 생성
gather_index_stats	인덱스에 대해 대해 통계 정보를 생성

EXECUTE dbms_stats.gather_table_stats ( 'HR','CUST_INDEX') ;

select count(*) from cust_index;
	
-- SQL을 튜닝하는데 필요한 정보들을 많이 포함하고 잇다.(쿼리수행결과, 실행계획, 실행통계)
set autotrace on; 

set timing on; 
select cust_last_name from cust_index
where customer_id = 158;

수행시간을 볼 수도 있지만, 쿼리가 어떤과정과 경로로 처리했는지까지도 확인 가능함!!

-- hr 로그인

set autotrace on;
set timing on; 
select employee_id, last_name,  salary from employees
where salary = 8000;