9
Partitioning and Storage-Related
Enhancements
Copyright © 2007, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to:
• Implement the new partitioning methods
• Employ data compression
• Create a SQL Access Advisor analysis session using
Enterprise Manager
• Create a SQL Access Advisor analysis session using
PL/SQL
• Set up a SQL Access Advisor analysis to get partition
recommendations
9-2 Copyright © 2007, Oracle. All rights reserved.
Oracle Partitioning
Core functionality Performance Manageability
Oracle8 Range partitioning Static partition Basic maintenance
Global range indexes pruning operations: add,
drop, exchange
Oracle8i Hash and composite Partitionwise joins Merge operation
range-hash partitioning Dynamic pruning
Oracle9i List partitioning Global index
maintenance
Oracle9i R2 Composite range-list Fast partition split
partitioning
Oracle10g Global hash indexes Local Index
maintenance
Oracle10g R2 1M partitions per table Multidimensional Fast drop table
pruning
Oracle More composite choices Interval Partitioning
REF Partitioning Partition Advisor
Database 11g
Virtual Column Partitioning
9-3 Copyright © 2007, Oracle. All rights reserved.
Partitioning Enhancements
• Interval partitioning
• System partitioning
• Composite partitioning enhancements
• Virtual column-based partitioning
• Reference partitioning
9-4 Copyright © 2007, Oracle. All rights reserved.
Interval Partitioning
• Interval partitioning is an extension of range
partitioning.
• Partitions of a specified interval are created when
inserted data exceeds all of the range partitions.
• At least one range partition must be created.
• Interval partitioning automates the creation of range
partitions.
9-5 Copyright © 2007, Oracle. All rights reserved.
Interval Partitioning: Example
CREATE TABLE SH.SALES_INTERVAL
PARTITION BY RANGE (time_id)
INTERVAL (NUMTOYMINTERVAL(1,'month')) STORE IN (tbs1,tbs2,tbs3,tbs4)
(
PARTITION P1 values less than (TO_DATE('1-1-2002','dd-mm-yyyy')),
PARTITION P2 values less than (TO_DATE('1-1-2003','dd-mm-yyyy')),
PARTITION P3 values less than (TO_DATE('1-1-2004','dd-mm-yyyy')))
AS
SELECT *
FROM [Link]
WHERE TIME_ID < TO_DATE('1-1-2004','dd-mm-yyyy');
Automatically created
when you insert data
P1 P2 P3 Pi1 … Pin
Range section Interval section
Transition point
9-6 Copyright © 2007, Oracle. All rights reserved.
Moving the Transition Point: Example
PREVIOUS
< 01/01/07 INSERT INTO orders_interval (…);
Transition point
Not yet
PREVIOUS materialized SYS_Px SYS_Py SYS_Pz SYS_Pt
< 01/01/07 < 01/08/06 < 01/11/06 < 01/12/06 < 01/03/08
Transition point
alter table orders_interval merge partitions
for(TO_DATE('15-10-2007','dd-mm-yyyy')),for(TO_DATE('15-11-2007','dd-mm-
yyyy')) into partition sys_p5z;
PREVIOUS SYS_Px SYS_Pz SYS_Pt
< 01/01/06 < 01/08/06 < 01/12/06 < 01/03/08
Transition point
9-7 Copyright © 2007, Oracle. All rights reserved.
System Partitioning
System partitioning:
• Enables application-controlled partitioning for selected
tables
• Provides the benefits of partitioning but the partitioning
and data placement are controlled by the application
• Does not employ partitioning keys like other
partitioning methods
• Does not support partition pruning in the traditional
sense
9-8 Copyright © 2007, Oracle. All rights reserved.
System Partitioning: Example
CREATE TABLE systab (c1 integer, c2 integer)
PARTITION BY SYSTEM
(
PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4
);
INSERT INTO systab PARTITION (p1) VALUES (4,5);
INSERT INTO systab PARTITION (p2) VALUES (150,2);
alter table systab merge partitions p1,p2 into partition p1;
9-9 Copyright © 2007, Oracle. All rights reserved.
System Partitioning: Guidelines
The following operations are supported for system-
partitioned tables:
• Partition maintenance operations and other DDL
operations
• Creation of local indexes
• Creation of local bitmapped indexes
• Creation of global indexes
• All DML operations
• INSERT SELECT with partition-extended syntax:
INSERT INTO <table_name> PARTITION(<partition-name>) <subquery>
9 - 10 Copyright © 2007, Oracle. All rights reserved.
Virtual Column–Based Partitioning
• Virtual column values are derived by the evaluation of a
function or expression.
• Virtual columns can be defined within a CREATE or
ALTER table operation.
CREATE TABLE employees
(employee_id number(6) not null,
• …
total_compensation as (salary *( 1+commission_pct))
• Virtual column values are not physically stored in the
table row on disk, but are evaluated on demand.
• Virtual columns can be indexed, and used in queries,
DML, and DDL statements like other table column types.
• Tables and indexes can be partitioned on a virtual
column and even statistics can be gathered upon them.
9 - 11 Copyright © 2007, Oracle. All rights reserved.
Virtual Column–Based Partitioning: Example
CREATE TABLE employees
(employee_id number(6) not null, first_name varchar2(30),
last_name varchar2(40) not null, emailvarchar2(25),
phone_number varchar2(20), hire_date date not null,
job_id varchar2(10) not null, salary number(8,2),
commission_pct number(2,2), manager_id number(6),
department_id number(4),
total_compensation as (salary *( 1+commission_pct))
)
PARTITION BY RANGE (total_compensation)
(
PARTITION p1 VALUES LESS THAN (50000),
PARTITION p2 VALUES LESS THAN (100000),
PARTITION p3 VALUES LESS THAN (150000),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
9 - 12 Copyright © 2007, Oracle. All rights reserved.
Reference Partitioning
• A table can now be partitioned based on the partitioning
method of a table referenced in its referential constraint.
• The partitioning key is resolved through an existing
parent/child relationship.
• The partitioning key is enforced by active primary key or
foreign key constraints.
• Tables with a parent/child relationship can be
equipartitioned by inheriting the partitioning key from
the parent table without duplicating the key columns.
• Partitions are automatically maintained.
9 - 13 Copyright © 2007, Oracle. All rights reserved.
Reference Partitioning: Benefit
Without using reference partitioning Reference partitioning
… Range(ORDER_DATE) …
Primary key (ORDER_ID)
Table ORDERS
Table
ORDER_ITEMS
… Range(ORDER_DATE) …
Foreign key (ORDER_ID)
Redundant storage/maintenance of ORDER_DATE Partition key inherited through PK/FK relationship
9 - 14 Copyright © 2007, Oracle. All rights reserved.
Reference Partitioning: Example
CREATE TABLE orders
( order_id NUMBER(12) , order_date DATE,
order_mode VARCHAR2(8), customer_id NUMBER(6),
order_status NUMBER(2) , order_total NUMBER(8,2),
sales_rep_id NUMBER(6) , promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
(PARTITION Q105 VALUES LESS THAN (TO_DATE('1-4-2005','DD-MM-YYYY')),
PARTITION Q205 VALUES LESS THAN (TO_DATE('1-7-2005','DD-MM-YYYY')),
PARTITION Q305 VALUES LESS THAN (TO_DATE('1-10-2005','DD-MM-YYYY')),
PARTITION Q405 VALUES LESS THAN (TO_DATE('1-1-2006','DD-MM-YYYY')));
CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL, line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL, unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
) PARTITION BY REFERENCE(order_items_fk);
9 - 15 Copyright © 2007, Oracle. All rights reserved.
Composite Partitioning Enhancements
• Range top level
– Range-Range
RANGE, LIST, INTERVAL
• List top level
SP1 SP1 … SP1 SP1
– List-List SP1
– List-Hash
SP2 SP2 SP2 … SP2 SP2
– List-Range
• Interval top level SP3 SP3 … SP3 SP3
SP3
– Interval-Range
– Interval-List SP4 SP4 SP4 … SP4 SP4
– Interval-Hash
LIST, RANGE, HASH
9 - 16 Copyright © 2007, Oracle. All rights reserved.
Range-Range Partitioning: Example
CREATE TABLE sales (
prod_id NUMBER(6) NOT NULL, cust_id NUMBER NOT NULL,
time_id DATE NOT NULL, channel_id char(1) NOT NULL,
promo_id NUMBER (6) NOT NULL,
quantity_sold NUMBER(3) NOT NULL,
amount_sold NUMBER(10,2) NOT NULL )
PARTITION BY RANGE (time_id)
SUBPARTITION BY RANGE (cust_id)
SUBPARTITION TEMPLATE
( SUBPARTITION sp1 VALUES LESS THAN (50000),
SUBPARTITION sp2 VALUES LESS THAN (100000),
SUBPARTITION sp3 VALUES LESS THAN (150000),
SUBPARTITION sp4 VALUES LESS THAN (MAXVALUE) )
(
PARTITION VALUES LESS THAN (TO_DATE('1-4-2007','DD-MM-YYYY')),
PARTITION VALUES LESS THAN (TO_DATE('1-7-2007','DD-MM-YYYY')),
PARTITION VALUES LESS THAN (TO_DATE('1-8-2007','DD-MM-YYYY')),
PARTITION VALUES LESS THAN (TO_DATE('1-1-2008','DD-MM-YYYY'))
);
9 - 17 Copyright © 2007, Oracle. All rights reserved.
Table Compression: Overview
• Oracle Database 11g extends compression for OLTP
data.
– Support for conventional DML operations
(INSERT, UPDATE, DELETE)
• New algorithm significantly reduces write overhead.
– Batched compression ensures no impact for most OLTP
transactions.
• No impact on reads
– Reads may actually see improved performance due to
fewer I/Os and enhanced memory efficiency.
9 - 18 Copyright © 2007, Oracle. All rights reserved.
Table Compression Concepts
Inserts are again
uncompressed.
Compressed
data
PCTFREE reached
triggers compression.
Uncompressed
data
Data block PCTFREE reached
triggers compression.
Header
PCTFREE
limit
Free
space
Inserts are
uncompressed.
9 - 19 Copyright © 2007, Oracle. All rights reserved.
Using Table Compression
• Requires database compatibility level at 11.1 or greater
• New syntax extends the COMPRESS keyword:
– COMPRESS [FOR {ALL | DIRECT_LOAD} OPERATIONS]
– FOR DIRECT_LOAD is the default: Refers to bulk load
operations from prior releases
– FOR ALL OPERATIONS: OLTP + direct loads
• Enable compression for new tables:
CREATE TABLE t1 COMPRESS FOR ALL OPERATIONS;
• Enable compression on existing table:
ALTER TABLE t2 COMPRESS FOR ALL OPERATIONS;
– Does not trigger compression on existing rows
9 - 20 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Overview
What
partitions, indexes,
and MVs do I need SQL
to optimize Solution Access
my entire Advisor
workload?
DBA
No expertise
required Component
Workload of CBO
Provides
implementation
script
9 - 21 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Usage Model
SQL Access
Advisor
SQL cache
Workload
Hypothetical
STS
Filter
Options
Indexes Materialized Materialized Partitioned
views views log objects
9 - 22 Copyright © 2007, Oracle. All rights reserved.
Possible Recommendations
Recommendation Comprehensive Limited
Add new (partitioned) index on table or materialized view. YES YES
Drop an unused index. YES NO
Modify an existing index by changing the index type. YES NO
Modify an existing index by adding columns at the end. YES YES
Add a new (partitioned) materialized view. YES YES
Drop an unused materialized view (log). YES NO
Add a new materialized view log. YES YES
Modify an existing materialized view log to add new YES YES
columns or clauses.
Partition an existing unpartitioned table or index. YES YES
9 - 23 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor Session: Initial Options
9 - 24 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor Session: Initial Options
9 - 25 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Workload Source
9 - 26 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Recommendation Options
9 - 27 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Recommendation Options
9 - 28 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Schedule and Review
9 - 29 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Results
9 - 30 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Results
9 - 31 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Results
9 - 32 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Results
9 - 33 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: Results
9 - 34 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: PL/SQL Procedure Flow
Step 3
Step 1 ADD_STS_REF
DELETE_STS_REF
CREATE_TASK EXECUTE_TASK
UPDATE_TASK_ATTRIBUTES INTERRUPT/CANCEL_TASK
DELETE_TASK MARK_RECOMMENDATION
QUICK_TUNE UPDATE_REC_ATTRIBUTES
GET_TASK_REPORT
Task-dependent GET_TASK_SCRIPT
SQL
Access Advisor
Advisor-dependent task
Report/Scripts
SET_TASK_PARAMETER
RESET_TASK
Step 2
9 - 35 Copyright © 2007, Oracle. All rights reserved.
SQL Access Advisor: PL/SQL Example
BEGIN 1
dbms_advisor.create_task(dbms_advisor.sqlaccess_advisor,'MYTASK');
END;
BEGIN
2
dbms_advisor.set_task_parameter('MYTASK','ANALYSIS_SCOPE','ALL');
dbms_advisor.set_task_parameter('MYTASK','MODE','COMPREHENSIVE');
END;
BEGIN 3
dbms_advisor.add_sts_ref('MYTASK','SH','MYSTS');
dbms_advisor.execute_task('MYTASK');
dbms_output.put_line(dbms_advisor.get_task_script('MYTASK'));
END;
9 - 36 Copyright © 2007, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Implement the new partitioning methods
• Employ data compression
• Create a SQL Access Advisor analysis session using
Enterprise Manager
• Create a SQL Access Advisor analysis session using
PL/SQL
• Set up a SQL Access Advisor analysis to get partition
recommendations
9 - 37 Copyright © 2007, Oracle. All rights reserved.
Practice 9: Overview
This practice covers the following topics:
• Using new partitioning schemes
• Using table compression
• Getting partitioning advices with SQL Access Advisor
9 - 38 Copyright © 2007, Oracle. All rights reserved.