In part 1 I mentioned the Parallel execution option of Oracle.
2- Introduction to Partitioning
Partitioning is defined as breaking down a large problem into smaller manageable pieces, the concept of Divide and Conquer. So with Oracle, partitioning refers to “Dividing Tables and Indexes into manageable pieces.”
Partitioning feature is used;
– To increase availability; this is derived from the fact that partitions are independent entities,
– To ease administration burdens; without partitions these operations will generally be a massive INSERT, UPDATE and DELETE(lots of redo&rollback I/O) statements, like archiving of data is on a schedule and repetitive,
– To enhance DML and query performance; during Parallel DML Oracle uses many threads or process to perform your INSERT,UPDATE or DELETE instead of single process with partitioning.
Also storing partitions in separate tablespaces enables you to;
– Reduce the possibility of data corruption in multiple partitions
– Back up and recover each partition independently
– Control the mapping of partitions to disk drives (important for balancing I/O load)
After Oracle 9i Release 2 Range, Hash, List or Composite(Range-Hash or Range-List) can be chosen as a partitioning method. Following demostrations show the benefits of partitioning;
Code listing 64a : Convert A Partition Into A Stand-alone Table Example and Split Partition Example
Code listing 64b : Partition Elimination Example
Code listing 64c : OLTP and LOCAL versus GLOBAL index partitioning Example
As a conclusion, we have 5 table partitioning methods; Range, Hash, List, Range-Hash, Range-List and 3 index partitioning methods; Global Non-partition, Global partition, Locally partition with Oracle. And important considerations before partitioning a table or an index are;
– Is the table the “right” size and how volatile is its data?
– What are your maintenance considerations?
– How queries filter data on this data and how to choose the partition key?
To benefit when using partitioning option, like parallelism mentioned in part 1, it is vital that you have a goal you want to achieve with it. Also “Partitioning <> fast=true”.
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Refences Used :
http://www.psoug.org/reference/partitions.html
Oracle® Database Concepts 10g Release 2 (10.2) Chapter 18 Partitioned Tables and Indexes
Oracle® Database Administrator’s Guide 10g Release 2 (10.2) Chapter 17 Managing Partitioned Tables and Indexes
Drop parent partition post by Jonathan Lewis
Effective Oracle by Design by Thomas Kyte Chapter 3-Architectural Decisions, Page 185
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 