It's All About ORACLE

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

Working with HASH Partition Tables

Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. With range or list partitioning, you must specify explicitly into which partition a given column value or set of column values is to be stored; with hash partitioning, MySQL takes care of this for you, and you need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.

1. Create a partition table:
CREATE TABLE Hash_Part_T
( ID  NUMBER(5),
HIREDATE DATE,
JOB VARCHAR2(15)
)
PARTITION BY HASH(HIREDATE)
(PARTITION H1, PARTITION H2, PARTITION H3, PARTITION H4);

OR 
CREATE TABLE Hash_Part_T1
( ID  NUMBER(5),
HIREDATE DATE,
JOB VARCHAR2(15)
)
PARTITION BY HASH(HIREDATE)
PARTITIONS 4;

OR 
We can specify tablespace:
create table emp2 (
   empno number(4), 
   ename varchar2(30), 
   sal   number
partition by hash(empno) (
  partition e1 tablespace emp1, 
  partition e2 tablespace emp2, 
  partition e3 tablespace emp3,
  partition e4 tablespace emp4
); 

create table emp2 (
   empno number(4), 
   ename varchar2(30), 
   sal   number
PARTITION BY HASH(empno)
PARTITIONS 3
STORE IN (empts1, empts2, empts3);

If you do not include a PARTITIONS clause, the number of partitions defaults to 1Using the PARTITIONS keyword without a number following it results in a syntax error.

2. INSERT and CHECK values all 4 partitions:
INSERT INTO Hash_Part_T  VALUES
INSERT INTO Hash_part_T VALUES(12, SYSDATE-100, 'CLERK');
INSERT INTO Hash_part_T VALUES(22, SYSDATE-200, 'ASSISTANT');
INSERT INTO Hash_part_T VALUES(9, SYSDATE-50, 'SALESMAN');
INSERT INTO Hash_part_T VALUES(34, SYSDATE-10, 'CLERK');
INSERT INTO Hash_part_T VALUES(78, SYSDATE-30, 'ENGINEER');
INSERT INTO Hash_part_T VALUES(99, SYSDATE-150, 'ANALYST');

SQL> SELECT * FROM Hash_part_t partition(h1);

        ID HIREDATE  JOB
---------- --------- ---------------
        12 05-JUL-13 CLERK
        78 13-SEP-13 ENGINEER

SQL> SELECT * FROM Hash_part_t partition(h2);

        ID HIREDATE  JOB
---------- --------- ---------------
        22 27-MAR-13 ASSISTANT
        34 03-OCT-13 CLERK

SQL> SELECT * FROM Hash_part_t partition(h3);

        ID HIREDATE  JOB
---------- --------- ---------------
         9 24-AUG-13 SALESMAN

SQL> SELECT * FROM Hash_part_t partition(h4);

        ID HIREDATE  JOB
---------- --------- ---------------
        99 16-MAY-13 ANALYST

3. Updating a record in partition like we did with Range and List Paritioning and check if it is updates it''s partition or not:
SQL> UPDATE     Hash_part_t
  2  SET HIREDATE=SYSDATE-200
  3  WHERE ID=34;

1 row updated.

SQL> SELECT * FROM Hash_part_t partition(h2);

        ID HIREDATE  JOB
---------- --------- ---------------
        22 27-MAR-13 ASSISTANT
        34 27-MAR-13 CLERK
Partition of the record, is not changed in case we update key value of partition.

4. We can insert any value. There is no key value restiction. Neither ORA-14400: 'inserted partition key does not map to any partition' Error nor ORA-14406: 'updated partition key is beyond highest legal partition key' while inserting or updating values, like we faced in Range and List Partitioning. 

5. Adding a new Partition:
ALTER TABLE Hash_part_t
ADD PARTITION;

PARTITION_NAME                 HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME                COMPRESS LOGGING   NUM_ROWS
------------------------------ ---------- ------------------ ------------------------------ -------- ------- ----------
H1                                                         1 USERS                          DISABLED YES              0
H2                                                         2 USERS                          DISABLED YES              0
H3                                                         3 USERS                          DISABLED YES              0
H4                                                         4 USERS                          DISABLED YES              0
SYS_P45                                                    5 USERS                          DISABLED YES

Also,
SQL> ALTER TABLE HASH_PART_T
  2  ADD PARTITION H6;
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('Hash_part_T');

PARTITION_NAME                 HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME                COMPRESS LOGGING   NUM_ROWS
------------------------------ ---------- ------------------ ------------------------------ -------- ------- ----------
H1                                                         1 USERS                          DISABLED YES              0
H2                                                         2 USERS                          DISABLED YES              0
H3                                                         3 USERS                          DISABLED YES              0
H4                                                         4 USERS                          DISABLED YES              0
SYS_P45                                                    5 USERS                          DISABLED YES
H6                                                         6 USERS                          DISABLED YES
6 rows selected. 

6.. Truncating a partition of Hash Partitioned table:
ALTER TABLE HASH_PART_T
TRUNCATE PARTITION SYS_P45;

7. Drop a Parition:
Dropping a parition is not allowed in HASH partitioning. If you try to drop using DROP PARTITION command you will get following errors:
SQL> ALTER TABLE HASH_PART_T
  2  DROP PARTITION SYS_P45;
DROP PARTITION SYS_P45
               *
ERROR at line 2:
ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method

8. COALESCE - We can not drop a partition from Hash Paritioned table, However we can coalesce partitions:
ALTER TABLE Hash_part_t
COALESCE PARTITIONS 2;

SQL> ALTER TABLE Hash_part_t
  2  COALESCE PARTITION;

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('Hash_part_T');

PARTITION_NAME                 HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME                COMPRESS LOGGING   NUM_ROWS
------------------------------ ---------- ------------------ ------------------------------ -------- ------- ----------
H1                                                         1 USERS                          DISABLED YES              0
H2                                                         2 USERS                          DISABLED YES              0
H3                                                         3 USERS                          DISABLED YES              0
H4                                                         4 USERS                          DISABLED YES              0
SYS_P45                                                    5 USERS                          DISABLED YES

SQL> ALTER TABLE Hash_part_t
  2  COALESCE PARTITION;

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('Hash_part_T');

PARTITION_NAME                 HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME                COMPRESS LOGGING   NUM_ROWS
------------------------------ ---------- ------------------ ------------------------------ -------- ------- ----------
H1                                                         1 USERS                          DISABLED YES              0
H2                                                         2 USERS                          DISABLED YES              0
H3                                                         3 USERS                          DISABLED YES              0
H4                                                         4 USERS                          DISABLED YES              0

1 comments:

This comment has been removed by a blog administrator.
 

You Might Also Like

Related Posts with Thumbnails

Pages