고정 헤더 영역
상세 컨텐츠
본문
제품 : ORACLE SERVER
작성날짜 : 2002-01-28
======================
(9i) LIST PARTITIONING
======================
PURPOSE
-------
Oracle 9i에서 처음 소개된 새로운 partition 방법인 LIST Partition을 소개한다.
Explanation
-----------
List partition은 몇개의 row가 partition에 map되어 있는지를 명시적으로 제어가
가능하도록 한다. 각각의 partition에 대해 별개의 partition key값을 가지고
나타낼수 있는데 이것은 range의 값을 가지고 partition하는 range partition과도
구별되며 hash fucntion에 따라 나누는 hash partition과는 구별된다.
List partition의 장점은 연관되지 않은 값들을 group화할 수 있는 점이다.
Table 뿐만 나니라 Index도 LIST partition방법으로 partition할 수 있다.
List partition의 제한은 다음과 같다.
- 오직 하나의 column만 key partition으로 가능하다.
- IOT는 List partition 할수 없다.
- 다른 partiton이나 같은 partition이라 할지라도 같은 값으로 partition
으로 partition할수 없다.
- RANGE partition에서 쓰이는 MAXVALUE로 List방법에는 bound을 줄수 없다.
Example
--------
<On Table>
1. CREATE a List Partitioned Table.
2. MODIFY (ADD/DROP Values).
3. ADD Partition.
4. MERGE.
5. RENAME.
6. SPLIT.
7. TRUNCATE.
8. EXCHANGE.
9. MOVE.
10. MODIFY DEFAULT ATTRIBUTES.
11. MODIFY REAL ATTRIBUTES.
<On Index>
아래의 command만이 가능하다.
1. MODIFY DEFAULT ATTRIBUTES.
2. MODIFY REAL ATTRIBUTES.
3. REBUILD PARTITION.
4. RENAME PARTITION.
1. ( CREATE a List Partitioned Table)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> CREATE TABLE employees_reg_p
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn_p NOT NULL
, email VARCHAR2(25) CONSTRAINT emp_email_nn_p NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE CONSTRAINT emp_hire_date_nn_p NOT NULL
, job_id VARCHAR2(10) CONSTRAINT emp_job_nn_p NOT NULL
, salary NUMBER(8,2) CONSTRAINT emp_salary_nn_p NOT NULL
, commission_pct NUMBER(2,2)
, manager_id NUMBER(8)
, department_id NUMBER(4)
, region VARCHAR2(15)
, CONSTRAINT emp_salary_min_p CHECK (salary > 0)) PCTFREE 60
partition BY LIST (REGION)
(partition Zone_1 VALUES('R1','R10','R11','R12') TABLESPACE users,
partition Zone_2 VALUES('R13','R14','R15','R16') TABLESPACE users,
partition Zone_3 VALUES('R17','R18','R19','R2') TABLESPACE users,
partition Zone_4 VALUES('R20','R21','R22','R23') TABLESPACE users,
partition Zone_5 VALUES('R24','R25','R26','R27') TABLESPACE users,
partition Zone_6 VALUES('R28','R29','R3','R30') TABLESPACE users,
partition Zone_7 VALUES ('R31','R32','R4','R5') TABLESPACE users,
partition Zone_8 VALUES ('R6','R7','R8','R9') TABLESPACE users);
Table created.
SQL> col table_name format a15
SQL> col partition format a10
SQL> col high_value format a30
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
--------------- ---------- --------------------------------- --------
EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 1
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 2
EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 3
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R22', 'R23' 4
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 5
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6
EMPLOYEES_REG_P ZONE_7 'R31', 'R32', 'R4', 'R5' 7
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8
8 rows selected.
SQL> select table_name, PARTITIONING_TYPE, PARTITION_COUNT
from user_part_tables
where table_name = 'EMPLOYEES_REG_P';
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
-------------------- -------------------- ---------------
EMPLOYEES_REG_P LIST 8
2. ( MODIFY a List Partition )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Partition Key을 drop하기전에 partition key를 포함하는 row을 반드시
delete하여야 한다. 그렇지 않으면 아래와 같은 error을 만나게 되다.
ORA-14518: partition contains rows corresponding to values being dropped
SQL> Delete employees_reg_p WHERE region in ('R32', 'R22');
SQL> ALTER TABLE employees_reg_p modify partition zone_4
DROP values ('R22');
Table altered.
SQL> ALTER TABLE employees_reg_p modify partition zone_7
DROP values ('R32');
Table altered.
SQL> col table_name format a15
SQL> col partition format a10
SQL> col high_value format a30
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
-------------------- ---------- ------------------------- ----------
EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 1
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16 2
EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 3
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R23' 4
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27 5
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6
EMPLOYEES_REG_P ZONE_7 'R31', 'R4', 'R5' 7
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8
8 rows selected.
특정 partition의 모든 값을 drop할수는 없다. 그 partition의 남은 마지막
값을 drop하고자 하면 아래와 같은 error가 날 것이다.
ORA-14317: cannot drop the last value of partition
3. ( ADD a List Partition )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Partition key을 add하거나 partition을 add할수 있다.
SQL> ALTER TABLE employees_reg_p ADD partition ZONE_ADD
values ('R22', 'R32');
SQL> col table_name format a15
SQL> col partition format a10
SQL> col high_value format a30
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
--------------- ---------- ------------------------------ ----------
EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 1
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 2
EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 3
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R23' 4
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 5
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6
EMPLOYEES_REG_P ZONE_7 'R31', 'R4', 'R5' 7
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8
EMPLOYEES_REG_P ZONE_ADD 'R22', 'R32' 9
9 rows selected.
SQL> ALTER TABLE employees_reg_p modify partition ZONE_ADD
DROP values ('R22');
Table altered.
SQL> ALTER TABLE employees_reg_p modify partition ZONE_4
ADD values ('R22');
Table altered.
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
--------------- ---------- ------------------------------ ----------
EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 1
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 2
EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 3
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R23', 'R22' 4
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 5
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6
EMPLOYEES_REG_P ZONE_7 'R31', 'R4', 'R5' 7
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8
EMPLOYEES_REG_P ZONE_ADD 'R32' 9
9 rows selected.
4. ( MERGE List Partitions )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
두개의 partition을 merge할수 있으며, 순서를 줄 필요가 없기 때문에 range
partition처럼 근접한 값일 필요는 없다.
SQL> ALTER TABLE employees_reg_p merge partitions zone_add, zone_7 into
partition zone_7;
Table altered.
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
--------------- ---------- ------------------------------ ----------
EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 1
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 2
EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 3
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R23', 'R22' 4
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 5
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6
EMPLOYEES_REG_P ZONE_7 'R32', 'R31', 'R4', 'R5' 7
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8
8 rows selected.
5. ( RENAME a List Partitions )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> ALTER TABLE employees_reg_p rename partition zone_7 to zone_merge;
Table altered.
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
--------------- ---------- ------------------------------ ----------
EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 1
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 2
EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 3
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R23', 'R22' 4
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 5
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6
EMPLOYEES_REG_P ZONE_MERGE 'R32', 'R31', 'R4', 'R5' 7
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8
8 rows selected.
6. ( SPLIT a List Partition )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> ALTER TABLE employees_reg_p merge partitions zone_1, zone_3 into
partition zone_merge1_3;
Table altered.
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
--------------- ---------- ------------------------------ ----------
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 1
EMPLOYEES_REG_P ZONE_MERGE 'R1', 'R10', 'R11', 'R12', 'R1 2
1_3 7', 'R18', 'R19', 'R2'
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R23', 'R22' 3
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 4
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 5
EMPLOYEES_REG_P ZONE_MERGE 'R32', 'R31', 'R4', 'R5' 6
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 7
SQL> ALTER TABLE employees_reg_p split partition ZONE_MERGE1_3
values ('R17', 'R18', 'R19', 'R2')
into ( partition ZONE_3, partition ZONE_1);
Table altered.
SQL> SELECT table_name, partition_name partition, high_value,
partition_position position
FROM user_tab_partitions
WHERE table_name='EMPLOYEES_REG_P'
order by position;
TABLE_NAME PARTITION HIGH_VALUE POSITION
--------------- ---------- ------------------------------ ----------
EMPLOYEES_REG_P ZONE_2 'R13', 'R14', 'R15', 'R16' 1
EMPLOYEES_REG_P ZONE_3 'R17', 'R18', 'R19', 'R2' 2
EMPLOYEES_REG_P ZONE_1 'R1', 'R10', 'R11', 'R12' 3
EMPLOYEES_REG_P ZONE_4 'R20', 'R21', 'R23', 'R22' 4
EMPLOYEES_REG_P ZONE_5 'R24', 'R25', 'R26', 'R27' 5
EMPLOYEES_REG_P ZONE_6 'R28', 'R29', 'R3', 'R30' 6
EMPLOYEES_REG_P ZONE_MERGE 'R32', 'R31', 'R4', 'R5' 7
EMPLOYEES_REG_P ZONE_8 'R6', 'R7', 'R8', 'R9' 8
8 rows selected.
7. ( TRUNCATE a List Partition )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> CREATE TABLE exchange_zone_3 as select * from employees_reg_p
where 1 = 2;
SQL> INSERT INTO exchange_zone_3 SELECT * FROM employees_reg_p PARTITION
(ZONE_3);
SQL> ALTER TABLE employees_reg_p TRUNCATE PARTITION ZONE_3 DROP STORAGE;
Table truncated.
8. ( EXCHANGE a List Partition )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> ALTER TABLE employees_reg_p EXCHANGE PARTITION ZONE_3
WITH TABLE exchange_zone_3
WITHOUT VALIDATION;
Table altered.
9. (MOVE a List Partition)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> ALTER TABLE employees_reg_p MOVE PARTITION ZONE_3
TABLESPACE example;
SQL> col table_name format a15
SQL> col partition_name format a15
SQL> col tablespace_name format a10
SQL> select table_name,partition_name,tablespace_name,partition_position
from user_tab_partitions where table_name='EMPLOYEES_REG_P'
order by partition_position
TABLE_NAME PARTITION_NAME TABLESPACE PARTITION_POSITION
--------------- --------------- ---------- ------------------
EMPLOYEES_REG_P ZONE_2 USERS 1
EMPLOYEES_REG_P ZONE_3 EXAMPLE 2
EMPLOYEES_REG_P ZONE_1 SYSTEM 3
EMPLOYEES_REG_P ZONE_4 USERS 4
EMPLOYEES_REG_P ZONE_5 USERS 5
EMPLOYEES_REG_P ZONE_6 USERS 6
EMPLOYEES_REG_P ZONE_MERGE SYSTEM 7
EMPLOYEES_REG_P ZONE_8 USERS 8
8 rows selected.
10. (MODIFY DEFAULT ATTRIBUTES)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PCTFREE 절처럼 table이나 tablespace level에서 inherited되어지는 절만 수정이
가능하다.
SQL> select TABLE_NAME, PARTITIONING_TYPE, DEF_PCT_FREE from user_part_tables
where table_name = 'EMPLOYEES_REG_P';
TABLE_NAME PARTITIONING_TYPE DEF_PCT_FREE
--------------- -------------------- ------------
EMPLOYEES_REG_P LIST 60
SQL> alter table EMPLOYEES_REG_P MODIFY DEFAULT ATTRIBUTES PCTFREE 40;
Table altered.
SQL> select TABLE_NAME, PARTITIONING_TYPE, DEF_PCT_FREE from user_part_tables
where table_name = 'EMPLOYEES_REG_P';
TABLE_NAME PARTITIONING_TYPE DEF_PCT_FREE
--------------- -------------------- ------------
EMPLOYEES_REG_P LIST 40
11. (MODIFY REAL ATTRIBUTES OF LIST PARTITION)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
특정 parition table과 연관된 current parameter가 수정된다.
SQL> select table_name, partition_name, pct_free
from user_tab_partitions
where table_name = 'EMPLOYEES_REG_P'
and partition_name = 'ZONE_3';
TABLE_NAME PARTITION_NAME PCT_FREE
--------------- -------------------- ----------
EMPLOYEES_REG_P ZONE_3 10
SQL> alter table EMPLOYEES_REG_P modify partition zone_3 pctfree 20;
Table altered.
SQL> select table_name, partition_name, pct_free
from user_tab_partitions
where table_name = 'EMPLOYEES_REG_P'
and partition_name = 'ZONE_3';
TABLE_NAME PARTITION_NAME PCT_FREE
--------------- -------------------- ----------
EMPLOYEES_REG_P ZONE_3 20
RELATED DOCUMENTS
-----------------
<Note:149116.1>
Oracle9i Administrator's guide
Oracle9i SQL Reference
