오라클] 인덱스(Index) 관리

2017.07.24 07:00



인덱스 관리



인덱스 관리에 대해 정리. 

본 블로그 내용의 대략적인 내용은 아래와 같다.


1) 테스트용 테이블스페이스(데이터용, 인덱스용) 생성


2) 테스트용 유저 생성


3) 테이블과 인덱스 생성


4) 테이블과 인덱스의 물리정보 조회


5) 데이터 삭제 전, 후 비교


6) 인덱스 재구성(rebuild)






1) 데이터용 테이블스페이스와 인덱스용 테이블스페이스 만들기


테이터를 입력하는 테이블이 사용하는 테이블스페이스(test_tbs)와 인덱스가 사용하는 테이블스페이스(test_idx)는 각각 만들어주는 것이 좋단다.


create tablespace test_tbs datafile '/ora_data/t1_tbs.dbf' size 10M;

create tablespace test_idx datafile '/ora_idx/t1_idx.dbf' size 5M; 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SYS @ SQL > create tablespace test_tbs datafile '/ora_data/t1_tbs.dbf' size 10M;
 
테이블스페이스가 생성되었습니다.
 
 
SYS @ SQL > create tablespace test_idx datafile '/ora_idx/t1_idx.dbf' size 5M; 
 
테이블스페이스가 생성되었습니다.
 
 
SYS @ SQL > col tablespace_name for a15
SYS @ SQL > col file_name for a30
SYS @ SQL > select tablespace_name, bytes/1024/1024 MB, file_name from dba_data_files;
 
TABLESPACE_NAME      MB FILE_NAME
--------------- ---------- ------------------------------
USERS              5 /ora_data/OELORA/users01.dbf
UNDOTBS1         125 /ora_data/OELORA/undotbs01.dbf
SYSAUX           570 /ora_data/OELORA/sysaux01.dbf
SYSTEM           690 /ora_data/OELORA/system01.dbf
EXAMPLE            100 /ora_data/OELORA/example01.dbf
SCOTT_TBS         20 /ora_data/scott_tbs01.dbf
SCOTT_TBS         20 /ora_data/scott_tbs02.dbf
SCOTT_IDX          3 /ora_idx/scott_idx01.dbf
SCOTT_IDX          3 /ora_idx/scott_idx02.dbf
TEST_TBS         10 /ora_data/t1_tbs.dbf
TEST_IDX          5 /ora_idx/t1_idx.dbf
 
11 개의 행이 선택되었습니다.
cs



2) 테스트용 유저 생성(idxtest)



사용자(idxtest)를 생성하고, 디폴트 테이블스페이스를 지정(test_tbs),  권한 부여


create user idxtest identified by tiger 

default tablespace test_tbs 

quota unlimited on t1_tbs 

quota unlimited on t1_idx 

TEMPORARY TABLESPACE TEMP;


grant connect, resource to idxtest;


1
2
3
4
5
6
7
8
9
10
11
12
SYS @ SQL > create user idxtest identified by tiger 
            default tablespace test_tbs 
            quota unlimited on test_tbs 
            quota unlimited on test_idx 
            TEMPORARY TABLESPACE TEMP;
 
사용자가 생성되었습니다.
 
 
SYS @ SQL > grant connect, resource to idxtest;
 
권한이 부여되었습니다.
cs



3) 테이블(t1), 인덱스(idx_t1) 생성


사용자를 테스트용 idxtest로 변경.


테스트용 테이블과 인덱스 생성. 인덱스 테이블스페이스 지정(test_idx)

(테스트용 테이블스페이스는 디폴트 테이블스페이스가 지정되어 있음)



CREATE TABLE t1 AS

SELECT ROWNUM seq,

    '2017' || LPAD(CEIL(ROWNUM/1000) , 2, '0' ) month,

     ROUND(DBMS_RANDOM.VALUE (100, 1000)) amt

FROM DUAL

CONNECT BY LEVEL <= 50000;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
SYS @ SQL > conn idxtest/tiger
연결되었습니다.
 
 
IDXTEST @ SQL > CREATE TABLE t1 AS
                  SELECT ROWNUM seq,
                     '2017' || LPAD(CEIL(ROWNUM/1000) , 2'0' ) month,
                      ROUND(DBMS_RANDOM.VALUE (1001000)) amt
                  FROM DUAL
                  CONNECT BY LEVEL <= 50000;  
 
테이블이 생성되었습니다.
 
 
IDXTEST @ SQL > commit;
 
커밋이 완료되었습니다.
 
 
IDXTEST @ SQL > select count(*from t1;
 
  COUNT(*)
----------
     50000
 
 
IDXTEST @ SQL > create index idx_t1 on t1(seq) tablespace test_idx;
 
인덱스가 생성되었습니다.
 
 
IDXTEST @ SQL > select table_name, tablespace_name from user_tables;
 
TABLE_NAME               TABLESPACE_NAME
------------------------------ ---------------
T1                   TEST_TBS
 
 
IDXTEST @ SQL > select index_name, tablespace_name from user_indexes;
 
INDEX_NAME               TABLESPACE_NAME
------------------------------ ---------------
IDX_T1                   TEST_IDX
 
cs



    

4) 테이블과 인덱스의 물리정보 조회


