Oracle Partitioning 2

Composite partitioning is a partitioning technique that combines some of the other partitioning methods. The table is initially partitioned by the first data distribution method and then each partition is sub- partitioned by the second data distribution method.

The following composite partitions are available:

•  Range-hash partitioning was introduced in Oracle 8i
• Range-list partitioning was introduced in Oracle 9i
•  Range-range partitioning was introduced in Oracle 11g
•  List-range partitioning was introduced in Oracle 11g
•  List-hash partitioning was introduced in Oracle 11g
•  List-list partitioning was introduced in Oracle 11g
•  Interval-range partitioning was introduced in Oracle 11g
•  Interval-list partitioning was introduced in Oracle 11g
•  Interval-hash partitioning was introduced in Oracle 11g
•  Hash-hash partitioning was introduced in Oracle 11gR2

For More details










SUB-PARTITIONS


Utilized most often when partition strategy does not provide small enough partition units to achieve maintenance goals. Sub-partitions further divide table based another column with partitions.

Oracle Partitioning

         Partitioning addresses key issues in supporting very large tables and indexes by splitting them into smaller and more manageable pieces called partitions.

 LOB  datatype can be partitioned
 LONG/LONG RAW datatype cannot be partitioned

When to Partition a table Table Size &  Historical data table 

Types of Partitioning

• Range Partitioning
• Hash Partitioning
• List Partitioning
• Interval Partitioning ( Only in 11G )
• REF Partitioning ( Only in 11G )
• Composite Partitioning

Range Partitioning

    Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key.

List Partitioning allows unordered and unrelated sets of data to be grouped and organized together

Interval partitions are automatically allocated based upon the value/interval that we set

   Restrictions on Interval Partitioning
•Cannot be used for index organized tables
•Must use only one partitioning key column and it must be a DATE or NUMBER
•Cannot create domain indexes on interval partitioned tables
•Are not supported at the sub-partition level

Benefits of Interval Partitioning
Range partitioned table can easily be converted to use interval partitioning by using the following command:

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Since Oracle will automatically create the new partition based on interval if a partition does not exist in range partition .So that the following error can be solved

ORA-14400: inserted partition key does not map to any partition


ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions.


Oracle uses some sort of fixed algorithm for defining partitions. Choose partition interval correctly.

REF partitioning child table inherits the partitioning strategy of parent table through PK-FK relationship


Different Types of REF  partitioning

---------

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE my_table AS SELECT * FROM my_other_table

• The best thing about CTAS is that it does not use rollback segments .
• The above query will copy all the data from the source table to destination table 
• CTAS is a particularly powerful tool for populating or rebuilding a single partition of a partitioned table 

     CREATE TABLE my_table AS SELECT * FROM my_other_table
             where 1= 2

•  The above query will not copy the data from the source table to destination table.It will only create the structure of source table. 
  

Points Explain Plans

• The rightmost uppermost operation of an explain plan is the first thing that the explain plan will execute.
• Read Right-Left and Top-Down on the same indentation level.
• Try to reduce the cardinality of the rowset as soon as possible.
• Using an index is not always the most beneficial approach. Where greater than 10% of rows in any table are joined, a HASH JOIN and Full Table Scan may be the best approach.
• Trust the Cost Based Optimiser but experiment by running the query. Cost and duration are not always the same.
• We use a SORT_AREA_SIZE set for Online Transaction Processing. Consider setting SORT_AREA_SIZE larger for long running batch jobs and other larger tables, but do this outside the normal working hours.
• This can be done using ALTER SESSION SET SORT_AREA_SIZE = 2M;