11g Partitioning Features :
What is Partitioning ?
Oracle Partitioning is the splitting of data sets usually into separate physical files using separate partition tablespaces.
Why is partitioning relevant to tuning ?
Partitioning can be used to break large tables into smaller subsets. Processing of smaller subsets of data separately and in parallel is petentially much faster than serial processing on very large data sets.
Different Partitioning methods :- (till 10g)
Partitions can be created on single or multiple columns of a table. A table can be devided into separate partitions based on three methods ( ranges of values , values in lists and hashing algorithms on columns).
1) Range Partition
2) List Partition
3) Hash Partition
4) Composite Partition (Range - Hash Partition and Range - List Partition)
The Optimizer can access individual partitions when processing SQL code. This process is termed pruning.
=== What is new with 11g ? =====
Extended Composite Partitions
Range top level
--Range-Hash (available since Oracle 8i)
--Range-List (available since Oracle 9.2)
List top level
Interval top level
1)Virtual Column based partitioning
Virtual Column Based Partitioning :
It is purely virtual , meta-data only. Virtual columns can have statistics and they are also eligible for partitioning key. This enhances the performance and manageability.
Reference Partitioning :
Oracle 11g introduced Reference Partitioning. Child table inherits the partitioning strategy of parent table through PK-FK relationship. This enhances the performance and manageability.
Interval Partitioning :
Extention to range partition and these are created as metadata information only.Partition created when new data is added.
Numtodsinterval - Convert a number into an interval day to second literal.
Numtoyminterval - Convert a number into an interval year to month literal.
Interval partitioning does not support subpartitions. Thus, you can create an interval partition on the main partition of a composite partitioned table, but the subpartition cannot be interval-partitioned.
Application-controlled partitioning and No partitioning keys.You must define which partition the data goes in when doing an insert of data.System partitioning gives you all the advantages partitioning, but leaves the decision of how the data is partitioned to the application layer.
Considers entire query workload to improve query performance.
DEMO DEMO DEMO DEMO DEMO DEMO DEMO
Virtual Column Based Partitioning
System Partitioning :
Single Partition Transportable for Oracle Data Pump :
1) create two tablespaces and assign quota to users
2) Create a partitioned table with some data and gather stats
3) Query the user_tab_partitions to see data
4) Make one tablespace as read only
5) Use expdp to export the partion
6) Drop the table and tablespace ( dont drop the datafiles of tablespace which is transported)
7) use impdp to import
8 ) Check the table data in user_tables
11g table Partitions Features