List partitioning allows creater flexibility in the mapping of rows to partitions than range or hash partitioning. Since the data is distributed based on discrete column values, unordered and unrelated sets of data can be grouped together with no relationship between the partitions.
There are several usage issues with respect to list partitioning:
- Only available to heap organized tables.
- Multicolumn partitioning not supported.
- All literals in the value lists must be unique.
NULLis a valid literal value.- There is no equivalent of
MAXVALUE. - All lists must have at lease one literal.
- Lists of literals cannot exceed 4KB
- Partition pruning, partition wise joins and parallelism are supported.
- Local indexes and global range partitioned indexes are supported.
1. CREATE TABLE List_Part_T
(ID NUMBER(5),
HIREDATE DATE,
JOB VARCHAR(15)
)
PARTITION BY LIST(JOB)
( PARTITION L1 VALUES('CLERK'),
PARTITION L2 VALUES('SALESMAN'),
PARTITION L3 VALUES('ENGINEER')
)
ENABLE ROW MOVEMENT;
2. INSERT rows:
INSERT INTO LIST_PART_T VALUES('1', SYSDATE-100, 'CLERK');
INSERT INTO LIST_PART_T VALUES('23', SYSDATE-10, 'SALESMAN');
INSERT INTO LIST_PART_T VALUES('41', SYSDATE-50, 'ENGINEER');
COMMIT;
3. Checking values in Partitions:
SQL> SELECT * FROM LIST_PART_T PARTITION(L1);
ID HIREDATE JOB
---------- --------- ---------------
1 01-JUL-13 CLERK
SQL> SELECT * FROM LIST_PART_T PARTITION(L2);
ID HIREDATE JOB
---------- --------- ---------------
23 29-SEP-13 SALESMAN
SQL> SELECT * FROM LIST_PART_T PARTITION(L3);
ID HIREDATE JOB
---------- --------- ---------------
41 20-AUG-13 ENGINEER
4. Adding value beyond the partition:
SQL> INSERT INTO LIST_PART_T VALUES('34', SYSDATE-30, 'ANALYST');
INSERT INTO LIST_PART_T VALUES('34', SYSDATE-30, 'ANALYST')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
5. Updating a record with non-defined partition value:
SQL> UPDATE LIST_PART_T
2 SET JOB='ANALYST'
3 WHERE JOB='ENGINEER';
UPDATE LIST_PART_T
*
ERROR at line 1:
ORA-14406: updated partition key is beyond highest legal partition key
6. Now updating with other partition value. Checking row movement.
SQL> UPDATE LIST_PART_T
SET JOB='CLERK'
WHERE JOB='ENGINEER';
1 row updated.
SQL> SELECT * FROM LIST_PART_T PARTITION(L3);
no rows selected
SQL> SELECT * FROM LIST_PART_T PARTITION(L1);
ID HIREDATE JOB
---------- --------- ---------------
1 01-JUL-13 CLERK
41 20-AUG-13 CLERK
7. Adding a new partition for defining a new Range:
ALTER TABLE LIST_PART_T
ADD PARTITION L4 VALUES('ANALYST');
8. Updating record and giving it new partition value.
SQL> SELECT * FROM LIST_PART_T PARTITION(L1);
ID HIREDATE JOB
---------- --------- ---------------
1 01-JUL-13 CLERK
41 20-AUG-13 CLERK
SQL> UPDATE LIST_PART_T
2 SET JOB='ANALYST'
3 WHERE ID=41;
1 row updated.
SQL> SELECT * FROM LIST_PART_T PARTITION(L1);
ID HIREDATE JOB
---------- --------- ---------------
1 01-JUL-13 CLERK
SQL> SELECT * FROM LIST_PART_T PARTITION(L4);
ID HIREDATE JOB
---------- --------- ---------------
41 20-AUG-13 ANALYST
9. Truncate Partition:
SQL> SELECT * FROM LIST_PART_T PARTITION(L2);
ID HIREDATE JOB
---------- --------- ---------------
23 29-SEP-13 SALESMAN
SQL> ALTER TABLE LIST_PART_T TRUNCATE PARTITION L2;
Table truncated.
SQL> SELECT * FROM LIST_PART_T PARTITION(L2);
no rows selected
10. Drop partition:
SQL> col format HIGH_VALUE format a10
SQL> set line 300
SQL> SELECT PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, TABLESPACE_NAME, COMPRESSION, LOGGING, NUM_ROWS
2 FROM user_tab_partitions
3 WHERE TABLE_NAME=UPPER('list_part_T');
PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME COMPRESS LOGGING NUM_ROWS
------------------------------ ---------- ------------------ ------------------------------ -------- ------- ----------
L1 'CLERK' 1 USERS DISABLED YES
L2 'SALESMAN' 2 USERS DISABLED YES
L3 'ENGINEER' 3 USERS DISABLED YES
L4 'ANALYST' 4 USERS DISABLED YES
SQL> alter table list_part_t drop partition l3;
Table altered.
SQL> alter table list_part_t drop partition l4;
Table altered.
SQL> SELECT PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, TABLESPACE_NAME, COMPRESSION, LOGGING, NUM_ROWS
2 FROM user_tab_partitions
3 WHERE TABLE_NAME=UPPER('list_part_T');
PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME COMPRESS LOGGING NUM_ROWS
------------------------------ ---------- ------------------ ------------------------------ -------- ------- ----------
L1 'CLERK' 1 USERS DISABLED YES
L2 'SALESMAN' 2 USERS DISABLED YES
11. Adding a composite partition:
ALTER TABLE LIST_PART_T
ADD PARTITION L3 VALUES ('ENGINEER', 'ANALYST')
PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME COMPRESS LOGGING NUM_ROWS
------------------------------ -------------------- ------------------ -------------------- -------- ------- ----------
L1 'CLERK' 1 USERS DISABLED YES
L2 'SALESMAN' 2 USERS DISABLED YES
L3 'ENGINEER', 'ANALYST' 3 USERS DISABLED YES
INSERT INTO LIST_PART_T VALUES('22', SYSDATE-70, 'ANALYST');
INSERT INTO LIST_PART_T VALUES('45', SYSDATE-40, 'ANALYST');
INSERT INTO LIST_PART_T VALUES('66', SYSDATE-55, 'ENGINEER');
COMMIT;
SQL> SELECT * FROM LIST_PART_T PARTITION(L3);
ID HIREDATE JOB
---------- --------- ---------------
45 30-AUG-13 ANALYST
66 15-AUG-13 ENGINEER
22 31-JUL-13 ANALYST
Q. How to rename a partition?
ALTER TABLE RANGE_PART_T
RENAME PARTITION P5 TO P4;
SQL> SELECT PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, TABLESPACE_NAME, COMPRESSION, LOGGING, NUM_ROWS
2 FROM user_tab_partitions
3 WHERE TABLE_NAME=UPPER('RANGE_part_T');
PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME COMPRESS LOGGING NUM_ROWS
------------------------------ -------------------- ------------------ -------------------- -------- ------- ----------
P1 10 1 USERS DISABLED YES 2
P2 20 2 USERS DISABLED YES 0
P3 35 3 USERS DISABLED YES 1
P5 60 4 USERS DISABLED YES
SQL> ALTER TABLE RANGE_PART_T RENAME PARTITION P5 TO P4;
Table altered.
SQL> SELECT PARTITION_NAME, HIGH_VALUE, PARTITION_POSITION, TABLESPACE_NAME, COMPRESSION, LOGGING, NUM_ROWS
2 FROM user_tab_partitions
3 WHERE TABLE_NAME=UPPER('RANGE_part_T');
PARTITION_NAME HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME COMPRESS LOGGING NUM_ROWS
------------------------------ -------------------- ------------------ -------------------- -------- ------- ----------
P1 10 1 USERS DISABLED YES 2
P2 20 2 USERS DISABLED YES 0
P3 35 3 USERS DISABLED YES 1
P4 60 4 USERS DISABLED YES
0 comments:
Post a Comment