| # 요약 | |
| Arraysize Allstats Last Partition # Partition Pruning # 정적(Static) 파티션 Pruning # 동적(Dynamic) 파티션 Pruning ■ 로컬 파티션 인덱스 (Local Partitioned Indexes) ■ 파티션 글로벌 인덱스 (Global Partitioned Indexes) |
|
[ Sample Table : hr.sal_emp ]
HR@ora11g> drop table hr.sal_emp purge;
Table dropped.
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 employees e, (select level as id from dual connect by level <= 5000);
Table created.
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
# 부분 범위 처리
- SQL에서 주어진 조건을 만족하는 데이터를 전체 범위로 처리하지 않고 운반단위(Arraysize)까지만 먼저 처리하여 그 결과를 유저 프로세스에 전달하고 다음 작업을 계속하겠다는 사용자의 요구가 있을 때까지 잠정적으로 수행을 멈추는 처리방식을 말한다.
- 예를 들어 10000건의 데이터를 스캔해야할 때 1000건만 읽어서 운반단위를 채울 수 있다면 10000건을 한꺼번에 다 읽지 않고 1000개씩 10번으로 나누어서 처리할 수 있다.
> SQL Developer : SQL문 조회 시 50행만 먼저 나오고 스크롤을 내려야 내용이 더 나온다.
환경설정 > 도구 > 데이터베이스 > 고급 > SQL 배열 인출 크기(50 ~ 200) 에서 설정
# 부분범위 처리를 할 수 없는 경우
- 그룹함수를 사용한 경우
- order by 절을 사용한 경우 : 정렬
- union, minus, intersect 를 사용한 경우 : 중복제거 시 sort 수행
# 부분점위 처리를 할 수 없는 경우 대체할 수 있는 방안은?
- order by 절에 사용된 컬럼에 index 를 이용하면 부분범위 처리를 할 수 있다.
- union -> union all + not exists
- minus -> not exists
- intersect -> exists
# 부분 범위 처리결과집합을 전송
- 전체 데이터를 한꺼번에 연속적으로 처리하지 않고 사용자로부터 fetch call이 있을 때마다 일정량 씩 나누어 전송하는 의미
- 오라클은 데이터를 클라이언트에게 전송할 때 일정량 씩 나누어 전송하며 이 설정은 arraysize 파라미터로 설정하여 운반단위를 조절한다.
- 대용량 데이터를 fetch해야할 때 arraysize를 크게 설정하면 이점은?
1. fetch call 횟수가 줄어들어 네트워크 부하가 감소하고 쿼리 성능이 향상된다.
2. 서버프로세스가 읽어야 할 블록 개수 감소
> arraysize : 유저프로세스가 서버프로세스한테 한번에 받을 수 있는 row 수
> 서버프로세스 입장에서 : 1000행을 전달할께
> 유저프로세스 입장에서 : 나는 15행(Arraysize) 밖에 못받으니 서버 니가 조절 좀 해서 주렴
> OLTP(빠른처리)환경말고 풀테이블스캔 대용량, 인덱스 스캔 대용량 등에서 수행
> Arraysize가 너무 크면 데이터가 더 있는줄알고 기다림이 발생할 수 있다.
# Trace Stat + Arraysize 15
HR@ora11g> select * from hr.sal_emp;
535000 rows selected.
HR@ora11g> show arraysize
arraysize 15
> 서버프로세스에서 액티브셋 결과를 15row 만큼 유저프로세스에 전달한다.
# SQL문 통계 정보
HR@ora11g> set autotrace trace stat
HR@ora11g> select * from hr.sal_emp;
535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
39134 consistent gets -- 읽은 블록 수
0 physical reads
0 redo size
31341975 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 -- 읽은 로우 수
> 읽은 로우 수 / fetch count = 535000 / 35668 = 14.999 = arraysize
# Trace Stat + Arraysize 100
# 100row씩 전달하겠다
HR@ora11g> set arraysize 100
HR@ora11g> select * from hr.sal_emp;
535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9019 consistent gets
0 physical reads
0 redo size
27400765 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
# Trace Stat + Arraysize 1000
# 1000row씩 전달하겠다
HR@ora11g> set arraysize 1000
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
26774815 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
# Trace Stat + Arraysize 2000
# 2000row씩 전달하겠다
HR@ora11g> set arraysize 2000
HR@ora11g> select * from hr.sal_emp;
535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3980 consistent gets
0 physical reads
0 redo size
26740105 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
# Trace Stat + Arraysize 5000
# 5000row씩 전달하겠다
HR@ora11g> set arraysize 5000
HR@ora11g> select * from hr.sal_emp;
535000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3818 consistent gets
0 physical reads
0 redo size
26719175 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
> 1000 이후로 I/O발생에 큰 변화가 없다. 데이터에 맞게 적절하게 조절하자.
HR@ora11g> set autotrace off
HR@ora11g> alter session set statistics_level = all;
Session altered.
# Arraysize 15
HR@ora11g> set arraysize 15
HR@ora11g> select * from sal_emp where salary between 5000 and 8000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 125K|00:00:00.05 | 11988 | 4 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 128K| 125K|00:00:00.05 | 11988 | 4 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
SYS@ora11g> show parameter db_file_multiblock_read_count
NAME TYPE VALUE
----------------------------- ------- -----
db_file_multiblock_read_count integer 92
> 테이블 블럭 크기 대비 풀스캔했다고 I/O 너무 발생한거 아닌가? > 풀테이블 스캔 시 멀티블럭보다 arraysize의 영향이 크다.
# Arraysize 1000
HR@ora11g> set arraysize 1000
HR@ora11g> select * from sal_emp where salary between 5000 and 8000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 125K|00:00:00.03 | 3837 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 128K| 125K|00:00:00.03 | 3837 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
# Arraysize 2000
HR@ora11g> set arraysize 2000
HR@ora11g> select * from sal_emp where salary between 5000 and 8000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 125K|00:00:00.03 | 3775 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 128K| 125K|00:00:00.03 | 3775 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
> arraysize 1000과 2000의 큰 차이가 없다. 기준은 없으니 적절하게 수정하자.
# Arraysize 5000
HR@ora11g> set arraysize 10000
SP2-0267: arraysize option 10000 out of range (1 through 5000)
> 최대 5000 까지만 설정 가능하다.
HR@ora11g> set arraysize 5000
HR@ora11g> select * from sal_emp where salary between 5000 and 8000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 125K|00:00:00.03 | 3737 |
|* 1 | TABLE ACCESS FULL| SAL_EMP | 1 | 128K| 125K|00:00:00.03 | 3737 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("SALARY">=5000 AND "SALARY"<=8000))
** arraysize 대비 데이터 양이 너무 적으면 오히려 데이터가 더 있나 기다림 발생할 수 있다.
# Arraysize 1000 + Index(salary)
HR@ora11g> set arraysize 1000
HR@ora11g> create index hr.sal_emp_idx on hr.sal_emp(salary);
Index created.
--- 대용량이라 강제로 인덱스 스캔 유도 (안하면 Full Scan 나온다)
HR@ora11g> 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'));
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 125K|00:00:00.15 | 67143 | 246 |
| 1 | TABLE ACCESS BY INDEX ROWID| SAL_EMP | 1 | 128K| 125K|00:00:00.15 | 67143 | 246 |
|* 2 | INDEX RANGE SCAN | SAL_EMP_IDX | 1 | 128K| 125K|00:00:00.03 | 372 | 246 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALARY">=5000 AND "SALARY"<=8000)
> Random I/O 때문에 Buffer 과다 발생
> Buffers 372 : Buffer Pinning 덕분
> Cluster Factor가 좋으면 상관없지만 대용량에서 Index Scan은 좋을게 없다.
# 테이블 통계 수집, 관련된 인덱스도 통계 수집
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.
SYS@ora11g> select num_rows, blocks, avg_row_len, last_analyzed
from user_tables
where table_name = 'SAL_EMP';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------
535000 3774 45 2024-02-27
HR@ora11g> select index_name, num_rows, blevel, leaf_blocks, clustering_factor, last_analyzed
from user_indexes
where table_name = 'SAL_EMP';
INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ---------- ---------- ----------- ----------------- -------------
SAL_EMP_IDX 535000 2 1057 218638 2024-02-27
[ Sample Table : hr.sal_emp ]
HR@ora11g> drop table hr.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.
HR@ora11g> exec dbms_stats.gather_table_stats('hr', 'sal_emp', granularity => 'auto')
PL/SQL procedure successfully completed.
HR@ora11g> select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'SAL_EMP';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE TABLESPACE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
--------------- ------------------ --------------- --------------- ---------- ---------- -----------
P1 1 5000 USERS 250000 1782 46
P2 2 10000 USERS 195000 1370 45
P3 3 30000 USERS 90000 635 45
PMAX 4 MAXVALUE USERS 0 0 0
# 파티션 정보 포함 실행계획 보기 : Allstats Last Partition
HR@ora11g> select * from sal_emp s where salary between 5000 and 8000;
# 파티션 정보 포함 실행계획 보기 : allstats last partition
HR@ora11g> 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 | | | | 125K|00:00:00.03 | 1463 |
| 1 | PARTITION RANGE SINGLE| | 1 | 120K| 2 | 2 | 125K|00:00:00.03 | 1463 |
|* 2 | TABLE ACCESS FULL | SAL_EMP | 1 | 120K| 2 | 2 | 125K|00:00:00.02 | 1463 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SALARY"<=8000)
> Pstart : 파티션 시작 번호 <- PARTITION_POSITION 참고
> Pstop : 파티션 종료 번호
> Partition Pruning 작동
# SQL문 통계 정보
HR@ora11g> show arraysize
arraysize 1000
HR@ora11g> set autotrace trace stat
HR@ora11g> select * from sal_emp s where salary between 5000 and 8000;
125000 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1463 consistent gets
270 physical reads
0 redo size
4928014 bytes sent via SQL*Net to client
1784 bytes received via SQL*Net from client
126 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
125000 rows processed
HR@ora11g> set autotrace off
# Partition Pruning
- SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능
HR@ora11g> alter session set statistics_level = all;
Session altered.
HR@ora11g> set arraysize 1000
# 정적(Static) 파티션 Pruning
- 파티션 키 컬럼을 상수 조건으로 조회
- 액세스할 파티션을 쿼리 최적화 시점에 결정(실행 계획을 생성할 때)
HR@ora11g> select * from sal_emp s where salary between 5000 and 8000;
HR@ora11g> 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 | | | | 125K|00:00:00.03 | 1463 | 36 |
| 1 | PARTITION RANGE SINGLE| | 1 | 120K| 2 | 2 | 125K|00:00:00.03 | 1463 | 36 |
|* 2 | TABLE ACCESS FULL | SAL_EMP | 1 | 120K| 2 | 2 | 125K|00:00:00.02 | 1463 | 36 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SALARY"<=8000)
# 동적(Dynamic) 파티션 Pruning
- 파티션 키 컬럼을 바인드 변수로 조회
- 쿼리의 최적화 시점에 액세스할 파티션을 미리 결정할 수 없다.
실행 시점에 결정된다.(execute)
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 s where salary between :b_start and :b_stop;
HR@ora11g> 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 | | | | 125K|00:00:00.04 | 1463 |
|* 1 | FILTER | | 1 | | | | 125K|00:00:00.04 | 1463 |
| 2 | PARTITION RANGE ITERATOR| | 1 | 120K| KEY | KEY | 125K|00:00:00.03 | 1463 |
|* 3 | TABLE ACCESS FULL | SAL_EMP | 1 | 120K| KEY | KEY | 125K|00:00:00.02 | 1463 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:B_STOP>=:B_START)
3 - filter(("SALARY"<=:B_STOP AND "SALARY">=:B_START))
[ Sample Table : hr.emp_non ]
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.
HR@ora11g> exec dbms_stats.gather_table_stats('hr','emp_non')
PL/SQL procedure successfully completed.
SYS@ora11g> select num_rows, blocks, avg_row_len, last_analyzed
from user_tables where table_name = 'EMP_NON';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------
107000 760 45 2024-02-27
HR@ora11g> alter session set statistics_level = all;
Session altered.
# employee_id = 1000
HR@ora11g> select * from hr.emp_non where employee_id = 1000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 745 |
|* 1 | TABLE ACCESS FULL| EMP_NON | 1 | 1 | 1 |00:00:00.01 | 745 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=1000)
# employee_id between 1000 and 25000
HR@ora11g> select count(*) from hr.emp_non where employee_id between 1000 and 25000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 744 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 744 |
|* 2 | TABLE ACCESS FULL| EMP_NON | 1 | 24002 | 24001 |00:00:00.01 | 744 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("EMPLOYEE_ID"<=25000 AND "EMPLOYEE_ID">=1000))
# index(employee_id) + employee_id = 1000
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';
INDEX_NAME NUM_ROWS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR LAST_ANALYZED
------------------------------ ---------- ---------- ----------- ----------------- -------------
EMP_NON_IDX 107000 1 223 740 2024-02-27
HR@ora11g> select * from hr.emp_non where employee_id = 1000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_NON | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | EMP_NON_IDX | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=1000)# index(employee_id) + employee_id between 1000 and 25000
HR@ora11g> select count(*) from hr.emp_non where employee_id between 1000 and 25000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 51 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 51 |
|* 2 | INDEX RANGE SCAN| EMP_NON_IDX | 1 | 24002 | 24001 |00:00:00.01 | 51 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID">=1000 AND "EMPLOYEE_ID"<=25000)
>>> 다른 사람은 index fast full scan 나오던데??
# Index Fast Full Scan 유도 : index_ffs()
HR@ora11g> select /*+ index_ffs(e emp_non_idx) */ count(*)
from hr.emp_non e where employee_id between 1000 and 25000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 230 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 230 |
|* 2 | INDEX FAST FULL SCAN| EMP_NON_IDX | 1 | 24002 | 24001 |00:00:00.01 | 230 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("EMPLOYEE_ID"<=25000 AND "EMPLOYEE_ID">=1000))
# Index Range Scan 유도 : index_rs()
HR@ora11g> select /*+ index_rs(e emp_non_idx) */ count(*)
from hr.emp_non e where employee_id between 1000 and 25000;
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 51 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 51 |
|* 2 | INDEX RANGE SCAN| EMP_NON_IDX | 1 | 24002 | 24001 |00:00:00.01 | 51 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID">=1000 AND "EMPLOYEE_ID"<=25000)
> INDEX RANGE SCAN인데 성능이 좋은이유? Buffer Pinning 작동 + Clustering Factor가 좋다.
[ Sample Table : hr.emp_local ]
HR@ora11g> 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.
HR@ora11g> exec dbms_stats.gather_table_stats('hr','emp_local', granularity => 'auto')
PL/SQL procedure successfully completed.
HR@ora11g> 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 294 45
P4 4 100000 USERS 20000 157 45
P5 5 120000 USERS 7001 67 45
PMAX 6 MAXVALUE USERS 0 0 0
SYS@ora11g> select num_rows, blocks, avg_row_len, last_analyzed
from user_tables where table_name = 'EMP_LOCAL';
NUM_ROWS BLOCKS AVG_ROW_LEN LAST_ANALYZED
---------- ---------- ----------- -------------
107000 831 45 2024-02-27
# Range Partition + employee_id = 1000
HR@ora11g> select * from hr.emp_local where employee_id = 1000;
HR@ora11g> 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)
> 1건 데이터를 찾고자 p1 파티션에 대해 Full Scan
> 파티션으로 나눠서 그나마 I/O가 덜 발생한거다
# Range Partition + employee_id between 1000 and 25000
HR@ora11g> select count(*) from hr.emp_local where employee_id between 1000 and 25000;
HR@ora11g> 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))
> > 1건 데이터를 찾고자 p1,p2 파티션에 대해 Full Scan
/*
index = segment
index = sort된 결과, 수정이 잦으면 안됨
- Index의 pctfree는 Index의 Split 현상 방지
- Table의 pctfree는 Row Migration 현상 방지
*/
■ 로컬 파티션 인덱스
- 파티션 테이블의 각 파티션과 파티션 인덱스의 각 파티션이 1 대 1 로 맵핑이 되는 인덱스이다.
- 테이블 파티션의 갯수화 인덱스 파티션의 갯수가 일치하며 파티션 테이블의 파티션 키와 파티션 인덱스의 인덱스 키가 일치한다.
- 유니관리 작업은 오라클이 자동으로 관리한다. 다른 파티션에 영량을 주지 않는다.
- prefixed : 파티션 인덱스를 생성할 때 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 의미
- nonprefixed : 파티션 인덱스를 생성할 때 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 의미
# 파티션 키컬럼(employee_id)에 대해 Index 설정 : local
HR@ora11g> create unique index hr.emp_local_idx on hr.emp_local(employee_id) local;
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_LOCAL'
and p.table_name = i.table_name
and p.index_name = i.index_name;
INDEX_NAME UNIQUENESS LOCALITY ALIGNMENT PARTITIONED PARTITION_COUNT
------------------------------ ---------- ---------- ------------ ----------- ---------------
EMP_LOCAL_IDX UNIQUE LOCAL PREFIXED YES 6
HR@ora11g> 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
# Local + employee_id = 1000
HR@ora11g> select * from hr.emp_local where employee_id = 1000;
HR@ora11g> 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 | 3 |
| 1 | PARTITION RANGE SINGLE | | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| EMP_LOCAL | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX UNIQUE SCAN | EMP_LOCAL_IDX | 1 | 1 | 1 | 1 | 1 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPLOYEE_ID"=1000)
# Local + employee_id between 1000 and 25000
HR@ora11g> select count(*) from hr.emp_local where employee_id between 1000 and 25000;
HR@ora11g> 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)
>>> 다른사람은 table access full scan 나오는데??? > INDEX RANGE SCAN 이 훨씬낫다.
[ Sample Table : hr.emp_global]
HR@ora11g> 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))
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.
HR@ora11g> select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_GLOBAL';
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
■ 파티션 글로벌 인덱스 (Partition Global Index)
- 파티션 테이블의 파티션 개수와 인덱스 파티션 의 파티션 개수가 일치 하지 않는다.
- 파티션 테이블의 파티션 키와 인덱스 파티션 키가 일치하지 않는다.
- 유지관리는 사용자(DBA)가 직접 해야한다.
# Global Index(hire_date) 생성
--- 사원번호 보다 날짜 검색이 많다는 가정에 인덱스 설정
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;
INDEX_NAME UNIQUENESS LOCALITY ALIGNMENT PARTITIONED PARTITION_COUNT
------------------------------ ---------- ---------- ------------ ----------- ---------------
EMP_GLOBAL_IDX NONUNIQUE GLOBAL PREFIXED YES 4
HR@ora11g> 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_ANALYZED PCT_FREE STATUS
--------------- --------------- --------------- ----------------------------------------------------------------- ------ ----------- -------- ----------------- ------------- -------- ------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 77 24000 15549 2024-02-27 10 USABLE
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 93 29000 19999 2024-02-27 10 USABLE
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 77 24000 17040 2024-02-27 10 USABLE
EMP_GLOBAL_IDX PMAX MAXVALUE 1 97 30000 20000 2024-02-27 10 USABLE
HR@ora11g> 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');
HR@ora11g> 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 | 85 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 85 |
| 2 | PARTITION RANGE SINGLE| | 1 | 15278 | 1 | 1 | 1000 |00:00:00.01 | 85 |
|* 3 | INDEX FAST FULL SCAN | EMP_GLOBAL_IDX | 1 | 15278 | 1 | 1 | 1000 |00:00:00.01 | 85 |
--------------------------------------------------------------------------------------------------------------------
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 > 인덱스만으로도 카운트 > not null 제약조건이 있기 때문에 가능
HR@ora11g> desc hr.emp_global
Name Null Type
------------- -------- ------------
EMPLOYEE_ID NUMBER
LAST_NAME NOT NULL VARCHAR2(25)
FIRST_NAME VARCHAR2(20)
HIRE_DATE NOT NULL DATE v
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
# INDEX RANGE SCAN
HR@ora11g> 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');
HR@ora11g> 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'))
> 파티션 인덱스를 범위스캔 시 버퍼피닝이 발생하여 더 효율적
# 파티션 부분 삭제
HR@ora11g> select partition_name, partition_position, high_value, tablespace_name, num_rows, blocks, avg_row_len
from user_tab_partitions
where table_name = 'EMP_GLOBAL';
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
HR@ora11g> 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_ANALYZED PCT_FREE STATUS
--------------- --------------- --------------- ------------------------------------------------------------------ ------ ----------- -------- ----------------- ------------- -------- ------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 77 24000 15549 2024-02-27 10 USABLE
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 93 29000 19999 2024-02-27 10 USABLE
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 77 24000 17040 2024-02-27 10 USABLE
EMP_GLOBAL_IDX PMAX MAXVALUE 1 97 30000 20000 2024-02-27 10 USABLE
--- 파티션 부분 삭제 : 데이터도 함께 삭제되는거 맞다.
HR@ora11g> alter table hr.emp_global drop partition p4;
Table altered.
HR@ora11g> 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
HR@ora11g> 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_ANALYZED PCT_FREE STATUS
--------------- --------------- --------------- ------------------------------------------------------------------ ------ ----------- -------- ----------------- ------------- -------- --------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 77 24000 15549 2024-02-27 10 UNUSABLE
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 93 29000 19999 2024-02-27 10 UNUSABLE
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 77 24000 17040 2024-02-27 10 UNUSABLE
EMP_GLOBAL_IDX PMAX MAXVALUE 1 97 30000 20000 2024-02-27 10 UNUSABLE
> 파티션을 부분 삭제한거지 인덱스는 그대로 있다
> 단, STATUS : USABLE -> UNUSABLE
--- Index Rebuild
HR@ora11g> alter index hr.emp_global_idx rebuild;
ORA-14086: a partitioned index may not be rebuilt as a whole
> 파티션 글로벌 인덱스는 각각 리빌드 해야한다. > 리터럴 문자 만들어서 일괄로 작업
↓
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.
HR@ora11g> 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_ANALYZED PCT_FREE STATUS
--------------- --------------- --------------- ------------------------------------------------------------------ ------ ----------- -------- ----------------- ------------- -------- ------
EMP_GLOBAL_IDX P2004 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 63 19515 12634 2024-02-27 10 USABLE
EMP_GLOBAL_IDX P2005 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 76 23580 16250 2024-02-27 10 USABLE
EMP_GLOBAL_IDX P2006 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA') 1 63 19514 13847 2024-02-27 10 USABLE
EMP_GLOBAL_IDX PMAX MAXVALUE 1 79 24391 16255 2024-02-27 10 USABLE
> STATUS : UNUSABLE -> USABLE
HR@ora11g> 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');
HR@ora11g> 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 | 4 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 4 |
| 2 | PARTITION RANGE SINGLE| | 1 | 15278 | 1 | 1 | 813 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_GLOBAL_IDX | 1 | 15278 | 1 | 1 | 813 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------------------------
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'))
> Rebuild 덕분에 I/O 1개 줄었다!
# Global Index 사용 중 파티션 추가 : Add X -> Split
alter index hr.emp_global_idx add partition p2007 values less than (to_date('2008-01-01','yyyy-mm-dd');
hash 말고는 add partition 추가 불가 -> split
alter index hr.emp_global_idx split partition pmax at (to_date('2008-01-01',' yyyy-mm-dd')) into (partition p2007, partition pmax);
HR@ora11g> 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 index emp_global_ind drop partition pmax;
>
alter index hr.emp_global_idx rebuild partition pmax;
HR@ora11g> 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';
'Oracle 11g > Tuning' 카테고리의 다른 글
| 95-1 DML작업 병렬처리 (0) | 2024.02.29 |
|---|---|
| 94 Parallel (3) | 2024.02.28 |
| 92 Partition (1) | 2024.02.26 |
| 91 통계정보 (0) | 2024.02.23 |
| 90 Pushdown, Pullup, Use_concat, Optimazer (0) | 2024.02.22 |