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:
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.
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,
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