아래는 오렌지에서 테이블스페이스의 사용량을 조회한 그림이다. 

테이블용 테이블스페이스는 10MB 중 3MB를 사용해서 30%, 

인덱스용 테이블스페이스는 5MB 중 2MB를 사용해서 40%를 사용한 것으로 나타난다. 




아래는 인덱스 분석자료로 인덱스의 유효성을 조사하는 쿼리이다. 

(LF_ROW에 대한 DEL_LF_ROWS의 비가 30%를 초과하면 인덱스를 재구축하도록 권장된다. )

   

    analyze index idx_t1 validate structure;

    select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats;


1
2
3
4
5
6
7
8
9
10
IDXTEST @ SQL > analyze index idx_t1 validate structure;
 
인덱스가 분석되었습니다.
 
IDXTEST @ SQL > select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats;
 
   BALANCE
----------
     0
 
cs


인덱스가 새로 생성되었을 때 BALANCE는 0이다




* 인덱스 모니터링


인덱스를 사용하는지 사용하지 않는지 모니터링하는 방법. 


alter index idx_t1 MONITORING USAGE; -- 모니터링 설정

select index_name, used from v$object_usage; -- 모니터링 조회

alter index idx_t1 NOMONITORING USAGE; -- 모니터링 해제



5) 데이터 삭제 후 비교


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
IDXTEST @ SQL > delete from t1 where seq between 1000 and 9999;
 
9000 행이 삭제되었습니다.
 
 
IDXTEST @ SQL > delete from t1 where seq between 20000 and 30000;
 
10001 행이 삭제되었습니다.
 
 
IDXTEST @ SQL > delete from t1 where seq between 37000 and 41000;
 
4001 행이 삭제되었습니다.
 
 
IDXTEST @ SQL > commit;
 
커밋이 완료되었습니다.
 
 
 
 
IDXTEST @ SQL > analyze index idx_t1 validate structure;
 
인덱스가 분석되었습니다.
 
IDXTEST @ SQL > select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats;
 
   BALANCE
----------
45.4520349
 
cs


일부 데이터를 삭제한 후 인덱스의 유효성을 조회해보면 인덱스가 생성되었을 때 0이었던 BALANCE가 45로 나빠진 상태가 된 것을 알 수 있다. (인덱스 리빌드 필요)


또 하나 알아둘 것. 

데이터를 삭제(delete)해도 테이블의 용량이나 인덱스의 용량이 줄어들거나 하지는 않는다. 




6) 인덱스 리빌드


alter index idx_t1 rebuild;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
IDXTEST @ SQL > alter index idx_t1 rebuild;
 
인덱스가 변경되었습니다.
 
 
IDXTEST @ SQL > analyze index idx_t1 validate structure;
 
인덱스가 분석되었습니다.
 
 
IDXTEST @ SQL > select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats;
 
   BALANCE
----------
     0
 
cs


index 리빌드 후 BALANCE가 다시 0이 되었다. 


아래 그림을 보면 테이블을 delete해도 테이블 용량은 줄어들지 않지만

index 리빌드 후에 용량이 줄어들었다(0.5MB)



인덱스 파일은 생성 후 delete, update, insert 등이 빈번하게 일어나면 블록들이 파편화하면서 제대로 성능을 발휘할 수 업을 뿐 아니라 인덱스 파일의 크기가 자꾸 커지는 문제가 있으므로 적절한 관리가 필요하다. 


아래 그림을 보면 BALANCE가 0이고 인덱스 파일시스템 사용량이 31%인 상태에서 데이터의 insert, delete, update가 일어난 뒤의 상황이다. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
IDXTEST @ SQL > select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats;
 
   BALANCE
----------
     0
 
IDXTEST @ SQL > insert into t1
SELECT ROWNUM seq,
    '2017' || LPAD(CEIL(ROWNUM/1000) , 2'0' ) month,
     ROUND(DBMS_RANDOM.VALUE (1001000)) amt
FROM DUAL
CONNECT BY LEVEL <= 50000;  2    3    4    5    6  
 
50000 개의 행이 만들어졌습니다.
 
 
IDXTEST @ SQL > commit;
 
커밋이 완료되었습니다.
 
 
IDXTEST @ SQL > delete from t1 where seq between 1000 and 9999;
 
9000 행이 삭제되었습니다.
 
 
IDXTEST @ SQL > delete from t1 where seq between 20000 and 30000;
 
10001 행이 삭제되었습니다.
 
 
IDXTEST @ SQL > update t1 set seq=seq*1.1;
 
57997 행이 갱신되었습니다.
 
 
IDXTEST @ SQL > commit;
 
커밋이 완료되었습니다.
 
 
IDXTEST @ SQL > analyze index idx_t1 validate structure;
 
인덱스가 분석되었습니다.
 
 
IDXTEST @ SQL > select (del_lf_rows_len / lf_rows_len) * 100 balance from index_stats;
 
   BALANCE
----------
51.1746027
 
cs



테이블 용량은 변함없지만 인덱스의 용량이 80%까지 올라간 것을 알 수 있다. 

저작자 표시 비영리 변경 금지
신고

또루아빠 SQL(오라클,mysql) , , , , , , ,

티스토리 툴바