[Oracle] 오라클 인덱스(Index)와 자동 인덱싱

인덱스(Index) 란?

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

SELECT를 통해 데이터를 조회할 때는 FULL SCAN을 통해 모든 데이터를 읽어오는 반면에

Index를 이용하면 Index를 비교하여 일치하는 데이터만을 읽어오기 때문에 속도가 향상됩니다.

 

인덱스(Index) 생성

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

 

인덱스(Index) 조회

SELECT * FROM USER_INDEXES

인덱스는 USER_INDEXE 시스템 뷰에서 조회할 수 있습니다.

 

인덱스(Index) 삭제

DROP [인덱스 명]

 

인덱스(Index)가 불필요한 경우

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

 

인덱스(Index)의 리빌딩

데이터의 생성, 삭제, 수정의 처리가 발생하여 시간이 경과함에 따라 생성한 인덱스의 속도가 저하될 수 있고, 인덱스는 트리의 구조로 이루어져 있어 트리의 한쪽이 무거워지는 경우 데이터 베이스의 성능이 저하될 수 있습니다. 따라서 일정한 주기로 인덱스를 리빌딩하는 작업을 해주는 것이 좋습니다.

 

자동 인덱싱(Automatic Indexing)

Oracle을 11g에서 19c를 업그레이드를 할 계획이 생겨 new features에 대해 조사를 하는 중에 19c에 자동 인덱싱이라는 새로운 기능이 생겨 소개를 하면 좋을 거 같아 이 포스팅을 작성하게 되었습니다.

 

자동 인덱싱은 애플리케이션의 워크로드에 따라 자동으로 데이터 베이스에 인덱스를 생성, 재구성, 삭체 하므로 데이터베이스의 성능을 향상합니다. 수만 개부터 수백만 개까지의 데이터를 다루는 데이터베이스에서는 인덱스가 매우 중요한데 인덱스를 업데이트해주지 않으면 성능이 저하되는데 이러한 인덱스의 단점을 보완시켜줄 수 있는 기능입니다.

 

자동 인덱싱은 다음과 같은 기능을 제공합니다.

• 백그라운드에서 미리 정의된 시간 간격에 따라 정기적으로 자동 인덱싱 프로세스를 실행
• 애플리케이션 워크로드에 따라 새로운 인덱스를 생성하고 기존의 성능이 떨어진 인덱스를 삭제합니다.
• 자동 인덱싱 작업과 관련된 보고서를 생성하는 PL/SQL API를 제공합니다.

 

자동 인덱싱(Automatic Indexing) 작동 방식

자동 인덱싱 프로세스는 15분마다 다음과 같은 작업을 수행합니다.

1. 자동 인덱스 후보 식별
2. 보이지 않는 자동 인덱스(SQL문에서 사용할 수 없는 인덱스) 생성 자동 인덱스는 단일 열이거나 다중 열일 수 있습니다.
3. 보이지 않는 자동 인덱스를 통해 SQL 문의 성능 향상이 검증되면 가시적 인덱스(SQL문에서 사용 가능)로 구성
4. 사용하지 않는 인덱스 삭제

자동 인덱싱(Automatic Indexing) 설정

자동 인덱싱을 활성화하고 SQL문에서 사용할 수 있도로 가시적 인덱스로 구성

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

자동 인덱싱을 활성화하지만, 보이지 않는 인덱스로 구성

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

자동 인덱싱 비활성화

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

 

Oracle 19c에 새롭게 업데이트된 자동 인덱싱에 대해 알아보았습니다.

시대가 흐름에 따라 빅데이터의 중요성이 부각되고 있는 때에 자동 인덱싱은 그러한 수요를 충족해줄 수 있는 괜찮은 기술이라고 생각되어 소개해드리고 싶어 작성하게 되었습니다.

Oracle 21c에서도 자동 인덱싱에 대한 업데이트 내용이 있는 것으로 보아 점차 성능이 향상될 거라고 생각합니다.

 

다음에 기회가 된다면 자동 인덱싱에 대한 성능을 리뷰해볼 수 있으면 좋겠네요.

더욱더 자세한 내용이 궁금하신 분들은 아래의 링크를 참조해주시기 바랍니다.

https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-FAE533D7-9C9E-48C9-9A61-EBC060BF3D70

Database Administrator’s Guide