PARTITIONSPARTITION in Oracle database can be defined as a concept in which data objects like tables, indexes, and index-organized tables can be reduced or subdivided into smaller areas or pieces and these portioned data objects can be accessed at the much finer level which helps in increasing the performance as it works on only on the relevant data, it is also easy to maintain and decreases the cost by appropriately storing the data and also increases the availability. Partition independance means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitons available.
SQL> create user tesdb identified by tesdb; User created. SQL> grant connect,resource,unlimited tablespace to tesdb; Grant succeeded. SQL> conn tesdb/tesdb Connected. Range Partition : (its based on number and date) A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range. Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator. SQL> create table tesdb_range (id int, name varchar2(20)) partition by range (id) (partition p1 values less than (10), partition p2 values less than (20), partition p3 values less than (30)); Table created. SQL> begin for i in 0..10 loop insert into tesdb_range values (i,'aa'); end loop; end; / PL/SQL procedure successfully completed. To View partitions : SQL> desc user_tab_partitions; SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ -------------------- ---------------------- TESDB_RANGE P1 10 TESDB_RANGE P2 20 TESDB_RANGE P3 30 To Check values in each partitions: SQL> select * from tesdb_range partition(p1); ID NAME ---------- ----------- 0 aa 1 aa 2 aa 3 aa 4 aa 5 aa 6 aa 7 aa 8 aa 9 aa 10 rows selected. SQL> select * from tesdb_range partition(p2); ID NAME ---------- -------------------- 10 aa SQL> select * from tesdb_range partition(p3); no rows selected Now we add some records out of the range partition . it can be error of (ORA-14400: inserted partition key does not map to any partition) SQL> insert into tesdb_range values (30,'aa'); insert into tesdb_range values (30,'aa') * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition We can?t add value of above 29, if we add the value above error will be occurred, on that case we set the (max value) conditions. In order to add values above mentioned range we can add a new partition with max value SQL> alter table tesdb_range add partition p4 values less than(maxvalue); Table altered. Drop Partition : SQL> alter table tesdb_range drop partition(p2); Table altered. SQL> select * from tesdb_range partition(p2); select * from tesdb_range partition(p2) * ERROR at line 1: ORA-02149: Specified partition does not exist Truncate the partiton: alter table tesdb_range truncate partition(p1); Table truncated. SQL> select * from tesdb_range partition(p1); no rows selectedMove Partition across tablespace : SQL> alter table tesdb_range move partition p1 tablespace users; Table altered.Rename Partition : SQL> select * from tesdb_range partition(p4); ID NAME ---------- -------------------- 40 ccc Now, rename the partition p4 to p6, SQL> alter table tesdb_range rename partition p4 to p6; SQL> Table altered.Now, check the p4 partition, SQL> select * from tesdb_range partition(p4); select * from tesdb_range partition(p4) * ERROR at line 1: ORA-02149: Specified partition does not exist SQL> select * from tesdb_range partition(p6); ID NAME ---------- -------------------- 40 ccc Split Partition : In p6 partition we have 6 values, SQL> select * from tesdb_range partition(p6); ID NAME ---------- -------------------- 40 ccc 41 ddd 45 rrr 50 yyy 100 hhh 80 uuu SQL> alter table tesdb_range split partition p6 at(45) into (partition p6a,partition p6b); Table altered. SQL> select * from tesdb_range partition(p6a); ID NAME ---------- -------------------- 40 ccc 41 ddd SQL> select * from tesdb_range partition(p6b); ID NAME ---------- -------------------- 45 rrr 50 yyy 100 hhh 80 uuu Views for Partition : SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------- --------------- ------------------ TESDB_RANGE P1 10 TESDB_RANGE P3 30 TESDB_RANGE P6A 45 TESDB_RANGE P6B MAXVALUE Merge Partition : SQL> alter table tesdb_range merge partitions p6a,p6b into partition p6; Table altered. SQL> select * from tesdb_range partition (p6a); select * from tesdb_range partition (p6a) * ERROR at line 1: ORA-02149: Specified partition does not exist SQL> select * from tesdb_range partition (p6); ID NAME ---------- -------------------- 40 ccc 41 ddd 45 rrr 50 yyy 100 hhh 80 uuu 6 rows selected. Interval Partition : It is a advanced concept of range partition(11g). Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition. Range or interval partitioning is often used to organize data by time intervals on a column of type DATE. Interval Partition Oracle create any partition whenever needed for data at the time Insesrted. It Improves the manageability. Interval Interval-list Interval-hast Interval-range interval-reference Key column must be number/date type. SQL> create table tesdb_interval (id int, name varchar2(20)) partition by range (id) interval (10) (partition i1 values less than (10), partition i2 values less than (20)); Table created. SQL> begin for i in 0..50 loop insert into tesdb_interval values (i,'aa'); end loop; end; / PL/SQL procedure successfully completed. select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE -------------------- ------------------------------ ------------------------------ TESDB_INTERVAL I1 10 TESDB_INTERVAL I2 20 TESDB_INTERVAL SYS_P261 30 TESDB_INTERVAL SYS_P262 40 TESDB_INTERVAL SYS_P263 50 TESDB_INTERVAL SYS_P264 60 TESDB_RANGE P3 30 TESDB_RANGE P5 10 TESDB_RANGE P6 MAXVALUE 9 rows selected. List Partition : List partitioning was added as a partitioning method in Oracle 9i, Release 1. List partitioning allows for partitions to reflect real-world groupings (e.g.. business units and territory regions). List partitioning differs from range partition in that the groupings in list partitioning are not side-by-side or in a logical range. SQL> create table tesdb_list (id int,city varchar2(20)) partition by list (city) (partition p_1 values ('chennai'), partition p_2 values ('madurai'), partition p_3 values (default)); Table created. select * from tesdb_list; no rows selected insert into tesdb_list values(1,'chennai'); insert into tesdb_list values(2,'madurai'); insert into tesdb_list values(1,''); insert into tesdb_list values(4,'chennai'); insert into tesdb_list values(5,'madurai'); insert into tesdb_list values(6,''); SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> select * from tesdb_list partition (p_1); ID CITY ---------- -------------------- 4 chennai SQL> select * from tesdb_list partition (p_2); ID CITY ---------- -------------------- 2 madurai 5 madurai SQL> select * from tesdb_list partition (p_3); ID CITY ---------- -------------------- 1 Now insert the city coloumn, any other city(salem), it will be consider default partitions. SQL> insert into tesdb_list values(10,'salem'); 1 row created. SQL> select * from tesdb_list Partition (p_3); ID CITY ---------- -------------------- 1 10 salem HASH PARTITION : (it will taken random partitions) hash partitioning, a row is placed into a partition based on the result of passing the partitioning key into a hashing algorithm. Using this approach, data is randomly distributed across the partitions rather than grouped. This is a good approach for some data, but may not be an effective way to manage historical data. SQL> create table tesdb_hash (id int,name varchar2(20)) partition by hash(id) (partition h1, partition h2, partition h3); Table created. SQL> begin for i in 1..15 loop insert into tesdb_hash values (1,'a'); end loop; end; / PL/SQL procedure successfully completed. SQL> select * from tesdb_hash partition (h1); no rows selected SQL> select * from tesdb_hash partition (h2); ID NAME ---------- -------------------- 1 a 1 a 1 a 1 a 1 a 1 a 1 a 1 a 1 a 1 a 1 a ID NAME ---------- -------------------- 1 a 1 a 1 a 1 a 15 rows selected. SQL> select * from tesdb_hash partition (h3); no rows selected System Partition : System partitioning is a partitioning method introduced in Oracle 11g that allows an application to control partition selection. Unlike other partitioning schemes, system partitions do not have partition keys. As such, partition extended syntax must be used when inserting rows into system partitioned tables SQL> create table tesdb_system (id int, name varchar2(20)) partition by system (partition p1, partition p2); Table created. SQL> insert into tesdb_system partition (p1) values (1,'a'); 1 row created. SQL> insert into tesdb_system partition (p2) values (2,'b'); 1 row created. SQL> declare i number(5); begin for i in 1..20 loop insert into tesdb_system partition (p1) values (i,'a'); end loop; end; / PL/SQL procedure successfully completed. SQL> select * from tesdb_system partition (p1); ID NAME ---------- -------------------- 1 a 1 a 2 a 3 a 4 a 5 a 6 a 7 a 8 a 9 a 10 a ID NAME ---------- -------------------- 11 a 12 a 13 a 14 a 15 a 16 a 17 a 18 a 19 a 20 a 21 rows selected. SQL> select * from tesdb_system partition (p2); ID NAME ---------- -------------------- 2 b Composite Partition : (SUB partition inside another partitions) Composite partitioning partitions the data rows using the range method, and within each partition, Oracle subpartitions the data using many methods. Oracle experts note that composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity.. of data placement through sub-partitioning. SQL> create table tesdb_composite (id int,name varchar2(20),deptno int) partition by range(id) subpartition by list (deptno) subpartition template( subpartition sub1 values (10,20,30) , subpartition sub2 values (40,50,60) , subpartition sub3 values (70,80,90)) (partition p1 values less than(10), partition p2 values less than(20), partition p3 values less than(maxvalue)); Table created. SQL> begin insert into tesdb_composite values (1,'aa',10); insert into tesdb_composite values (2,'ba',20); insert into tesdb_composite values (3,'ca',30); insert into tesdb_composite values (4,'da',40); insert into tesdb_composite values (5,'ea',50); insert into tesdb_composite values (6,'fa',60); insert into tesdb_composite values (7,'ga',70); insert into tesdb_composite values (8,'ha',80); insert into tesdb_composite values (9,'ia',90); end; / PL/SQL procedure successfully completed. SQL> insert into tesdb_composite values (19,'aa',10); 1 row created. SQL> insert into tesdb_composite values (18,'aa',10); 1 row created. SQL> insert into tesdb_composite values (5,'aa',10); 1 row created. SQL> SELECT partition_name, subpartition_name, tablespace_name, high_value FROM user_tab_subpartitions WHERE table_name = 'TESDB_COMPOSITE';
Reference Partition : Reference partitioning provides the ability to partition a table based on the partitioning scheme of the table referenced in its referential constraint. A table can now be partitioned based on the partitioning method of a table referenced in its referential constraint. Tables with a parent/child relationship can be equipartitioned by inheriting the partitioning key from the parent table without duplication of the key columns. If the parent table is a composite-partitioned table, then the table will have one partition for each subpartition of its parent. Reference Partitioning allows you to use a Referential Integrity Constraint to equi-partition a "Child" Table with a "Parent" Table. SQL> create table ref_tesdb(id int constraint pk2 primary key,place varchar(30)) partition by list(place) (partition p1 values('chennai'), partition p2 values('mumbai')); Table created. SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='REF_TESDB'; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ -------------------- ---------------------------------------- REF_TESDB P1 'chennai' REF_TESDB P2 'mumbai' SQL > create table fore_tesdb(name varchar2(20) not null,sid int not null,constraint fk2 foreign key(sid) ☛ Join to Learn from Experts: Oracle SQL Training in Chennai by TesDBAcademy
« Previous
Next Topic »
(Oracle Database - Flash back)
|