2024년 2월 27일

 

1교시

 

drop table hr.sal_emp purge;

 

샘플 테이블 만들기

HR@ora11g>

create table sal_emp
nologging
as 
select 
    rownum as employee_id, 
    last_name, 
    first_name, 
    hire_date, 
    job_id, 
    salary, 
    manager_id, 
    department_id
from 
    hr.employees e, 
    (select level as id from dual connect by level <= 5000);

 

Table created.

 

HR@ora11g> select * from sal_emp where salary between 5000 and 8000;

125000 rows selected.

 

HR@ora11g> exec dbms_stats.gather_table_stats('hr', 'sal_emp')
PL/SQL procedure successfully completed.

 

HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'SAL_EMP';

 

  NUM_ROWS     BLOCKS AVG_ROW_LEN
----------------------------------------------------------------
    535000       3774          45

 

HR@ora11g> select * from hr.sal_emp;

535000 rows selected.

 

# 부분 범위 처리

SQL 에서 주어진 조건을 만족하는 데이터를 전체 범위로 처리하지 않고 운반단위 (arraysize) 까지만 먼저 처리하여

그 결과를 유저 프로세스에 전달하고 다음 작업을 계속하겠다는 사용자의 요구가 있을 때까지

(예를 들면, SQLD 에서 사용자가 스크롤바를 내릴 때까지)

잠정적으로 수행을 멈추는 처리 방식

 

예를 들어 1만 건의 데이터를 스캔해야 할 때 1천 건만 읽어서 운반단위를 채울 수 있다면

1만 건을 한꺼번에 다 읽지 않고, 천 개씩 10번으로 나누어서 처리할 수 있다.

 

# 부분 범위 처리를 할 수 없는 경우

- 그룹함수를 사용한 경우

- order by 절을 쓸 때 (정렬작업이 일어나는 경우)

  order by 절을 쓰면서 부분범위 처리를 할 수 있으려면?

  인덱스를 설계해야 한다.

  (인덱스가 만들어져 있으면 굳이 sorting 하지 않고 그냥 순서대로 읽어서 fetch 할 수 있으므로)

- union, minus, intersect 를 사용한 경우 (중복 제거를 위해 정렬작업을 해야 하므로)

  그러니 union 대신 union all 과 not exists 를 쓰자. (union all 은 중복 상관 없으므로 부분 범위 처리 가능)

  minus 대신 not exists 연산자, intersect 대신 exists 연산자 쓰기

  (개발자들이 쿼리를 잘못 짜면 메모리 사용량 나빠짐... 주의)

 

# 부분 범위 처리를 할 수 없는 경우 대체할 수 있는 방안은?

- order by 절에 사용된 컬럼에 index 를 이용하면 부분 범위 처리할 수 있다.

- union → union all + not exists

- minus → not exists

- intersect → exists

 

# 부분 범위 처리결과 집합을 전송:

전체 데이터를 한꺼번에 연속적으로 처리하지 않고, 사용자로부터 fetch call 이 있을 때마다

일정량씩 나누어 전송하는 의미 (유저 프로세스 입장에서)

오라클은 데이터를 클라이언트에게 전송할 때, 일정량씩 나누어 전송하며

이 설정은 arraysize 파라미터로 설정하여 운반 단위를 조절한다.

 

대용량 데이터를 fetch 해야 할 때 arraysize 를 크게 설정했을 때의 이점

1. fetch call 횟수가 줄어들어 네트워크 부하가 감소하고 쿼리 성능이 좋아진다.

2.  서버 프로세스가 읽어야 할 블록의 개수가 감소한다. (한번 I/O call 을 이용할 때 arraysize 만큼 읽어들일 수 있으므로)

 

HR@ora11g> show arraysize
arraysize 15

(arraysize 가 15라는 건 유저 입장에서 active set 결과를 15행만 받을 수 있다는 뜻,

 arraysize 는 유저 프로세스가 서버 프로세스로부터 받을 수 있는 데이터 행의 수)

 

HR@ora11g> set autotrace trace stat

 

얘를 다시 수행해보면: 수행은 하지만 화면상에 출력해주지는 않는다. (set autotrace trace stat 덕)

