쌍용강북교육원 국비학원 34일차
색인
예를 들어 색인(Index)은 두꺼운 책의 뒷면에서 ‘찾기’와 같은 역할을 합니다.
find의 특징은 정렬되어 있다는 것입니다. 인덱스에 저장된 데이터도 정렬해서 저장하는 것이 특징이다.
where 절이 있는 인덱스 선택한 명령어의 속도를 개선하는 데 사용됩니다.
인덱스 생성
1. where 절에서 자주 사용되는 컬럼에 생성되어야 합니다.
2. 선택도가 높은 컬럼에서 수행해야 합니다.
※ 선택도 – 선택도가 높을수록 독특함.
3. 카디널리티가 높은 열에 생성해야 합니다.
※ 카디널리티의 사전적 의미는 집합 구성원의 수로 상대적인 개념입니다.
카디널리티가 높다는 것은 중복성이 거의 없는 고유한 데이터에 대해 카디널리티가 상대적으로 높다는 것을 의미합니다.
낮은 카디널리티는 중복성 수준이 높다는 것을 의미하며 중복 데이터가 많을수록 카디널리티가 낮습니다.
열에 고유 인덱스 생성 열에는 중복 값을 입력할 수 없으며 고유한 값만 입력됩니다. 반대로 Non-Unique는 중복을 허용합니다.. 고유 인덱스는 고유하지 않은 인덱스보다 약간 더 빠릅니다.
(문법)
(고유한) 인덱스 인덱스 이름 만들기
on 해당 테이블 이름(컬럼 이름 asc | dsce) ;
열에 기본 키 제약 조건 또는 고유 제약 조건이 할당되면 해당 열에 고유 인덱스가 자동으로 생성됩니다. 생성된 인덱스 이름이 제약 조건 이름이 됩니다. (또한 인덱스를 삭제하려면 변경을 통해 제약 조건을 삭제해야 합니다.)
※ 컬럼명에 함수를 삽입하여 함수 기반 인덱스를 생성할 수도 있습니다.
인덱스를 확인하는 방법
SQL Developer에서 계획(실행 계획)을 확인하는 방법
select 문 실행 시 인덱스를 통한 데이터 조회 여부. 인덱스를 사용하지 않고 전체 테이블 스캔으로 얻은 것인지를 확인하는 것을 SQL 계획(실행 계획)이라고 합니다.
계획(실행 계획) 및 추적(자동 추적) 둘의 차이점은 Plan은 SQL을 실행하기 전에 Oracle Optimizer(옵티마이저, 옵티마이저)에게 SQL을 실행하는 방법을 알려주고 Trace는 SQL을 실행하고 결과를 알리기 위해 Oracle Optimizer가 SQL을 실행한 방법을 보여줍니다.
따라서 추적은 정확도 측면에서 계획보다 훨씬 정확합니다. 계획은 말 그대로 계획이기 때문입니다. 따라서 Oracle Optimizer가 실제로 실행할 때 다양한 이유로 다르게 실행될 수 있습니다. Trace는 정확하지만 SQL에 대한 긴 대기 시간이 오래 걸리는 단점이 있습니다. (COST 값이 낮을수록 속도가 빠름)
LAST_CR_BUFFER_GETS 및 LAST_ELAPSED_TIME 열이 추적에 표시됩니다. LAST_CR_BUFFER_GETS는 SQL 실행 중 각 단계에서 읽은 블록 수를 의미하며 LAST_ELAPSED_TIME은 경과 시간 정보입니다.
즉, 이 정보를 통해 어느 구간이 오래 걸렸는지 확인할 수 있으므로 해당 구간의 값이 작게 되도록 SQL 튜닝을 수행한다.
DML(삽입, 업데이트, 삭제)이 자주 발생하는 테이블에 인덱스를 생성하면 DML 작업 속도가 느려지고 인덱스 분할이 발생하므로 인덱스가 최소한으로 많은 것은 결코 좋지 않다.
※ 인덱스 분할이란 인덱스의 블록이 1개에서 2개로 쪼개지는 현상을 말합니다. 인덱스는 정렬 후 저장하기 때문에 중간에 인덱스를 삽입했을 때 발생하는 현상입니다.
※ 추적을 위해서는 SYS 또는 SYSTEM의 허가를 받은 후 재접속해야 합니다.
사용자 이름 select_catalog_role 부여;
사용자 이름으로 사전을 선택할 수 있습니다.
재건
인덱스가 생성된 테이블을 드랍하면 테이블의 데이터는 삭제되지만 인덱스의 데이터는 삭제되지 않고 사용하지 않는다는 것만 표시된다. 따라서 사용하지 않는 인덱스 정보로 인해 인덱스를 사용하여 선택하면 인덱스 검색 속도가 느려집니다. 이 경우 검색 속도를 높이려면 인덱스 재구축을 수행해야 합니다.
업데이트 또한 인덱스에 대한 ‘업데이트’ 작업이 없으며 인덱스를 삭제하고 다시 삽입합니다. 따라서 인덱스 삭제의 단점과 인덱스 삽입 시 발생하는 인덱스 분할이 발생한다. 따라서 자주 업데이트하는 경우에도 인덱스 재구축을 수행해야 합니다.
(문법)
인덱스 인덱스 이름 유효성 검사 구조 변경 ;
인덱스 삭제
인덱스 삭제 index-name ;
인덱스 상태 (균형) 확인
(문법)
구문 분석 색인 색인 이름 유효성 검사 구조 ; (분석)
선택 (del_lf_rows_len / lf_rows_len) * 100
index_stats에서
여기서 name = ‘인덱스 이름’;
※ 값이 0에 가까울수록 지수 상태가 좋습니다. (저울은 깨지지 않습니다.) 값을 보기 전에 항상 분석을 먼저 해야 합니다.
복합 인덱스 생성
두 개 이상의 열로 묶인 인덱스입니다. where 절에서 두 개의 컬럼을 사용하는 경우 각각의 인덱스를 생성하여 사용하는 것보다 하나의 인덱스에 두 개의 컬럼을 결합하여 사용하는 것이 좋다. 속도 향상에 유용합니다. (복합 인덱스를 잘 사용하면 select 문의 속도가 빨라집니다.)
복합 인덱스를 생성할 때 선행 열을 결정하는 것이 중요합니다. 선행 컬럼은 지정하는 첫 번째 컬럼이며 선택 기준은 where 절에서 가장 자주 사용되는 컬럼과 선택도가 높은 컬럼이어야 합니다.