Part 1: Partitioning of Tables:
Introduction:
Partitioning addresses in supporting very large tables and indexes by letting you decompose into small and more managable pieces called "Partitions".
It is like concept devide and conquer which is very useful to the very large tables and indexes in VLDBs.
sql queries and DML statements do not need to be modified in order to access the partitioned tables.
All partitions of the partitioned object should reside on the tablespaces of same block size.
Tables can be partitioned into up to 64,000( till 10g 64K-1) (from 10g 1024K-1) seperate partitions.Any table can ne partitioned except those tables containing columns with LONG or LONG RAW, however we can partition the tables with columns CLOBs and BLOBs.
When to partition a table:
Table greater than 2GB should always be considered for partitioning.
Tables contains historical data in which the new data is added into the newest partition. Example for historical table is where only the current month's data is updatable and other months data is read-only.
When performance benifits, the additional maintanance at the partition level is required.
Advanatages of partitiong:
1. Management at individual partition level for data loads,index creation and rebuilding, backup/recovery.
2. Concurrent operations can be performed on different partitions of the same table/index.
3.Parallel DML operations can be performed at partition level.This reduces the response time for data-intensive operations in VLDBs.you can use parallel query and parallel DML with range- and hash-partitioned tables.
4.Partition wise joining which brokes the smaller joins that are performed either in parallel or sequential.
5. Increased query performance by selecting only relavant partitions rather than entire table by using technique called "partition pruning".
Partition pruning:
The Oracle server explicitly recognizes partitions and
subpartitions. It then optimizes SQL statements to mark the partitions or
subpartitions that need to be accessed and eliminates (prunes) unnecessary
partitions or subpartitions from access by those SQL statements.
In other words,
partition pruning is the skipping of unnecessary index and data partitions or
subpartitions in a query.
The optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column (with the exception of the
The optimizer cannot prune partitions if the SQL statement applies a function to the partitioning column (with the exception of the
TO_DATE
function). Similarly, the optimizer cannot use an index if the SQL statement
applies a function to the indexed column, unless it is a function-based indexPartition key is set of one or more columns that determines the partition for each row.Oracle automatically directs insert/update/delete operations to the appropriate partitions through the use of partition key.
partition key can't contain a LEVEL,rowid, pseudo columns or a column of type rowid.
When the partition key value has been updated by sql, oracle auotmatically moves the row into appropriate partition using row movement.
If you want to select the data from perticular partiton:
SELECT * FROM supplier_parts PARTITION (p1);Partition Methods:
Range partitions: Used when there are logical ranges of data.
Possible usage: dates, part numbers, and serial numbers, salary ranges etc.
List partitinos: Used to list together unrelated data into partitions.
Possible usage: a number of states list partitioned into a region.
Hash partitions: - Used to spread data evenly over partitions.
Possible usage: data has no logical groupings.
Composite partitions: Is a combination of other partitioning methods
Ex: 1)Range-Hash : Used to range partition first, then spreads data into hash partitions.
2) Range-List : Used to range partition first, then spreads data into list partitions.
1. Range Partitions:
This is first type of partition introduced by oracle with 9i version.Range partition maps the data to partitions based on the partition key values. It is most common type and often used with dates
Ex: might want to partition sales data into monthly partition.
When using range partition-
Each partition has cluase "VALUES LESS THAN" clause which specified the upper bound for that partition.
Any key values which is equal to or greater than this value will be directed into the next higher partition.
All partitions (except the first) have an implicit lower bound specified by the "values less than" clause on previous partition.
The last partition is the "catch all" partition which has "MAXVALUE" cluase, this cluase represents virtual infinite value that sorts higher than any other possible values for partition key inclusing the NULL values.
EX:
create table sales_range
(salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) ( PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')), PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')), PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')), PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')),
partition sales_max values less than MAXVALUE );
2.List partitions:
List partitioning enables you to explicitly control how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition
The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.
Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning
Ex:
CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20), sales_amount NUMBER(10), sales_date DATE) PARTITION BY LIST(sales_state) ( PARTITION sales_west VALUES('California', 'Hawaii'), PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'), PARTITION sales_central VALUES('Texas', 'Illinois') PARTITION sales_other VALUES(DEFAULT) );
TheDEFAULTpartition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.
3. Hash partitions:
Oracle's hash partitioning distributes data by applying a proprietary hashing algorithm to the partition key and then assigning the data to the appropriate partition. By using hash partitioning, DBAs can partition data that may not have any logical ranges. Also, DBAs do not have to know anything about the actual data itself. Oracle handles all of the distribution of data once the partition key is identified.
It is a better choice than range partitioning when:
The sizes of range partitions would differ quite substantially or would be difficult to balance manually.
NOTE: The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.
CREATE TABLE sales_hash (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), week_no NUMBER(2)) PARTITION BY HASH(salesman_id) PARTITIONS 4 STORE IN (data1, data2, data3, data4);
This statement creates 4 partitions with default naming convention in 4 diff tablespaces.
4. Composite patition (Sub-partition):
Composite partitioning partitions data using the range method, and within each
partition, subpartitions it using the hash or list method.
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 subpartitioning.Composite range-hash partitioning combines both the ease of range partitioning and the benefits of hashing for data placement, striping, and parallelism.Ex: Range-hash partitionCREATE TABLE sales_composite (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) SUBPARTITION BY HASH(salesman_id) SUBPARTITION TEMPLATE( SUBPARTITION sp1 TABLESPACE data1, SUBPARTITION sp2 TABLESPACE data2, SUBPARTITION sp3 TABLESPACE data3, SUBPARTITION sp4 TABLESPACE data4) (PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')) PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')) PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')) PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY')) PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));Composite range-list partitioning combines both the ease of range partitioning and the benefits of list partitioning at the subpartition level.Ex: Range-list partitionCREATE TABLE bimonthly_regional_sales (deptno NUMBER, item_no VARCHAR2(20), txn_date DATE, txn_amount NUMBER, state VARCHAR2(2)) PARTITION BY RANGE (txn_date) SUBPARTITION BY LIST (state) SUBPARTITION TEMPLATE( SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1, SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2, SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')) );When you use a template, Oracle names the subpartitions by concatenating the partition name, an underscore, and the subpartition name from the template.Data disctionary views to view partitions:
DBA_TAB_PARTITIONS
DBA_PART_TABLES
DBA_PART_KEY_COLUMNS - can find the partitioned key columns.
DBA_TAB_SUBPARTITIONS
DBA_SUBPART_KEY_COLUMNS
DBA_SUBPARTITION_TEMPLATES
select partition_name, substr(PARTITION_POSITION,1,3), PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE from dba_tab_partitions
where table_name = '&tname';
10g New features:
--------------------------
1) Maximum number partitions allowed for an object has been increased from 64K-1 to 1024K-1.
2) Enhanced partition pruning for complex queries.
3) Resource optimized for DROP TABLE for partitioned tables, This feature enables drop large partitioned table is internally split to drop chunks of partitions.It will drop in incremental fashion which helps optimize resource consumption.
11g New features:
--------------------------
1) Ref partitioning:
The 11g REF partitioning allows you to partition a table based on the values of columns within other tables.
To create the reference-partitioned table you must specify "PARTITION BY REFERENCE" cluase in the create table statement. This cluase specified the referentila constraint name.
The referential constraint must be enabled and enforced.
Foreign key column will become the partitioned key value for that table.
Ex:
CREATE TABLE orders
( order_id NUMBER(12),
order_date TIMESTAMP WITH LOCAL TIME ZONE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id))
PARTITION BY RANGE(order_date)
( PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
);
CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);
2) Interval partitions:
This is a new 11g partitioning scheme that automatically creates time-based partitions as new data is added.
If you are using Range partitions there is maintanance cost.
Note that the partition names are system generated,but you can rename them if you like.
Ex:
create table selling_stuff_daily
( prod_id number not null, cust_id number not null , sale_dt date not null, qty_sold number(3) not null , unit_sale_pr number(10,2) not null , total_sale_pr number(10,2) not null , total_disc number(10,2) not null) partition by range (sale_dt) interval (numtoyminterval(1,'MONTH')) ( partition p_before_1_jan_2007 values less than (to_date('01-01-2007','dd-mm-yyyy')));
For day interval partition:
numtodsinterval(1,'DAY')
In this case, Oracle will create the next partition for dates less than 02-01-2007 when the first record that
belongs in that partition is created. Other than the interval keyword, this statement is just like a regular create table statement with range partition.
