|
The
Specialist
Database News You
Can Use
A monthly
newsletter for Oracle users
January 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Partition Pruning
You're invited to submit your Oracle-related questions to us at askdbspecialists01@dbspecialists.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of Chris Lawson's book, The Art and Science of Performance Tuning. (US shipping addresses only).
This month's question comes from Sujit C. of India:
What is partition pruning and can you explain how it works?
Chris Lawson of the Database Specialists team responds: Partition pruning is a term used in reference to table partitioning. It refers to the ability of the Oracle optimizer to disregard, or "prune," those table partitions that are not needed to satisfy a query. Instead of scanning an entire (huge) table, just a small portion is scanned. This capability to eliminate unneeded partitions is a big reason that so many data warehouses use partitioning.
In many cases, partitioning is keyed on a date column. For example, a sales transaction table might be partitioned on Transaction_Date, with the sales for each month stored in a separate partition. Then, when a report is run to summarize sales for a given month, only a single partition need be scanned. All of the other partitions are "pruned." Consider the following simple query and execution plan:
SELECT COUNT(*), SUM (Dollar_Amount)
FROM Sales
WHERE Transaction_Date BETWEEN :Start_Date AND :End_Date;
Execution Plan
---------------------------------------------------
SELECT STATEMENT GOAL: CHOOSE
SORT (AGGREGATE)
PARTITION RANGE (ITERATOR) PARTITION:KEYKEY
TABLE ACCESS (FULL) OF 'SALES' PARTITION:KEYKEY
The execution plan shows that Oracle will only scan certain partitions of the Sales table. Ordinarily the "PARTITION:" designation in the execution plan indicates a range of partitions to be accessed. In this case "KEYKEY" appears instead of a range of partition numbers, indicating that Oracle will decide which partitions to access at execution time based on the values of the bind variables in the query.
In order to glean a performance advantage, the sizing of the partitions should be similar to the criteria specified in the most commonly run reports. That is, a lot of partitions need to be pruned if partitioning is to provide a performance gain. Thus, if most queries are based on querying a single month of data, then partitions should likewise be divided into months. If there is a large mismatch between the partition size and the query search criteria, partition pruning may not provide any performance gain.
Great Oracle Learning Opportunities in February!
Next month is packed with educational opportunities in the Oracle user group community. Database Specialists, Inc. has been invited to give technical presentations at the following user group conferences:
- Northwest Oracle Users Group (NWOUG) February 9-10 in Bellevue, WA
- Rocky Mountain Oracle Users Group (RMOUG) February 11-12 in Denver, CO. (We'll also have a booth in the exhibit hall.)
Look for us also at the Northern California Oracle Users Group (NoCOUG) on February 19 in Redwood Shores, CA. Each of these events will be full of outstanding educational opportunities developed by the regional Oracle users groups. User group conferences are information-packed, inexpensive, and a great way to network. Mark your calendars today!
Come see us and pick up a free Oracle wait events reference poster.
Database Specialists President Elected to Lead Local Oracle Users Group
For the second year in a row, Database Specialists president Roger Schrag has been elected to lead the 400+ member Northern California Oracle Users Group (NoCOUG). As NoCOUG president, Roger says, "I am looking forward to another exciting year. We have a great board of directors, and there is a lot in store for the members of NoCOUG."
Roger is entering his fourth year on the board of directors for NoCOUG, an organization that is dedicated to offering education, information sharing, and networking opportunities. "As a professional in the Oracle arena, I have always felt it's important to share knowledge and make positive contributions to help others. Spending my time with NoCOUG is one of the ways I accomplish that. Plus, I continue to meet outstanding people along the way," says Roger. For more information about NoCOUG, visit http://www.nocoug.org.
Oracle Internals, Scripts, and Tips
A very popular free resource in the Oracle DBA community is provided by an Australian company called Ixora. Ixora's principal consultant is the well-known Steve Adams, author of Oracle8i Internal Services. Browse Ixora's website and you'll find a huge amount of information—including details about Oracle internals that you may not be able to find anywhere else. The area of the website covering Oracle internals discusses topics such as datatypes, controlfiles, redo, and I/O. There's also a section on Oracle performance tuning tips, covering areas such as:
- Physical database design
- Database creation
- Database server tuning
- Database administration
- Tuning methodology
The scripts area of the Ixora website contains resources for your review in areas such as wait events, query optimization, I/O optimization, X$ tables, dumps and traces Get started on the home page at http://www.ixora.com.au.
Back Issues:
Previous
Next
|
About The Specialist 