We help IT Professionals succeed at work.

Oracle Partitioning White Paper

I don't wish to go thru tonnes of doc links so  looking for a whitepaper on 10g/11g Oracle Partitioning.

Kind Regards
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019

Did you Google it?  The first two links look good: http://www.google.com/#hl=en&q=oracle+partitioning

I still suggest the online docs.
Most Valuable Expert 2011
Top Expert 2012

can you be more specific about what you're trying to learn?

Chakravarthi AyyalaDatabase Administrator

sometimes, i prefer/practice/suggest learn by example.
you can as well search in EE on Partitioning and you can see the questions and answers and you will get an idea, if you are completely new to partitioning.
then you can read the exact item you are looking for at tahiti.oracle.com.


I have a 4+ terabyte BW database. Some of the tables are in range of 100-200GB. None of them are partitioned. The performance is pretty bad with application when specific BI reports were compiled not using right indexes. I can't add new indexes because of ETL and size of tables.
I need to know how partitioning can help me here so I am specifically looking for
- Types of Partitioning
- Advantages and drawbacks against each other
- When to use a specific partition type with real life example
- How SQL can enhance using right partition
- Indexes related to partitioned tables
- Optimizer statistics related to partitioned tables
- Partition maintenance

Kind Regards
Most Valuable Expert 2011
Top Expert 2012
your list of "specifics" is pretty much all of partitioning.  We can give you some overview, but in the end, you're going to need to do some reading.

types -
 range (data segregated by content < some value)
  list (each element in the list is one partition)
  hash (define "buckets", data  is hashed into one of the buckets)

advantages -
   partition pruning - if optimizer can pick a partition or subset of partitions to access, it has less io.
   purging - mostly applies to range, but possibly list.   simply drop entire partition when no longer needed.

   partition scanning/merging - when accessing multiple partitions, the query results need to be merged.  The parallel operations (if any) must be coordinated.  The overhead of managing the multiple partitions can be a net loss in performance for some queries
   maintenance - partitioning means you have more objects, more pieces to keep track of.  moving data around means you may need to periodically rebuild global indexes

when to use?  -  range,  partition data by dates.  Orders by shipping month,  transactions by day,  megawatts per quarter,  etc
  list - sales by division  east/west.   medical data by practice/insurance carrier,
hash - data that is segregated by easily identifiable criteria but not in a fixed way or too many values to list  for example customer names

how sql uses right partition - that's up to the optimizer but basically it boils down to  if your sql clauses are such that they can be mapped to partitions, then you'll get partition pruning and/or parallel operations.  If they can't.  You won't.

indexes - you can partition indexes along with the tables,  these are called local.  You can also have global indexes, that are either not partitioned at all,  or are partitioned by different criteria than the table itself.    For example  partition orders table data by shipping month, partition indexes on that orders by customer id

statistics - are maintained at the partition level and global to the entire table/index

maintenance - index rebuilds,  creating new partitions,  archiving/purging old ones.  pretty much the same thing you'd need to do for an unpartitioned table, except with more steps.