1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 하반기 - 오라클 데이터베이스 스터디
  4. 07. 파티션의 새로운 기능

07. 파티션의 새로운 기능 (by nav012) [2016.12.10]

h1.1. Partition online move






SQL> create table part_move_test (
name varchar2(10),
dob date
)
partition by range (dob)
interval (numtodsinterval(7, 'DAY'))
(partition p_jan1971 values less than (to_DatE('19710101','yyyymmdd'))
);

Statement Processed.

SQL> insert into part_move_test values ('name1',to_Date('2016/12/01','yyyy/mm/dd'));

1 rows Inserted.

SQL> insert into part_move_test values ('name2',to_Date('2016/12/02','yyyy/mm/dd'));

1 rows Inserted.

SQL> insert into part_move_test values ('name3',to_Date('2016/12/03','yyyy/mm/dd'));

1 rows Inserted.

SQL> insert into part_move_test values ('name4',to_Date('2016/12/04','yyyy/mm/dd'));

1 rows Inserted.


SQL> SELECT table_name, partition_name, num_rows, tablespace_name 
FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PART_MOVE_TEST';


TABLE_NAME                     PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME
------------------------------ ------------------------------ ---------- ------------------------------
PART_MOVE_TEST                 P_JAN1971                               0 USERS
PART_MOVE_TEST                 SYS_P426                                1 USERS
PART_MOVE_TEST                 SYS_P427                                3 USERS

SQL> alter table part_move_test move partition SYS_P426 tablespace ts_1 online ;

Table altered.

SQL>  SELECT table_name, partition_name, num_rows, tablespace_name
FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='PART_MOVE_TEST'

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS TABLESPACE_NAME
------------------------------ ------------------------------ ---------- ------------------------------
PART_MOVE_TEST                 P_JAN1971                               0 USERS
PART_MOVE_TEST                 SYS_P426                                1 TS_1
PART_MOVE_TEST                 SYS_P427                                3 USERS

SQL>


  • Table Partition 의 Online move 가 가능해 짐.
  • Non partition table 의 Online move 는 여전히 불가능


SQL> create table move_test (name varchar2(30), dob date ) tablespace users;

Table created.

SQL> insert into move_test select object_name, created from dba_objects;
90794 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> alter table move_test move tablespace ts_1 online ;
alter table move_test move tablespace ts_1 online
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option






h1.2. Interval-Reference Partition





Reference partition 이란?
  • 11g 에서 등장한 파티션 방식
  • 부모-자식 관계의 테이블이 있을 때 자식 테이블에는 파티션 정의 없어도 부모 테이블의 파티션 구조를 따라 생성 됨



일반 Referenace partition


  • 11g/12c 에서 사용 가능


SQL> create table orders (
order_id number
,name varchar2(30)
,order_date date
)
partition by range (order_date) 
(
partition p_1 values less than (to_date('2015/01/01','yyyy/mm/dd')),
partition p_2 values less than (to_date('2016/01/01','yyyy/mm/dd')),
partition p_3 values less than (to_date('2017/01/01','yyyy/mm/dd'))
);

Statement Processed.

SQL> alter table orders add constraint pk_orders primary key (order_id);

Statement Processed.

SQL> create table order_items (
order_id number not null
,product_id number
,sales_cmount number
,constraint order_items_fk foreign key (order_id) references orders (order_id)  on delete cascade
)
partition by reference (order_items_fk);

Statement Processed.

SQL> insert into orders select object_id, object_name, created from dba_objects;

90816 rows Inserted.

SQL> commit;

Commit complete.

SQL> insert into order_items select object_id, namespace, data_object_id from dba_objects;

90812 rows Inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDERS')

PL/SQL executed.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDER_ITEMS')

PL/SQL executed.


SQL> select table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS','ORDER_ITEMS');

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDER_ITEMS                    P_3                                                                                                                     50
ORDER_ITEMS                    P_2                                                                                                                      9
ORDER_ITEMS                    P_1                                                                                                                  90753
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         54
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         P_1                            TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      90753

6 rows selected.

