It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

LIST Partitioning Explored

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.
  • NULL is 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


1 comments:

Looking for the Ultimate Dating Website? Join and find your perfect date.

 

You Might Also Like

Related Posts with Thumbnails

Pages