Oracle Partitioning

1Get an Environment

You get started by simply heading over to a free service from Oracle called livesql.oracle.com. This service lets you run SQL and create database objects with no software required other than your browser. There are also hundreds of sample scripts and tutorials on a multitude of topics to assist you with learning about the Oracle Database

Oracle Partitioning is also available as a fully supported feature of all Oracle Database Cloud Services and on-premise Oracle Database Enterprise edition. Here is a quick primer on LiveSQL by Oracle Vice President Mike Hichwa:

Oracle LiveSQL features:

2A First Look at Partitioning Syntax

Perhaps the most common example of partitioning in Oracle databases is to divide up a large data into partitions based on a time attribute. The largest tables in your applications are often a time-based record of critical business activities. It could be sales transactions for a retail business, mobile phone calls for a telecommunications business, or deposits and withdrawals for a banking institution. In all of these cases, there are a couple of common elements, namely each transaction (row) in the table has a time stamp of when the transaction occurred, and typically the volume of such transactions is high, making the table large in size in a short period of time. Partitioning is a natural fit for such tables, because queries often want to only peruse time-based subsets of the data, for example, transactions for the current month, or current week. Also, breaking the large table into smaller more manageable sized pieces is useful for adminstrators from the perspective of maintenance. Time is a continuous (analog) measurement, and thus, a large table would be segmented into time-based ranges, hence the term used for this opertaion is range based partitioning. This video walks you through the creation a simple range partitioned table.

Key Points:

3Performance Benefits

Even with just a simple range partitioning example, we have enough tools at our disposal to examine the potential performance benefits possible by partitioning a table. When SQL queries consume too much I/O, often the only resolution considered is to create indexes on the table. The premise of indexing is simple: locate data more quickly and avoid scanning data unnecessarily. Partitioning a table takes the same approach via a concept known as "pruning" or "elimination". If a query on a partitioned table contains appropriately phrased predicates that include the partitioning column(s), the optimizer can generate an execution plan that will bypass those partitions that by definition, could not contain data relevant to the query. The following video shows a demonstration of this, including a comparison of the cost of partition pruning versus a conventional indexing strategy.

Key Points:

4Multi-column Range Partitioning

For many tables, a single column accurately defines the ranges via which you might want to partition your data. But range partitioning in the Oracle Database is not limited to that.

You can nominate more than one column to be the partitioning "key", ie, the columns that define where data will be stored. The order of the columns is critical as the video demonstration shows:

Key Points:

5Hash Partitioning

Sometimes partitioning is not about a logical segmentation of a table based on its attributes as is the case for range partitioning. When any database table gets massive in size, it is harder to manage for adminstrators because maintenance on such a table typically involves longer downtime for your business applications. Hash partitioning lets you segment a table in equi-sized chunks based on a hashing function applied to one or more columns of the table.

Creating a hash partitioned table is easy to do, but the number of hash partitions nominated is critical, as the video demonstration shows:

Key Points:

6List Partitioning

Range partitioning, as the name suggests, is about carving up data that is analog in nature, that is, a continuous range of values. This is why dates are a natural candidate for a range-based partitioning scheme.

But sometimes the column you might want to partition on contains a discrete set of values, which is when LIST partitioning is the best solution. Creating a LIST partitioned table requires nominating the discrete values for each partition, or relying on the new AUTOMATIC clause in 12c Release 2. The explicit value nomination technique is shown in the following video demonstration:

Key Points:

7Partitions of Partitions

Even once partitioned, a table may still be extremely large. Or the partitioning scheme may result in partitions that are not equally sized on disk. For example, archiving off of older data might mean that historical partitions are far smaller than the current partitions. Equi-sized partitions might be beneficial in particular when it comes to performing operations in paralle on a per-partition basis.

Partitions can be segmented further into subpartitions. Each partition can have it's own subpartitioning scheme, or all partitions can share a common schema. The syntax for subpartitioning is explained in the following video demonstration:

Key Points:

8Interval Partitions

For incoming data, a partition must already exist that the incoming partitioning key would map to. If a partition does not exist, transactions will fail with ORA-14400. In earlier versions of Oracle Database, this meant that administrators had to take extreme care to ensure that partitions for future data were defined or risk application outages. Interval partitioning (introduced in Oracle Database 11g) removes this risk by automatically creating new partitions on a partitioned table as required when new data arrives.

Interval partitioned tables are different from range partition in that logical "gaps" in the partition coverage are permitted. How to define interval partitioned tables is explained in the following video demonstration:

Key Points:

9Converting to Interval Partitions

