Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Oracle Business Intelligence, OLAP and BI training and consulting tips . . .

Click here for more
Oracle News Headlines



Oracle Partitioning Review
February 11, 2004
Mark Rittman

Part of the work I'm looking to do next week involves partitioning an Oracle 8i data warehouse, so I've put a few notes together, and links to useful sites, which might be useful for anyone else considering a similar exercise. Here we go...

The first port of call for information on Oracle 8i partitioning is the online documentation available on OTN. According to the Oracle 8i Data Warehousing Guide's section on Partitioning Data:

"... Partitioning features ... significantly enhance data access and greatly improve overall applications performance. This is especially true for applications accessing tables and indexes with millions of rows and many gigabytes of data.

Partitioned tables and indexes facilitate administrative operations by allowing these operations to work on subsets of data. For example, you can add a new partition, organize an existing partition, or drop a partition with less than a second of interruption to a read-only application.

Using the partitioning methods described in this section can help you tune SQL statements to avoid unnecessary index and table scans (using partition pruning).

You can also improve the performance of massive join operations when large amount of data (for example, several million rows) are joined together by using partition-wise joins. Finally, partitioning data greatly improves manageability of very large databases and dramatically reduces the time required for administrative tasks such as backup and restore."

With Oracle 8i (8.1.7 in our instance) there are three types of partition,

  • Range Partitioning, which maps data to partitions based on a range of column values (usually a date column)
  • Hash Partitioning, which maps data to partitions based on a hashing algorithm, evenly distributing data between the partitions. This is typically used where ranges aren't appropriate, i.e. customer number, product ID
  • Composite Partitioning, a combination of range partitioning further subdivided by hash partitioning.

In our instance, the tables that are being partitioned are all keyed by transaction date, so our first approach will be to range partition by date, and then, because we've got a multi-processor server, and multiple physical disk units, try out composite partitioning to see if we get a further performance gain.

The main reason that partitioning is being considered for this data warehouse is because of the partition pruning feature.

In addition to table partitioning, you can also partition indexes with Oracle 8i, and the two usually are put in place as part of the same exercise. Again, according to the Oracle 8i Data Warehousing Guide;

"Partition pruning is a very important performance feature for data warehouses. In partition pruning, the cost-based optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list. This allows Oracle to perform operations only on partitions relevant to the SQL statement. Oracle does this when you use range, equality, and IN-list predicates on the range partitioning columns, and equality and IN-list predicates on the hash partitioning columns.

Partition pruning can also dramatically reduce the amount of data retrieved from disk and reduce processing time. This results in substantial improvements in query performance and resource utilization. If you partition the index and table on different columns (with a global, partitioned index), partition pruning also eliminates index partitions even when the underlying table's partitions cannot be eliminated.

On composite partitioned objects, Oracle can prune at both the range partition level and hash subpartition level using the relevant predicates

This feature is particularly useful for us as our queries typically are restricted to a particular range of dates (the last month, the last quarter, the last year and so on), the table contains data for several years, and the execution plans usually involve full table scans. As part of a two-pronged approach to dealing with performance issues, we're looking to

  • Make sure appropriate indexes are in place,
  • but where the optimizer chooses not to use them (or cannot use them), make the full table scans as efficient as possible

By partitioning our fact tables by month, using range partitioning, the hope is that only those partitions that are required to service the query will be full table scanned - with the rest of the partitions being 'pruned' - making us deal with only a small subset of the total table data.

As well as partitioning the table, you then need to decide how the accompanying indexes are partitioned. With Oracle 8i, you've got two index partitioning options;

  • Local indexes (created using LOCAL attribute) that are partitioned in the same way as the associated table
  • Global indexes (created using the GLOBAL attribute) that can be partitioned in any way

Typically, on a data warehousing fact table range partitioned on a date column, a local index is created on the date column. This has the advantage that index partitions can be pruned in the same way as table partitions, and you can maintain each index partition independently of the other index partitions, which is particularly useful if you're dropping an old partition, or loading data into a new partition. Global partitions are usually used for OLTP applications where queries are very precise and the overhead of scanning multiple local indexes could slow down response times.

For more background information on table and index partitioning with Oracle 8i, this Don Burleson article for is a good concise guide. One point that Don picks up on that I hadn't thought of before, is that for each individual partition you specify, you can separately specify the storage parameters. According to the article;

"Oracle has also included the ability for each partition to have its own values for the internal Oracle control structures of an object. These include PCTFREE, PCTUSED, INITTRANS, and MAXTRANS.

This feature can be especially useful when a table is partitioned so that only the most recent partition is updated. In this case, all of the earlier partitions would have PCTUSED set to 100 and PCTFREE set to 0. By allowing control over the partitions as if they were separate tables, the DBA can “pack” static data into the Oracle blocks and save space. The current partition would have PCTFREE set to a higher number to allow for row expansion as the rows are updated."

Having looked at the basics, I did a few searches on Google to find out how partitions worked in practice, taking care to make sure that we were looking at 8i partitioning - which presumably wouldn't be as refined and easy to work with as partitioning in 9i and 10g.

One of the first papers I came across was this one by Larry Miller at Oriolecorp, entitled "Oracle 8 Partitions". The paper starts off with an explanation of Oracle 8 partitioning, explains how local and global indexes work, and then gives some feedback and advice on Oracle 8 partitioning in practice. The key pros and cons raised by the paper were;

"Tests have underlined the following points :

  • Better management of the balance and physical location of data
  • Increased performance, especially for Management Reporting (when accesses are limited, thanks to the WHERE clause, to some partitions)
  • Increased performance with parallel DML for massive updates
  • Possibility to delete quickly a huge amount of data (purges)
  • Good maintenance functions (split, move)

Weak points :

  • The best way to partition the data is hard to determine
  • How to index becomes nightmarish (many possible combinations, depends on how the table has been partitioned)
  • A row cannot be migrated from a partition to another partition other than by deleting it and reinserting it (with the correct value for the partition key)"

Take a look at the paper if you get a chance, as there's a useful amount of feedback on the building and maintenance of table and index partitions, plus a good advice section at the end.

Some additional articles I've come across that were useful in getting some background included;




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational