oracle database 11g partitioning, the next generation · oracle database 11g partitioning, the next...
TRANSCRIPT
<Insert Picture Here>
Oracle Database 11gPartitioning, the Next GenerationHermann Baer, OracleDirector Product Management, Server Technologies – Data Warehousing
Agenda
• History and evolution of Oracle Partitioning• Proven functionality in 7th generation
• Oracle database 11g - new functionality in detail• Complete the basic partitioning strategies
• Broaden and simplify partitioning
• Q&A
The Concept of PartitioningSimple Yet Powerful
Large Table
Difficult to Manage
Partition
Divide and Conquer
Easier to Manage
Improve Performance
Composite Partition
Better Performance
More flexibility to match business needs
JANFEB
JANFEB
USA
EUROPEORDERS
ORDERS
ORDERS
Transparent to applications
Oracle Partitioning: Over Ten Years of Development
Fast drop table“Multi-dimensional”pruning
1M partitions per tableOracle10g R2
Local Index maintenance
Global hash indexesOracle10g
Fast partition splitComposite range-list partitioning
Oracle9i R2
Global index maintenance
List partitioningOracle9i
Merge operationPartition-wise joins
“Dynamic” pruning
Hash and composite range-hash partitioning
Oracle8i
Basic maintenance operations: add, drop, exchange
“Static” partition pruning
Range partitioning
Global range indexes
Oracle8
ManageabilityPerformanceCore functionality
Oracle Partitioning: Over Ten Years of Development
Interval PartitioningPartition Advisor
More composite choicesREF PartitioningVirtual Column Partitioning
Oracle Database 11g
Fast drop table“Multi-dimensional”pruning
1M partitions per tableOracle10g R2
Local Index maintenance
Global hash indexesOracle10g
Fast partition splitComposite range-list partitioning
Oracle9i R2
Global index maintenance
List partitioningOracle9i
Merge operationPartition-wise joins
“Dynamic” pruning
Hash and composite range-hash partitioning
Oracle8i
Basic maintenance operations: add, drop, exchange
“Static” partition pruning
Range partitioning
Global range indexes
Oracle8
ManageabilityPerformanceCore functionality
ILM Assistant
• Freely available tool on OTN to enable the information lifecycle management of partitioned objects
Oracle Database 11g
• Complete the basic partitioning strategies• New composite partitioning methods
• Range-range, list-range, list-list, list-hash
• Broaden and simplify partitioning usage• Virtual column based partitioning
• REF Partitioning
• Interval Partitioning
• Partition Advisor
... and that's new ...
Implementing Oracle ILM
• Oracle ILM Assistant manages the life cycle of data leveraging Oracle Partitioning
• Define Lifecycle Definitions
• Illustrates storage costs & savings
• Calendar of events
• Simulates the impact of partitioning on a table
• Advises how to
• Partition a table
• Generates scripts to move data when required
Traditional Storage ApproachAll data resides on single storage tier
High Performance Storage Tier = $72 per Gb
All data on active = $972,000!
ActiveActive
Information Lifecycle ManagementReduce storage costs accordingly
5% Active 35% Less Active 60% Historical
High Performance Storage Tier = $72 per Gb
Low cost Storage Tier= $14 per Gb
Read only Storage Tier= $7 per Gb
$49,800 $67,700 $58,000
SQL Access Advisor Overview
Workload
SQL Access Advisor
Solution
Component
of CBOProvides
implementation
script
No expertiserequired
DBA
Whatpartitions, indexes, and MVs do I need
to optimizemy entire
workload?
Partition AdvisorPart of SQL Access Advisor
Indexes Materialized
views
Materialized
views log
SQL Access Advisor
Hypothetical
SQL cache
Filter Options
STS
Workload
Partitioned
objects
• 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 multi-column range partitioning
• Extensions in Oracle Database 11g• List-Range
• Range-Range
• List-Hash
• List-List
Extended Composite Partitioning Strategies
Table SALESRANGE(order_date)-RANGE(ship_date)�
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007
... ...
... ...
...
...
...
...
Jan 2006
Feb 2006
May 2006
Composite Partitioning - Concept
Composite Partitioning - Concept
Table SALESRANGE(order_date)-RANGE(ship_date)�
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007
... ...
... ...
...
...
...
...
Jan 2006
Feb 2006
May 2006
Mar 2006
• All records with order_date in March 2006
Composite Partitioning - Concept
Table SALESRANGE(order_date)-RANGE(ship_date)�
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007
... ...
... ...
...
...
...
...
Jan 2006
Feb 2006
May 2006
• All records with ship_date in May 2006
May2006
Composite Partitioning - Concept
Table SALESRANGE(order_date)-RANGE(ship_date)�
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007
... ...
... ...
...
...
...
...
Jan 2006
Feb 2006
May 2006
Mar 2006
• All records with order_date in March 2006ANDship_date in May 2006
May2006
Interval Partitioning
• Partitioning is key-enabling functionality for managing large volumes of data• One logical object for application transparency
• Multiple physical segments for Administration
• Improves Manageability, Availability, and Performance MarJan
Feb
CDRs
SQLSQL
Application
Automate the partition management
BUT• Physical segmentation requires additional data
management overhead• E.g. new partitions must be created on-time for new data
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
CREATE TABLE sales (order_date DATE, ...)�
PARTITON BY RANGE (order_date)�
INTERVAL(NUMTOYMINTERVAL(1,'month')�
(PARTITION p_first VALUES LESS THAN ('01-FEB-2006');
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
First segment is created
• As easy as One, Two, Three ..
Interval Partitioning
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
Other partitions only exist in metadata
• 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-FEB-2006');
Interval Partitioning
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
INSERT INTO sales (order_date DATE,
...)�
VALUES ('04-MAR-2006',...);
New segment is automatically allocated
• 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-FEB-2006');
Interval Partitioning
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
INSERT INTO sales (order_date DATE,
...)�
VALUES ('17-OCT-2009',...);
... whenever data for a new partition arrives
• 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-FEB-2006');
Interval Partitioning
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
• Interval partitioned table can have classical range and automated interval section• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
Interval Partitioning
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
• Interval partitioned table can have classical range and automated interval section• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
• MERGE and move old partitions for ILM
Range partition section
Interval Partitioning
Table SALES
Jan 2006
... ...
Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009
...
• Interval partitioned table can have classical range and automated interval section• Automated new partition management plus full partition
maintenance capabilities: “Best of both worlds”
• MERGE and move old partitions for ILM
Range partition section Interval partition section
VALUES ('13-NOV-2009') �• Insert new data• - Automatic segment creation
Interval Partitioning
Table SALES
...
2005 Q1 2006 Oct 2006
• Range partitioned tables can be extended into interval partitioned tables• Simple metadata command
• Investment protection
Q2 2006
...
Interval Partitioning
Table SALES
...
2005 Q1 2006 Oct 2006Q2 2006
ALTER TABLE sales (order_date DATE, ...)�
SET INTERVAL(NUMTOYMINTERVAL(1,'month');
• Range partitioned tables can be extended into interval partitioned tables• Simple metadata command
• Investment protection
...
New monthlyInterval partitions
Old range partition table
...
REF Partitioning
• Related tables benefit from same partitioning strategy• Sample order – lineitem
• Redundant storage of the same information solves this problem• Data overhead
• Maintenance overhead
Business Problem
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
... ...
Feb 2006
Table LINEITEMS
Jan 2006
... ...
Feb 2006
• RANGE(order_date) �
• Primary key order_id
• RANGE(order_date) �
• Foreign key order_id
• Redundant storage of order_date
• Redundant maintenance
REF Partitioning
Table ORDERS
Jan 2006
... ...
Feb 2006
Table LINEITEMS
Jan 2006
... ...
Feb 2006
• RANGE(order_date) �
• Primary key order_id
• RANGE(order_date) �
• Foreign key order_id
• RANGE(order_date) �
• Foreign key order_id
PARTITION BY REFERENCE• Partitioning key inherited through
PK-FK relationship
Virtual Columns
• 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
Business Problem
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 Adams
12507 Blake
12666
12875 Smith
King
Virtual Columns - Example
12500 Adams
12507 12Blake
12666 12
12875 12Smith
King
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)))�
12
• Base table with all attributes ...• ... is extended with the virtual (derived) column
Virtual Columns - Example
12500 Adams
12507 12Blake
12666 12
12875 12Smith
King
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) ...
12
• Base table with all attributes ...• ... is extended with the virtual (derived) column• ... and the virtual column is used as partitioning key
32320 Jones
32407 32Clark
32758 32
32980 32Phillips
32
... Hurd
• Optimized performance
• Comprehensive partitioning strategies to addressall business problems
• One consistent way to manage all your data
• Not just for data warehouse and high-end OLTP any more
• New referential, virtual column, composite, and interval partitioning features bring partitioning to mainstream
• Reduced total cost of ownership
• Place less used data on lower cost storage
• Proven functionality in th generation• Experience comes with age and customer usage
• Questions? Enhancement ideas?• Contact [email protected]
Oracle Partitioning