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 1. Using 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:
Post a Comment