HR@ora11g> select * from hr.sal_emp;

 

 

HR@ora11g> select * from hr.sal_emp;
535000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      39135  consistent gets             --- 읽어들인 블록의 개수
          0  physical reads
          0  redo size
   31351975  bytes sent via SQL*Net to client
     392746  bytes received via SQL*Net from client
      35668  SQL*Net roundtrips to/from client            --- fetch count (=fetch call)
          0  sorts (memory)
          0  sorts (disk)
     535000  rows processed           --- 읽은 row 의 수

 

전체 행의 수 / fetch count = 535000 / 35668 = 14.99943927329819 = 아까 나온 array size

array size 만큼 fetch call 이 발생한 것

 

즉, SQL*Net roundtrips to/from client 는 array size 의 영향을 받는다.

 

대용량 테이블을 다룰 때에는 arraysize를 바꾸어서 

 

HR@ora11g> show arraysize
arraysize 15

 

HR@ora11g> set arraysize 100

arraysize 를 100으로 = 100개씩 유저에게 전달하도록 함

 

HR@ora11g> set autotrace trace stat

 

HR@ora11g> select * from hr.sal_emp;

HR@ora11g> select * from hr.sal_emp;

535000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       9030  consistent gets
          0  physical reads
          0  redo size
   27410765  bytes sent via SQL*Net to client
      59259  bytes received via SQL*Net from client
       5351  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     535000  rows processed

블록 I/O 수가 훨씬 줄어들어 있는 것이 보인다. (39135 > 9030)

 

HR@ora11g> set arraysize 1000

active set 결과를 1000개씩 유저 프로세스에 전달하도록 함

 

HR@ora11g> set autotrace trace stat
HR@ora11g> select * from hr.sal_emp;

HR@ora11g> select * from hr.sal_emp;

535000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4244  consistent gets
          0  physical reads
          0  redo size
   26784815  bytes sent via SQL*Net to client
       6294  bytes received via SQL*Net from client
        536  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     535000  rows processed

 

HR@ora11g> set arraysize 2000
HR@ora11g> set autotrace trace stat
HR@ora11g> select * from hr.sal_emp;

HR@ora11g> select * from hr.sal_emp;

535000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3979  consistent gets
          0  physical reads
          0  redo size
   26750105  bytes sent via SQL*Net to client
       3357  bytes received via SQL*Net from client
        269  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     535000  rows processed

 

더 이상 드라마틱하게 줄어들지는 않음.

 

HR@ora11g> set arraysize 5000
HR@ora11g> set autotrace trace stat
HR@ora11g> select * from hr.sal_emp;

HR@ora11g> select * from hr.sal_emp;

535000 rows selected.

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3819  consistent gets
          0  physical reads
          0  redo size
   26729175  bytes sent via SQL*Net to client
       1586  bytes received via SQL*Net from client
        108  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     535000  rows processed

---

2교시

 

53만 건의 데이터를 담고 있는 sal_emp 테이블을 SQLD 에서 조회해보면: 50건씩만 나온다.

50건 인출되고, 또 50건 인출되고, ... 그런 식 (페이징 처리)

 

select * from hr.sal_emp;

 

좋은 점: 50만 건 데이터 중에서 맨 위의 50건만 보려고 하면, 데이터를 몽땅 불러올 필요없이

앞부분 데이터만 보고 끝낼 수 있다.

 

SQLD 에서 arraysize 설정하는 방법

 

작업 표시줄에서 도구 클릭 > 환경설정

 

 

 

HR@ora11g> set autotrace off

HR@ora11g> set arraysize 15

 

gather_plan_statistics 매번 써주기 귀찮으니까 이렇게 ↓ (무조건 통계 수집)

HR@ora11g> alter session set statistics_level = all;
Session altered.

 

select * from sal_emp where salary between 5000 and 8000;

125000 rows selected.

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

버퍼 수 보소...

 

HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'SAL_EMP';

 

  NUM_ROWS     BLOCKS    AVG_ROW_LEN
----------------------------------------------------------------
    535000                    3774             45                      --- 블록 수가 3774인데 버퍼 수가 12013... 실화냐...

 