SQL>
SQL> SELECT 
TABLE_NAME, PARTITIONING_TYPE, REF_PTN_CONSTRAINT_NAME, INTERVAL
FROM USER_PART_TABLES
WHERE TABLE_NAME IN ('ORDERS','ORDER_ITEMS')
;

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME        INTERVAL
------------------------------ --------- ------------------------------ --------------------------------------------------
ORDERS                         RANGE
ORDER_ITEMS                    REFERENCE ORDER_ITEMS_FK

SQL>




부모 테이블이 Interval partition 일 경우의 Referenace partition


  • 12c 에서만 사용 가능


SQL> create table orders (
order_id number
,name varchar2(30)
,order_date date
)
partition by range (order_date) interval (numtodsinterval(7,'DAY'))
(partition p_1 values less than (to_date('2015/01/01','yyyy/mm/dd'))
);

Statement Processed.

SQL> alter table orders add constraint pk_orders primary key (order_id);

Statement Processed.

SQL> create table order_items (
order_id number not null
,product_id number
,sales_cmount number
,constraint order_items_fk foreign key (order_id) references orders (order_id)  on delete cascade
)
partition by reference (order_items_fk);

Statement Processed.

SQL> insert into orders select object_id, object_name, created from dba_objects where owner != 'TESTUSER';

90793 rows Inserted.

SQL> commit;

Commit complete.

SQL> insert into order_items select object_id, namespace, data_object_id from dba_objects where owner != 'TESTUSER';

90793 rows Inserted.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDERS')

PL/SQL executed.

SQL> exec dbms_stats.gather_Table_stats('TESTUSER','ORDER_ITEMS')

PL/SQL executed.

