Insert Picture Here Getting to know the ins

Getting to know the ins and outs of Oracle Partitioning in Oracle Database 11 g Ananth Raghavan Senior Director, Database Development

Agenda • Partitioning in a nutshell • Oracle 11 g. . a quite old release • What’s new in 11. 2. 0. 2? • Some things less known • Q&A 3

The Concept of Partitioning Simple Yet Powerful ORDERS USA EUROPE JAN FEB Large Table Partition Composite Partition Difficult to Manage Divide and Conquer Better Performance Easier to Manage More flexibility to match business needs Improve Performance Transparent to applications 4

Arup Nanda Senior Director Database Engineering and Operations Starwood Hotels “Partitioning is a key enabler for the performance of our system. We just could not process our data volumes without Partitioning. ” 5

Partitioning Benefits Partitioning is • Faster – By touching and maintaining only the relevant data • Cheaper – By storing the data appropriately and most cost-effective • Flexible – By managing partitions autonomously 6 6

Partition for Performance ---Sales Table--- 06 -Jan 06 -Feb 06 -Mar • Only relevant partitions will be accessed – Static pruning with known values in advance – Dynamic pruning uses internal recursive SQL to find the relevant partitions • Minimizes I/O operations – Provides massive performance gains 06 -Apr 06 -May SELECT sum(sales_amount) FROM sales WHERE sales_date BETWEEN ‘ 01 -MAR-2006’ AND ‘ 31 -MAY-2006’; 06 -Jun 7

Partition for Tiered Storage ORDERS TABLE (7 years) 2003 2008 2009 95% Less Active 5% Active Low End Storage Tier High End Storage Tier 2 -3 x less per terabyte © 2009 Oracle Corporation 8

Flexible Partition for Manageability/Availability Order Table (partitioned by quarter) Q 4’ 05 Q 1’ 06 Q 2’ 06 Q 3’ 06 Other data & queries not affected 9

Agenda • Partitioning in a nutshell • Oracle 11 g. . a quite old release • What’s new in 11. 2. 0. 2? • Some things less known • Q&A 10

XX Days of Oracle Database 11 g • days since Oracle 11 g Release 1 – Released on • days since Oracle 11 g Release 2 – Released on As of 09/21/2010, inclusive 11

XX Days of Oracle Database 11 g • 1168 days since Oracle 11 g Release 1 – Released on 07/11/2007 • days since Oracle 11 g Release 2 – Released on As of 09/21/2010, inclusive 12

XX Days of Oracle Database 11 g • 1168 days since Oracle 11 g Release 1 – Released on 07/11/2007 • 385 days since Oracle 11 g Release 2 – Released on 09/01/2009 • Time for you to upgrade • . . and for me to rehash Oracle Database 11 g As of 09/21/2010, inclusive 13

XX Days of Oracle Database 11 g • 1168 days since Oracle 11 g Release 1 – Released on 07/11/2007 • 385 days since Oracle 11 g Release 2 – Released on 09/01/2009 • Time for you to upgrade • . . and for me to rehash Oracle Database 11 g As of 09/21/2010, inclusive 14

Oracle Partitioning Over a decade of development Core functionality Performance Manageability Oracle 8. 0 Range partitioning Global Range indexes Static partition pruning Basic maintenance: ADD, DROP, EXCHANGE Oracle 8 i Hash partitioning Range-Hash partitioning Partition-wise joins Dynamic partition pruning Expanded maintenance: MERGE Oracle 9 i List partitioning Oracle 9 i R 2 Range-List partitioning Oracle 10 g Global Hash indexes Oracle 10 g R 2 1 M partitions per table Oracle 11 g Virtual column based partitioning More composite choices REF partitioning Oracle 11 g R 2 Hash-Hash partitioning * available with 11. 2. 0. 2 Global index maintenance Fast partition SPLIT Local Index maintenance Multi-dimensional pruning Fast DROP TABLE Interval partitioning Partition Advisor Incremental stats mgmt “AND” pruning Multi-branch execution Segment creation on demand* 15 15

Interval Partitioning • Partitions are created automatically as data arrives 16

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 17

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 • First segment is created – Mandatory to have a defined lower bound • Other segments only meta-data – Will be allocated when data is inserted 18

Composite Partitioning • Data is organized in along two dimensions – Record placement is deterministically identified by dimensions • Example RANGE-LIST Jan 2009 Feb 2009 Mar 2009 Apr 2009 … USA … EMEA Nov 2009 Dec 2009 … … … 19

