[Database/SQL]오라클 인덱스란~

안녕하세요 IT깡패's입니다.

요번 시간에는 오라클의 인덱스에 대한 포스팅 글을 써보도록 할께요~~

인덱스(Index)란??

'색인'이라고도 불리며 데이터베이스 객체 중 하나

테이블에 인덱스를 작성

인덱스의 역할은 검색속도에 향상에 있습니다.

테이블과는 별개의 독립된 객체로 작성되지만 테이블에 의존적인 특성이 있지요~

기본키나 유일키와 같은 제약 조건이 지정되면 따로 인덱스를 생성하지 않더라도 해당 키에 대한 인덱스가 자동으로 생성됩니다.

- 인덱스의 장점

검색 속도가 빨라집니다.

시스템의 부하를 줄여 전체적인 성능을 향상시킵니다.

- 인덱스의 단점

인덱스 생성에 시간이 필요합니다.

인덱스를 위한 추가적인 공간이 필요합니다.

검색 외 데이터 변경(INSERT / UPDATE / DELETE)가 빈번한 일어날 경우 성능이 저하될 우려가 있습니다.

인덱스가 필요한 경우

- 테이블의 행의 수가 많은 경우

- WHERE문에 특정 컬럼이 많이 사용될 때

- 검색 결과가 전체 데이터의 2~4%정도일 때

- JOIN에 자주 사용되는 컬럼

- NULL을 포함하는 행이 많은 컬럼일 경우

인덱스가 불필요한 경우

- 데이터가 적은(수천 건 미만) 경우

- 조회 보다 삽입, 수정, 삭제 처리가 많은 테이블일 경우

- 조회 결과가 전체 행의 15% 이상 조회할 것으로 예상되는 경우

인덱스(Index) 생성

*고유 인덱스(Unique Index)

중복되지 않는 데이터를 인덱스의 키 값으로 지정하는 인덱스

성능이 좋습니다

인덱스를 생성한 컬럼에 대해서 중복되는 값을 테이블에 삽입하려고 하면 에러 발생

- 단일 컬럼 고유 인덱스 생성

CREATE UNIQUE INDEX index_name ON table_name(column_name);

*비고유 인덱스(Non-Unique Index)

중복되는 데이터를 인덱스의 키 값으로 지정하는 인덱스

- 단일 컬럼 비고유 인덱스 생성

CREATE INDEX index_name ON table_name(column_name);

*함수 기반 인덱스

인덱스를 사용하려면 WHERE 절의 조건을 다른 형태로 가공해서는 안 됩니다.

만약 WHERE절의 조건으로 'sal + 100'과 같이 가공된 형태로 사용해야 하고 인덱스를 생성하고 싶다면 함수 기반 인덱스를

생성해야 합니다.

인덱스의 생성으로 검색 속도를 향상되긴 하지만 데이터베이스 성능향상을 위한 근본적인 해결방안이 아닙니다. 또한 쿼리 조건이 바뀌면

인덱스를 다시 생성해야하는 단점이 있습니다.

- 함수 기반 인덱스 생성

CREATE INDEX index_name ON table_name(column_name 가공 형태);

- 함수 기반 인덱스 예시

CREATE INDEX idx_sal_add_bonus ON emp(sal + 100);

*결합 인덱스(Composite Index)

두 개 이상의 컬럼을 합쳐서 인덱스 생성

WHERE 절의 조건 컬럼이 2개 이상 AND로 연결되어 사용되는 경우 많이 사용

- 결합 인덱스 생성

CREATE INDEX index_name ON table_name(column_name1, column_name2, ...);

*BITMAP 인덱스

데이터 값의 종유가 적고 동일한 데이터가 많을 경우 사용

예) 성별(column) : 남, 여

데이터의 변경량이 적고 값이 종류가 적을 때 좋은 인덱스

데이터가 어디에 존재하는지 정보를 BIT를 표시합니다.

BITMAP INDEX 생성 이후 컬럼 값이 새로 하나 더 생긴다면 인덱스를 전부 수정해줘야합니다.

- BITMAP 인덱스 생성

CREATE BITMAP INDEX index_name ON table_name(column_name);

인덱스 제거

DROP INDEX index_name;

인덱스 재 생성

대량의 DML 작업을 수행하면 인덱스의 성능이 저하되므로 재설정을 해주는 것이 좋습니다.

꾸준한 관리가 필요합니다.

ALTER INDEX index_name REBUILD;

인덱스 조회

USER_INDEXES 뷰를 이용해 정보를 조회할 수 있습니다.

SELECT table_name, index_name, index_type   FROM user_indexes   where table_name = '테이블명';

인덱스는 또 댜량의 데이터를 관리해서 사용하기 때문에 오라클말고 TIBRO같은 대용량 데이터베이스를 관리하는 도구에서 많이 사용됩니다.

인덱스의 글을 적다보니 피곤이 몰려오는것 같습니다~~

어서 개발에 대한 내용들을 마스터 할 날이 왔으면 좋겠네요~ㅎㅎ;;