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:
Data is partitioned
In a table or index according to a range of values
According to a hash function
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:
Oracle can restrict SQL requests to only those partitions that are affected by the query.
Oracle maintenance is improved because index and table partitions can be reorganized and independently maintained.
Disk load balancing can be improved because table and index partitions can be transported to other disks independently of the entire table or index.
Parallel queries run faster because the parallel query optimizer detects partitions and creates parallel processes to simultaneously read each partition.
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.
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.
Now let us illustrate how a partition improves performance on a query.
In the query below we select all sales in 1997 in Raleigh.
With partitions, we can re-formulate our query to include only the 1997 partition. See below.
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.