Transcript 11g_partitioning_ins_and_outs - Oracle DBA

<Insert Picture Here>
Getting to know the ins and outs of Oracle Partitioning in Oracle
Database 11g
Ananth Raghavan
Senior Director, Database Development
Agenda
• Partitioning in a nutshell
• Oracle 11g .. 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
ORDERS
ORDERS
USA
EUROPE
JAN FEB
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
<Insert Picture Here>
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
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-3x less per terabyte
© 2009 Oracle Corporation
8
Flexible
Partition for Manageability/Availability
Order Table
(partitioned by quarter)
Q4’05
Q1’06
Q2’06
Q3’06
Other data & queries not affected
9
Agenda
• Partitioning in a nutshell
• Oracle 11g .. a quite old release
• What’s new in 11.2.0.2?
• Some things less known
• Q&A
10
XX Days of Oracle Database 11g
•
days since Oracle 11g Release 1
– Released on
•
days since Oracle 11g Release 2
– Released on
As of 09/21/2010, inclusive
11
XX Days of Oracle Database 11g
• 1168 days since Oracle 11g Release 1
– Released on 07/11/2007
•
days since Oracle 11g Release 2
– Released on
As of 09/21/2010, inclusive
12
XX Days of Oracle Database 11g
• 1168 days since Oracle 11g Release 1
– Released on 07/11/2007
• 385 days since Oracle 11g Release 2
– Released on 09/01/2009
• Time for you to upgrade
• .. and for me to rehash Oracle Database 11g
As of 09/21/2010, inclusive
13
XX Days of Oracle Database 11g
• 1168 days since Oracle 11g Release 1
– Released on 07/11/2007
• 385 days since Oracle 11g Release 2
– Released on 09/01/2009
• Time for you to upgrade
• .. and for me to rehash Oracle Database 11g
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 8i
Hash partitioning
Range-Hash partitioning
Partition-wise joins
Dynamic partition
pruning
Expanded maintenance:
MERGE
Oracle 9i
List partitioning
Oracle 9i R2
Range-List partitioning
Oracle 10g
Global Hash indexes
Oracle 10g R2
1M partitions per table
Oracle 11g
Virtual column based
partitioning
More composite choices
REF partitioning
Oracle 11g R2
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 Apr2009
…
USA
…
EMEA
Nov 2009 Dec 2009
…
…
…
…
…
…
19
Composite Partitioning
• Composite partitioning available since Oracle 8i
– Range-Hash
– Range-List (9i)
• Extensions in Oracle Database 11g
–
–
–
–
List-Range
Range-Range
List-Hash
List-List
• Extensions in Oracle Database 11g Release 2
– Hash-Hash
20
REF Partitioning
• Inherit partitioning strategy
Line
Items
Stock
Holds
ORDERS
Back
Orders
Stock
Holds
Line
Items
ORDERS
JAN
ORDERS
Pick
Lists
Back
Orders
Line
Items
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 3NF order entry data model
• Redundant storage of same information solves 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
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
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
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
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
27
Agenda
• Partitioning in a nutshell
• Oracle 11g .. 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 11g ..
• What’s new in 11.2.0.2?
• Some things less known
• Q&A
31
Some Things less known
• Oracle Database 11g Release 1 (11.1)
– Interval partitioning versus Range partitioning
– Deferred segment creation versus Interval partitioning
– Child tables of REF partitioning are “different”
• Oracle Database 11g 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 (<value>) 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 p2 VALUES LESS THAN (20)
UPDATE INDEXES
(i_toto (PARTITION p2 UNUSABLE));
42
Enhanced Pruning Capabilities
Oracle Database 11g 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 9th generation
– Experience comes with age and customer usage
• Oracle 11g .. 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 [email protected]
46
Q&A
47
For More Information
search.oracle.com
Oracle Partitioning
or
oracle.com
48
49