oracle 결합 인덱스와 인덱스 제거 및 설계도 작성

oracle 결합 인덱스

oracle 결합 인덱스를 생성할 때는 선택도 또는 카디널리티 값을 확인하는 게 중요한데 선택도 또는 카디널리티에 따라 액세스 비용이 결정되기 때문이다. 선택도와 카디널리티는 중복 인덱스 삭제 유무를 판단하는데도 도움이 된다. 조금 더 효율적인 인덱스를 생성하려면 인덱스 설계도를 기반으로 생성하는 것이 좋다.

oracle 결합 인덱스

oracle 결합 인덱스를 생성할 때는 선택도가 높은지 낮은지 확인이 필요하다. 특히 선택도가 낮은지에 따라 인덱스 생성 여부가 결정된다. 선택도는 총 레코드 중 조건절에 의해 필터링되어 선택되는 비율을 말한다. 선택도가 높은 인덱스는 생성할 필요가 없다. 선택도가 높으면 조건절에 의해 필터링되는 레코드 값이 많다는 의미이고 그만큼 많은 테이블 액세스가 발생하기 때문이다. 이러한 이유 때문에 인덱스를 생성 시 반드시 선택도와 카디널리티를 확인해야 한다. 위 내용은 여러 컬럼으로 생성된 결합 인덱스의 경우에도 적용이 된다. 하지만 인덱스를 생성할 때 컬럼의 순서는 크게 중요하지 않다. 조금 더 자세하게 설명하자면 인덱스 액세스 조건인 컬럼의 순서는 크게 중요하지 않다. 하지만 인덱스 액세스 조건이 아닌 다른 컬럼을 선행컬럼으로 지정하면 해당 인덱스의 효율성은 떨어지게 되며 새로운 인덱스를 생성해야 하는 이유가 되고 여러 개의 불필요한 인덱스가 생성되게 되면 결국 데이터베이스의 성능은 떨어지는 부정적인 영향을 끼칠 것이다. 정리하면 인덱스를 생성할 때 선택도와 카디널리티가 매우 중요하다. 하지만 인덱스를 구성하고 있는 각각의 컬럼 간 순서를 결정할 때는 컬럼의 카디널리티나 선택도보다 해당 컬럼을 항상 사용하는지 또는 컬럼에 해당하는 연산자 형태가 중요한 판단 기준이다.

중복 인덱스 제거

아래와 같은 3개의 인덱스는 중복 인덱스이다. X01(계약번호 + 계약일자), X02(계약번호 + 계약일자 + 취소일자), X03(계약번호 + 계약일자 + 취소일자 + 가입일자). 위 인덱스 들은 X03이 X02를 포함하고 있고 X02가 X01을 포함하고 있기 때문에 X03을 제외한 나머지 X01과 X02 인덱스는 삭제해도 인덱스는 정상적으로 작동된다. 그렇다면 아래와 같은 인덱스는 중복인덱스 일까? X01(계약번호 + 계약일자), X02(계약번호 + 취소일자), X03(계약번호 + 가입일자)의 경우 첫 번째 컬럼인 계약번호는 모두 동일하지만 두 번째 컬럼이 모두 다르기 때문에 중복이 아니다. 하지만, 첫 번째 컬럼인 계약번호의 선택도 또는 카디널리티가 낮으면 3개의 인덱스 모두 중복 인덱스라고 봐도 무관하다. 그 이유를 살펴보자. 우선 첫 번째 컬럼인 계약번호의 카디널리티가 낮다는 말은 전체 건수에서 중복되는 계약건수가 낮다는 뜻이다. 커디널리티가 2라고 가정하면 총건수에서 2건이 조회되는 것을 뜻하고, 계약번호로 원하는 레코드 값을 충분히 찾을 수 있기 때문이다. 즉 위와 같은 3개의 인덱스는 카디널리티에 따라 중복 인덱스가 되거나 불완전 중복 인덱스가 된다. 실제 테스트를 진행해 보면 카디널리티에 따라서 인덱스의 컬럼 유무를 정할 수 있다는 것을 확인할 수 있다.

설계도 작성

인덱스를 설계할 때는 테이블 또는 SQL만 고려하는 것이 아니라 시스템 전체 효율을 고려해야 하며 완벽한 인덱스를 설계하기 위해서는 설계도가 필요하다. 우선 인덱스를 설계하기 전에 해당 인덱스를 사용하는 유형 모두를 조사해야 한다. 인덱스를 어떻게 생성하느냐에 따라 SQL의 성능이 결정되고 더 나아가 불필요한 스캔과 액세스를 막아 데이터베이스 성능을 향상 시킬 수 있다. 또한 인덱스 생성 전 또는 생성과 동시에 파티션을 구성해야 효율적인 인덱스를 생성할 수 있다. SQL 성능이 느려 또는 조금 더 빠르게 레코드 값을 찾기 위해 그때 상황에 맞는 인덱스를 생성하는 경우가 많다. 이러한 경우는 불필요한 인덱스가 많아져 데이터베이스 용량 증가의 원인이 되며 효율적이지 못한 인덱스가 생성되면 불필요한 인덱스 스캔과 액세스가 발생하게 되어 데이터베이스에 부정적인 영향을 준다. 효율적인 인덱스를 설계한다는 것은 최소 수량의 인덱스로 연관된 모든 경로를 제공할 수 있다는 것이다. 설계도 작성 전 이러한 모든 것들이 선행되어야 하고 전반적인 파악이 끝난다면 컬럼 조합과 순서, 분포도 조사를 기준으로 후보 컬럼 선정 등을 선정한다. 인덱스 구조는 일반적으로 나무를 거꾸로 돌려놓은 듯한 B트리 구조를 많이 사용한다. B트리 구조는 OLTP성 DB에 많이 사용하며 입력, 삭제, 수정이 편하다는 장점을 가지고 있지만 처리하려는 범위가 넓은 경우 속도가 저하된다는 단점을 가지고 있다.