Transcript Partitioning - Oracle DBA

Partitioning – Let’s
Divide and Conquer!
Gavin Soorma,
Senior Oracle DBA, Bankwest
Agenda
•
•
•
•
•
•
•
•
•
•
The what, who and why of Partitioning
Partitioning – decisions and challenges
Partitioning – It’s evolution
Types of Partitioning
What’s new in Oracle11g?
Partitioned Indexes
Composite Partitioning
Partition Maintenance Operations
Partitioning and the Cost-based Optimizer
Converting a non-partitioned table to a partitioned table
What is Partitioning?
a) A Structure dividing a space into parts (noun)
b) To divide or separate (verb)
Source: Oxford English Dictionary
•
Additional licensable option of the Oracle Enterprise Edition.
•
Partitioning allows a table, index or index-organized table to be divided
and subdivided into smaller pieces called partitions and sub-partitions.
•
A partitioned object has multiple pieces that can be managed either
collectively or individually.
•
Each partition has its own name, and may optionally have its own storage
characteristics.
•
Tables are partitioned using a 'partitioning key', a set of column/columns
which determine in which partition a given row will reside.
Really Big
Table
Really Big Table
Partitioning stores a data segment (Table, Index, LOB) as
multiple segments while retaining a logically massive
structure.
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Who Partitions?
• Deciding on what and how to partition is both a Developer and
DBA job.
• A good of understanding of the business rules needs to be
known about how the data is utilized within Oracle. For example,
how data is loaded and queried by the application?
• A great portion of care needs to done in selection of the type of
partitioning along with the partition key.
• Poor selection of partition or partition key could lead to poor DML
and DDL performance.
• Always test, test, and test again prior to implementing in production.
Why Partition?
• For Manageability
Partitioning enables data management operations such data loads, index
creation and rebuilding, and backup/recovery at the partition level, rather
than on the entire table. This results in significantly reduced times for these
operations.
• For Performance
Partitioning improves query performance. In many cases, the results of a
query can be achieved by accessing a subset of partitions, rather than the
entire table. Partition Pruning and Partition-wise joins can provide orderof-magnitude gains in performance.
• For Availability
Partitioning increases the availability of mission-critical databases if critical
tables and indexes are divided into partitions to reduce the maintenance
windows, recovery times, and impact of failures.
Decisions and Challenges
• License cost of Partitioning option (~11,000$ per CPU)
• Number of Partitions.
• Choosing the partitioning key column.
• Partitioning Key – single column, multiple column.
• Choosing the type of partitioning: Range, Hash-List, Range-Hash,
Range-List, List-List, Range-Range ….
• Which tables to Partition …. All tables > 2GB (Oracle says so …)
• Think about it if table is > 1 million rows (I say so …)
• Partitioned tables with non partitioned or partitioned index
• Global Index vs Local Index
Oracle Partitioning
10 years of innovation…
Database Release
Core Functionality
Oracle 8.0 (1997)
Range Partitioning
Oracle 8i
Hash and Composite
Partitioning
Oracle 9i
List Partitioning
Oracle 9i Release 2
Composite Range-List
Partitioning
Oracle 10g
Global Hash Indexes
Oracle 10g Release 2
1M partitions per table
Oracle 11g
Interval Partitioning, System
Partitioning, REF Partitioning,
Virtual Column Partitioning,
Partition Advisor , Composite
All Partitioning
Partitioning Methods
Oracle provides the following partitioning methods(pre 11g):
• Range Partitioning
• List Partitioning
• Hash Partitioning
• Composite Partitioning
Composite Partitioning is a combination of the methods shown above
Composite Partitioning
Range-Hash
Range-List
Partitioned by date_of_sale
then ….
Partitioned by salesman_id
Partitioned by date_of_sale
then ….
Partitioned by sales_region
RANGE Partitioning
• Introduced in Oracle 8.0
• Useful when Data has logical ranges into which it can be
distributed by – example, a range of dates
• Data is mapped to partitions based on ranges of partition key values
established for each partition
• Each partition has a VALUES LESS THAN clause, which specifies
a non inclusive upper bound for the partitions.
• All partitions, except the first, have an implicit lower bound specified
by the VALUES LESS THAN clause on the previous partition
• A MAXVALUE literal can be defined for the highest partition.
MAXVALUE represents a virtual infinite value
Range Partitioning
Partitioning Method
create table order_details
(order_id number,
Partitioning Column (Key)
order_date date)
partition by range (order_date)
(partition p_jan values less than (to_date('01-FEB-2009','DD-MONYYYY')),
partition p_feb values less than (to_date('01-MAR-2009','DD-MONYYYY')),
partition p_mar values less than (to_date('01-APR-2009','DD-MONYYYY')),
partition p_2009 values less than (MAXVALUE)
)
;
Partition descriptions identifying partition bounds
Hash Partitioning
• Introduced in Oracle 8i.
• Enables partitioning of data that does not lend itself to
either range or list partitioning
• As a better alternative to range partitioning when:
We do not know beforehand how much data maps to a
particular range.
The size of range partitions would differ substantially.
Range partitioning would cause the data to be undesirably
clustered.
Hash Partitioning
• Hash function applied to the partitioning key column to place row in
required partition.
• Balances the data distribution between all partitions.
• Is an effective means of distributing data, because Oracle hashes
the data into a number of partitions, each of which can reside on a
separate device.
• Hash Partitioning enables the use of performance features like
Partition-wise joins when two tables are hash partitioned on the join
key.
Hash Partitioning
• Not suitable for purging and archiving data models.
• Partition pruning is limited to using equality or IN-list predicates.
• User has no control of the row to partition mapping.
• Partition maintenance tasks like splitting, dropping and merging
cannot be carried out.
• Partitions can only be added and coalesced.
CREATE TABLE employees (
empno NUMBER(4),
ename VARCHAR2(30),
sal
NUMBER
)
PARTITION BY HASH (empno) (
PARTITION h1 TABLESPACE t1,
PARTITION h2 TABLESPACE t2,
PARTITION h3 TABLESPACE t3,
PARTITION h4 TABLESPACE t4
);
CREATE TABLE employees (
empno NUMBER(4),
ename VARCHAR2(30),
sal
NUMBER
)
PARTITION BY HASH(empno)
PARTITIONS 3
STORE IN (t1,t2,t3)
;
List Partitioning
• Introduced in Oracle 9i.
• List Partitioning is useful for data that has discrete or distinct values.
• Enables to group and organize unordered and unrelated sets of
data.
• Gives data warehouse administrators precise control over which
data belongs in each partition.
• Enables the partitioning strategy to closely model underlying
business processes.
• Unlike range and hash partitioning, multicolumn partition keys are
not supported for list partitioning.
CREATE TABLE sales_list
(salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES ('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES ('Texas', 'Illinois'),
PARTITION sales_other VALUES (DEFAULT)
);
11g Interval partitioning
• Pre 11g new partitions must be created in advance for new data.
• Additional partitioning management overhead.
• 11g interval partitioning automates partition management.
• Extension of range partitioning.
• Automatic creation of range partitions based on interval.
• Segments are allocated as soon as new data arrives.
• Local indexes are created and maintained as well
CREATE TABLE order_details
(order_id NUMBER,
order_date DATE)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P_FIRST VALUES LESS THAN ('01-JAN-2009'))
;
SQL>
select partition_name from user_tab_partitions
where table_name='ORDER_DETAILS';
PARTITION_NAME
-----------------------------P_FIRST
SQL>
insert into order_details
values
(10001,'15-JAN-2009');
1 row created.
SQL> commit;
Commit complete.
SQL> select partition_name from user_tab_partitions
where table_name='ORDER_DETAILS';
PARTITION_NAME
-----------------------------P_FIRST
SYS_P101
REF Partitioning
• Related tables benefit from the same partitioning strategy.
Example:Orders and Line Items table
• Redundant storage of the same data solves the problem.
But Data and maintenance overhead …
• Oracle 11g introduces REF partitioning
Child table inherits the same partitioning strategy as the parent table
via PK-FK relationships.
Enhanced performance as well as manageability.
Partition maintenance operations on parent table cascade to child
table.
Before REF Partitioning
Table ORDERS
…
RANGE
(order_date)
…
PRIMARY KEY
(order_id)
Jan 2009
Feb 2009
Dec 2009
Redundant storage of order_date
Table LINEITEMS
RANGE
(order_date)
…
…
FOREIGN KEY
(order_id)
Jan 2009
Feb 2009
Dec 2009
11g REF Partitioning
Table ORDERS
…
…
RANGE
(order_date)
PRIMARY KEY
(order_id)
Jan 2009
Feb 2009
Partition By Reference
Partitioning Key in Child Table
Inherited through PK-FK
relationship
Table LINEITEMS
…
…
RANGE
(order_date)
FOREIGN KEY
(order_id)
Jan 2009
Feb 2009
CREATE TABLE mycustomers
(cust_id NUMBER,
cust_first_name VARCHAR2(20),
cust_last_name VARCHAR2(20),
cust_gender CHAR(1))
PARTITION BY LIST (cust_gender)
(PARTITION p_male VALUES ('M'),
PARTITION p_female VALUES ('F')
);
SQL> ALTER TABLE mycustomers ADD CONSTRAINT p_cust_id PRIMARY KEY (cust_id);
Table altered.
CREATE TABLE mysales
(cust_id NUMBER NOT NULL, quantity_sold NUMBER(10,2),
amount_sold
NUMBER(10,2),
CONSTRAINT fk_sales_01
FOREIGN KEY (cust_id)
REFERENCES mycustomers(cust_id))
PARTITION BY REFERENCE (fk_sales_01);
SQL>
SELECT TABLE_NAME, PARTITIONING_TYPE,
REF_PTN_CONSTRAINT_NAME FROM USER_PART_TABLES WHERE
TABLE_NAME IN ('MYCUSTOMERS','MYSALES');
TABLE_NAME
-----------------------------MYCUSTOMERS
MYSALES
PARTITION REF_PTN_CONSTRAINT_NAME
--------- -------------------------LIST
REFERENCE FK_SALES_01
Extended Composite Partitioning
Data is partitioned along two dimesions
Introduced in Oracle 8i with Range/Hash
9i extended to Range/List
11g extended to all combinations
Range
List
Hash
Range
11g
9i
8i
List
11g
11g
11g
Range/Range
List/Range
List/List
Order Date, Shipping Date
Salesman, Date of Sale
State, County
Range-Range Partitioning
Ship_date
…
Jan 08
Feb 08
…
…
…
…
…
…
Dec 08
…
Jan 08
…
Feb 08
Order_date
…
Mar 08
…
Dec 08
11g Virtual Column Partitioning
• Virtual columns introduced in Oracle 11g.
• Virtual columns using functions and expressions.
• Virtual column not stored physically.
• Partition data as per business rules and requirements – not just
based on application requirements.
• Treated as real columns – only DML not allowed.
• Enhanced performance and manageability
CREATE TABLE emp_year_sal
(ename VARCHAR2(20),
sal NUMBER,
yearly_sal AS (sal*12) VIRTUAL)
PARTITION BY RANGE (yearly_sal)
(PARTITION low_sal VALUES LESS THAN (20000),
PARTITION mid_sal VALUES LESS THAN (40000),
PARTITION high_sal VALUES LESS THAN (60000),
PARTITION others VALUES LESS THAN (MAXVALUE));
SQL> SELECT ename,sal,yearly_sal FROM
emp_year_sal;
ENAME
SAL YEARLY_SAL
---------- ---------- ---------SMITH
800
9600
ALLEN
1600
19200
WARD
1250
15000
JONES
2975
35700
MARTIN
1250
15000
BLAKE
2850
34200
CLARK
2450
29400
SCOTT
3000
36000
SQL> SELECT ename,sal,yearly_sal
(low_sal);
FROM emp_year_sal PARTITION
ENAME
SAL YEARLY_SAL
-------------------- ---------- ---------SMITH
800
9600
ALLEN
1600
19200
WARD
1250
15000
MARTIN
1250
15000
TURNER
1500
18000
ADAMS
1100
13200
SQL> SELECT ename,sal,yearly_sal FROM emp_year_sal
PARTITION(mid_sal);
ENAME
SAL YEARLY_SAL
-------------------- ---------- ---------JONES
2975
35700
BLAKE
2850
34200
CLARK
2450
29400
SCOTT
3000
36000
FORD
3000
36000
10g Partitioning - Summary
Partitioning Strategy Data Distribution
Sample Business
Usage
Range Partitioning
Based on consecutive ranges
of values
Orders table range
partitioned by order_date
List Partitioning
Based on unordered lists of
values.
Orders table list partitioned
by country
Hash Partitioning
Based on a hash algorithm.
Orders table hash partitioned
by customer_id
Composite Partitioning
•
Range-Range
•
Range-List
•
Range-Hash
•
List-List
•
List-Range
•
List-Hash
Based on a combination of
two of the above-mentioned
basic
techniques of Range, List,
Hash, and Interval Partitioning
•Orders table is range
partitioned by order_date
and sub-partitioned by hash
on customer_id
•Orders table is range
partitioned by order_date
and sub-partitioned by range
on shipment_date
11g Partitioning - Summary
Partitioning Extension
Partitioning Key
Sample Business Usage
Interval Partitioning
• Interval
•Interval-Range
• Interval-List
• Interval-Hash
An extension to Range
Partition. Defined by an
interval, providing equi-width
ranges. With the exception of
the first partition all partitions
are automatically created
ondemand
when matching data
arrives.
Orders table partitioned by
order_date with a predefined
daily interval, starting with
'01-Jan-2007'
REF Partitioning
Partitioning for a child table is
inherited from the parent table
through a primary key –
foreign key relationship. The
partitioning keys are not stored
in actual columns in the child
table.
(Parent) Orders table range
partitioned by order_date
and inherits the partitioning
technique to (child) order
lines table. Column
order_date is only present in
the parent orders table
Virtual column based
Partitioning
Defined by one of the
abovementioned
partition techniques
and the partitioning key is
based on a virtual column.
Virtual columns are not stored
on disk and only exist as
metadata.
Orders table has a virtual
column that derives the sales
region based on the first
three digits of the customer
account number. The orders
table is then list partitioned
by sales region.
Partition Data Dictionary Views
•
•
•
•
•
•
•
DBA_PART_TABLES
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_PART_KEY_COLUMNS
DBA_PART_HISTOGRAMS
DBA_PART_INDEXES
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
Working with Partitions
SQL> select order_date from order_details partition(p_jan);
SQL> select count(*) from SALES_DATA_COMP
subpartition(SALES_2000_SP2);
$ exp system/manager TABLES=(order_details:p_jan)
$ exp system/manager TABLES=(order_details:p_jan,
order_details:p_jan_subpart1)
Local and Global Indexes
LOCAL INDEX
Index partition
equipartitioned
with table
Single index partition
only contains rows from
corresponding table partition
GLOBAL INDEX
Index partition can
contain rows from
several table
partitions
LOCAL Partitioned Index
• Equi-partitioned – each partition of local index exactly associated
with corresponding partition of the table.
• Cannot explicitly add or drop local index partitions – partitions to the
index are added or dropped based on partitions being added or
dropped from base table.
• Provide higher availability and ease of maintenance.
• Partition maintenance operations on base table will only affect
corresponding local index partition – other partitions of the index are
not affected improving availability.
• Most suited for DSS environments - easier to manage during data
loads and during partition-maintenance operations
SQL> select partition_name from user_tab_partitions where
table_name='ORDER_DETAILS';
PARTITION_NAME
--------------P_FIRST
SYS_P81
SYS_P82
SQL>
create index order_det_ind_local on order_details
(order_date)
LOCAL
<< NO PARTITIONING KEY DEFINED
(partition p1_ind tablespace users,
partition p2_ind tablespace example);
create index order_det_ind_local on order_details (order_date)
*
ERROR at line 1:
ORA-14024: number of partitions of LOCAL index must equal that of
the underlying table
SQL>
create index order_det_ind_local on order_details
(order_date)
LOCAL
tablespace example;
Index created.
SQL>
select partition_name,tablespace_name from
user_ind_partitions where
index_name='ORDER_DET_IND_LOCAL';
PARTITION_NAME
--------------P_FIRST
SYS_P102
SYS_P103
TABLESPACE_NAME
-----------------------------EXAMPLE
EXAMPLE
EXAMPLE
Global Partitioned Index
• Index partitioning key is independent of the table partitioning
method.
• Better suited for OLTP environments than local indexes.
• Better performance as they minimise the number of index partition
probes.
• Lower availability than local indexes as partition maintenance
operations can affect all the index partitions.
Global Partitioned Indexes
• Highest partition of the global index needs to have a MAXVALUE
clause to ensure all rows of the underlying table are represented –
this partition cannot be dropped.
• Can be created as a global hash or global range partitioned index.
• Can enable partition pruning to take place at the index level even if
not possible on the underlying partitioned table
CREATE INDEX order_id_ind_global
ON order_details (order_id)
Table Partitioned
GLOBAL PARTITION BY RANGE (order_id)
on order_date
(PARTITION p_ind1 values less than (100001),
PARTITION p_ind2 values less than (200001),
PARTITION p_ind3 values less than (300001)); PARTITION p_ind3
values less than (300001))
*
ERROR at line 6:
ORA-14021: MAXVALUE must be specified for all columns
CREATE INDEX order_id_ind_global
ON order_details (order_id)
GLOBAL PARTITION BY RANGE (order_id)
(PARTITION p_ind1 values less than (100001),
PARTITION p_ind2 values less than (200001),
PARTITION p_ind3 values less than (300001),
PARTITION p_ind_others values less than (MAXVALUE));
Partition Maintenance
Operations
• Add
• Coalesce
• Drop
• Truncate
• Split
• Exchange
• Move
• Rename
• Merge
….
Consider the effect of these operations on Index partitions …..
Partition Maintenance
ALTER TABLE sales ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB1999' ) TABLESPACE tsx;
ALTER TABLE scubagear ADD PARTITION p_named TABLESPACE gear5;
ALTER TABLE parts MOVE PARTITION depot2 TABLESPACE ts094
NOLOGGING COMPRESS;
ALTER TABLE order_details
SPLIT PARTITION p_2009 AT (TO_DATE ('01-JUL-2009','DD-MON-YYYY'))
INTO (PARTITION p_2009h1, PARTITION p_2009h2);
ALTER TABLE four_seasons MERGE PARTITIONS quarter_one,
quarter_two INTO PARTITION quarter_two ;
Index Maintenance
• Indexes in UNUSABLE state is one of the major issues in dealing
with partitioned tables and indexes.
• SELECT or DML statement that accesses index in such state will
return an ORA-01502 error.
• Partition maintenance operations will mark the affected local index
partition and ALL global index partitions as UNUSABLE.
ALTER TABLE MOVE PARTITION
ALTER TABLE SPLIT PARTITION
ALTER TABLE TRUNCATE PARTITION
ALTER INDEX SPLIT PARTITION
• SQL*Loader operations which bypass index maintenance
SQL> SELECT PARTITION_NAME FROM USER_IND_PARTITIONS
WHERE INDEX_NAME='SALES_DATA_IND';
PARTITION_NAME
-----------------------------SALES_1998
SALES_1999
SALES_2000
SALES_2001
P_2009
LOCAL Index
SQL> ALTER TABLE sales_data MOVE PARTITION sales_1999 TABLESPACE
users;
Table altered.
SQL> SELECT PARTITION_NAME,STATUS FROM USER_IND_PARTITIONS WHERE
INDEX_NAME='SALES_DATA_IND';
PARTITION_NAME
-----------------------------SALES_1998
SALES_1999
SALES_2000
SALES_2001
P_2009
STATUS
-------USABLE
UNUSABLE
USABLE
USABLE
USABLE
SQL> ALTER TABLE sales_data TRUNCATE PARTITION sales_1999_h2;
Table truncated.
SQL> select partition_name,status from user_ind_partitions where
index_name='PROD_ID_IND';
PARTITION_NAME
-----------------------------P1
P2
P_OTHERS
STATUS
-------UNUSABLE
UNUSABLE
UNUSABLE
ALL Global Index Partitions are marked as UNUSABLE
even though only one single table partition has been accessed
SQL> SELECT COUNT (*) FROM sales_data
WHERE time_id ='01-DEC-1999'
*
ERROR at line 1:
ORA-01502: index 'SH.SALES_DATA_IND' or partition of such index
is in unusable state
SQL> ALTER SESSION
SET SKIP_UNUSABLE_INDEXES=TRUE;
System altered.
SQL> SELECT COUNT (*) FROM sales_data
WHERE time_id ='01-DEC-1999‘
COUNT(*)
---------310
SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM sales_data
WHERE time_id ='01-DEC-1999';
Explained.
SQL> SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------Plan hash value: 1021418022
-----------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
9 |
342 (26)| 00:00:05 |
|
|
|
1 | SORT AGGREGATE
|
|
1 |
9 |
|
|
|
|
|
2 |
PARTITION RANGE SINGLE|
|
276 | 2484 |
342 (26)| 00:00:05 |
2 |
2 |
|* 3 |
TABLE ACCESS FULL
| SALES_DATA |
276 | 2484 |
342 (26)| 00:00:05 |
2 |
2 |
------------------------------------------------------------------------------------------------------
Because index partition is in an UNUSABLE state, a full table scan is being
performed of the SALES_DATA table
SQL> ALTER INDEX sales_data_ind REBUILD PARTITION sales_1999;
Index altered.
SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM
='01-DEC-1999';
sales_data
WHERE time_id
Explained.
SQL> SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------Plan hash value: 3608419564
---------------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
9 |
5
(0)| 00:00:01 |
|
|
|
1 | SORT AGGREGATE
|
|
1 |
9 |
|
|
|
|
|
2 |
PARTITION RANGE SINGLE|
|
310 | 2790 |
5
(0)| 00:00:01 |
2 |
2 |
|* 3 |
INDEX RANGE SCAN
| SALES_DATA_IND |
310 | 2790 |
5
(0)| 00:00:01 |
2 |
2 |
----------------------------------------------------------------------------------------------------------
Update Global Indexes
•
By default, many table maintenance operations on partitioned tables
invalidate (mark UNUSABLE) global indexes.
•
We can override this default behaviour if you specify UPDATE GLOBAL
INDEXES.
•
Partition DDL statement takes longer to execute since indexes which were
previously marked UNUSABLE are updated
SQL> ALTER TABLE sales_data move partition sales_2000 tablespace
example UPDATE GLOBAL INDEXES;
SQL> SELECT PARTITION_NAME,STATUS FROM USER_IND_PARTITIONS WHERE
INDEX_NAME='PROD_ID_IND';
PARTITION_NAME
STATUS
------------------------------ -------P1
USABLE
P2
USABLE
P_OTHERS
USABLE
Partition Pruning
• Very important feature for VLDB and Data Warehouses.
• CBO eliminates unneeded partitions when building a partition
access list.
• Operations performed only on partitions relevant to the SQL
statement dramatically reduce the amount of disk reads as well as
CPU time.
• If using global partitioned indexes, can perform partition pruning on
the index partitions by eliminating index partitions even if table
partitions cannot be eliminated
• Range Partitioning
– range, equality and IN-list predicates
• Hash Partitioning
– equality and IN-list predicates
SQL> EXPLAIN PLAN FOR SELECT COUNT(*) FROM
time_id='21-JAN-2000';
sales_data WHERE
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------Plan hash value: 1021418022
-----------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |
8 |
246
(3)| 00:00:03 |
|
|
|
1 | SORT AGGREGATE
|
|
1 |
8 |
|
|
|
|
|
2 |
PARTITION RANGE SINGLE|
|
468 | 3744 |
246
(3)| 00:00:03 |
5 |
5 |
|* 3 |
TABLE ACCESS FULL
| SALES_DATA |
468 | 3744 |
246
(3)| 00:00:03 |
5 |
5 |
------------------------------------------------------------------------------------------------------
The Pstart and Pstop columns indicate that a single partition has been accessed
by the optimizer even though the “TABLE ACCESS FULL” operation is indicated
Partition-wise Joins
• Significantly improve the performance when joining tables with
millions of rows.
• Useful in VLDB and DSS environments.
• Applies to Merge and Hash joins and not to Nested Loop joins.
• Two tables that are equi-partitioned on the join column.
• Optimizer breaks the join operation into a number of smaller joins
that can be performed sequentially or in parallel.
• If using parallel joins, will minimise the data exchanged by parallel
slaves
CREATE TABLE "SH"."SALES_DATA_HASH"
(
"PROD_ID" NUMBER NOT NULL ENABLE,
"CUST_ID" NUMBER NOT NULL ENABLE,
"TIME_ID" DATE NOT NULL ENABLE,
"CHANNEL_ID" NUMBER NOT NULL ENABLE,
"PROMO_ID" NUMBER NOT NULL ENABLE,
"QUANTITY_SOLD" NUMBER(10,2) NOT NULL ENABLE,
"AMOUNT_SOLD" NUMBER(10,2) NOT NULL ENABLE
)
PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
PARTITION BY HASH ("CUST_ID")
PARTITIONS 4
STORE IN (EXAMPLE, USERS)
;
NOLOGGING
CREATE TABLE SH.CUSTOMERS_HASH
(CUST_ID NUMBER,
CUST_FIRST_NAME VARCHAR2(20),
CUST_LAST_NAME VARCHAR2(40),
CUST_CITY VARCHAR2(30))
PARTITION BY HASH (CUST_ID)
PARTITIONS 4
STORE IN (EXAMPLE, USERS)
;
Both tables are hash partitioned on the CUST_ID column
SQL> EXPLAIN PLAN FOR
SELECT SUM (a.amount_sold),b.cust_city
FROM sales_data_hash a, customers_hash b
WHERE a.cust_id =b.cust_id
GROUP BY b.cust_city;
Explained.
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------Plan hash value: 4232629991
-------------------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
| Pstart| Pstop |
-------------------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
839K|
44M| 1158 (10)| 00:00:14 |
|
|
|
1 | HASH GROUP BY
|
|
839K|
44M| 1158 (10)| 00:00:14 |
|
|
|
2 |
PARTITION HASH ALL |
|
839K|
44M| 1085
(4)| 00:00:14 |
1 |
4 |
|* 3 |
HASH JOIN
|
|
839K|
44M| 1085
(4)| 00:00:14 |
|
|
|
4 |
TABLE ACCESS FULL| CUSTOMERS_HASH | 62069 | 1818K|
61
(2)| 00:00:01 |
1 |
4 |
|
5 |
TABLE ACCESS FULL| SALES_DATA_HASH |
839K|
20M| 1012
(3)| 00:00:13 |
1 |
4 |
Statistics
---------------------------------------------------------7 recursive calls
0 db block gets
4794 consistent gets
296 physical reads
0 redo size
18197 bytes sent via SQL*Net to client
932 bytes received via SQL*Net from client
42 SQL*Net roundtrips to/from client
2 sorts (memory)
Note the physical reads and
0 sorts (disk)
consistent
gets using the Partition wise join on Hash
Partitioned versus Non Partitioned tables
Statistics
-------------------------------------------7 recursive calls
0 db block gets
6039 consistent gets
4100 physical reads
0 redo size
18197 bytes sent via SQL*Net to client
932 bytes received via SQL*Net from client
42 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
608 rows processed
Using DBMS_REDEFINITION
SQL> EXEC
DBMS_REDEFINITION.CAN_REDEF_TABLE('SH','SALES_NO_PART');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
CREATE TABLE "SH"."SALES_INTERIM"
(
"PROD_ID" NUMBER NOT NULL ENABLE,
...
) PCTFREE 5 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
TABLESPACE "EXAMPLE"
PARTITION BY RANGE ("TIME_ID")
(PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN1999','DD-MON-YYYY')),
...
...
PARTITION SALES_2001 VALUES LESS THAN (TO_DATE('01-JAN-2002','DDMON-YYYY')),
PARTITION P_2009 VALUES LESS THAN (MAXVALUE)
);
DECLARE
ERROR_COUNT PLS_INTEGER := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SH', 'SALES_NO_PART',
'SALES_INTERIM',1, TRUE, TRUE, TRUE, FALSE,ERROR_COUNT);
DBMS_OUTPUT.PUT_LINE('ERRORS := ' || TO_CHAR(ERROR_COUNT));
END;
/
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE
('SH','SALES_NO_PART','SALES_INTERIM');
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE
TABLE_NAME='SALES_NO_PART';
PARTITION_NAME
-----------------------------SALES_1998
SALES_1999
SALES_2000
SALES_2001
P_2009
SQL> SELECT COUNT(*) FROM SALES_NO_PART PARTITION(SALES_1999);
COUNT(*)
---------247945
SQL> DROP TABLE SALES_INTERIM;
TABLE DROPPED.
Exchange Partition
SQL> select partition_name from user_tab_partitions where
table_name='SALES_NO_PART';
PARTITION_NAME
-----------------------------SALES_1998
SALES_1999
SALES_2000
SQL> select count(*) from sales_2001; << NON PARTITIONED TABLE
COUNT(*)
---------259418
SQL> alter table sales_no_part add partition sales_2001
2 values less than ('01-JAN-2002') tablespace example;
Table altered.
SQL> select partition_name from user_tab_partitions where
table_name='SALES_NO_PART';
PARTITION_NAME
-----------------------------SALES_1998
SALES_1999
SALES_2000
SALES_2001
SQL>
ALTER TABLE sales_no_part
EXCHANGE PARTITION sales_2001
WITH TABLE sales_2001
UPDATE GLOBAL INDEXES;
Table altered.
SQL> select count(*) from sales_no_part partition(sales_2001);
COUNT(*)
---------259418
THANKS FOR ATTENDING!
Gavin Soorma
[email protected]
Phone: 0417 713 124