이건 arraysize 때문인데, arraysize 가 15로 되어 있으면

풀 테이블 스캔을 할 때 한 블록 안에 15행 이상이 들어있더라도 15행까지밖에 읽고 전달할 수 있기 때문에

한 블록을 여러 번 액세스하게 된다.

 

HR@ora11g> show parameter db_file_multiblock_read_count

 

arraysize 증가시키기

HR@ora11g> set arraysize 1000

 

세션이 만약 바뀌었다가 다시 돌아왔다면 아래 문장 다시 수행해주고

alter session set statistics_level = all;

 

select * from sal_emp where salary between 5000 and 8000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

버퍼 수가 많이 줄어든 것이 보인다.

 

HR@ora11g> set arraysize 2000

 

select * from sal_emp where salary between 5000 and 8000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

arraysize 가 1000일 때와 버퍼수가 크게 달라지지 않았다.

 

HR@ora11g> set arraysize 10000
SP2-0267: arraysize option 10000 out of range (1 through 5000)

set arraysize 는 5000까지가 한계

 

그럼 5000으로 설정해보자.

HR@ora11g> set arraysize 5000

 

select * from sal_emp where salary between 5000 and 8000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

arraysize를 5000으로 했는데도 버퍼수가 별로 달라지지 않음...

 

arraysize를 1000정도로만 해놔도 괜찮을 듯.

다시 1000으로 바꾸자.

 

HR@ora11g> set arraysize 1000

 

hr.sal_emp 테이블의 월급 컬럼에 인덱스 걸기

HR@ora11g> create index hr.sal_emp_idx on hr.sal_emp(salary);

Index created.

 

테이블이 워낙 커서 인덱스 스캔을 안할 수도 있으므로 인덱스 스캔 유도하기

select /*+ index(s sal_emp_idx) */ * from sal_emp s where salary between 5000 and 8000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

풀 테이블 스캔했을 때보다 I/O 가 더 많이 발생함... 버퍼 수 실화냐...

(대용량일 때는 인덱스 스캔이 그리 좋지 않을 수 있다. 클러스터링 팩터가 나쁜 경우 더더욱 그러함)

 

이게 풀 테이블 스캔시의 버퍼 수

---

3교시

 

# 테이블 통계 수집, 관련된 인덱스도 통계 수집

 

테이블 통계 수집을 하면서, 테이블과 관련 있는 인덱스까지 통계수집을 하려면: cascade 옵션

HR@ora11g>

exec dbms_stats.gather_table_stats('HR', 'SAL_EMP', CASCADE => TRUE)

PL/SQL procedure successfully completed.

 

인덱스만 통계 수집하기

HR@ora11g>

exec dbms_stats.gather_index_stats('HR', 'SAL_EMP_IDX')

PL/SQL procedure successfully completed.

 

HR@ora11g>

select num_rows, blocks, avg_row_len, last_analyzed from user_tables where table_name = 'SAL_EMP';

 

HR@ora11g>
select index_name, num_rows, blevel, leaf_blocks, clustering_factor, last_analyzed
from user_indexes
where table_name = 'SAL_EMP';

HR@ora11g> drop table sal_emp;
Table dropped.

파티션을 가지고 새로 대용량 테이블 만들기

 

HR@ora11g>

create table hr.sal_emp
partition by range(salary)
(partition p1 values less than(5000),
 partition p2 values less than(10000),
 partition p3 values less than(30000),
 partition pmax values less than (maxvalue))
nologging
as select rownum as employee_id, last_name, first_name, hire_date, job_id, salary, manager_id, department_id
from hr.employees e, (select level as id from dual connect by level <= 5000);

Table created.

exec dbms_stats.gather_table_stats('HR', 'SAL_EMP', granularity => 'auto')

PL/SQL procedure successfully completed.

 

HR@ora11g>
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

 

select * from hr.sal_emp where salary between 5000 and 8000;

 

1행 single: 저 파티션만 읽었다는 뜻

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

select * from hr.sal_emp where salary between 5000 and 8000;

 

파티션 넘버링: 파티션의 시작(Pstart)과 파티션의 끝(Pstop)도 보여준다.

