데이터베이스 Index
Index란?
데이터 액세스 속도를 높이기 위한 테이블과 연결된 자료구조 입니다 .
인덱스는 연결된 데이터와 논리적 및 물리적으로 독립적인 스키마 개체입니다. 따라서 테이블에 물리적으로 영향을 주지 않고 인덱스를 삭제하거나 생성할 수 있습니다.
테이블에 인덱스가 없다면 값을 찾기 위해 테이블에 모든 블럭을 스캔해야 하므로 I/O가 증가하고 속도도 그 만큼 오래 걸립니다.
디스크 I/O 줄입니다.
책에서 색인 또는 목차와 유사하며, 인덱스는 데이터의 물리적 주소를 참조하고 있는 포인터.
데이터를 정렬하여 저장.
테이블에 많은 인덱스가 있으면 데이터베이스도 인덱스를 업데이트해야 하므로 DML 성능이 저하됩니다.
정렬된 상태를 유지해야 되기 때문에 insert, update, delete를 통해 데이터가 변경될 때 재정렬 작업이 필요함.
인덱스는 테이블의 전체 데이터중 10% 이하의 데이터를 처리하는 경우에 효율적.
인덱스 또한 저장공간이 필요하며 데이터베이스의 약 10%에 해당하는 추가 저장공간이 필요함.
기본 키와 고유 키에는 자동으로 인덱스가 생성되지만 외래키는 자동으로 생성하지 않습니다.
index 대상 컬럼을 선정하는 기준
조건절에 자주 사용되며 cardinality가 높은 거(카디널리티: 테이블 행 수에 대한 고유 값의 비율)
외래키와 같은 조인조건으로 사용되는 컬럼.
Index 종류
B*TREE,B+TREE, … 이 있다.
오라클: B-트리 인덱스, 비트맵 인덱스, 파티션 인덱스, 함수 인덱스, 도메인 인덱스
B*TREE
오라클에서 많이 사용됨.
Root / Branch / Leaf Node로 구성.
Oracle Index
Unique Index
인덱스 안에 있는 컬럼Key값에 중복되는 데이터가 없다.(성능이 좋음)
create unique index 인덱스명
Non Unique Index
중복되는 데이터가 들어가야 하는 경움(key로 지정한 필드의 중복된 값이 들어갈 수 있다.)
create index idx_dept3 on dept3(dname);
FBI 인덱스( Function Based Index ) : 함수기반 인덱스
where절의 조건이 sal + 100 > 200 이러한 조건일 경우
단순히 index컬럼을 sal로만 지정하게 되면 인덱스가 적용되지 않고 검색쿼리가 수행되게 됨
인덱스도 테이블명(sal+100)의 형태로 “함수기반 인덱스”로 사용해야 함.
create index upper_names_i on table ( upper ( name ) );
Descending Index : 내림차순으로 인덱스를 생성한다.
: 큰 값을 많이 조회하는 SQL에 생성하는 것이 좋다.
ex) 최근 날짜부터 조회, 회사 매출 조회
create index idx_emp_sal on emp(sal desc);
결합 인덱스(Composite Index)
인덱스 생성시에 두개 이상의 컬럼을 합쳐서 인덱스를 생성하는 인덱스
주로 where 절의 조건이 되는 컬럼이 2개 이상으로 and로 연결되는 경우 사용된다.
create index 인덱스명 on 테이블명(컬럼명1, 컬럼명2);
복합 인덱스는 절이 복합 인덱스에 있는 열의 전체 또는 선행 부분을 참조 SELECT하는 명령문에 대한 데이터 검색 속도를 높일 수 있습니다 . WHERE따라서 정의에 사용된 열의 순서가 중요합니다. 일반적으로 가장 자주 액세스하는 열이 먼저 표시됩니다.
선행 열의 카디널리티가 매우 낮은 경우와 같은 일부 경우에는 데이터베이스가 이 인덱스의 건너뛰기 스캔을 사용할 수 있습니다
[ BITMAP 인덱스의 종류 ]
**: 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용된다.
Bitmap Index를 생성하려면 데이터의 변경량이 적어야 하고, 값의 종류도 적은 곳이 좋다.
일반적으로 OLAP환경에서 많이 생성하게 되는대
Bitmap Index는 어떤 데이터가 어디에 있다는 지도정보(MAP)를 Bit로 표기하게 된다.
데이터가 존재하는 곳은 1로 표시하고, 데이터가 없는 곳은 0으로 표기한다.
정보를 찾을 때 1인 값만 찾게 된다!
SQL > create bitmap index 인덱스명 on 테이블명(컬럼);
. 만약 들어갈 BLOCK에 이미 다른 데이터가 들어가 있고 꽉차 있는경우, 데이터를 잘라서 새로운 LEAF 블록에 절반을 넣습니다
여기서 한건이 아닌 절반의 데이터를 분할 하는 이유는 앞으로 더 들어올 데이터의 자리를 미리 마련해주기 위해서 입니다.
읽는 블록의 갯수뿐만 아니라, IO하는 횟수도 중요합니다. INDEX SCAN 한 블럭씩 읽어야 합니다. 즉, IO의 단위가 1블럭이 됩니다. FULL SCAN의 경우 모두 다 읽어야하지만 DB_FILE_MULTIBLOCK_READ_COUNT 파라미터의 설정에 따라 한번에 여러 블럭을 읽어 INDEX SCAN보다 효율적일 수 있습니다.
인덱스 분할
- Index Split이란? : 인덱스의 Block들이 하나에서 두개로 나누어지는 현상
-> 인덱스는 데이터가 순서대로 정렬되어 저장되게 되는데, 기존 블럭에 여유 공간이 없는 상황에서
그 블럭에 새로운 데이터가 입력되어야 하는 경우
오라클은 기존 블럭의 내용 중 일부를 새 블럭에다가 기록한 다음 기존 블럭에 빈 공간을 만들어서
새로운 데이터를 추가하게 된다.
–> 따라서, 성능면에서 매우 불리하다.
a)Index Split은 새로운 블럭을 할당 받고 key를 옮기는 복잡한 작업을 수행
b)Index Split이 이루어지는 동안 해당 블럭에 대해 키 값이 변경되면 안되므로 DML이 블로킹된다.
enq:TX-index contention 대기 이벤트 발생(RAC-gc current split)
OLTP (Online Transaction Processing) - 온라인 트랜잭션 처리
네트워크상의 여러 이용자가 실시간으로 데이터베이스의 데이터를 갱신하거나 조회하는 등의 단위 작업을 처리하는 방식을 말한다. 주로 신용카드 조회 업무나 자동 현금 지급 등 금융 전산 관련 부문에서 많이 발생하기 때문에 ‘온라인 거래처리’라고도 한다. 이 방식의 특징은 기존 컴퓨터 통신에서 이용해 온 온라인 방식과 달리 다수의 이용자가 거의 동시에 이용할 수 있도록 송수신 자료를 트랜잭션(데이터 파일의 내용에 영향을 미치는 거래 ·입출고 ·저장 등의 단위 행위) 단위로 압축, 비어 있는 공간을 다른 사용자들이 함께 쓸 수 있도록 한 점이다.
일반적으로 테이블 전체 로우 수의 15%이하의 데이터를 조회할 때 인덱스를 생성한다.
** **
2 테이블 건수가 상당히 적다면 굳이 인덱스를 만들 필요가 없다. -> 테이블 건수가 적으면 인덱스를 경유하기보다 테이블 전체를 스캔하는 것이 더 빠르다.
** **
3 인덱스 생성시 컬럼은 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 지정하는 것이 좋다. (NULL값을 많이 갖는 컬럼은 피하는 것이 좋다.)
** **
4 결합 인덱스 생성시에는 컬럼의 순서가 중요하다.
-> 보통, 자주 사용하는 컬럼을 앞에 지정한다.
데이터의 검색보다 수정, 삭제, 삽입 작업이 빈번한 테이블에는 인덱스를 생성하지 않는 것이 좋다.
-> 인덱스는 DML작업에는 성능이 좋지 않기 때문에 검색을 위주로 하는 테이블에 생성하는 것이 좋다.(위에서 언급한 성능 이슈들이 발생할 수 있다.)
** **
7 인덱스 생성시 무엇보다 가장 중요한 점은 SQL 쿼리가 인덱스를 잘 활용할 수 있게끔 짜여져야 한다는 것이다.(쿼리를 잘 짜서 만들자!)
비트맵 대신 B-트리가 기본값인 이유는 무엇입니까?
비트맵 인덱스에는 엄청난 단점이 있습니다.
쓰기 동시성을 죽이고 있습니다.
이는 한 세션 의 삽입이 다른 세션의 삽입을 차단할 수 있는 Oracle Database의 몇 안 되는 상황 중 하나 입니다. 이것은 대부분의 OLTP 응용 프로그램에 대해 의심스럽습니다.
왜요?
음, 테이블 행을 삽입, 업데이트 또는 삭제할 때마다 데이터베이스는 인덱스를 동기화 상태로 유지해야 합니다. 이것은 B-트리에서 필요에 따라 리프 항목을 변경하여 트리를 걸어 내려가는 방식으로 발생합니다. 이 시각화 도구 에서 이것이 어떻게 작동하는지 볼 수 있습니다 .
그러나 비트맵은 전체 시작/끝 rowid 범위를 잠급니다! 따라서 값이 RED인 행을 추가한다고 가정해 보겠습니다. 동일한 범위에 값이 RED인 다른 행을 추가하려는 다른 삽입은 첫 번째 행이 커밋될 때까지 차단됩니다!
이것은 업데이트의 더 큰 문제입니다. B-트리의 업데이트는 실제로 이전 값을 삭제하고 새 값을 삽입하는 것입니다. 그러나 비트맵을 사용하면 Oracle Database는 이전 값과 새 값 모두에 대해 영향을 받는 rowid 범위를 잠가야 합니다!
결과적으로 비트맵 인덱스는 한 번에 하나의 프로세스만 기록하는 테이블에 가장 적합합니다. 이는 보고 테이블이나 데이터 웨어하우스에서 흔히 발생합니다. 그러나 일반적인 응용 프로그램은 아닙니다.
인덱스 압축
alter index olym_event_year_i rebuild compress 1;
https://blogs.oracle.com/sql/post/how-to-create-and-use-indexes-in-oracle-database
보이지 않는 인덱스
보이지 않는 인덱스 는DML 작업이며 옵티마이저에서 기본적으로 사용하지 않습니다.. 인덱스를 보이지 않게 만드는 것은 인덱스를 사용할 수 없게 만들거나 삭제하는 것의 대안입니다. 보이지 않는 인덱스는 인덱스를 삭제하기 전에 인덱스 제거를 테스트하거나 전체 응용 프로그램에 영향을 주지 않고 일시적으로 인덱스를 사용하는 데 특히 유용합니다.
SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL, DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,’?’,’Rebuild’),’Check’) CNF FROM USER_INDEXES I WHERE I.BLEVEL > 2 ORDER BY I.BLEVEL DESC