January 2004

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). Great Oracle Learning Opportunities in February!

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.

 

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: Database Specialists President Elected to Lead Local Oracle Users Group

  • 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.

 

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.” Oracle Internals, Scripts, and Tips

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.

 

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.

Leave a Reply

Your email address will not be published. Required fields are marked *