How to Create and Manage Partition Tables Tables in Oracle Table Partitioning
Now a days enterprises run databases of hundred of Gigabytes Gigabytes in size. These These databases are known as ery !arge "atabases #!"$%. &ro' Oracle er. (.) Oracle has pro*ided the feature of table partitioning i.e. you can partition a table according to so'e criteria . &or e+a'ple you ha*e a ,-!, table with the following structure ,uppose this table contains 'illions of records/ but all the records belong to four years only i.e. 0110/ 0112/ 0113 and 0114. -nd 'ost of the ti'e you are concerned about only only one year i.e. you gi*e 5ueries like the following select su'#a't% fro' sales where year601107 select product/su'#a't% fro' sales where year60112 Group by product7 Now whene*er you gi*e gi*e 5ueries like this Oracle Oracle will search the whole whole table. 8f you partition this table according to year/ then the perfor'ance is i'pro*e since oracle will scan only a single partition instead of whole table. CREATING PARTITION TABLES
To create create a partition table gi*e the following state'ent create table sales #year nu'ber#4%/ product *archar2#0)%/ a't nu'ber#0)/2%% partition by range #year% partition p0 *alues less than #0112% tablespace u0/ partition p2 *alues less than #0113% tablespace u2/ partition p3 *alues less than #0114% tablespace u3/ partition p4 *alues less than #0119% tablespace u4/ partition partiti on p9 *alues less than #M-:-!;% #M-:-!;% tablespace u97 8n the abo*e e+a'ple sales table is created with 9 partitions. Partition p0 will contain rows of year 0110 and
it will be stored in tablespace u0. Partition p2 will contain rows of year 0112 and it will be stored in tablespace u2. ,i'ilarly p3 and p4. 8n the abo*e e+a'ple if you don
•
=ange Partitioning Hash Partitioning
•
!ist Partitioning
•
Co'posite Partitioning
Range Partitioning
This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed7 for e+a'ple/ *alue of year. Perfor'ance is best when the data e*enly distributes across the range Hash partitioning
;se hash partitioning if your data does not easily lend itself to range partitioning/ but you would like to partition for perfor'ance and 'anageability reasons. Hash partitioning pro*ides a 'ethod of e*enly distributing data across a specified nu'ber of partitions. =ows are 'apped into partitions based on a hash *alue of the partitioning key The following e+a'ple shows how to create a hash partition table.
The following e+a'ple creates a hash>partitioned table. The partitioning colu'n is partno/ four partitions are created and assigned syste' generated na'es/ and they are placed in four na'ed tablespaces #tab0/tab2/ ...%. C=-T T-$! products #partno N;M$=/ description -=CH-=2 #?)%% P-=T8T8ON $@ H-,H #partno% P-=T8T8ON, 4 ,TO= 8N #tab0/ tab2/ tab3/ tab4%7
List Partitioning
;se list partitioning when you re5uire e+plicit control o*er how rows 'ap to partitions. @ou can specify a list of discrete *alues for the partitioning colu'n in the description for each partition. This is different fro' range partitioning/ where a range of *alues is associated with a partition/ and fro' hash partitioning/ where the user has no control of the row to partition 'apping.
!ist partitioning allows unordered and unrelated sets of data to be grouped and organized together *ery naturally The following e+a'ple creates a table with list partitioning create table custo'ers #custcode nu'ber#9%/ Na'e *archar2#2)%/ -ddr *archar2#0)/2%/ City *archar2#2)%/ $al nu'ber#0)/2%% Partition by list #city%/ Partition northA8ndia *alues #B"!H8
8f a row is inserted in the abo*e table then oracle 'aps the *alue of city colu'n and whiche*er partition list 'atches the city colu'n the row is stored in that partition.
COMPOSITE PARTITONING
Co'posite partitioning partitions data using the range 'ethod/ and within each partition/ subpartitions it using the hash 'ethod. Co'posite partitions are ideal for both historical data and striping/ and pro*ide i'pro*ed 'anageability of range partitioning and data place'ent/ as well as the parallelis' ad*antages of hash partitioning. Dhen creating co'posite partitions/ you specify the followingE •
•
Partitioning 'ethodE range Partitioning colu'n#s%
•
Partition descriptions identifying partition bounds
•
,ubpartitioning 'ethodE hash
•
,ubpartitioning colu'n#s%
•
Nu'ber of subpartitions for each partition or descriptions of subpartitions
The following state'ent creates a co'posite>partitioned table. 8n this e+a'ple/ three range partitions are created/ each containing eight subpartitions. $ecause the subpartitions are not na'ed/ syste' generated na'es are assigned/ but the ,TO= 8N clause distributes the' across the 4 specified tablespaces #tab0/ .../tab4%. C=-T T-$! P=O";CT, #partno N;M$=/ description -=CH-=#32%/ costprice N;M$=% P-=T8T8ON $@ =-NG #partno% ,;$P-=T8T8ON $@ H-,H#description% ,;$P-=T8T8ON, ( ,TO= 8N #tab0/ tab2/ tab3/ tab4% #P-=T8T8ON p0 -!;, !,, TH-N #0))%/
P-=T8T8ON p2 -!;, !,, TH-N #2))%/ P-=T8T8ON p3 -!;, !,, TH-N #M-:-!;%%7
LISTING INFORMATION ABOUT PARTITION TABLES
To see how 'any partitioned tables are there in your sche'a gi*e the following state'ent select F fro' userApartAtables7 To see on partition le*el partitioning infor'ation select F fro' userAtabApartitions7 ALTERING PARTITION TABLES
To add a partition @ou can add add a new partition to the high end #the point after the last e+isting partition%. To add a partition at the beginning or in the 'iddle of a table/ use the ,P!8T P-=T8T8ON clause. &or e+a'ple to add a partition to sales table gi*e the following co''and. alter table sales add partition p? *alues less than #011?%7 To add a partition to a Hash Partition table gi*e the following co''and. alter table products add partition7 Then Oracle adds a new partition whose na'e is syste' generated and it is created in the default tablespace. To add a partition by user define na'e and in your specified tablespace gi*e the following co''and. alter table products add partition p9 tablespace u97 To add a partition to a !ist partition table gi*e the following co''and.
alter table custo'ers add partition centralA8ndia *alues #B$HOP-!
Coalescing partitions is a way of reducing the nu'ber of partitions in a hash> partitioned table/ or the nu'ber of subpartitions in a co'posite>partitioned table. Dhen a hash partition is coalesced/ its contents are redistributed into one or 'ore re'aining partitions deter'ined by the hash function. The specific partition that is coalesced is selected by Oracle/ and is dropped after its contents ha*e been redistributed. To coalesce a hash partition gi*e the following state'ent. alter table products coalesce partition7 This reduces by one the nu'ber of partitions in the table products. !ROPPING PARTITIONS
To drop a partition fro' =ange Partition table/ !ist Partition or Co'posite Partition table gi*e the following co''and. alter table sales drop partition p97 Once you ha*e drop the partition and if you ha*e created a global inde+ on the table. Then you ha*e to rebuild the global inde+ after dropping the partition by gi*ing the following state'ent. alter inde+ salesAind rebuild7 To a*oid rebuilding of inde+es after dropping of the partitions you can also first delete all the records and then drop the partition like this delete fro' sales where year601147 alter table sales drop partition p47
This 'ethod is 'ost appropriate for s'all tables/ or for large tables when the partition being dropped contains a s'all percentage of the total data in the table. -nother 'ethod of dropping partitions is gi*e the following state'ent. -!T= T-$! sales "=OP P-=T8T8ON p9 ;P"-T G!O$-! 8N":,7 This causes the global inde+ to be updated at the ti'e the partition is dropped. E"hanging a Range# Hash# or List Partition
To e+change a partition of a range/ hash/ or list>partitioned table with a nonpartitioned table/ or the re*erse/ use the -!T= T-$! ... :CH-NG P-=T8T8ON state'ent. -n e+a'ple of con*erting a partition into a nonpartitioned table follows. 8n this e+a'ple/ table stocks can be range/ hash/ or list partitioned. -!T= T-$! stocks :CH-NG P-=T8T8ON p3 D8TH stockAtableA37 Merging Partitions
;se the -!T= T-$! ... M=G P-=T8T8ON, state'ent to 'erge the contents of two partitions into one partition. Te two original partitions are dropped/ as are any corresponding local inde+es. @ou cannot use this state'ent for a hash>partitioned table or for hash subpartitions of a co'posite>partitioned table. @ou can only 'erged two adacent partitions/ you cannot 'erge non adacent partitions. &or e+a'ple the 'erge the partition p2 and p3 into one partition p23 gi*e the following state'ent. alter table sales 'erge partition p2 and p3 into partition p237 Mo$i%&ing Partitions' A$$ing (al)es
;se the MO"8&@ P-=T8T8ON ... -"" -!;, clause of the -!T= T-$! state'ent to e+tend the *alue list of an e+isting partition. !iteral *alues being added 'ust not ha*e been included in any other partitionIs *alue list. The partition
*alue list for any corresponding local inde+ partition is correspondingly e+tended/ and any global inde+/ or global or local inde+ partitions/ re'ain usable. The following state'ent adds a new set of cities #IOCH8I/ IM-NG-!O=I% to an e+isting partition list. -!T= T-$! custo'ers MO"8&@ P-=T8T8ON southAindia -"" -!;, #IOCH8I/ IM-NG-!O=I%7 Mo$i%&ing Partitions' !ropping (al)es
;se the MO"8&@ P-=T8T8ON ... "=OP -!;, clause of the -!T= T-$! state'ent to re'o*e literal *alues fro' the *alue list of an e+isting partition. The state'ent is always e+ecuted with *alidation/ 'eaning that it checks to see if any rows e+ist in the partition that correspond to the set of *alues being dropped. 8f any such rows are found then Oracle returns an error 'essage and the operation fails. Dhen necessary/ use a "!T state'ent to delete corresponding rows fro' the table before atte'pting to drop *alues. @ou cannot drop all literal *alues fro' the *alue list describing the partition. @ou 'ust use the -!T= T-$! ... "=OP P-=T8T8ON state'ent instead. The partition *alue list for any corresponding local inde+ partition reflects the new *alue list/ and any global inde+/ or global or local inde+ partitions/ re'ain usable. The state'ent below drops a set of cities #BOCH8I and IM-NG-!O=I% fro' an e+isting partition *alue list. -!T= T-$! custo'ers MO"8&@ P-=T8T8ON southAindia "=OP -!;, #BOCH8
@ou can split a single partition into two partitions. &or e+a'ple to split the partition p9 of sales table into two partitions gi*e the following co''and alter table sales split partition p9 into #Partition p? *alues less than #011?%/ Partition pJ *alues less then #M-:-!;%%7
TRUNCATING PARTITON
Truncating a partition will delete all rows fro' the partition. To truncate a partition gi*e the following state'ent alter table sales truncate partition p97
