[오라클]INDEX 생성 가이드라인

0017. INDEX 생성 가이드라인

☞ 12-5 b-tree index

* 특성
1. 컬럼값이 같으면 rowid 순으로 정렬
2. null에는 인덱스가 생성되지 않는다.

* 인덱스에 대한 DML 작업 효과

Index 에는 pctused파라미터가 없다.

이말은 인덱스를 삭제하고 남은 공간을 다시 활용하지 않는 다는 뜻이다.
이런 이유 때문에 인덱스는 주기적으로 정리 되어야 하며 그렇게 하지 않으면 인덱스의 사이즈는 점점 증가 한다.

☞ 12-7 bitmap index

index를 경유하면 모든 데이터를 읽지않고 원하는 범위의 데이터만 읽기 때문에 빠른 성능을 보장받을 수 있다. 하지만 그범위가 아주 넓은 경우에는 오히려 더 나쁜 성능을 나타내게 된다. 이처럼 B*tree inex의 단점은 cardinality가 낮은 속성에 대하여 취약하는 것이다.
(색깔의 종류가 적을 수록 좋다)

☞ 12-9 비트리 인데스와 비트맵 인덱스 비교

비트리 인덱스는 카디널리티가 높은 것에 유리하고 비트맵은 낮은것에 유리 함. 그리고 비트맵 인덱스는 비트맵 세그먼트 수준의 잠금을 사용하기 때문에 비트맵 인덱스의 키열을 갱신하면 더 많은 비용이 들지만 B트리 인덱스에서는 테이블의 각행에 해당하는 항목을 잠금으로 update 할 때 유리 . 비트맵은 부울 연산 식을 사용해서 부울 수식에 유리.
OLTP(게시판이나 수시로 바끼는 것)환경에서는 B트리 인덱스가 유리. 대형 정적 테이블에서 복합 질의 사용하는 데이터 웨어하우스 환경에서는 비트맵이 유리.

☞ 12-10 일반 B 트리 인덱스 생성

create index hr.employees_last_name_indx
on hr.employees(last_name)
pctfree 30
storage (initial 200k next 200k
pctincrease 0 maxextents 50)
tablespace indx;

last_name 열을 사용하여 employees 테이블에 인덱스를 생성



주의) 밑에 tablespace indx;를 지정하지 않으면 동시에 사용자가 접속하면 index를 사용하면 lock이 걸리기 때문에 지정해 줘야함

☞ 12-13 인덱스 생성시 가이드라인

create index emp_hiredate on emp(hiredate) pctfree 30 initrans 10;

1. 인덱스를 사용하면 쿼리 속도는 빨라지나 DML 작업속도는 느려진다.

2. 별도의 테이블 스페이스에 인덱스 세그먼트를 둔다.

3. 대용량 테이블에 인덱스 생성시 nologging 옵션을 쓰는게 좋다.

이유) 큰 인덱스의 경우 리두로그파일 생성을 방지하면 성능을 상당히 향상시킬수 있으므로

4. 인덱스 생성시 initrans(각 블록에서 미리 할당하는 트랜잭션 항목수) 값을 테이블 보다 많이 줘야 한다.

이유) 인덱스 항목은 자신이 인덱스하는 행보다 작기 때문에 인덱스 블록은 블록마다 많은 항목을 포함 해당 테이블보다 인덱스에 대한 initrans가 높아야 한다.

☞ 12-15 비트맵 인덱스 생성

create bitmap index orders_region_id_idx
on orders(region_id)
pctfree 30
storage(initial 200k next 200k
pctincrease 0 maxextents 50)
tablespace indx;

 

☞ 12-18 인덱스 저장 영역 매개변수 변경

alter index employees_last_name_idx
storage(next 400k
maxextents 100);

 

☞ 12-20 인덱스 공간 할당 및 할당 해제

alter index orders_region_id_idx
allocate extent (size 200k
datafile '/disk6/indx01.dbf');

 

alter index orders_id_idx
deallocate unused;

테이블에 대량의 삽입 작업 전에 인덱스에 확장영역을 추가 할수 있으며 확장 영역을 추가하면 인덱스가 동적으로 할당 되어 성능 저하를 방지 할수 있다.


☞ 12-21 인덱스 재구축

alter index orders_region_id_idx rebuild
teablespace indx02;

* 기존 인덱스를 데이터 소스로 사용하여 인덱스 구축
* 기존 인덱스를 사용하면 다시 구축할 필요가 없으므로 성능 향상
* 새 인덱스를 구축할 충분한 공간 필요
* 새 인덱스를 구축하는 동안 quary에서 기존 인덱스를 사용 가능

@ 재구축이 필요한 상황

1. 인덱스가 있는 테이블 스페이스를 이동할때
2. 인덱스 테이블을 alter table ........ move tablespace 명령을 사용하여 이동

SQL> select table_name, tablespace_name from user_tables
2 where table_name='EMP';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
EMP USERS

SQL> create index emp_ename
2 on emp(ename);

인덱스가 생성되었습니다.

SQL> alter table emp
2 move tablespace tools;

테이블이 변경되었습니다.

SQL> select ename
2 from emp
3 where ename='SCOTT';

select ename
*
1행에 오류:
ORA-01502: 인덱스 'SCOTT.EMP_ENAME'또는 인덱스 분할영역은 사용할 수 없은
상태입니다

SQL> alter index emp_ename rebuild;

