Transcript Partition - yesterday, today, and tomorrow

<Insert Picture Here>
Oracle Partitioning – Yesterday, Today, and Tomorrow
Ananth Raghavan
Senior Development Manager, Oracle Partitioning
The following is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions.
The development, release, and timing of any
features or functionality described for Oracle’s
products remain at the sole discretion of Oracle.
Agenda
• History and evolution of Oracle Partitioning
• Proven functionality in 7th generation
•
•
•
•
Fundamental concepts for success
Resolving some myths
Upcoming new functionality
Q&A
Agenda
• History and evolution of Oracle Partitioning
• Proven functionality in 7th generation
•
•
•
•
Fundamental concepts for success
Resolving some myths
Upcoming new functionality
Q&A
What is Oracle Partitioning?
It is
• Powerful functionality to logically partition objects into
smaller pieces
• Only driven by business requirements
• Partitioning for Performance, Manageability, and
Availability
It is not
• Just a way to physically divide – or clump - any large
data set into smaller buckets
• Enabling pre-requirement to support a specific
hardware/software design
• Hash mandatory for shared nothing systems
Physical versus Logical Partitioning
Shared Everything Architecture - Oracle
Logical Partitioning
• Does not underlie any constraints
•
SMP, MPP, Cluster, Grid does not
matter
• Purely based on the business
requirement
•
DB
Availability. Manageability,
Performance
• Beneficial for every environment
•
Provides the most comprehensive
functionality
Physical versus Logical Partitioning
Shared Nothing Architecture
Physical Partitioning
• Fundamental system setup
requirement
•
Node owns piece of DB
• Enables parallelism
•
DB
DB
DB
Number of partitions is equivalent to
min. parallelism
• Always needs HASH distribution
•
Equally sized partitions per node
required for proper load balancing
Oracle Partitioning
The way to Oracle Database 10g Release 2
• Oracle9i Release 2
• Oracle 8.0
• RANGE partitioning
• Fast Split
• Fundamental Maintenance
• Composite RANGE-LIST
operations
• DEFAULT partition for LIST
• Static pruning
• Oracle Database10g Release 1
• Oracle8i
• Internal re-architecture (scalability)
• HASH, composite RANGE-HASH
• Global hash-partitioned indexes
• MERGE partitions
• Local index maintenance
• Dynamic pruning
• Oracle Database 10g Release 2
• Partition-wise joins
• One million partitions
• Oracle9i Release 1
• Multi-dimensional pruning
• LIST partitioning
• Resource optimized drop table
• Global index maintenance
Core functionality: Partitioning techniques
* Lots of enhancements not shown here, e.g. IOTs, MVs
Oracle Partitioning
The way to Oracle Database 10g Release 2
• Oracle9i Release 2
• Oracle 8.0
• RANGE partitioning
• Fast Split
• Fundamental Maintenance
• Composite RANGE-LIST
operations
• DEFAULT partition for LIST
• Static pruning
• Oracle Database10g Release 1
• Oracle8i
• Internal re-architecture (scalability)
• HASH, composite RANGE-HASH
• Global hash-partitioned indexes
• MERGE partitions
• Local index maintenance
• Dynamic pruning
• Oracle Database 10g Release 2
• Partition-wise joins
• One million partitions
• Oracle9i Release 1
• Multi-dimensional pruning
• LIST partitioning
• Resource optimized drop table
• Global index maintenance
Partition Maintenance
* Lots of enhancements not shown here, e.g. IOTs, MVs
Oracle Partitioning
The way to Oracle Database 10g Release 2
• Oracle9i Release 2
• Oracle 8.0
• RANGE partitioning
• Fast Split
• Fundamental Maintenance
• Composite RANGE-LIST
operations
• DEFAULT partition for LIST
• Static pruning
• Oracle Database10g Release 1
• Oracle8i
• Internal re-architecture (scalability)
• HASH, composite RANGE-HASH
• Global hash-partitioned indexes
• MERGE partitions
• Local index maintenance
• Dynamic pruning
• Oracle Database 10g Release 2
• Partition-wise joins
• One million partitions
• Oracle9i Release 1
• Multi-dimensional pruning
• LIST partitioning
• Resource optimized drop table
• Global index maintenance
Performance
* Lots of enhancements not shown here, e.g. IOTs, MVs
Agenda
• History and evolution of Oracle Partitioning
• Proven functionality in 7th generation
•
•
•
•
Fundamental concepts for success
Resolving some myths
Upcoming new functionality
Q&A
Oracle Partitioning
Fundamental Concepts for Success
• While performance seems to be the most visible one,
don't forget about the rest, e.g.
• Partitioning must address all business-relevant areas of
Performance, Manageability, and Availability
• Partition autonomy is crucial
• Fundamental requirement for any partition maintenance
operations
• Acknowledge partitions as metadata in the data dictionary
Oracle Partitioning
Fundamental Concepts for Success
• Provide full partition autonomy
• Use local indexes whenever possible
• Enable partition all table-level operations for partitions, e.g.
TRUNCATE, MOVE, COMPRESS
• Make partitions visible and usable for database
administration
• Partition naming for ease of use
• Maintenance operations must be partition-aware
• Also true for indexes
• Maintenance operations must not interfere with online
usage of a partitioned table
Agenda
• History and evolution of Oracle Partitioning
• Proven functionality in 7th generation
•
•
•
•
Fundamental concepts for success
Resolving some myths
Upcoming new functionality
Q&A
Resolving some Myths
“Global Index maintenance is bad”
• Partition maintenance takes longer when global
indexes are updated
• Global index maintenance is incremental
• No complete rebuild
• Fully transparent
• Table and partition are not locked
• Continuous and transparent availability
Resolving some Myths
“Oracle cannot add or remove data from a
partitioned table”
• Oracle provides the most comprehensive set of data
add and removal operations
• DROP removes a partition, including data and metadata
• TRUNCATE provides a fast data removal, preserving the
metadata
• EXCHANGE provides the capability to exchange a partition
with a standalone table
• Preserves data for both sides of the exchange
• Fast data in
• Fast data out
Resolving some Myths
“Local indexes are not important and cannot be
used as primary or unique index”
• Local indexes are crucial for full partition autonomy
• Logical grouping of partition and index segments enables fast
maintenance operations
• Only local indexes enable full physical data separation for
partitioned objects
• Local indexes can be unique or primary keys
• Inclusion of partitioning key mandatory for local autonomy
• Most indexes are non-unique
Resolving some Myths
“Oracle's Partition creation syntax is worse
than the syntax of other vendors”
• No “syntactical sugar’ in SQL for initial object creation
• Provided by Enterprise Manager
• No metadata extension
• Subpartition templates provide real metadata
• Initial creation and future partitions
• It’s getting even better ..
• Stay tuned for the upcoming new features section
Resolving some Myths
“ .. Create partitions
every month ..”
Resolving some Myths
Agenda
• History and evolution of Oracle Partitioning
• Proven functionality in 7th generation
•
•
•
•
Fundamental concepts for success
Resolving some myths
Upcoming new functionality
Q&A
Future Directions (OW 2004/2005 slide)
Extended Partitioning Strategies
• Partition techniques enable partitioning for your business
• The more techniques the better
• Map your business process the most optimal way
• Oracle Database 11g enhances the existing partitioning
strategies significantly
•
•
•
•
Extended composite partitioning strategies
Virtual column based partitioning
Interval Partitioning
REF Partitioning
<Insert Picture Here>
Extended Composite
Partitioning
Extended Composite Partitioning
Strategies
• Concept of composite partitioning
• Data is partitioned along two dimensions (A,B)
• A distinct value pair for the two dimensions uniquely
determines the target partitioning
• Composite partitioning is complementary to multicolumn range partitioning
• Extensions in Oracle Database 11g
•
•
•
•
List-Range
Range-Range
List-Hash
List-List
Composite Partitioning - Concept
Table SALES
RANGE(order_date)-RANGE(ship_date)
Jan
2006
...
...
Feb
2006
...
...
...
...
Jan
2007
...
...
Jan 2006
Feb 2006
...
...
Mar 2006
Jan 2007
Composite Partitioning - Concept
Table SALES
RANGE(order_date)-RANGE(ship_date)
Jan
2006
...
• All records with
order_date in
...
March 2006
Feb
2006
...
...
...
...
May
2006
...
...
Jan 2006
Feb 2006
...
...
Mar
Mar2006
2006
Jan 2007
Composite Partitioning - Concept
Table SALES
RANGE(order_date)-RANGE(ship_date)
• All records with
Jan
2006
...
...
Feb
2006
...
...
...
...
May
May
2006
...
...
Jan 2006
Feb 2006
...
...
Mar 2006
Jan 2007
ship_date in
May 2006
Composite Partitioning - Concept
Table SALES
RANGE(order_date)-RANGE(ship_date)
• All records with
Jan
2006
...
...
Feb
2006
...
...
...
...
May
May
2006
...
...
Jan 2006
Feb 2006
...
...
Mar
Mar2006
2006
Jan 2007
order_date in
March 2006
AND
ship_date in
May 2006
<Insert Picture Here>
Virtual Column based
Partitioning
Virtual Columns
Business Problem
• Extended Schema attributes are fully derived and
dependent on existing common data
• Redundant storage or extended view definitions are
solving this problem today
• Requires additional maintenance and creates overhead
Solution
• Oracle Database 11g introduces virtual columns
• Purely virtual, meta-data only
• Treated as real columns except no DML
• Virtual columns can have statistics
• Virtual columns are eligible as partitioning key
• Enhanced performance and manageability
Virtual Columns - Example
• Base table with all attributes ...
CREATE TABLE accounts
(acc_no
number(10)
not null,
acc_name
varchar2(50) not null, ...
12500
12507
12666
12875
Adams
Blake
King
Smith
Virtual Columns - Example
• Base table with all attributes ...
• ... is extended with the virtual (derived) column
CREATE TABLE accounts
(acc_no
number(10)
not null,
acc_name
varchar2(50) not null, ...
acc_branch number(2)
generated always as
(to_number(substr(to_char(acc_no),1,2)))
12500
12507
12666
12875
Adams
Blake
King
Smith
12
12
12
12
Virtual Columns - Example
• Base table with all attributes ...
• ... is extended with the virtual (derived) column
• ... and the virtual column is used as partitioning key
CREATE TABLE accounts
(acc_no
number(10)
not null,
acc_name
varchar2(50) not null, ...
acc_branch number(2)
generated always as
(to_number(substr(to_char(acc_no),1,2)))
partition by list (acc_branch) ...
12500
12507
12666
12875
Adams
Blake
King
Smith
12
12
12
12
...
32320
32407
32758
32980
Jones
Clark
Hurd
Phillips
32
32
32
32
<Insert Picture Here>
Interval Partitioning
Interval Partitioning
• Partitioning is key-enabling functionality for
managing large volumes of data
Application
• One logical object for application transparency
• Multiple physical segments for Administration
• Improves Manageability, Availability, and
Performance
SQL
CDRs
Mar
Jan
BUT
Feb
• Physical segmentation requires additional data
management overhead
• E.g. new partitions must be created on-time for new data
Automate the partition management
Interval Partitioning
• Interval Partitioning
• Extension to Range Partitioning
• Full automation for equi-sized range partitions
• Partitions are created as metadata information only
• Start Partition is made persistent
• Segments are allocated as soon as new data
arrives
• No need to create new partitions
• Local indexes are created and maintained as well
No need for any partition management
Interval Partitioning
• As easy as One, Two, Three ..
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,'month')
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006');
Table SALES
...
Jan 2006
Feb 2006
Mar 2006
First segment is created
...
Jan 2007
...
Oct 2009
Nov 2009
Interval Partitioning
• As easy as One, Two, Three ..
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,'month')
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006');
Table SALES
...
Jan 2006
Feb 2006
Mar 2006
...
Jan 2007
...
Oct 2009
Other partitions only exist in metadata
Nov 2009
Interval Partitioning
• As easy as One, Two, Three ..
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,'month')
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006');
Table SALES
...
Jan 2006
Feb 2006
Mar 2006
...
Jan 2007
...
Oct 2009
Nov 2009
New segment is automatically allocated
INSERT INTO sales (order_date DATE,
...)
VALUES ('04-MAR-2006',...);
Interval Partitioning
• As easy as One, Two, Three ..
CREATE TABLE sales (order_date DATE, ...)
PARTITON BY RANGE (order_date)
INTERVAL(NUMTOYMINTERVAL(1,'month')
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006');
Table SALES
...
Jan 2006
Feb 2006
Mar 2006
...
Jan 2007
...
Oct 2009
Nov 2009
... whenever data for a new partition arrives
INSERT INTO sales (order_date DATE,
...)
VALUES ('17-OCT-2009',...);
Interval Partitioning
• Interval partitioned table can have classical range and
automated interval section
• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
Table SALES
...
Jan 2006
Feb 2006
Mar 2006
...
Jan 2007
...
Oct 2009
Nov 2009
Interval Partitioning
• Interval partitioned table can have classical range and
automated interval section
• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
Table SALES
...
Jan 2006
Feb 2006
Mar 2006
Range partition section
...
Jan 2007
...
Oct 2009
1. MERGE and move old partitions for ILM
Nov 2009
Interval Partitioning
• Interval partitioned table can have classical range and
automated interval section
• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
Table SALES
...
Jan 2006
Feb 2006
Mar 2006
Range partition section
...
Jan 2007
Oct 2009
Interval partition section
...
Nov 2009
1. MERGE and move old partitions for ILM
VALUES ('13-NOV-2009')
1. Insert new data
•
- Automatic segment creation
Interval Partitioning
• Range partitioned tables can be extended into interval
partitioned tables
• Simple metadata command
• Investment protection
Table SALES
...
...
2005
Q1 2006
Q2 2006
Oct 2006
Interval Partitioning
• Range partitioned tables can be extended into interval
partitioned tables
• Simple metadata command
• Investment protection
Table SALES
...
...
2005
Q2 2006
Q1 2006
Old range partition table
...
Oct 2006
New monthly
Interval partitions
ALTER TABLE sales (order_date DATE, ...)
SET INTERVAL(NUMTOYMINTERVAL(1,'month');
<Insert Picture Here>
REF Partitioning
REF Partitioning
Business Problem
• Related tables benefit from same partitioning strategy
• Sample order – lineitem
• Redundant storage of the same information solves
this problem
• Data overhead
• Maintenance overhead
Solution
• Oracle Database 11g introduces REF Partitioning
• Child table inherits the partitioning strategy of parent table
through PK-FK relationship
• Intuitive modelling
• Enhanced Performance and Manageability
Before REF Partitioning
Table ORDERS
...
Jan 2006
• RANGE(order_date)
... • Primary key order_id
Feb 2006
• Redundant storage of order_date
• Redundant maintenance
Table LINEITEMS
...
Jan 2006
Feb 2006
• RANGE(order_date)
... • Foreign key order_id
REF Partitioning
Table ORDERS
...
Jan 2006
• RANGE(order_date)
... • Primary key order_id
Feb 2006
PARTITION BY REFERENCE
• Partitioning key inherited through
PK-FK relationship
Table LINEITEMS
...
Jan 2006
Feb 2006
• RANGE(order_date)
... • Foreign key order_id
Oracle Partitioning
• Proven functionality in 7th generation
• Experience comes with age and customer usage
• Fundamental concepts for success
• The most comprehensive and complete offering
• There will be always myths
• .. and envious competitors
• Ground-breaking new functionality available soon
• 8th generation will be even better
Oracle Partitioning
• Proven functionality in 8th generation
• Experience comes with age and customer usage
• Fundamental concepts for success
• The most comprehensive and complete offering
• There will be always myths
• .. and envious competitors
• Ground-breaking new functionality available soon
• 9th generation will be even better
• There is always room for improvements
• Any ideas?? Email [email protected]
The preceding is intended to outline our general
product direction. It is intended for information
purposes only, and may not be incorporated into any
contract. It is not a commitment to deliver any
material, code, or functionality, and should not be
relied upon in making purchasing decisions.
The development, release, and timing of any
features or functionality described for Oracle’s
products remain at the sole discretion of Oracle.
For More Information
http://search.oracle.com
Oracle Partitioning
or
http://www.oracle.com/