1행을 보면: 파티션 하나만 읽어서 single 나옴.

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

 

# 파티션 테이블 스캔시 정보

Pstart: 시작 파티션 번호

Pstop: 종료 파티션 번호

 

# 파티션 프루닝 Partition Pruning

SQL 조건절을 분석하여, 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능

 

HR@ora11g> show arraysize
arraysize 1000

 

HR@ora11g> set autotrace trace stat

 

HR@ora11g>  select * from hr.sal_emp where salary between 5000 and 8000;

125000 rows selected.

 

125000 / 126 = 992.0634920634921 = 대략 1천

 

HR@ora11g> set autotrace off

 

partition_position 컬럼은 파티션을 만든 순서를 보여준다.

select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';

---

4교시

 

# 정적 (static) 파티션 프루닝 partition pruning

- 파티션 키 컬럼을 상수 조건으로 조회

- 액세스할 파티션을 쿼리 최적화 시점에 결정 (실행 계획을 생성할 때)

 

HR@ora11g> alter session set statistics_level = all;

select * from sal_emp where salary between 5000 and 8000;

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

# 동적 (dynamic) 파티션 프루닝 partition pruning

- 파티션 키 컬럼을 바인드 변수로 조회

- 쿼리의 최적화 시점에 액세스할 파티션을 미리 결정할 수 없다. (어떤 값이 들어올지 모르므로)

  실행 시점에 결정된다.

 

HR@ora11g> var b_start number
HR@ora11g> var b_stop number
HR@ora11g> execute :b_start := 5000
PL/SQL procedure successfully completed.

HR@ora11g> execute :b_stop := 8000
PL/SQL procedure successfully completed.

 

HR@ora11g> select * from sal_emp where salary between :b_start and :b_stop;

125000 rows selected.

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

 

변수처리 안한거랑 비교해보기 (어떤 값이 들어올지 모르기 때문에 상수처리된 것과 필터 부분은 다르지만 I/O는 똑같다)

 

 

샘플 테이블 만들기

 

HR@ora11g>

create table hr.emp_non
as select rownum as employee_id, last_name, first_name, hire_date, job_id, salary, manager_id, department_id
from hr.employees e, (select level as id from dual connect by level <= 1000);

Table created.

 

통계 수집하기

exec dbms_stats.gather_table_stats('hr', 'emp_non')

PL/SQL procedure successfully completed.

select num_rows, blocks, avg_row_len, last_analyzed from user_tables where table_name = 'EMP_NON';

alter session set statistics_level = all;

Session altered.

select * from hr.emp_non where employee_id = 1000;

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

HR@ora11g> select count(*) from emp_non where employee_id between 1000 and 25000;

 

  COUNT(*)
-----------------
     24001

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

풀 테이블 스캔이 일어난 것이 보인다.

 

인덱스 생성하기

HR@ora11g> create unique index hr.emp_non_idx on hr.emp_non(employee_id);
Index created.

 

테이블에 걸린 인덱스 보기

HR@ora11g>

select index_name, num_rows, blevel, leaf_blocks, clustering_factor, last_analyzed
from user_indexes
where table_name = 'EMP_NON';

 

HR@ora11g> SELECT * from emp_non where employee_id = 100;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

힌트를 쓰지 않고도 인덱스 범위 스캔이 되는지 보기

 

HR@ora11g> select count(*) from emp_non where employee_id between 1000 and 25000;

인덱스 범위 스캔이라고 나옴 + I/O 51번 발생 + Reads 가 50번...

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

인덱스 패스트 풀 스캔 유도

HR@ora11g> select /*+ index_ffs(e emp_non_idx) */ count(*) from emp_non e where employee_id between 1000 and 25000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

I/O 가 훨씬 많이 발생한 것이 보인다.

 

인덱스 범위 스캔이 훨씬 더 성능이 좋은 이유는? 버퍼 피닝 때문에 + 클러스터링 팩터가 좋아서.

 

HR@ora11g> select index_name, num_rows, blevel, leaf_blocks, clustering_factor, last_analyzed
from user_indexes
where table_name = 'EMP_NON';

---

 

5교시

 

# 샘플 테이블 만들기

