1. Range
2. Hash and
3. List
Partions.
1. Range Partition.
This type of partition is very useful when dealing with data that has logical
ranges into which can be distributed eg. year.
SQL>create table purchase (pur_date date, pur_item varchar2(30), pur_amount number)
Partition by range (pur_date)
(
partition PUR2008 values less than (to_date('01-01-2009','DD-MM-YYYY')) tablespace users,
partition PUR2009 values less than (to_date('01-01-2010','DD-MM-YYYY')) tablespace users,
partition OTHERS values less than (maxvalue) tablespace users
)
/
Table created.
SQL> create index glb_index on purchase (pur_date);
Index created.
SQL> create index loc_index on purchase (pur_date, pur_item) local;
Index created.
SQL> select partition_name, table_name from user_Tab_partitions where table_name = 'PURCHASE';
PARTITION_NAME TABLE_NAME
------------------------------ ------------------------------
OTHERS PURCHASE
PUR2008 PURCHASE
PUR2009 PURCHASE
SQL> select INDEX_NAME,PARTITION_NAME ,STATUS,GLOBAL_STATS from user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS GLO
------------------------------ ------------------------------ -------- ---
LOC_INDEX PUR2008 USABLE NO
LOC_INDEX PUR2009 USABLE NO
LOC_INDEX OTHERS USABLE NO
SQL> select index_name, index_type, table_name,partitioned from user_indexes where table_name = 'PURCHASE';
INDEX_NAME INDEX_TYPE TABLE_NAME PAR
------------------------------ --------------------------- ------------------------------ ---
GLB_INDEX NORMAL PURCHASE NO
LOC_INDEX NORMAL PURCHASE YES
Selecting From Partitions:-
SQL> select * from purchase partition (pur2008);
PUR_DATE PUR_ITEM PUR_AMOUNT
--------- ------------------------------ ----------
01-JAN-08 Cupboards 50000
01-JAN-08 Cupboards 50000
SQL> select * from purchase partition (pur2009);
PUR_DATE PUR_ITEM PUR_AMOUNT
--------- ------------------------------ ----------
01-OCT-09 Computers 50000
01-NOV-09 Keyboards 2000
01-DEC-09 Mouse 1000
SQL> select * from purchase partition (others);
PUR_DATE PUR_ITEM PUR_AMOUNT
--------- ------------------------------ ----------
01-MAY-10 Speakers 500
01-JUL-10 Monitors 10000
Split Partitions:-
SQL> alter table purchase split partition others at (to_date('01-01-2011','DD-MM-YYYY')) into (partition PUR2010, partition OTHERS);
Table altered.
SQL> select * from purchase partition (pur2010);
PUR_DATE PUR_ITEM PUR_AMOUNT
--------- ------------------------------ ----------
01-MAY-10 Speakers 500
01-JUL-10 Monitors 10000
SQL> select * from purchase partition (others);
no rows selected
To Drop Partitions:-
SQL> alter table purchase drop partition PUR2008 update global indexes;
Table altered.
Note:-
While dropping a partition if that particular partitions contains
any data then we need to add the 'UPDATE GLOBAL INDEXES' keyword
it the table has any global indexes else the indexes will be unusable.
Alternatively we can also perform the below methods so that index will became
usable.
a)
SQL> alter table purchase drop partition PUR2008;
SQL> alter index glb_index rebuild;
or
b)
SQL> delete from purchase partition (PUR2010);
SQL> alter table purchase drop partition PUR2010;
To Add Partition:-
SQL> alter table purchase add partition PUR2011 values less than (to_date('01-01-2012','DD-MM-YYYY'));
alter table purchase add partition PUR2011 values less than (to_date('01-01-2012','DD-MM-YYYY'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
SQL> alter table purchase drop partition OTHERS update global indexes;
Table altered.
SQL> alter table purchase add partition PUR2011 values less than (to_date('01-01-2012','DD-MM-YYYY'));
Table altered.
Convert A Partition Into A Stand-alone Table Using Exchange Partition.
Syntax:-
ALTER TABLE
WITH TABLE
SQL> create table purchase_2008 as select * from purchase where 1 =2;
Table created.
SQL> desc purchase_2008;
Name Null? Type
----------------------------------------- -------- ----------------------------
PUR_DATE DATE
PUR_ITEM VARCHAR2(30)
PUR_AMOUNT NUMBER
SQL> alter table purchase exchange partition PUR2008 with table purchase_2008;
Table altered.
SQL> select * from purchase partition (PUR2008);
no rows selected
SQL> select * from purchase_2008;
PUR_DATE PUR_ITEM PUR_AMOUNT
--------- ------------------------------ ----------
01-JAN-08 Cupboards 50000
01-FEB-08 Cots 50000
Mergeing contents of one partitions into another partitions, Once it is
merged the partition will me removed. Once Merge is completed
rebuild the indexes.
SQL> select * from purchase partition (PUR2011);
no rows selected
SQL> SELECT * FROM PURCHASE PARTITION (PUR2010);
PUR_DATE PUR_ITEM PUR_AMOUNT
--------- ------------------------------ ----------
01-JAN-08 Cupboards 50000
01-JAN-08 Cupboards 50000
01-OCT-09 Computers 50000
01-NOV-09 Keyboards 2000
01-DEC-09 Mouse 1000
01-MAY-10 Speakers 500
01-JUL-10 Monitors 10000
7 rows selected.
SQL> alter table purchase merge partitions pur2010, pur2011 into partition pur2011;
Table altered.
SQL> select * from purchase partition (PUR2011);
PUR_DATE PUR_ITEM PUR_AMOUNT
--------- ------------------------------ ----------
01-JAN-08 Cupboards 50000
01-JAN-08 Cupboards 50000
01-OCT-09 Computers 50000
01-NOV-09 Keyboards 2000
01-DEC-09 Mouse 1000
01-MAY-10 Speakers 500
01-JUL-10 Monitors 10000
7 rows selected.
SQL> SELECT * FROM PURCHASE PARTITION (PUR2010);
SELECT * FROM PURCHASE PARTITION (PUR2010)
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> select index_name, status from user_indexes where table_name = 'PURCHASE';
INDEX_NAME STATUS
------------------------------ --------
GLB_INDEX UNUSABLE
LOC_INDEX N/A
SQL> alter index glb_index rebuild;
Index altered.
SQL> select index_name, status from user_indexes where table_name = 'PURCHASE';
INDEX_NAME STATUS
------------------------------ --------
GLB_INDEX VALID
LOC_INDEX N/A
Truncating Partitions:-
SQL> alter table purchase truncate partition pur2011 update indexes;
Table truncated.
Hash Partitions:-
Use hash partition if your data does not easily lend itself to range partition,
but you would like to partition for performance and manageibility reasons.
Hash partition provides a method of evenly distributing data accross a specified
number of partitons. Rows are mapped nto partitions based on a hash value
of the Partition key.
Eg.
Create a Hash Partitioned Table.
SQL> create table purchase (pur_date date, pur_item varchar2(30))
partition by hash (pur_date)
partitions 4
store in (users, tools, data, data)
Table created.
SQL> SELECT PARTITION_NAME, TABLE_NAME FROM USER_TAB_PARTITIONS;
PARTITION_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_P21 PURCHASE
SYS_P22 PURCHASE
SYS_P23 PURCHASE
SYS_P24 PURCHASE
Add Partition:-
SQL> alter table purchase add partition;
Table altered.
SQL> SELECT PARTITION_NAME, TABLE_NAME FROM USER_TAB_PARTITIONS;
PARTITION_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_P22 PURCHASE
SYS_P23 PURCHASE
SYS_P24 PURCHASE
SYS_P21 PURCHASE
SYS_P25 PURCHASE
Note :- You cannot drop a hash partiton.
List partition:-
Use list artition when you require explict control over how rows map to partitions.
You can specify a list of discret e values for the partitioning column in the
description for each partition.
SQL> create table customers (name varchar2(30), city varchar2(30), addr varchar2(60))
partition by list (city)
(
partition north_india values ('DELHI','BOMBAY','CALCUTTA') tablespace users,
partition south_india values ('CHENNAI','BANGALORE','KOCHIN') tablespace users
);
Table created.
SQL> select partition_name , table_name from user_tab_partitions;
PARTITION_NAME TABLE_NAME
------------------------------ ------------------------------
NORTH_INDIA CUSTOMERS
SOUTH_INDIA CUSTOMERS
Adding additional values to a partition.
SQL> alter table customers MODIFY PARTItion SOUTH_INDIA add values ('MANGALORE');
Table altered.
Add partition:-
SQL> alter table customers add partition others values ('OTHERS');
Table altered.
Drop partiton:-
SQL> alter table customers drop partition others update indexes;
Table altered.
No comments:
Post a Comment