[Oracle] invalid 된 index를 rebuild 하기

💡 DB reorg 작업이란?

데이터 update를 반복하고 insert와 delete를 반복하다 보면 row migration 현상이 발생함. row migration 현상이 많이 발생하면 검색 성능이 떨어짐.
--> 이를 해결하기 위해서 db reorg 작업을 함

---

💡 DB reorg 작업하는 법

1. 테이블을 다른 테이블 스페이스로 move 시키고
2. 관련된 인덱스를 rebulid 함

---

[실습1] row migration 현상이 일어났다고 가정하고 db reorg 작업을 수행하세요

■ 실습1.  invalid 된 인덱스를 rebuild 하기

#1.  scott 유져에서  demo 스크립트를 다시 수행합니다.
@demo

#2.  emp 테이블의 sal에 인덱스를 생성합니다.

 create  index  emp_sal   on  emp(sal);

 select  index_name, status 
  from  user_indexes
  where  table_name='EMP';

 select  /*+ index(emp emp_sal) */ ename, sal
    from emp
    where  sal = 3000;


#3.  emp 테이블을 다른 테이블 스페이스로 이동

 왜 move 시키는가?    db org 작업 때문입니다. 

select  table_name,  tablespace_name
  from   user_tables
  where  table_name='EMP';

select  tablespace_name  from  dba_tablespaces;

alter  table  emp   move   tablespace   ts05;

select  table_name,  tablespace_name
  from   user_tables
  where  table_name='EMP';


#4.  emp_sal 인덱스의 상태를 확인

-- invalid 됨 (UNUSABLE)
select  index_name, status
  from  user_indexes
  where  table_name='EMP';

-- 에러발생
 select  /*+  index(emp emp_sal)  */  ename, sal
   from  emp
   where  sal = 3000;


#5.  emp_sal 인덱스의 상태를 정상으로 회복 시킵니다. 

alter   index   emp_sal   rebuild  online;

-- status가 다시 VALID로 바뀜
select  index_name,  status
  from  user_indexes
  where  table_name='EMP';

select /*+ index(emp  emp_sal)  */ ename, sal
  from  emp
  where  sal = 3000;

---

문제1. emp 테이블에 인덱스를 다음과 같이 4개를 걸고 dept 테이블에도 인덱스를 2개 거시오. 그리고 emp와 dept를 다른 테이블스페이스로 move 하고 관련된 unusable 된 인덱스를 rebuild 하시오

@demo
create  index  emp_sal  on emp(sal);
create  index  emp_job on emp(job);
create  index  emp_mgr  on emp(mgr);
create  index  emp_empno  on emp(empno);
create  index  dept_loc  on dept(loc);
create  index  dept_dname on dept(dname);

create  tablespace  ts507  
  datafile '/home/oracle/ts507.dbf' size 50m;
  
alter  table emp  move  tableSpace  ts507;
alter  table dept  move  tableSpace  ts507;

-- invalid 상태
select index_name, status
 from user_indexes
 where table_name in ('EMP', 'DEPT');

답:

create   or  replace   procedure   rebuild_index 
 authid current_user  
 is  
 begin
        for  i  in  (  select  index_name
                        from   user_indexes
                        where  status='UNUSABLE')   loop

       execute  immediate 'alter  index  ' || i.index_name  || ' rebuild  online ' ;
     
     end  loop;
end;
/

exec rebuild_index;

-- valid로 바뀜
select index_name, status
 from user_indexes
 where table_name in ('EMP', 'DEPT');

---

[실습2] DBA는 아침마다 프로시져나 함수들의 상태를 확인해야하는데 상태가 INVALID 되어있으면 VALID 시켜야합니다. 이를 테스트하시오

■ 실습1.  INVALID 된 프로시져를 다시 컴파일 하기

#1.  프로시져를  생성합니다. 
connect  scott/tiger

create  or replace  procedure  pro1
is
  v_sal   emp.sal%type;
begin
  select  sal  into  v_sal
     from emp
      where  ename='SCOTT';
end;
/


#2.  프로시져와 관련된 테이블을 DROP 합니다.

orcl(SCOTT) > drop table emp;


#3.  DROP 된 테이블을 flashback 으로 복구합니다.

orcl(SCOTT) > flashback table emp to before drop;


#4.  프로시져의 상태를 확인합니다.

-- INVALID 상태
orcl(SCOTT) > select object_name, status
                    from  user_objects
                   where object_name='PRO1';


#5.  프로시져를 compile 합니다. 

orcl(SCOTT) > alter procedure pro1 compile;

-- VALID 상태로 변경됨
orcl(SCOTT) > select object_name, status
                    from  user_objects
                   where object_name='PRO1';

---

문제1. emp 테이블을 다시 drop 하고 flashback한 다음 오렌지와 sql developer에서 invalid 된 프로시져를 쉽게 확인해보시오

오렌지에서는 schema browser 클릭

---

[실습3] invalid 된 객체가 많을 때 한번에 valid 시키는 방법

ㄴ 버튼 클릭