create table emp_local
partition by range(employee_id)
(partition p1 values less than(20000),
 partition p2 values less than(40000),
 partition p3 values less than(80000),
 partition p4 values less than(100000),
 partition p5 values less than(120000),
 partition pmax values less than(maxvalue))
nologging
as select rownum as employee_id, last_name, first_name, hire_date, job_id, salary,
manager_id, department_id
from employees e, (select level as id from dual connect by level <= 1000);

Table created.

HR@ora11g> exec dbms_stats.gather_table_stats('hr', 'emp_local', granularity => 'auto')
PL/SQL procedure successfully completed.

select partitIon_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_LOCAL';

 

HR@ora11g> select num_rows, blocks, avg_row_len from user_tables where table_name = 'EMP_LOCAL';

 

HR@ora11g> select * from emp_local where employee_id = 1000;

1000번 사원 조회: 1건 데이터를 찾고자 전체 파티션을 다 뒤짐

 

데이터 한 건 찾자고 I/O가 140건이나 발생한 것이 보인다.

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

 

HR@ora11g> select count(*) from emp_local where employee_id between 1000 and 25000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

1번과 2번 파티션을 몽땅 뒤적거림

 

자주 쓰는 컬럼(파티션 테이블의 키 컬럼)에 파티션 인덱스 걸기

 

HR@ora11g> create unique index hr.emp_local_idx on hr.emp_local(employee_id) local;

Index created.

 

파티션의 키 컬럼과 인덱스의 키 컬럼이 같을 때는 인덱스 만들 때 맨 뒤에 local 이라고 써주기

(유지관리는 오라클에서 알아서 해준다.)

 

※ 인덱스는 정렬이 필수이기 때문에 잘못 만든 인덱스는 업데이트할 게 아니라 지우고 다시 만들어야 한다.

※ emp 테이블의 사원번호 컬럼에 인덱스가 걸려 있고

   테이블에 사원번호가 1, 3, 5, 7 순서로 들어갔다고 하자.

   그래서 인덱스가 이미 1, 3, 5, 7 순서로 만들어져 있는데

   뒤늦게 사원번호 2번이 테이블에 들어온다면?

   이미 인덱스는 1, 3, 5, 7 순서로 만들어져 있기 때문에 2가 들어갈 자리가 없다.

   하지만 2는 꼭 1과 3 사이에 들어가야 한다. 절대 다른 리프 블록에 들어갈 수 없다.

   이럴 때 해결책: leaf block split (리프 블록의 수가 늘어남)

   하지만 이건 해결책다운 해결책이 아님, 리프 블록의 수가 늘어나서 좋을 것이 없다. (인덱스 범위 스캔할 때 문제 발생)

   그래서 leaf block split 을 해결하기 위해 인덱스는 pct free 를 가진다.

   (인덱스 행과 행 사이에 여유공간을 만들어두면 인덱스 스플릿이 덜 발생하기 때문)

   그래서 테이블 블록의 pct free 와 인덱스 블록의 pct free 는 서로 다른데

   maxtrans 값을 보장하고자 하는 것은 공통점이지만

   테이블 블록에서는 row migration 을 막기 위해서, 인덱스 블록에서는 index split 을 막기 위해서

   pct free 를 둔다는 것은 차이점이라고 할 수 있다.

 

HR@ora11g>

select i.index_name, i.uniqueness, p.locality, p.alignment, i.partitioned, p.partition_count
from user_indexes i, user_part_indexes p
where i.table_name = 'EMP_LOCAL'
and p.table_name = i.table_name
and p.index_name = i.index_name;

 

HR@ora11g> select * from emp_local where employee_id = 1000;

 

HR@ora11g>

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

 

HR@ora11g> select count(*) from emp_local where employee_id between 1000 and 25000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

 

아까하고 비교해 보면 I/O 가 훨씬 줄어든 것을 볼 수 있다.

HR@ora11g> select count(*) from emp_local where employee_id between 1000 and 25000;

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

파티션 풀 스캔 발생

 

HR@ora11g>

select /*+ index_rs(e emp_local_idx) */ count(*) from emp_local em where employee_id between 1000 and 25000;

인덱스 범위 스캔 유도하기

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

 

