Day 46 (DB_인덱스)

인덱스는 논리적 인덱스와 물리적 인덱스 두가지가 있다. 논리적 인덱스는 단일 열 또는 연결된 인덱스, 고유 또는 비고유 인덱스, 함수 기반 인덱스, 도메인 인덱스가 있다. 물리적 인덱스는 분할된 또는 분할되지 않은 인덱스, B트리 인덱스, 비트맵 인덱스가 있다.

일반적으로 테이블에서 사용하는 인덱스가 B트리 인덱스이고, 비트맵 인덱스는 트랜젝션이 1개만 생성되므로 특별한 DB에서 사용한다.

 

B 트리 인덱스는 리프노드에만 데이터가 저장된다. 저장된 데이터는 인덱스 항목으로 인덱스 항목 헤더, 키 열 길이, 키 열 값, ROWID 합쳐져 정렬되어 저장되어 있다.

full table scan은 H/W안 까지 블록 전체를 읽는다. index scan은 인덱스가 포함된 블록에서 조건과 일치하는 값을 찾고 해당 행의 rowid를 참조하여 data읽는 방식이다. 

 

인덱스가 추가될때 leafnode가 늘어남에 따라 branch가 증가하여 root가 1개가 될때까지 증가하여 위 방향으로 트리구조가 성장하게 된다. btree인덱스를 이용해 data를 찾을때 모든 데이터 깊이(depth)만큼 소요되기 때문에 데이터 탐색 시차가 없다.

 

비트맵인덱스는 기수(cardinality)가 낮은 열에서만 사용이 가능하다. 왜냐하면 중복값이 많아야 활용이 가능한 인덱스 이기 때문이다.

비트맵인덱스의 구조는 트리구조와 동일하다. 리프노드의 크기는 동일하기때문에 비트맵인덱스의 각각 리프노드의 첫번째 행과 마지막 행이 전부 동일하다. 그리고 데이터양도 2진수로 이루어진 비트맵의 크기도 행의 갯수만큼 존재한다. 데이터 값의 검색 조건은 OR연산을 이용해 여러 행의 검색을 선택적으로 할수있다.  단점은 행 추가시 모든 행에 데이터가 추가되기 때문에 모든 행에 lock이 걸린다. 따라서 트랜젝션이 여러개가 발생되지 않는다.

 

B트리 인덱스와 비트맵 인덱스는 각각 특성이 달라 비교할 수 는 없지만 각자 특징을 적으면 아래와 같다.

B트리 인덱스	비트맵 인덱스
높은 기수 열에 적합함	낮은 기수 열에 적합함
키 갱신시 상대적으로 적은 비용이 듬	키 갱신시 상대적으로 많은 비용이 듬
OR 술어를 사용하는 질의에서 비효과적임	OR 술어를 사용하는 질의에서 효과적임
OLTP(OnLine Transactional Process)에 유용함	데이터 웨어하우징에 유용함

 

인덱스를 재구축을 하게되면 삭제되어 사용할 수 없는 빈 블록안의 데이터가 없어져 쓸모없는 리소스를 차지하지 않는다.

ALTER INDEX 인덱스명 REBUILD
[TABLESPACE 테이블스페이스명];

ALTER INDEX명령어 뒤에 테이블스페이스를 작성하면 지정한 테이블 스페이스에 재구축 하고 적지 않으면 현재 사용하고 있는 테이블 스페이스에 재구축 한다.

 

ANALYZE INDEX 인덱스명
VALIDATE STRUCTURE;

인덱스 및 유효성 검사는 Table검사시 연결된 인덱스까지 모두 검사가 된다. 하지만 항상 동기화가 되는것은 아니기 때문에 항상 유효성 검사 결과를 보기 전에  유효성값을 갱신해야 한다.

 

실습 인덱스를 생성하고 관리해라.

소유자가 ST인 테이블을 조회한다.

통계정보를 확인한다. 하지만 갱신되지 않았기 때문에 출력된 값에 의미는 없는 쓰레기 값이다.

테이블의 통계정보를 갱신한다.

스키마의 통계정보를 갱신한다.

dbms_stats 패키지로 갱신되지 않는 통계정보를 갱신한다.

갱신된 통계정보를 확인한다.

dbms_stats 패키지로 갱신되지 않는 통계정보를 갱신하고 확인한다.

테이블과 테이블 스페이스의 정보를 확인한다.

테이블 스페이스의 사용 가능한 크기를 확인한다.

사용 가능한 테이블 스페이스 크기가 할당되지 않아 테이블 스페이스가 이동되지 않는다.

테이블 스페이스 할당량을 확인한다.

테이블을 다른 테이블스페이스로의 이동

테이블이 속한 테이블 스페이스를 확인한다.

테이블이 이동되어 인덱스가 깨져있는 상태이기 때문에 이동된 테이블스페이스에서 재구축한다

인덱스 상태가 변경된 것을 확인한다.

테이블 통계정보를 갱신한다.

SELECT table_name, num_rows, chain_cnt, blocks, empty_blocks as empty, avg_space, avg_row_len
FROM dba_tables
WHERE OWNER = 'ST' AND table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE');
            // 테이블을 구성하는 행의 수, 체인화 된 블록수, 구성하는 블록수등의 정보를 출력한다.

SELECT table_name, index_name, status, num_rows, leaf_blocks, blevel
FROM dba_indexes
WHERE OWNER = 'ST' AND table_name IN ('STUDENT', 'PROFESSOR', 'COURSE', 'SCORE'); 
            // 테이블과 연관된 인덱스 이름, 인덱스 상태, 행의 수, 리프블록수, 깊이 등의 정보를 출력한다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('ST', 'STUDENT');
            // 테이블 통계정보 중 실행계획에 영향을 주는 요소에 대한 통계정보를 갱신한다.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('ST');
          // 스키마의 모든 세그먼트의 통계정보 중 실행계획에 영향을 주는 요소에 대한 통계정보를 갱신한다.

ANALYZE TABLE st.student COMPUTE STATISTICS; // 테이블정보를 갱신 한다.

SELECT name, blocks, lf_rows, del_lf_rows FROM index_stats;
          // 인덱스의 리프노드의 행의 갯수와 삭제된 행의 갯수를 출력한다.

ANALYZE INDEX st.st_sname VALIDATE STRUCTURE; // 인덱스정보를 갱신 한다.

SELECT d.tn, round(total/1000000) "TOTAL(MB)",
round(free/1000000) "free(MB)",
TO_CHAR(round(free/total*100.1),'99.9')||'%' rate
FROM (SELECT tablespace_name tn, sum(bytes) total FROM dba_data_files
GROUP BY tablespace_name) d,
(SELECT tablespace_name tn, sum(bytes) free FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tn=f.tn;                                   // 사용가능한 테이블 스페이스의 용량을 확인한다.


ALTER TABLE st.student MOVE
TABLESPACE indx;                   // 테이블을 indx테이블 스페이스로 이동하여 재구축한다.