Composite Partitioning • Composite partitioning available since Oracle 8 i – Range-Hash – Range-List (9 i) • Extensions in Oracle Database 11 g – – List-Range-Range List-Hash List-List • Extensions in Oracle Database 11 g Release 2 – Hash-Hash 20

REF Partitioning • Inherit partitioning strategy Line Items Stock Holds ORDERS Stock Holds Line Items ORDERS Back Orders ORDERS Pick Lists Back Orders Line Items JAN Stock Holds Pick Lists FEB Pick Lists Partition ORDERS by Date Stock Holds ORDERS Back Orders Line Items Stock Holds Back Orders MAR Line Items ORDERS Pick Lists Back Orders APR Pick Lists 21

REF Partitioning Business problem • Related tables benefit from same partitioning strategy – Sample 3 NF order entry data model • Redundant storage of same information solves problem – Data overhead – Maintenance overhead Solution • Oracle Database 11 g introduces REF Partitioning • Child table inherits the partitioning strategy of parent table through PK-FK relationship • Intuitive modelling • Enhanced Performance and Manageability 22

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) • Primary key order_id Jan 2006 Feb 2006 23

With REF Partitioning Table ORDERS. . . Jan 2006 Feb 2006 • RANGE(order_date). . . • Primary key order_id PARTITION BY REFERENCE • Partitioning key inherited through PK-FK relationship Table LINEITEMS. . . • RANGE(order_date). . . • Foreign key order_id Jan 2006 Feb 2006 24

Virtual Column-Based Partitioning ORDERS ORDER_ID ORDER_DATE CUSTOMER_ID. . . ----------- -9834 -US-14 12 -JAN-2007 65920 8300 -EU-97 14 -FEB-2007 39654 3886 -EU-02 16 -JAN-2007 4529 2566 -US-94 19 -JAN-2007 15327 3699 -US-63 02 -FEB-2007 REGION AS (SUBSTR(ORDER_ID, 6, 2)) ------ 18733 US EU EU US US ORDERS USA • REGION requires no storage • Partition by ORDER_DATE, REGION EMEA JAN FEB 25

