1 / 57

Oracle Partitioning – Yesterday, Today, and Tomorrow

Oracle Partitioning – Yesterday, Today, and Tomorrow. Ananth Raghavan Senior Development Manager, Oracle Partitioning.

prue
Download Presentation

Oracle Partitioning – Yesterday, Today, and Tomorrow

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle Partitioning – Yesterday, Today, and Tomorrow Ananth Raghavan Senior Development Manager, Oracle Partitioning

  2. 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.

  3. Agenda • History and evolution of Oracle Partitioning • Proven functionality in 7th generation • Fundamental concepts for success • Resolving some myths • Upcoming new functionality • Q&A

  4. Agenda • History and evolution of Oracle Partitioning • Proven functionality in 7th generation • Fundamental concepts for success • Resolving some myths • Upcoming new functionality • Q&A

  5. 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

  6. Physical versus Logical PartitioningShared Everything Architecture - Oracle Logical Partitioning • Does not underlie any constraints • SMP, MPP, Cluster, Grid does not matter • Purely based on the business requirement • Availability. Manageability, Performance • Beneficial for every environment • Provides the most comprehensive functionality DB

  7. Physical versus Logical PartitioningShared Nothing Architecture Physical Partitioning • Fundamental system setup requirement • Node owns piece of DB • Enables parallelism • Number of partitions is equivalent to min. parallelism • Always needs HASH distribution • Equally sized partitions per node required for proper load balancing DB DB DB

  8. Oracle PartitioningThe way to Oracle Database 10g Release 2 • Oracle 8.0 • RANGE partitioning • Fundamental Maintenance operations • Static pruning • Oracle8i • HASH, composite RANGE-HASH • MERGE partitions • Dynamic pruning • Partition-wise joins • Oracle9i Release 1 • LIST partitioning • Global index maintenance • Oracle9i Release 2 • Fast Split • Composite RANGE-LIST • DEFAULT partition for LIST • Oracle Database10g Release 1 • Internal re-architecture (scalability) • Global hash-partitioned indexes • Local index maintenance • Oracle Database 10g Release 2 • One million partitions • Multi-dimensional pruning • Resource optimized drop table Core functionality: Partitioning techniques * Lots of enhancements not shown here, e.g. IOTs, MVs

  9. Oracle PartitioningThe way to Oracle Database 10g Release 2 • Oracle 8.0 • RANGE partitioning • Fundamental Maintenance operations • Static pruning • Oracle8i • HASH, composite RANGE-HASH • MERGE partitions • Dynamic pruning • Partition-wise joins • Oracle9i Release 1 • LIST partitioning • Global index maintenance • Oracle9i Release 2 • Fast Split • Composite RANGE-LIST • DEFAULT partition for LIST • Oracle Database10g Release 1 • Internal re-architecture (scalability) • Global hash-partitioned indexes • Local index maintenance • Oracle Database 10g Release 2 • One million partitions • Multi-dimensional pruning • Resource optimized drop table Partition Maintenance * Lots of enhancements not shown here, e.g. IOTs, MVs

  10. Oracle PartitioningThe way to Oracle Database 10g Release 2 • Oracle 8.0 • RANGE partitioning • Fundamental Maintenance operations • Static pruning • Oracle8i • HASH, composite RANGE-HASH • MERGE partitions • Dynamic pruning • Partition-wise joins • Oracle9i Release 1 • LIST partitioning • Global index maintenance • Oracle9i Release 2 • Fast Split • Composite RANGE-LIST • DEFAULT partition for LIST • Oracle Database10g Release 1 • Internal re-architecture (scalability) • Global hash-partitioned indexes • Local index maintenance • Oracle Database 10g Release 2 • One million partitions • Multi-dimensional pruning • Resource optimized drop table Performance * Lots of enhancements not shown here, e.g. IOTs, MVs

  11. Agenda • History and evolution of Oracle Partitioning • Proven functionality in 7th generation • Fundamental concepts for success • Resolving some myths • Upcoming new functionality • Q&A

  12. Oracle PartitioningFundamental 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

  13. Oracle PartitioningFundamental 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

  14. Agenda • History and evolution of Oracle Partitioning • Proven functionality in 7th generation • Fundamental concepts for success • Resolving some myths • Upcoming new functionality • Q&A

  15. 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

  16. 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

  17. 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

  18. 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

  19. Resolving some Myths “ .. Create partitions every month ..”

  20. Resolving some Myths

  21. Agenda • History and evolution of Oracle Partitioning • Proven functionality in 7th generation • Fundamental concepts for success • Resolving some myths • Upcoming new functionality • Q&A

  22. Future Directions (OW 2004/2005 slide)

  23. 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

  24. <Insert Picture Here> Extended Composite Partitioning

  25. 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 multi-column range partitioning • Extensions in Oracle Database 11g • List-Range • Range-Range • List-Hash • List-List

  26. 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

  27. Mar 2006 Composite Partitioning - Concept Table SALES RANGE(order_date)-RANGE(ship_date) • All records with order_date in March 2006 Jan 2006 ... ... Feb 2006 ... ... ... ... May 2006 ... ... ... ... Jan 2006 Feb 2006 Mar 2006 Jan 2007

  28. Composite Partitioning - Concept Table SALES RANGE(order_date)-RANGE(ship_date) • All records with ship_date in May 2006 Jan 2006 ... ... Feb 2006 ... ... ... ... May 2006 May 2006 ... ... ... ... Jan 2006 Feb 2006 Mar 2006 Jan 2007

  29. Composite Partitioning - Concept Table SALES RANGE(order_date)-RANGE(ship_date) • All records with order_date in March 2006ANDship_date in May 2006 Jan 2006 ... ... Feb 2006 ... ... ... ... May 2006 May 2006 ... ... ... ... Mar 2006 Jan 2006 Feb 2006 Mar 2006 Jan 2007

  30. <Insert Picture Here> Virtual Column based Partitioning

  31. 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 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

  32. Adams Blake King Smith 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

  33. 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))) 12 12500 12 12507 12 12666 12 12875

  34. Adams Jones Blake Clark King Smith Phillips 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) ... 12 32 12500 32320 12 32 12507 32407 ... 12 32 12666 32758 Hurd 12 32 12875 32980

  35. <Insert Picture Here> Interval Partitioning

  36. Application SQL CDRs Mar Jan Feb 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 BUT • Physical segmentation requires additional data management overhead • E.g. new partitions must be created on-time for new data Automate the partition management

  37. 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

  38. Table SALES ... ... ... Jan 2006 Feb 2006 Mar 2006 Jan 2007 Oct 2009 Nov 2009 First segment is created 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');

  39. 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 Other partitions only exist in metadata

  40. Interval Partitioning • As easy as One, Two, Three .. INSERT INTO sales (order_date DATE, ...) VALUES ('04-MAR-2006',...); 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

  41. Interval Partitioning • As easy as One, Two, Three .. INSERT INTO sales (order_date DATE, ...) VALUES ('17-OCT-2009',...); 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

  42. 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

  43. 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 Range partition section MERGE and move old partitions for ILM

  44. 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 Range partition section Interval partition section MERGE and move old partitions for ILM • Insert new data • - Automatic segment creation VALUES ('13-NOV-2009')

  45. Interval Partitioning • Range partitioned tables can be extended into interval partitioned tables • Simple metadata command • Investment protection Table SALES ... ... Q2 2006 2005 Q1 2006 Oct 2006

  46. Interval Partitioning • Range partitioned tables can be extended into interval partitioned tables • Simple metadata command • Investment protection Table SALES ... ... ... Q2 2006 2005 Q1 2006 Oct 2006 Old range partition table New monthly Interval partitions ALTER TABLE sales (order_date DATE, ...) SET INTERVAL(NUMTOYMINTERVAL(1,'month');

  47. <Insert Picture Here> REF Partitioning

  48. 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 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

  49. Before REF Partitioning Table ORDERS • RANGE(order_date) • Primary key order_id ... ... Jan 2006 Feb 2006 • Redundant storage of order_date • Redundant maintenance Table LINEITEMS • RANGE(order_date) • Foreign key order_id ... ... Jan 2006 Feb 2006

More Related