인덱스가 변경되었습니다.

SQL> select ename
2 from emp
3 where ename='SCOTT';

ENAME
----------
SCOTT

SQL> @p.sql

file toto.sql(이)가 기록되었습니다

테이블이 잘렸습니다.

1* select ename
1* explain plan set statement_id='MyTest' for select ename

해석되었습니다.

Query Plan
---------------------------------------------------------------
SELECT STATEMENT Cost =
INDEX RANGE SCAN EMP_ENAME NON-UNIQUE

실습2)

1* alter index emp_ename rebuild reverse
SQL> /

인덱스가 변경되었습니다.

SQL> select index_name, index_type
2 from user_indexes;

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
C_ID_DATE NORMAL
C_ID_PK NORMAL
DEPT200_DEPTNO_PK NORMAL
EMP21_EMPNO_PK NORMAL
EMP22_EMPNO_PK NORMAL
EMP_BIT_JOB BITMAP
EMP_ENAME NORMAL/REV
EMP_HIREDATEON NORMAL
SYS_C003006 NORMAL
SYS_IL0000030314C00001$$ LOB

10 개의 행이 선택되었습니다.

bitmap 도 될까 ?

☞ 12-23 index 온라인으로 재구축

alter index ordes_id_idx rebuild online;

9i 에서 인덱스 재 구축 작업 동안 동시에 DML 작업 수행할수있는것

SQL> update test1
2 set sal=0
3 where id=9993;

2 행이 갱신되었습니다.

 

다른세션에서 인덱스를 생성하려하거나 재구축 또는 삭제하려하는게 안된다.

SQL> alter index test1_sal rebuild reverse;
alter index test1_sal rebuild reverse
*
1행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

SQL> /

인덱스가 변경되었습니다.

SQL> /
alter index test1_sal rebuild reverse
*
1행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

SQL> drop index test1_sal;
drop index test1_sal
*
1행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

SQL> /

인덱스가 삭제되었습니다.

SQL> create index test1_sal
2 on test1(sal);
on test1(sal)
*
2행에 오류:
ORA-00054: 자원이 사용중이고, NOWAIT가 지정되어 있습니다

다음 실습은 sh 유져에서 작업할것이다. sh 계정을 활성화시키시요 ( 유쌤 ^^)

SQL> create index sales_amount_sold
2 on sales(amount_sold);

인덱스가 생성되었습니다.

다른세션에서

SQL> update sales
2 set amount_sold=0
3 where prod_id=250;

475 행이 갱신되었습니다.

책에 나온 내용과 테스트가 틀리다. 책이 틀린것 같다. ( 또 오라클이 오버했네요. 안되는거 된다고 하고...)

그런데 재구축은 책이 맞습니다.

먼저 online 을 붙이지 않고 실습하고

alter index slaes_amount_sold rebuild ;

다른세션에서

SQL> update sales
2 set amount_sold=0
3 where prod_id=250;

475 행이 갱신되었습니다.

online 붙이고 실습하라

alter index slaes_amount_sold rebuild online;

SQL> update sales
2 set amount_sold=0
3 where prod_id=250;

475 행이 갱신되었습니다.

☞ 12-23 index 유효성 검사

SQL> analyze index sales_amount_sold validate structure;

SQL> select blocks, pct_used, distinct_keys
2 , lf_rows, del_lf_rows
3 from index_stats;

BLOCKS PCT_USED DISTINCT_KEYS LF_ROWS DEL_LF_ROWS
---------- ---------- ------------- ---------- -----------
2816 90 6755 1016271 0

del_lf_rows 와 lf_rows 의 비율이 30% 를 초과하는 경우에 인덱스를 재구성한다

☞ 12-23 사용되는 index 와 안되는 인덱스 구별 (9i 뉴피쳐) SQL> alter index sales_amount_sold monitoring usage; 인덱스가 변경되었습니다. SQL> select * from v$object_usage; 다른세션에서 select * from sales where amount_sold=250 / 그리고 다시 SQL> select * from v$object_usage; 보면 use 가 yes 로 나올것이다. ☞ 12-24 인덱스 병합 alter index orders_id_idx coalesce;

coalesce 작업을 수행하기 전 첫번때 두개의 최하위 블록은 50%가 채워진 상태이고, 인덱스가 단편화 되었으므로 병합하여 하나로 합친다.

☞ 12-25 인덱스 및 유효성 검사

ANALYZE INDEX orders_region_id_idx
VALIDATE STRUCTURE;

인덱스를 분석하여 다음을 수행
* 모든 인덱스 블록에 대해 손상된 블록을 확인. 이명령을 수행해도 인덱스 항목이 테이블의 데이터에 대응되는지 여부는 확인되니 않음

* index_stats 뷰를 인덱스 정보로 채움

SELECT (del_lf_rows_len/ lf_lows_len) * 100 as balancing from index_stats;

15% 만 rebuilding 함

☞ 12-29 사용 되지 않은 익덱스 식별

*인덱스 사용에 대한 모니터를 시작하는 방법

ALTER INDEX hr.dept_id_idx
MONITORING USAGE

* 인덱스 사용에 대한 모니터를 중지하는 방법

ALTER INDEX hr.dept_id_idx
NOMONITORING USAGE

==>인덱스 사용에 대한 통계를 수집하여 V$OBJECT_USAGE에 표시

SELECT index_name, used from V$OBJECT_USAGE;