Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South
and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't
make it this year, check out TidalScale at www.tidalscale.com. Be
sure to click on the Solutions link and look through the Oracle resources.

Purpose

Partitioning and subpartitioning of tables and indexes is a technique for creating a single logical entity, a table or index,
mapping multiple separate segments allowing the optimizer to access a smaller number of blocks to respond to a SQL statement.

Oracle supports partitioning only for tables, indexes on tables, materialized views, and indexes on materialized views. Oracle does not support partitioning of clustered tables or indexes on clustered tables.

Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning.

Explain Plan PSTART/PSTOP Values

KEY(I)

IN subquery

KEY(SQ)

Recursive subquery

Global Index

A single index covering all partitions.

Hash Partitioning

Enables partitioning of data that does not lend itself to range or list partitioning.

-- to view the value Oracle is using within a specific session for hashing

SELECT program, sql_hash_value, prev_hash_value
FROM gv$session;

Interval Partitioning

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.

Invalidating Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)

COALESCE (HASH)

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

List Partitioning

Explicitly controls 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.

Local Index

Separate indexes for each partition. A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.
Unique local indexes are useful for OLTP environments. You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table.

Partition

Decompose a table or index into smaller, more manageable pieces, called partitions.
Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partition Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of from 1 to 16 columns that determines the partition for each row.

Partitioning Pruning

Oracle optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access. Partition pruning is the skipping of unnecessary index and data partitions or subpartitions by a query.

Range Partitioning

Maps data to partitions based on ranges of partition key values that you establish for each partition.

Referential Partitioning

Data is mapped to partitions based on values defined in a referential constraint (foreign key)

Subpartition

Partitions created within partitions. They are just partitions themselves and there is nothing special about them.

SELECT COUNT(*) FROM partdemo;
SELECT COUNT(*) FROM partdemo WHERE deptno = 10;
SELECT COUNT(*) FROM partdemo WHERE deptno BETWEEN 1 AND 19;
SELECT COUNT(*) FROM partdemo WHERE deptno BETWEEN 1 AND 20;
SELECT COUNT(*) FROM partdemo WHERE deptno IN(10,30);