Partitioned Tuning   «Prev  Next»
Lesson 2 Overview of partitioning
Objective Describe partitioned objects.

Define Oracle Objects

Oracle Partitioning

As the name implies, the Oracle partitioning facility allows large Oracle tables and indexes to be divided into manageable pieces that all have the same logical attributes. These pieces are accessed and manipulated independently, and can be managed as if they were separate entities. This is especially important in working with large scale data warehouses.
We know scale is an important factor in the performance of a data warehouse. Partitioning is an especially useful feature for Oracle data warehouses, because the DBA is provided with a tool to manage these partitions independently.
Oracle partitioning can be extremely complex. For purposes of this module, we will focus on partitioning issues surrounding Oracle performance. let us look at the types of partitions Oracle uses.

Types of partitions

Oracle provides three types of partitioning for tables and indexes:

Partition Type Data is partitioned
Range In a table or index according to a range of values
Hash According to a hash function
Composite By range and further subdivides the data into sub-partitions using a hash function

Advantages of Partitioning

Oracle partitioning can improve Oracle performance in a variety of ways:
  1. Oracle can restrict SQL requests to only those partitions that are affected by the query.
  2. Oracle maintenance is improved because index and table partitions can be reorganized and independently maintained.
  3. Disk load balancing can be improved because table and index partitions can be transported to other disks independently of the entire table or index.
  4. Parallel queries run faster because the parallel query optimizer detects partitions and creates parallel processes to simultaneously read each partition.
  5. Some Oracle SQL statements run faster because the Oracle SQL optimizer recognizes those partitions that are required for a query and only accesses those partitions.

Oracle 12c Performance Tuning

Creating a Partition

To see how partitions are defined let us examine a sample sales table for an Oracle data warehouse by viewing the code below.
As we see from this syntax, the table is defined so that the data for each year resides in a separate partition, and each partition resides in a separate tablespace.
create table
  sales_data
( 
  store_nbr    number,
  city         char(40)
  year         number(4),
  month        number(2),
  units_sold   number,
  sales_dollar number
)
partition by range(year)
(
  partition p1998 values less than(1998) tablespace data_1998,
  partition p1998 values less than(1999) tablespace data_1999,
  partition p2000 values less maxvalue tablespace data_2000,



Define Partitions Independent Entities

Using a partition to improve query performance

Now let us illustrate how a partition improves performance on a query. In the query below we select all sales in 1997 in Raleigh.

SELECT SUM(units_sold)
FROM sales_data
WHERE 
year = 1997
AND 
city = 'Raleigh';
This is a regular sql select statement.
With partitions, we can re-formulate our query to include only the 1997 partition. See below.

This is the sql, re-formulated to include only one partition
This query limits the scope of the query to only those partitions that need to be accessed, thereby improving performance.
The next lesson looks at how partitions work and the different DBA views Oracle provides for partitioned objects.

Create Partitioned Table - Exercise

Before you continue, click the exercise link below to test your knowledge of the creation of table and index partitions.
Create Partitioned Table - Exercise