728x90
반응형
SMALL
#) partition table 생성
create table hr.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.
#) 통계 수집, granularity 작업
exec dbms_stats.gather_table_stats('hr', 'emp_local', granularity=>'auto');=> PL/SQL procedure successfully completed.
#) partition 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_LOCAL';PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------- -------------------- -------------------- ---------- ---------- -----------
P1 20000 USERS 19999 156 44
P2 40000 USERS 20000 157 45
P3 80000 USERS 40000 296 45
P4 100000 USERS 20000 157 45
P5 120000 USERS 7001 67 45
PMAX MAXVALUE USERS 0 0 0
#) partition 확인
select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_LOCAL';PARTITION_NAME PARTITION_POSITION HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------- ------------------ -------------------- -------------------- ---------- ---------- -----------
P1 1 20000 USERS 19999 156 44
P2 2 40000 USERS 20000 157 45
P3 3 80000 USERS 40000 296 45
P4 4 100000 USERS 20000 157 45
P5 5 120000 USERS 7001 67 45
PMAX 6 MAXVALUE USERS 0 0 0
#) 생성된 table 확인
select num_rows, blocks, avg_row_len
from user_tables
where table_name = 'EMP_LOCAL'; NUM_ROWS BLOCKS AVG_ROW_LEN
---------- ---------- -----------
107000 833 45
#) 확인
select * from hr.emp_local where employee_id = 1000;
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 140 |
| 1 | PARTITION RANGE SINGLE| | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 140 |
|* 2 | TABLE ACCESS FULL | EMP_LOCAL | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 140 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=1000)table access full => 비효율성의 i/o 발생
#) 범위 스캔 확인
select count(*) from hr.emp_local where employee_id between 1000 and 25000; COUNT(*)
----------
24001
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 279 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 279 |
| 2 | PARTITION RANGE ITERATOR| | 1 | 24002 | 1 | 2 | 24001 |00:00:00.01 | 279 |
|* 3 | TABLE ACCESS FULL | EMP_LOCAL | 2 | 24002 | 1 | 2 | 24001 |00:00:00.01 | 279 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("EMPLOYEE_ID"<=25000 AND "EMPLOYEE_ID">=1000))=> Pstart | Pstop: partition 1번과 2번 을 보고 출력했다는 의미
■ local partition index
- 로컬 파티션 인덱스는 파티션 테이블의 각 파티션과 인덱스의 각 파티션이 1:1로 매핑되는 인덱스이다.
- 테이블 파티션의 개수와 인덱스 파티션의 개수가 일치하며, 파티션 테이블의 파티션 키와 파티션 인덱스의 인덱스 키가 일치한다.
- 유지관리: 오라클이 자동으로 관리한다. 특정 파티션에 변경이 있어도 다른 파티션에 영향을 주지 않는다.
ex) 삭제 drop partition 한다면? 인덱스 파티션도 같이 삭제 된다.
#1. prefixed: 파티션 인덱스를 생성할 때 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 의미.(조합 파티션을 만들지 않고서는 의미가 없다.)
#2. nonprefixed: 파티션 인덱스를 생성할 때 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 의미.
tip) index
#1) index 수정시: 정렬이 되어 있어 수정이 되지 않고 해당 data는 삭제되고 새롭게 data 들어간다
#2) index의 pctfree: split 발생. rowmigration 발생X
#) 파티션의 index 생성
-> 파티션의 키 컬럼을 갖고 인덱스 생성
-> [해석]: employee_id 를 갖고 table의 index, partition의 index 를 생성 할 때, 'local' 같이 써주면 인덱스 생성 가능하다.
create unique index hr.emp_local_idx on hr.emp_local(employee_id) local;=> Index created.
#) 파티션 index 확인
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;INDEX_NAME UNIQUENES LOCALI ALIGNMENT PAR PARTITION_COUNT
------------------------------ --------- ------ ------------ --- ---------------
EMP_LOCAL_IDX UNIQUE LOCAL PREFIXED YES 6
#) 1000번 사원 조회
select * from hr.emp_local where employee_id = 1000;
#) 실행 계획 확인, partition 추가
select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 3 | 1 |
| 1 | PARTITION RANGE SINGLE | | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| EMP_LOCAL | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
|* 3 | INDEX UNIQUE SCAN | EMP_LOCAL_IDX | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
---------------------------------------------------------------------------------------------------------------------------------------
-- 파티션에서도 index scan 한다.
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"=1000)
#) 범위 스캔 확인
select count(*) from hr.emp_local where employee_id between 1000 and 25000; COUNT(*)
----------
24001
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 53 | 50 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 53 | 50 |
| 2 | PARTITION RANGE ITERATOR| | 1 | 24002 | 1 | 2 | 24001 |00:00:00.01 | 53 | 50 |
|* 3 | INDEX RANGE SCAN | EMP_LOCAL_IDX | 2 | 24002 | 1 | 2 | 24001 |00:00:00.01 | 53 | 50 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID">=1000 AND "EMPLOYEE_ID"<=25000)
#) 힌트 사용(index_rs), 범위 스캔 확인
select /*+ index_rs(e emp_local_idx) */ count(*)
from hr.emp_local e
where employee_id between 1000 and 25000; COUNT(*)
----------
24001
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 53 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 53 |
| 2 | PARTITION RANGE ITERATOR| | 1 | 24002 | 1 | 2 | 24001 |00:00:00.01 | 53 |
|* 3 | INDEX RANGE SCAN | EMP_LOCAL_IDX | 2 | 24002 | 1 | 2 | 24001 |00:00:00.01 | 53 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID">=1000 AND "EMPLOYEE_ID"<=25000)
#) 인덱스의 partition 이름 확인
select index_name, partition_name
from user_ind_partitions
where index_name = 'EMP_LOCAL_IDX';INDEX_NAME PARTITION_NAME
------------------------------ --------------------
EMP_LOCAL_IDX P1
EMP_LOCAL_IDX P2
EMP_LOCAL_IDX P3
EMP_LOCAL_IDX P4
EMP_LOCAL_IDX P5
EMP_LOCAL_IDX PMAX
■ partition global index
- 파티션 테이블의 파티션 개수와 인덱스 파티션의 파티션 개수가 서로 다르다.
- 파티션 테이블의 파티션 키와 인덱스 파티션의 인덱스 키가 일치하지 않는다.
- 유지관리: 사용자(DBA)가 직접 해야 한다.
#) partition table 생성
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.
#) 통계 수집, granularity 작업
exec dbms_stats.gather_table_stats('hr', 'emp_global', granularity=>'auto');=> PL/SQL procedure successfully completed.
#) partition 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_GLOBAL';PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------- -------------------- -------------------- ---------- ---------- -----------
P1 20000 USERS 19999 156 44
P2 40000 USERS 20000 157 45
P3 80000 USERS 40000 296 45
P4 100000 USERS 20000 157 45
P5 120000 USERS 7001 67 45
PMAX MAXVALUE USERS 0 0 0
#) index 생성
-> 다른 컬럼(hire_date)을 갖고 index 생성해야 할 경우
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.
#) 파티션 index 확인
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;INDEX_NAME UNIQUENES LOCALI ALIGNMENT PAR PARTITION_COUNT
------------------------------ --------- ------ ------------ --- ---------------
EMP_GLOBAL_IDX NONUNIQUE GLOBAL PREFIXED YES 4
#) index 생성 되었는지 확인
-> index's pctfree: split 현상을 방지하기 위함
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';INDEX_NAME PARTITION_NAME HIGH_VALUE BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR LAST_ANAL PCT_FREE
-------------------- -------------------- ------------------------------ ---------- ----------- ---------- ----------------- --------- ----------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00' 1 77 24000 15549 27-FEB-24 10
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00' 1 93 29000 20001 27-FEB-24 10
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00' 1 77 24000 17041 27-FEB-24 10
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX PMAX MAXVALUE 1 97 30000 20003 27-FEB-24 10
#) full scan 확인
select * from hr.emp_global where employee_id = 1000;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 140 |
| 1 | PARTITION RANGE SINGLE| | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 140 |
|* 2 | TABLE ACCESS FULL | EMP_GLOBAL | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 140 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID"=1000)=> i/o 발생량 많음
#) 날짜 범위 스캔
select count(*)
from hr.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
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 85 | 77 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 85 | 77 |
| 2 | PARTITION RANGE SINGLE| | 1 | 15278 | 1 | 1 | 1000 |00:00:00.01 | 85 | 77 |
|* 3 | INDEX FAST FULL SCAN | EMP_GLOBAL_IDX | 1 | 15278 | 1 | 1 | 1000 |00:00:00.01 | 85 | 77 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("HIRE_DATE"<=TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HIRE_DATE">=TO_DATE('
2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))=> index fast full scan → hire_date: not null 제약조건, buffer pinning 은 돌아가지 않음!
#) 제약 조건 확인
desc emp_global Name Null? Type
------------- -------- -------------
EMPLOYEE_ID NUMBER
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
#) 날짜 범위 스캔, 힌트 사용(index range scan 유도)
select /*+ index_rs(e emp_global_idx) */ count(*)
from hr.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'));--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 5 |
| 2 | PARTITION RANGE SINGLE| | 1 | 15278 | 1 | 1 | 1000 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | EMP_GLOBAL_IDX | 1 | 15278 | 1 | 1 | 1000 |00:00:00.01 | 5 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("HIRE_DATE">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"HIRE_DATE"<=TO_DATE(' 2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
=> i/o 줄이기 위함
=> index range scan Buffers i/o 확인 → buffer pinning 은 돌아감.
#) partition 확인
select partition_name, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_GLOBAL';PARTITION_NAME HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------- ------------------------------ -------------------- ---------- ---------- -----------
P1 20000 USERS 19999 156 44
P2 40000 USERS 20000 157 45
P3 80000 USERS 40000 296 45
P4 100000 USERS 20000 157 45
P5 120000 USERS 7001 67 45
PMAX MAXVALUE USERS 0 0 0
#) index에서 STATUS 확인!
- index 'USABLE' 상태 확인
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';INDEX_NAME PARTITION_NAME HIGH_VALUE BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR LAST_ANAL PCT_FREE STATUS★
-------------------- --------------- ------------------------------ ---------- ----------- ---------- ----------------- --------- ---------- --------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00' 1 77 24000 15549 27-FEB-24 10 USABLE(실행 가능)
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00' 1 93 29000 20001 27-FEB-24 10 USABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00' 1 77 24000 17041 27-FEB-24 10 USABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX PMAX MAXVALUE 1 97 30000 20003 27-FEB-24 10 USABLE[시나리오]
#) partition 삭제
alter table hr.emp_global drop partition p4;=> Table altered.
#) index에서 STATUS 확인!
- status 상태 확인 'USABLE' => 'UNUSABLE'
-> 특정 partition 삭제하면, 전체 index 사용불가된다.
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';INDEX_NAME PARTITION_NAME HIGH_VALUE BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR LAST_ANAL PCT_FREE STATUS
-------------------- --------------- ------------------------------ ---------- ----------- ---------- ----------------- --------- ---------- --------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00' 1 77 24000 15549 27-FEB-24 10 UNUSABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00' 1 93 29000 20001 27-FEB-24 10 UNUSABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00' 1 77 24000 17041 27-FEB-24 10 UNUSABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX PMAX MAXVALUE 1 97 30000 20003 27-FEB-24 10 UNUSABLE
#) 날짜 범위 스캔, 장애 발생
select /*+ index_rs(e emp_global_idx) */ count(*)
from hr.emp_global e
where hire_date between to_date('2001-01-01', 'yyyy-mm-dd')
and to_date('2001-12-31', 'yyyy-mm-dd');=> 오류 발생
=> ORA-01502: index 'HR.EMP_GLOBAL_IDX' or partition of such index is in unusable state
#) index 상태 다시 확인
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';INDEX_NAME PARTITION_NAME HIGH_VALUE BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR LAST_ANAL PCT_FREE STATUS
-------------------- --------------- ------------------------------ ---------- ----------- ---------- ----------------- --------- ---------- --------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00' 1 77 24000 15549 27-FEB-24 10 UNUSABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00' 1 93 29000 20001 27-FEB-24 10 UNUSABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00' 1 77 24000 17041 27-FEB-24 10 UNUSABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX PMAX MAXVALUE 1 97 30000 20003 27-FEB-24 10 UNUSABLE
[해결방안]
1) 새롭게 생성: 대용량의 table이라면 리스크가 크다!
2) 로오그, 리빌드 작업
#) 2번 방법, 오류발생
alter index hr.emp_global_idx rebuild;=> ORA-14086: a partitioned index may not be rebuilt as a whole
--> [해석]: 전체로 복구 못하니, 각각 partition을 리빌드 작업 해줘야 한다.
#) 개별로 작업
alter index hr.emp_global_idx rebuild partition p2004;=> Index altered.
alter index hr.emp_global_idx rebuild partition p2005;
alter index hr.emp_global_idx rebuild partition p2006;
alter index hr.emp_global_idx rebuild partition pmax;=> Index altered.
#) index에서 STATUS 확인!
- status 상태 확인 'USABLE'
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';INDEX_NAME PARTITION_NAME HIGH_VALUE BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR LAST_ANAL PCT_FREE STATUS
-------------------- --------------- ------------------------------ ---------- ----------- ---------- ----------------- --------- ---------- --------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00' 1 63 19515 12634 27-FEB-24 10 USABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00' 1 76 23580 16252 27-FEB-24 10 USABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00' 1 63 19514 13848 27-FEB-24 10 USABLE
, 'SYYYY-MM-DD HH24:MI:SS', 'N
LS_CALENDAR=GREGORIA
EMP_GLOBAL_IDX PMAX MAXVALUE 1 79 24391 16258 27-FEB-24 10 USABLE
#) 복구 확인 작업
select /*+ index_rs(e emp_global_idx) */ count(*)
from hr.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=> 그 전보다는 count가 적다
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last partition'));-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 1 |00:00:00.01 | 4 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 4 | 3 |
| 2 | PARTITION RANGE SINGLE| | 1 | 15278 | 1 | 1 | 813 |00:00:00.01 | 4 | 3 |
|* 3 | INDEX RANGE SCAN | EMP_GLOBAL_IDX | 1 | 15278 | 1 | 1 | 813 |00:00:00.01 | 4 | 3 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("HIRE_DATE">=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "HIRE_DATE"<=TO_DATE('
2001-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| 재분배 방식, pq_distribute (0) | 2024.02.28 |
|---|---|
| Parallel, 병렬 처리 (0) | 2024.02.28 |
| 정적(static) 파티션 pruning, 동적(dynamic) 파티션 pruning (0) | 2024.02.27 |
| 부분 범위 처리, 운반 단위, arraysize 조절 (1) | 2024.02.27 |
| Data Pump_expdp, impdp (1) | 2024.02.26 |