HR@ora11g> 

select index_name, partition_name
from user_ind_partitions
where index_name = 'EMP_LOCAL_IDX';

 

★ 로컬 파티션 인덱스

- 파티션 테이블의 각 파티션과 파티션 인덱스의 각 파티션이 일대일로 맵핑되는 인덱스

- 테이블 파티션의 개수와 인덱스 파티션의 개수가 일치하며, 파티션 테이블의 파티션 키와

  파티션 인덱스의 인덱스 키가 일치한다.

- 생성만 하면 유지 관리 작업은 오라클이 알아서 해주고

  다른 파티션에 아무런 영향을 주지 않는다.

 

★ 로컬 프리픽스 local prefixed index / 로컬 논 프리픽스 local non-prefixed index

- 로컬 프리픽스: 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 의미한다.

- 로컬 논 프리픽스: 파티션 인덱스를 생성할 때 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 의미한다.

---

 

6교시

 

★ 파티션 글로벌 인덱스 partition global index

- 파티션 테이블의 파티션 개수와, 인덱스 파티션의 파티션 개수가 일치하지 않는다.

- 파티션 테이블의 파티션 키와 인덱스 파티션 키가 일치하지 않는다.

- 유지관리는 사용자가 (DBA가) 직접 해야 한다.

 

create table hr.emp_global
partition by range(employee_id)
(partition p1 values less than(20000),
 partition p2 values less than(40000),
 partition p3 values less than(80000),
 partition p4 values less than(100000),
 partition p5 values less than(120000),
 partition pmax values less than(maxvalue))
nologging
as select rownum as employee_id, last_name, first_name, hire_date, job_id, salary,
manager_id, department_id
from employees e, (select level as id from dual connect by level <= 1000);

 

Table created.

 

HR@ora11g> exec dbms_stats.gather_table_stats('hr', 'emp_global', granularity => 'auto');
PL/SQL procedure successfully completed.

 

select partitIon_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_GLOBAL';

 

HR@ora11g>

create index hr.emp_global_idx on hr.emp_global(hire_date) global
partition by range(hire_date)
(partition p2004 values less than(to_date('2005-01-01', 'yyyy-mm-dd')),
 partition p2005 values less than(to_date('2006-01-01', 'yyyy-mm-dd')),
 partition p2006 values less than(to_date('2007-01-01', 'yyyy-mm-dd')),
 partition pmax values less than(maxvalue)); 
Index created.

HR@ora11g>
select i.index_name, i.uniqueness, p.locality, p.alignment, i.partitioned, p.partition_count
from user_indexes i, user_part_indexes p
where i.table_name = 'EMP_GLOBAL'
and p.table_name = i.table_name
and p.index_name = i.index_name;

글로벌 인덱스가 만들어졌다. (왜냐하면 파티션 키 컬럼과 다른 컬럼에 인덱스를 걸었기 때문에)

 

 

select index_name, partition_name, high_value, blevel, leaf_blocks, num_rows, clustering_factor, last_analyzed, pct_free
from user_ind_partitions
where index_name = 'EMP_GLOBAL_IDX';

 

인덱스를 리빌드 rebuild 해야 할 때
1. blevel 이 너무 깊을 때
2. 잎 블록이 너무 많을 때

 

HR@ora11g> select count(*) from emp_global where hire_date between to_date('2001-01-01', 'yyyy-mm-dd') and to_date('2001-12-31', 'yyyy-mm-dd');

  COUNT(*)
-----------------
      1000

 

HR@ora11g> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

인덱스 패스트 풀 스캔 발생, 버퍼 수 85...

 

not null 제약조건이 걸려 있기 때문에 인덱스만 가지고도 정확한 count 값을 구할 수 있다는 것을 알 수 있다

> 굳이 테이블 액세스 안함

 

desc emp_global

 

인덱스 범위 스캔을 유도해서 버퍼 피닝이 돌아가도록 함

HR@ora11g> select /*+ index rs(e emp_global_idx) */ count(*) from emp_global e where hire_date between to_date('2001-01-01', 'yyyy-mm-dd') and to_date('2001-12-31', 'yyyy-mm-dd');

 

  COUNT(*)