Virtual Columns • Base table with all attributes • Virtual (derived) column based on values of other columns • No cross-table references possible CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar 2(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 26

Virtual Columns - Example • Base table with all attributes • Virtual (derived) column based on values of other columns • No cross-table references possible • Virtual column is used as partitioning key CREATE TABLE accounts (acc_no number(10) not null, acc_name varchar 2(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 . . . 32320 32407 32758 32980 Jones Clark Hurd Phillips 32 32 27

Agenda • Partitioning in a nutshell • Oracle 11 g. . a quite old release • What’s new in 11. 2. 0. 2? • Some things less known • Q&A 28

Deferred Segment Creation A. k. a Segment creation on demand • Segment creation for partitioned tables (and indexes) is delayed until first data inserted – Support for partitioned objects beginning with 11. 2. 0. 2 • Specifically beneficial for pre-packaged applications – Common deployments consist of thousands of tables, many of them being empty – Reduced storage foot print – Faster initial deployment • Leverage this functionality after database migration – API to drop segments for existing empty objects 29 29

Deferred Segment Creation A. k. a Segment creation on demand • Enabled by DEFAULT with compatible=11. 2 – Init. ora: deferred_segment_creation = [TRUE | FALSE ] • Session and system level attribute – Object level: SEGMENT CREATION [IMMEDIATE | DEFERRED} • Indexes inherit the attribute from the table – No support for bitmap join indexes and domain indexes 30 30

Agenda • Partitioning in a nutshell • XX days of Oracle 11 g. . • What’s new in 11. 2. 0. 2? • Some things less known • Q&A 31

Some Things less known • Oracle Database 11 g Release 1 (11. 1) – Interval partitioning versus Range partitioning – Deferred segment creation versus Interval partitioning – Child tables of REF partitioning are “different” • Oracle Database 11 g Release 2 (11. 2) – Partitioning and partially unusable indexes – Enhanced index maintenance for partition maintenance operations – Enhanced pruning capabilities 32

Interval versus Range Partitioning • Partition bounds – Interval partitions have lower and upper bound – Range partitions only have upper bounds • Lower bound derived by previous partition • Partition naming – Interval partitions cannot be named in advance • Use the PARTITION FOR () clause – Range partitions must be named 33

Interval versus Range Partitioning, cont. • Partition merge – Multiple non-existent interval partitions are silently merged – Only two adjacent range partitions can be merged at any point in time • Number of partitions – Interval partitioned tables have always one million partitions • Non-existent partitions “exist” through INTERVAL clause • No MAXVALUES clause for interval partitioning – Maximum value defined through number of partitions and INTERVAL clause – Range partitioning can have up to one million partitions • MAXVALUES clause defines most upper partition 34

Interval versus Range Partitioning, cont. • Interval partitioned table has classical range and automated interval section – Concept of a “transition point” – [USER|ALL|DBA]_TAB_PARTITIONS. INTERVAL Table SALES . . . Jan 2006 Feb 2006 Mar 2006 Range partition section . . . Jan 2007 . . . Oct 2009 Nov 2009 Interval partition section Transition point 35

Deferred Segment Creation versus Interval Partitioning • Interval Partitioning – Maximum number of one million partitions are pre-defined • Explicitly defined plus interval-based partitions – No segments are allocated for partitions without data • New record insertion triggers segment creation – Ideal for “ever-growing” tables • “Standard” Partitioning with deferred segment creation – Only explicitly defined partitions are existent • New partitions have to be added via DDL – No segments are allocated for partitions without data • New record insertion triggers segment creation when data matches pre-defined partitions – Ideal for sparsely populated pre-defined tables 36

REF Partitioning • Inheritance of partition key couples parent and child tables together – Child tables do not have a partitioning key – PK – FK relationship cannot be disabled or even dropped • Due to the tight coupling, some things are different – [Sub]Partition names are inherited down from the parent to the child tables • No system-generated names unless parent has them – Child partitions are by default co-located with the parent partition • Default for user is automatically overwritten 37

REF Partitioning • Partition maintenance operations (PMOPs) – PMOP that change the table structure are implicit for child tables and inherited from the parent table • ADD, DROP, MERGE, and SPLIT – PMOPs without structure changes are fully supported • MOVE, EXCHANGE – TRUNCATE works in the presence of PK-FK relationship • Partition-wise Joins (PWJ) – Joining parent and child tables are always eligible for PWJ, due to the known data co-location in the joining partitions 38

Unusable Indexes • Unusable index partitions are commonly used in environments with fast load requirements – “Safe” the time for index maintenance at data insertion – Unusable index segments do not consume any space (11. 2) • Unusable indexes are ignored by the optimizer – SKIP_UNUSABLE_INDEXES = [TRUE | FALSE ] • Partitioned indexes can be used by the optimizer even if some partitions are unusable – Prior to 11. 2, static pruning and only access of usable index partitions mandatory – With 11. 2, intelligent rewrite of queries using UNION ALL 39

Multi-Branch Execution • Sample plan 40

Intelligent Multi-Branch Execution • Intelligent UNION ALL expansion in the presence of partially unusable indexes – Transparent internal rewrite – Usable index partitions will be used – Full partition access for unusable index partitions 41

Enhanced Index Maintenance for PMOPs • Set a global index UNUSABLE right at partition creation time ALTER TABLE toto ADD PARTITION p 2 VALUES LESS THAN (20) UPDATE INDEXES (i_toto (PARTITION p 2 UNUSABLE)); 42

Enhanced Pruning Capabilities Oracle Database 11 g Release 2 • Extended modeling capabilities for better data placement and pruning – Support for virtual columns as primary and foreign key for Reference Partitioning • Enhanced optimizer support for Partitioning – Multi-predicate pruning – Intelligent multi-branch execution plan with unusable index partitions 43 43

“AND” Pruning • All predicates on partition key will used for pruning – Dynamic and static predicates will now be used combined • Example: – Star transformation with pruning predicate on both the FACT Dynamic pruning table and a dimension FROM sales s, times t … Static pruning WHERE s. time_id = t. time_id. . AND t. fiscal_year in (2000, 1999) AND s. time_id between TO_DATE('01 -JAN-1999', 'DD-MON-YYYY') and TO_DATE('01 -JAN-2000', 'DD-MON-YYYY') 44 44

“AND” Pruning • Sample plan 45

Summary • Partitioning in a nutshell • Proven functionality in 9 th generation – Experience comes with age and customer usage • Oracle 11 g. . a quite old release • It’s time to upgrade – Comprehensive partitioning for all business problems • One consistent way to manage all your data – Manageability and performance benefits • Questions? Enhancement ideas? – Contact hermann. baer@oracle. com 46

Q&A 47

For More Information search. oracle. com Oracle Partitioning or oracle. com 48

49