Divide and Conquer Using Oracle Database Partitioning
Introduced with Oracle8, Oracle partitioning has matured over the years, through Oracle8i and Oracle9i. There has been a significant amount of documentation and publications on various aspects of partitioning. However, most of these publications focus on the database administration aspects of partitioning, including the ease of space management, ease of reorganization, selective unavailability during maintenance, and so on. Very few of these publications provide a SQL programmer’s view to partitioning — the way partitioning helps to improve the performance of SQL queries. This article takes a practical approach to illustrate the impact of partitioning on the query performance.
Case Study
We will begin with a simple case study to demonstrate how you can use partitioning to improve your applications’ performance. The case study involves a hypothetical sales application, the main functions of which are forecasting sales, collecting actual sales data, and comparing actual sales with the forecast. This simple sales application consists of two tables. The sales forecast data is captured in the table SALES_FORECAST, which stores the forecasted quantity for 10,000 parts for each of the 365 days of the year 2002. The actual sales data for these parts over the same period of time is captured in the table ACTUAL_SALES. Each of these two tables contains 3,650,000 rows. The table definitions are given in Listing1 as follows:
DESC SALES_FORECAST Name Null? Type ----------------------------------------- -------- ---------- PART_ID NUMBER(6) FORECAST_DATE DATE QUANTITY NUMBER(4) DESC ACTUAL_SALES Name Null? Type ----------------------------------------- -------- ---------- PART_ID NUMBER(6) SALE_DATE DATE QUANTITY NUMBER(4)
The main task of our case study involves writing SQL queries to:
- Determine the number of parts and the days (part-day combinations) with actual sale quantity of less than 500 for PART_IDs below 1600.
- Determine the number of parts and the days (part-day combinations) for which the actual sales quantity is more than the corresponding forecast quantity.
The first task can be accomplished by the SQL in Listing2:
SELECT COUNT(*)
FROM ACTUAL_SALES
WHERE PART_ID < 1600
AND QUANTITY < 500;
COUNT(*)
----------
29245
In Listing2, the elapsed time is the time it took to execute this query in SQL*Plus, which indicates that it took 7.04 seconds to execute this query. The second task can be accomplished by the SQL in Listing3:
SELECT COUNT(*) FROM SALES_FORECAST F, ACTUAL_SALES S WHERE S.PART_ID = F.PART_ID AND S.SALE_DATE = F.FORECAST_DATE AND S.QUANTITY > F.QUANTITY; COUNT(*) ---------- 1825057 Elapsed: 00:03:57.04
It took 3 minutes 57.04 seconds to execute this query.
In the next section, we will see how partitioning can improve the performance of the above two queries.
Enter Partitioning
When the volume of data grows, it is beneficial to partition the data in a table into multiple partitions. Oracle provides several ways to partition the tables and indexes. Oracle8 supported partitioning by range, hash, and composite partitioning. Oracle9i introduced list partitioning. (Refer to Oracle documentation for details on these partitioning techniques.)
In this article, we will focus on the practical use of range partitioning and apply it to our case study. To demonstrate the advantage of partitioning, we created two partitioned tables corresponding to the two tables, SALES_FORECAST and ACTUAL_SALES. Each table is partitioned into 10 partitions, and the tables are partitioned based on range of values of the column PART_ID. We named the partitioned tables, SALES_FORECAST_PR and ACTUAL_SALES_PR to indicate that we have range partitioned the tables. Listing4 shows the partitioned table definitions:
CREATE TABLE SALES_FORECAST_PR ( PART_ID NUMBER (6), FORECAST_DATE DATE, QUANTITY NUMBER(4) ) PARTITION BY RANGE (PART_ID) ( PARTITION P1 VALUES LESS THAN (1001), PARTITION P2 VALUES LESS THAN (2001), PARTITION P3 VALUES LESS THAN (3001), PARTITION P4 VALUES LESS THAN (4001), PARTITION P5 VALUES LESS THAN (5001), PARTITION P6 VALUES LESS THAN (6001), PARTITION P7 VALUES LESS THAN (7001), PARTITION P8 VALUES LESS THAN (8001), PARTITION P9 VALUES LESS THAN (9001), PARTITION P10 VALUES LESS THAN (10001) ) ; CREATE TABLE ACTUAL_SALES_PR ( PART_ID NUMBER (6), SALE_DATE DATE, QUANTITY NUMBER(4) ) BY RANGE (PART_ID) ( PARTITION P1 VALUES LESS THAN (1001), … and so on. ) ;
Once the tables are created, we inserted data into these two tables by SELECTing data from the corresponding non-partitioned tables, as shown in Listing5:
INSERT INTO SALES_FORECAST_PR SELECT * FROM SALES_FORECAST; INSERT INTO ACTUAL_SALES_PR SELECT * FROM ACTUAL_SALES; COMMIT;
Then, we analyzed the tables to compute statistics, and executed the queries against the partitioned tables. The queries on the partitioned tables and the results are shown in Listing6.
SELECT COUNT(*)
FROM ACTUAL_SALES_PR
WHERE PART_ID < 1600
AND QUANTITY < 500;
COUNT(*)
----------
29245
Elapsed: 00:00:02.04
SELECT COUNT(*)
FROM SALES_FORECAST_PR F, ACTUAL_SALES_PR S
WHERE S.PART_ID = F.PART_ID
AND S.SALE_DATE = F.FORECAST_DATE
AND S.QUANTITY > F.QUANTITY;
COUNT(*)
----------
1825057
Elapsed: 00:01:54.05
The results are interesting. Note that the queries on the partitioned tables execute much faster compared to the same queries on the corresponding non-partitioned tables. Everything else remaining the same, partitioning the tables improved the performance drastically. In the following section, we will analyze why and how partitioning improves query performance and introduce some key concepts of partitioning.
Performance Analysis
We used the following tools to analyze the performance:
• SQL_TRACE / TKPROF
• Event 10128
Let’s examine the two queries using the above two tools.
First Query
The first query (to determine number of part-day combinations with actual sale quantity of less than 500 for PART_IDs below 1600) takes 7.04 seconds on the non-partitioned table. The SQL_TRACE / TKPROF output of the query (shown in Listing7) indicates that it performs 10535 physical reads from the disk.
The SQL_TRACE / TKPROF output of the same query on the partitioned table (shown in Listing8) indicates that it performs only 2124 physical disk reads (as compared to 10535 physical disk reads in case of the query on the non-partitioned table).
Isn’t it intriguing? How can a query on the partitioned table give the same information by performing only 1/5th of the physical disk reads as that of the same query on the non-partitioned table? There must be something in partitioning that intelligently directs Oracle to read fewer blocks from the disk. What is it? Let’s look at execution plans of the queries for further analysis.
The execution plan of the query on the non-partitioned table (shown in Listing9) indicates that the Oracle optimizer performed a full table scan on ACTUAL_SALES to find the result of the query.
The execution plan of the query on the partitioned table is shown in Listing10.
The execution plan of the query on the partitioned table is almost identical to that of the query on the partitioned table, except that it has two extra columns at the end. And that’s the key. The last two columns (Pstart and Pstop) indicate that to find the result of the query, Oracle starts scanning the ACTUAL_SALES_PR table at partition 1 and stops at partition 2, instead of scanning the whole table.
You can also monitor which all partitions are being scanned by setting the event 10128 before executing the query. Listing 11 shows how to set the event 10128.
alter session set events '10128 trace name context forever, level 2';
SELECT COUNT(*)
FROM ACTUAL_SALES_PR
WHERE PART_ID < 1600
AND QUANTITY < 500;
COUNT(*)
----------
29245
alter session set events '10128 trace name context off';
Once the query is executed after setting the event 10128, the information shown in Listing12 (known as partition iterator information) is written to a trace file.
Partition Iterator Information: partition level = PARTITION call time = RUN order = ASCENDING Partition iterator for level 1: iterator = RANGE [0, 1] index = 0 current partition: part# = 0, subp# = 65535, abs# = 0 current partition: part# = 1, subp# = 65535, abs# = 1
Listing 12 indicates that the optimizer scans only two partitions — partition 0 and 1. (Note that the partition numbers shown in the execution plan are slightly different from the numbers shown in the partition iterator information in the trace file. The execution plan counts partitions starting with 1, whereas the partition iterator counts partitions starting with 0).
The execution plan as well as the partition iterator information indicate that out of 10 partitions of this table, Oracle needs to scan only the first two partitions to find the result. Why?
Partition pruning
Here lies a key concept of partitioning called partition pruning. Since the query specifies a WHERE clause predicate on the partition key (PART_ID) of the table, the Oracle optimizer intelligently determines that it is not necessary to scan the entire table, and that it can get the result by scanning only those partitions that match the query predicate on PART_ID. By deciding to scan only two partitions, Oracle saves time and resources and therefore executes the query faster.
Partition pruning is also called partition elimination. When you query a partitioned table, the Oracle optimizer obtains the partition information from the table definition, and maps that onto the WHERE clause predicates of the query. If the WHERE clause specifies one or more columns of the partition key, then Oracle decides to scan only those partitions that satisfy the WHERE condition, and does not scan other partitions. Partition pruning can improve performance significantly by reducing the amount of data retrieved from the disk, as depicted in the case study above. Keep this in mind while writing SQL queries on partitioned tables. As you can see from this example, partition pruning helps Oracle optimizer to return the results of the query by:
- scanning only 2 out of 10 partitions,
- performing only 2124 physical reads as compared to 10535 physical reads.
- executing the query in 2.04 seconds as compared to 7.04 seconds.
Some important aspects of partition pruning are:
- Partition pruning applies to partitioned indexes as well. If you have a partitioned index on a table, and the execution plan involves scanning the index, Oracle optimizer will decide to scan only those partitions of the index that satisfy the WHERE clause predicate of the query specifying the partition key.
- For range or list partitioned tables, Oracle can prune partitions when the WHERE clause contains a range, equality, LIKE or IN predicate on a partition key column.
- In case of hash partitioned tables, partition pruning occurs only when the WHERE clause contains either IN or an equality operation on the partition key, e.g. PART_ID = 1003 or PART_ID IN (1002,1003). If it contains a range, such as PART_ID BETWEEN 1002 AND 1004, partition pruning does not occur. Please note the restriction on hash partitioned tables as compared to the range or list partitioned tables.
- For composite partitioned tables and indexes, partition pruning can be applied at the partition, as well as the sub-partition, level.
- When the WHERE clause predicate evaluates a function on the partition key, e.g., TO_CHAR(PART_ID), partition pruning does not occur. The only allowable function to be applied on the partition key to effect partition pruning is TO_DATE().
Second Query
The second query (to determine the number of parts and the days (part-day combinations) where the actual sales quantity is more than the corresponding forecast quantity) takes three minutes and 57.04 seconds on the non-partitioned table. This is a join operation on two tables, each containing more than three million rows. The SQL_TRACE / TKPROF output of the query (shown in Listing13) indicates that it performs 110480 physical reads from the disk.
The SQL_TRACE / TKPROF output of the same query on the partitioned table (shown in Listing14) indicates that it performs only 44270 physical disk reads (as compared to 110480 physical disk reads in case of the query on the non-partitioned table).
Isn’t it interesting to see that the query on the partitioned tables gets the result by performing less than half the number of physical reads as compared to the query on the non-partitioned tables? No wonder the query on the partitioned tables is much faster. Let’s look at the execution plan of the two queries.
The execution plan of the query on the non-partitioned table is shown in Listing15.
The execution plan of the query on the partitioned table is shown in Listing16.
To monitor operations performed on the partitions, you can set the event 10128 before executing the query. Listing17 shows how to set the event 10128:
alter session set events '10128 trace name context forever, level 2'; SELECT COUNT(*) FROM SALES_FORECAST_PR F, ACTUAL_SALES_PR S WHERE S.PART_ID = F.PART_ID AND S.SALE_DATE = F.FORECAST_DATE AND S.QUANTITY > F.QUANTITY; COUNT(*) ---------- 1825057 alter session set events '10128 trace name context off';
Once the query is executed after setting the event 10128, the partition iterator information shown in Listing18 is written to a trace file:
Partition Iterator Information: partition level = PARTITION call time = RUN order = ASCENDING Partition iterator for level 1: iterator = RANGE [0, 9] index = 0 current partition: part# = 0, subp# = 65535, abs# = 0 *** 2003-06-07 21:41:06.000 current partition: part# = 1, subp# = 65535, abs# = 1 *** 2003-06-07 21:41:18.000 current partition: part# = 2, subp# = 65535, abs# = 2 *** 2003-06-07 21:41:30.000 current partition: part# = 3, subp# = 65535, abs# = 3 *** 2003-06-07 21:41:46.000 current partition: part# = 4, subp# = 65535, abs# = 4 *** 2003-06-07 21:41:59.000 current partition: part# = 5, subp# = 65535, abs# = 5 *** 2003-06-07 21:42:10.000 current partition: part# = 6, subp# = 65535, abs# = 6 *** 2003-06-07 21:42:22.000 current partition: part# = 7, subp# = 65535, abs# = 7 *** 2003-06-07 21:42:33.000 current partition: part# = 8, subp# = 65535, abs# = 8 *** 2003-06-07 21:42:47.000 current partition: part# = 9, subp# = 65535, abs# = 9
The execution plan and the partition iterator information indicate that the join of the two partitioned tables are performed one partition at a time. How does this happen? This situation is a bit more complex to understand than partition pruning.
Partition-wise joins
When the two tables being joined are partitioned along the join column(s), the Oracle optimizer breaks the join operation into multiple smaller joins. Each smaller join is a join between the corresponding partitions of the two tables. Since each smaller join involves only a small number of rows from each table, it can be performed efficiently. In this example, the join of the two partitioned tables is actually performed as 10 joins of partitions from each table. As you can see, 10 smaller joins is faster compared to one big join. This type of join is called partition-wise join, and is a very important concept in partitioning. As you can see from this example, partition-wise join helps Oracle optimizer to return the results of the query by:
- joining individual partitions from each table
- perform only 44270 physical reads as compared to 110480 physical reads.
- execute the query in 1 minute and 54.05 seconds as compared to three minutes and 57.04 seconds.
Full and partial partition-wise joins
There are two types of partition-wise joins: full or partial. When the two tables involved in the join are equipartitioned (i.e., both the tables have the same partition key, and the data in any partition of one table relates to the data in the corresponding partition of the other table), Oracle can perform a full partition-wise join. In our case study, the second query on the partitioned tables is an example of a full partition-wise join.
Since both the tables (ACTUAL_SALES_PR and SALES_FORECAST_PR) are range partitioned on the column PART_ID with the same range specification, rows in one partition of ACTUAL_SALES_PR relate to the data in the corresponding partition of SALES_FORECAST_PR. This is an example of two tables being equipartitioned. When these two tables are joined on the partition key (PART_ID), Oracle performs a full partition-wise join. Note that if these two tables are joined on any other column (instead of PART_ID), a partition-wise join will not take place. In our example, the two tables are range partitioned. A full partition-wise join can also take place if the two tables are equipartitioned using any other technique (e.g., hash-hash, list-list, composite-composite, range-composite, composite-hash). Refer to Oracle9i Data Warehousing Guide for a detailed description of all these combinations.
Even though you might like to do so, it is not always possible to have the two tables involved in the join equipartitioned on the join column. In such situations, the Oracle optimizer can decide to use a partial partition-wise join. For a partial partition-wise join, only one table needs to be partitioned; the second table may or may not be partitioned. When Oracle decides to use a partial partition-wise join, it picks one table as a reference table. If only one table is partitioned, the partitioned table is picked as the reference table. If both the tables are partitioned, any one can be picked as the reference table.
Once the reference table is chosen, Oracle repartitions the second table dynamically, based on the reference table, to make the two tables equipartitioned. After this, the join is performed just like a full partition-wise join. As you’ve probably realized by now, a partial partition-wise join is less efficient compared with a full partition-wise join, because it involves the overhead of dynamically partitioning one table based on the reference table. In the example of Listing19 , a partitioned table ACTUAL_SALES_PR is joined with a non-partitioned table SALES_FORECAST:
SELECT COUNT(*) FROM SALES_FORECAST F, ACTUAL_SALES_PR S WHERE S.PART_ID = F.PART_ID AND S.SALE_DATE = F.FORECAST_DATE AND S.QUANTITY > F.QUANTITY;
This example depicts a partial partition-wise between a partitioned and a non-partitioned table. A partial partition-wise join can also take place for other combinations of tables as well, e.g., hash-list, composite-composite, range-range, and so on. (Refer to Oracle9i Data Warehousing Guide for a detail description of all these combinations).
Parallel partition-wise joins
In a parallel execution environment, the performance of partition-wise joins can further be improved by executing the joins of individual partitions in parallel.
Using partition pruning and partition-wise joins together
Partition pruning and partition-wise joins can be used together in a single query. It is very common to have queries using partition pruning and partition-wise joins together. An example is shown in Listing20.
SELECT COUNT(*) FROM SALES_FORECAST_PR F, ACTUAL_SALES_PR S WHERE S.PART_ID = F.PART_ID AND S.SALE_DATE = F.FORECAST_DATE AND S.QUANTITY > F.QUANTITY AND S.PART_ID < 1401;
To execute this query, Oracle optimizer will first apply partition pruning to eliminate the partitions of both the tables that don’t satisfy the WHERE clause predicate (PART_ID < 1401). Then, the selected partitions are joined using partition-wise joins.
Conclusion
Partitioning brings numerous benefits. In this article, we have presented a case study to illustrate performance benefits of partition pruning and partition-wise joins. These two features of Oracle partitioning can help improve query performance tremendously. The SQL programmers should keep these features in mind while designing SQL queries on partitioned tables, so that the queries are enabled for partition pruning and partition-wise joins.
--
Sanjay Mishra is a database administrator, application architect and software developer with more than twelve years of industry experience. He has worked extensively in the areas of database architecture, database management, backup / recovery, performance tuning, Oracle Parallel Server, parallel execution, partitioning. He has authored three books published by O'Reilly & Associates (Oracle Parallel Processing, Oracle SQL Loader: The Definitive Guide, Mastering Oracle SQL). Presently, he works as a database architect at Dallas-based i2 Technologies. Sanjay can be reached at smishra_tech@yahoo.com.
Contributors : Sanjay Mishra
Last modified 2005-02-24 02:31 PM