-----------------
      1000

 

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

근데 나 왜 버퍼 수 똑같아? ... 선생님은 5개만 나오셨는데?

 

select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_GLOBAL';

select index_name, partition_name, high_value, blevel, leaf_blocks, num_rows,
clustering_factor, last_analyzed, pct_free, status
from user_ind_partitions
where index_name = 'EMP_GLOBAL_IDX';

 

 

파티션 테이블 삭제
alter table hr.emp_global drop partition p4;

Table altered.

 

select index_name, partition_name, high_value, blevel, leaf_blocks, num_rows,
clustering_factor, last_analyzed, pct_free, status
from user_ind_partitions
where index_name = 'EMP_GLOBAL_IDX';

 

select /*+ index_rs(e emp_global_idx) */ count(*) from emp_global e where hire_date between to_date('2001-01-01', 'yyyy-mm-dd') and to_date('2001-12-31', 'yyyy-mm-dd');

이 상태에서 인덱스 범위 스캔을 유도하면: 인덱스가 사용 불가능한 상태라고 하면서 오류가 발생한다.

 

select /*+ index_rs(e emp_global_idx) */ count(*) from emp_global e where hire_date between to_date('2001-01-01', 'yyyy-mm-dd') and to_date('2001-12-31', 'yyyy-mm-dd')
*

ERROR at line 1:
ORA-01502: index 'HR.EMP_GLOBAL_IDX' or partition of such index is in unusable state

 

select index_name, partition_name, high_value, blevel, leaf_blocks, num_rows,
clustering_factor, last_analyzed, pct_free, status
from user_ind_partitions
where index_name = 'EMP_GLOBAL_IDX';

 

인덱스 리빌드 : 오류 나옴 (정상)

HR@ora11g> alter index hr.emp_global_idx rebuild;

alter index hr.emp_global_idx rebuild
               *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

 

글로벌 인덱스의 귀찮은 점: 각각 개별로 하나씩 re-org 해줘야 한다는 것...

HR@ora11g>

alter index hr.emp_global_idx rebuild partition p2004;

Index altered.

HR@ora11g>

alter index hr.emp_global_idx rebuild partition p2005;

Index altered.

HR@ora11g>

alter index hr.emp_global_idx rebuild partition p2006;

Index altered.

HR@ora11g>

alter index hr.emp_global_idx rebuild partition pmax;

Index altered.

 

select index_name, partition_name, high_value, blevel, leaf_blocks, num_rows,
clustering_factor, last_analyzed, pct_free, status
from user_ind_partitions
where index_name = 'EMP_GLOBAL_IDX';

 

 

인덱스 범위 스캔 유도

select /*+ index_rs(e emp_global_idx) */ count(*) from emp_global e where hire_date between to_date('2001-01-01', 'yyyy-mm-dd') and to_date('2001-12-31', 'yyyy-mm-dd');

 

  COUNT(*)
----------
       813

 

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last partition'));

 

파티션 그룹에 인덱스를 추가하는 건 안된다. (해시 스플릿 빼고는)

 

alter index emp_global_idx add partition p2007 values less than(to_date('2008-01-01', 'yyyy-mm-dd'));
> 오류 발생 (선생님도 오류 발생하심)

ORA-14640: add/coalesce index partition operation is valid only for hash partitioned global indexes

alter index emp_global_idx split partition pmax at (to_date('2008-01-01', 'yyyy-mm-dd')) into (partition p2007, partition max);

select index_name, partiton_name, high_value, blevel, leaf_blocks, num_rows, clustering_factor, last_analyzed, pct_free, status
from user_ind_partitions
where index_name = 'EMP_GLOBAL_IDX';

alter index emp_global_idx drop partition p2007;

select index_name, partiton_name, high_value, blevel, leaf_blocks, num_rows, clustering_factor, last_analyzed, pct_free, status
from user_ind_partitions
where index_name = 'EMP_GLOBAL_IDX';
> pmax 파티션만 unusable 상태로 바뀐다.

alter index hr.emp_global_idx rebuild partition pmax;
> 그래서 pmax 파티션만 rebuild 해서 usable 상태로 바꿈