SQL> select
table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS','ORDER_ITEMS');  2    3

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDER_ITEMS                    SYS_P523                                                                                                                14
ORDER_ITEMS                    SYS_P522                                                                                                                 9
ORDER_ITEMS                    SYS_P521                                                                                                                17
ORDER_ITEMS                    P_1                                                                                                                  90753
ORDERS                         SYS_P523                       TO_DATE(' 2016-06-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         14
ORDERS                         SYS_P522                       TO_DATE(' 2015-04-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         SYS_P521                       TO_DATE(' 2016-12-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         17
ORDERS                         P_1                            TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      90753

8 rows selected.

SQL> SELECT 
TABLE_NAME, PARTITIONING_TYPE, REF_PTN_CONSTRAINT_NAME, INTERVAL
FROM USER_PART_TABLES
WHERE TABLE_NAME IN ('ORDERS','ORDER_ITEMS')
;

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME        INTERVAL
------------------------------ --------- ------------------------------ --------------------------------------------------
ORDERS                         RANGE                                    NUMTODSINTERVAL(7,'DAY')
ORDER_ITEMS                    REFERENCE ORDER_ITEMS_FK                 YES

SQL>


  • 이 경우 11g 에서는 아래와 같은 에러 발생


SQL> create table order_items (
order_id number not null
,product_id number
,sales_cmount number
,constraint order_items_fk foreign key (order_id) references orders (order_id)  on delete cascade
)
partition by reference (order_items_fk)
;

ORA-14659: Partitioning method of the parent table is not supported






h1.3. Cascade 기능





h3.Truncate partition 의 개선
*reference partition 사용 시 truncate partition 명령어와 exchange partition 의 옵션으로 쓰일 수 있음

  • 부모-자식 테이블 간 on delete cascade 옵션으로 정의되어 있어야 한다




SQL> select count(*) from orders partition (p_1);

COUNT(*)   
-----------
      90753

1 rows selected.

SQL> select count(*) from order_items partition (p_1);

COUNT(*)   
-----------
      90753

1 rows selected.

SQL> alter table orders truncate partition (p_1) cascade;

Statement Processed.
SQL> select count(*) from orders partition (p_1);

COUNT(*)   
-----------
          0

1 rows selected.

SQL> select count(*) from order_items partition (p_1);

COUNT(*)   
-----------
          0

1 rows selected.




  • 반면 11g 에서는 아래와 같은 에러가 발생한다.


SQL> select count(*) from orders partition (p_1);

COUNT(*)   
-----------
      66187

1 rows selected.

SQL> select count(*) from order_items partition (p_1);

COUNT(*)   
-----------
      66187

1 rows selected.

SQL> alter table orders truncate partition (p_1) cascade
ORA-14054: invalid ALTER TABLE TRUNCATE PARTITION option




  • 12c 에서도 on delete cascade 옵션이 정의되지 않으면 다음과 같은 에러가 발생한다


SQL> alter table orders truncate partition (p_1) cascade
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "TESTUSER"."ORDER_ITEMS"






h1.4. 복수 개 파티션의 유지 보수





여러 파티션을 한꺼번에 Altering 가능하다

  • 12c 에서 partition 2개의 partition 을 add, 1개의 파티션을 3개로 split 할 경우 아래와 같이 가능하다.




SQL> alter table orders add
partition p_4 values less than (to_Date('2018/01/01','yyyy/mm/dd')),
partition p_5 values less than (to_Date('2019/01/01','yyyy/mm/dd'));

Statement Processed.


SQL> alter table orders split partition p_1 into (
partition p_1_1 values less than (to_date('2013/01/01','yyyy/mm/dd')),
partition p_1_2 values less than (to_date('2014/01/01','yyyy/mm/dd')),
partition p_1_3 );

Statement Processed.


SQL>  select
table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS');  2    3

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDERS                         P_1_1                          TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_1_2                          TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_1_3                          TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         55
ORDERS                         P_4                            TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_5                            TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.




  • 반면 11g 에서는 아래와 같은 에러가 발생한다.

SQL> alter table orders add
partition p_4 values less than (to_Date('2018/01/01','yyyy/mm/dd')),
partition p_5 values less than (to_Date('2019/01/01','yyyy/mm/dd'))
ORA-14043: only one partition may be added


SQL> alter table orders split partition p_1 into (
partition p_1_1 values less than (to_date('2013/01/01','yyyy/mm/dd')),
partition p_1_2 values less than (to_date('2014/01/01','yyyy/mm/dd')),
partition p_1_3 )
ORA-14028: missing AT or VALUES keyword




  • 11g 에서 동일한 결과를 얻으려면 아래와 같이 개별 파티션으로 작업 해야 한다

SQL> alter table orders add
partition p_4 values less than (to_Date('2018/01/01','yyyy/mm/dd'));

Statement Processed.

SQL> alter table orders add
partition p_5 values less than (to_Date('2019/01/01','yyyy/mm/dd'));

Statement Processed.


SQL> alter table orders split partition p_1 at (to_date('2013/01/01','yyyy/mm/dd')) into (
partition p_1_1,
partition p_1_2
);

Statement Processed.

SQL> alter table orders split partition p_1_2 at (to_date('2014/01/01','yyyy/mm/dd')) into (
partition p_1_2,
partition p_1_3
);

Statement Processed.


SQL>  select 
table_name, partition_name, high_value, num_rows
from user_tab_partitions where table_name in ('ORDERS');

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDERS                         P_1_1                          TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      66187
ORDERS                         P_1_2                          TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_1_3                          TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         12
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA      20208
ORDERS                         P_4                            TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_5                            TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

7 rows selected.

SQL>




  • 추가로 Partition merge 도 가능 해 졌다


SQL> alter table orders merge partitions p_1_1, p_1_2, p_1_3 into partition p_1;

Statement Processed.


SQL> /

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                         NUM_ROWS
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------
ORDERS                         P_5                            TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_4                            TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS                         P_3                            TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA         55
ORDERS                         P_2                            TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA          9
ORDERS                         P_1                            TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL>






5. Partial index






  • 지정 된 파티션에만 인덱스가 생성 되도록 하는 기능
  • 11g 에서 유사하게 구현 하려면 전체 Local Index 를 unusable 로 생성해고 개별 partition 에 rebuild 하는 방법 밖에 없었다.
  • 12c 에서는 Global/Local 타입과 무관하게 개별 파티션에 인덱스 적용이 가능하다
  • full/partial indexing 타입이 있다.
  • 테이블 속성으로 인덱싱 여부를 관리해야 한다.



Full/Partial index



*Full indexing
모든 파티션에 인덱스가 생성


*Partial indexing
지정 된 파티션에는 인덱스가 생성 혹은 생성되지 않게 지정 가능함
인덱스 사용 공간 절약 가능
유지 보수 작업을 빠르게 할 수 있음 ex> 인덱싱 되지 않은 글로벌 파티션의 리빌드 등




create table orders (
order_id number
,name varchar2(30)
,order_date date
)
indexing off ---(1)
partition by range (order_date) 
(
partition p_1 values less than (to_date('2015/01/01','yyyy/mm/dd')) indexing off, ---(2)
partition p_2 values less than (to_date('2016/01/01','yyyy/mm/dd')) indexing on,  ---(3)
partition p_3 values less than (to_date('2017/01/01','yyyy/mm/dd'))               ---(4)
)
;

alter table orders modify partition p_1 indexing on; ---(5)
alter table orders modify partition p_1 indexing off;

SQL> select
table_name, partition_name, indexing
from user_tab_partitions where table_name='ORDERS'
;

TABLE_NAME                     PARTITION_NAME                 INDE
------------------------------ ------------------------------ ----
ORDERS                         P_1                            OFF
ORDERS                         P_2                            ON
ORDERS                         P_3                            OFF


  • (1) 개별 파티션에 indexing 옵션을 지정하지 않았을 때 기본 속성값을 지정
  • (2) p_1 파티션에는 index 적용
  • (3) p_2 파티션에는 index 적용하지 않음
  • (4) p_3 파티션에는 indexing 옵션을 명시하지 않았으므로 기본 속성인 indexing off(1) 에 따라 감
  • (5) Alter table 으로 indexing 속성 변경 가능





Index 의 full/partial index 생성 비교 - Global full/Global partial/Local full/Local partial




Global Full Case



SQL> create index ind1_orders_full on orders(order_date, order_id, name) global indexing full;

Index created.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_FULL';

INDEX_NAME                     TABLE_NAME                     INDEXING
------------------------------ ------------------------------ --------
IND1_ORDERS_FULL               ORDERS                         FULL

SQL>

SQL> select bytes from dba_Segments where segment_name='IND1_ORDERS_FULL';

     BYTES
----------
 284164096

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_FULL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS
------------------------------ ------------------------------ ------- ----------
IND1_ORDERS_FULL               ORDERS                         FULL       4540569




Global Partial Case



 SQL> create index ind1_orders_partial on orders(order_id, name, order_date) global indexing partial;

Index created.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXING
------------------------------ ------------------------------ --------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL

SQL> select bytes from dba_Segments where segment_name='IND1_ORDERS_PARTIAL';

     BYTES
----------
     65536

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS
------------------------------ ------------------------------ ------- ----------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL        450

SQL>




local Full case



SQL> create index ind2_orders_full on orders(order_date, name) local indexing full;

Index created.

SQL> select segment_name, partition_name, bytes from dba_Segments where segment_name='IND2_ORDERS_FULL';

SEGMENT_NAME                   PARTITION_NAME                      BYTES
------------------------------ ------------------------------ ----------
IND2_ORDERS_FULL               P_3                                196608
IND2_ORDERS_FULL               P_2                                 65536
IND2_ORDERS_FULL               P_1                             234881024

SQL>
SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, GLOBAL_STATS, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND2_ORDERS_FULL';

INDEX_NAME                     TABLE_NAME                     INDEXIN GLO   NUM_ROWS
------------------------------ ------------------------------ ------- --- ----------
IND2_ORDERS_FULL               ORDERS                         FULL    NO     4540569
SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS from user_ind_partitions where index_name='IND2_ORDERS_FULL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
IND2_ORDERS_FULL               P_1                               4537650
IND2_ORDERS_FULL               P_2                                   450
IND2_ORDERS_FULL               P_3                                  2469




Local Partial Case



SQL> create index ind2_orders_partial on orders(order_date, name) local indexing partial;

Index created.

SQL> select segment_name, partition_name, bytes from dba_Segments where segment_name='IND2_ORDERS_PARTIAL';

SEGMENT_NAME                   PARTITION_NAME                      BYTES
------------------------------ ------------------------------ ----------
IND2_ORDERS_PARTIAL            P_2                                 65536

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, GLOBAL_STATS, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND2_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN GLO   NUM_ROWS
------------------------------ ------------------------------ ------- --- ----------
IND2_ORDERS_PARTIAL            ORDERS                         PARTIAL NO         450

SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS, STATUS, SEGMENT_CREATED from user_ind_partitions where index_name='IND2_ORDERS_PARTIAL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS STATUS   SEG
------------------------------ ------------------------------ ---------- -------- ---
IND2_ORDERS_PARTIAL            P_1                                     0 UNUSABLE NO
IND2_ORDERS_PARTIAL            P_2                                   450 USABLE   YES
IND2_ORDERS_PARTIAL            P_3                                     0 UNUSABLE NO




Index 의 full/partial index rebuild 비교 - Global partial/Local partial




Global Partial case



SQL> alter index ind1_orders_partial rebuild ;

Index altered.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS
------------------------------ ------------------------------ ------- ----------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL        450

SQL> alter table orders modify partition p_3 indexing on ;

Table altered.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS, STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS STATUS
------------------------------ ------------------------------ ------- ---------- --------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL        450 VALID

SQL> alter index IND1_ORDERS_PARTIAL rebuild ;

Index altered.

SQL> SELECT INDEX_NAME, TABLE_NAME, INDEXING, NUM_ROWS, STATUS FROM USER_INDEXES WHERE INDEX_NAME='IND1_ORDERS_PARTIAL';

INDEX_NAME                     TABLE_NAME                     INDEXIN   NUM_ROWS STATUS
------------------------------ ------------------------------ ------- ---------- --------
IND1_ORDERS_PARTIAL            ORDERS                         PARTIAL       2919 VALID

SQL>




Local Partial case



-- 생성되지 않은 partitial index 에 rebuild 시에는?
SQL> alter index IND2_ORDERS_PARTIAL rebuild partition p_1 ;

Index altered.

SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS, STATUS, SEGMENT_CREATED from user_ind_partitions where index_name='IND2_ORDERS_PARTIAL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS STATUS   SEG
------------------------------ ------------------------------ ---------- -------- ---
IND2_ORDERS_PARTIAL            P_1                               4537650 USABLE   YES
IND2_ORDERS_PARTIAL            P_2                                   450 USABLE   YES
IND2_ORDERS_PARTIAL            P_3                                     0 UNUSABLE NO

SQL>

-- indexing 되지 않은 partition 에 altering 시에는?

SQL> alter table orders modify partition p_3 indexing on ;

Table altered.

SQL>
SQL> select INDEX_NAME, PARTITION_NAME, NUM_ROWS, STATUS, SEGMENT_CREATED from user_ind_partitions where index_name='IND2_ORDERS_PARTIAL';

INDEX_NAME                     PARTITION_NAME                   NUM_ROWS STATUS   SEG
------------------------------ ------------------------------ ---------- -------- ---
IND2_ORDERS_PARTIAL            P_1                               4537650 USABLE   YES
IND2_ORDERS_PARTIAL            P_2                                   450 USABLE   YES
IND2_ORDERS_PARTIAL            P_3                                  2469 USABLE   YES

SQL>


-- 이 때 table partition 속성은?

SQL> select
table_name, partition_name, indexing
from user_tab_partitions where table_name='ORDERS'
;

TABLE_NAME                     PARTITION_NAME                 INDE
------------------------------ ------------------------------ ----
ORDERS                         P_1                            OFF -- local index rebuild
ORDERS                         P_2                            ON
ORDERS                         P_3                            ON  -- partition indexing on

SQL>




결론은 다음과 같다


  • 복합적으로 두 가지 방법으로 운영해야 한다
    1. 테이블의 개별 파티션에 indexing 속성 지정
    2. 인덱스 생성 시 partial 키워드로 생성
  • Global / Local index 모두에 사용 가능하다
  • Local index 일 경우는 처음부터 특정 파티션에 unusable 로 인덱스가 생성되는 개념
    Partition 을 altering 하거나 index rebuild 시 파티션 인덱스가 생성
    index rebuild 만 할 경우 인덱스만 생성되고 파티션 속성은 변하지 않음
  • Global index 일 경우는 특정 파티션에만 global 하게 인덱스가 생성
    partition 을 altering 후 index rebuild 시 신규 altering 한 파티션 데이터가 인덱스에 포함 됨
    index rebuild 만 할 경우 아무것도 변하지 않음














  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2016년 하반기 - 오라클 데이터베이스 스터디
  4. 07. 파티션의 새로운 기능