Existing range partitioned tables can be converted into interval partition tables with a simple conversion command, requiring no downtime or data migration. The existing partitions in the range partitioned table remained defined as "range partitions" whilst new partitions created dynamically are "interval partitions". Thus a table that was not initially created as an interval partitioned table is a hybrid between the two, containing both types of partitions. Because intervals are defined as an offset from an initial fixed range partition boundary, you cannot drop all of the range partitions in an interval partitioned table. At least one must always remain. In Oracle Database 12c Release 2, the database will automatically convert the oldest interval partition into a range partition if no initial range partition boundary can be found.

How to convert a range partitioned table to an interval partitioned one is explained in the following video demonstration:

Key Points:

10Interval Partitions for Lists

INTERVAL partitioning is great for range partitioning to avoid the need for regular maintenance by a DBA to ensure that all values can be stored. The same facility is available for LIST partitioned tables from Oracle Database 12c Release 2 onwards. In this way, you can ensure that legal data values that have not been defined as partition keys can automatically create partitions on the fly.

The syntax and usage for automatic lists is explained in the following video demonstration:

Key Points:

11Reference Partitioning

Tables in a relational database do not work in isolation, and link them via declarative referential integrity. For this reason, a large table might not contain the column upon which we wish to partition it by. For example, a SALES table may be partitioned by a SALES_DATE, but a child table (say) SALES_ITEMS may only have a foreign back to the the parent SALES table, and thus no SALES_DATE column to partition on. Reference partitioning can be used to handle these more complex designs.

Reference partitions are demonstrated in the following video:

Key Points:

12Indexes on Partitioned Tables

An index normally points to a row in a physical segment (a table). But a partitioned table consists of multiple physical segments, so the index structures and the data contained within the index need to be slightly different for partitioned tables. If the index spans all partitions, what happens when we perform maintenance on a single partition, such as dropping it? What happens to the index entries that reference the now dropped partition?

Indexes syntax and their relationshiup to partitioned tables is covered in the following video:

Key Points:

13Locally Partitioned Indexes

An index can be equipartitioned with its underlying table. Such an index is known as a local index. Local indexes can have significantly benefits when it comes to Information Lifecycle Management (ILM). Because table partitions operations such as TRUNCATE and DROP and isolated to a single index partition, the remainder of the index remains available and never needs unncessary maintenance. Local indexes are also lend themselves to easier partition exchange which is a useful technique for either archiving old data or introducing new data into a partitioned table with zero downtime.

Then benefits of local indexes are explained in the following video demonstration:

Key Points:

14Local versus Global Indexes

The previous two videos discuss several apparent limitations of global indexes and plenty of advantages of local indexes. This seems to suggest that local indexes should always be used in preference to global indexes. This is not the case, and each indexing technique should be employed to best match the application requirements you have. A local index can be the absolutely wrong choice for particular kinds of queries. However, sometimes you may need to choose some design compromises if you want to get the best of both worlds - partition independence but still with solid declarative database design.

Deciding on which index type to use is explained in the following video demonstration:

Key Points:

15Special Indexing Use-cases

This tutorial series has covered the common partitioning strategies adopted by developers to build successful applications on large volumes of data. However, there are niche cases that also need to be considered. For example, you could have an index that partitioned on a table that is not partitioned. Similarly, the index partitioning strategy might not align with the partitioning strategy for the underlying table. Such examples are typically rare, however there is one important case where hash partitioning an index is critical to achieve extreme levels of OLTP performance.

The hash partitiong technique for OLTP performance is explained in the following video demonstration:

Key Points:

16Querying Partitioned Tables

Perhaps the most attractive benefit of partitioning is that performance improvements that can be achieved when querying partitioned tables. Partition pruning is the term used to describe when the predicates of a query are such that the entire table does not need to be scanned, but only a subset of the partitions. Detecting partition pruning relies on understanding the execution plan for a given query.

Various examples of when partitioning pruning can occur (and when it cannot) are explained in the following video demonstration:

Key Points:

17Partition Queries with Joins

Partitioned tables are rarely queried in isolation, and thus gaining the benefits of partition pruning even when a partitioned table is joined to another table, or involved with a subquery is a critical feature of the Oracle Database. By utilising a memory structure know as a Bloom filter, the database can quickly identify which partitions will be needed to satisfy a given query.

Detecting Bloom filter usage and understanding the execution plan is explained in the following video demonstration:

Key Points:

18Other Performance Opportunities

When two tables have identical partitioning definitions, the database can take advantage of this knowledge. If we had tables of animals, then commonsense tells us we will never match breeds between the dogs partition in one table and the cats partition in the other table. A join should be able to detect when two disparate partitions could never have a matching row, and therefore be eliminated from a join operation. This is known as a partition-wise join.

Partition-wise joins are explained in the following video demonstration:

Key Points: