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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Did you Google it?  The first two links look good:

I still suggest the online docs.
can you be more specific about what you're trying to learn?

Chakravarthi AyyalaDatabase AdministratorCommented:
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
crazywolf2010Author Commented:
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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.