Partitioning

1
Partitioning Lets Divide and Conquer!

• Gavin Soorma,
• Senior Oracle DBA, Bankwest

2
Agenda

• The what, who and why of Partitioning
• Partitioning decisions and challenges
• Partitioning Its evolution
• Types of Partitioning
• Whats 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

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

4
Partitioning stores a data segment (Table,
Index, LOB) as multiple segments while retaining
a logically massive structure.
Really Big Table
Partition
Partition
Partition
Partition
Partition
Partition
Really Big Table
Partition
Partition
Partition
Partition
Partition
Partition
Partition
Partition
5
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.

6
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
  order-of-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.

7
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 gt 2GB
  (Oracle says so )
• Think about it if table is gt 1 million rows (I
  say so )
• Partitioned tables with non partitioned or
  partitioned index
• Global Index vs Local Index

8
Oracle Partitioning10 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
9
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
10
Composite Partitioning
Range-List Partitioned by date_of_sale then
. Partitioned by sales_region
Range-Hash Partitioned by date_of_sale then
. Partitioned by salesman_id
11
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

12
Range Partitioning
Partitioning Method

• create table order_details
• (order_id number,
• order_date date)
• partition by range (order_date)
• (partition p_jan values less than
  (to_date('01-FEB-2009','DD-MON-YYYY')),
• partition p_feb values less than
  (to_date('01-MAR-2009','DD-MON-YYYY')),
• partition p_mar values less than
  (to_date('01-APR-2009','DD-MON-YYYY')),
• partition p_2009 values less than (MAXVALUE)
• )

Partitioning Column (Key)
Partition descriptions identifying partition
bounds
13
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.

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

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

16

• 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
• )

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

18

• 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)
• )

19
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

20

• 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'))

SQLgt select partition_name from
user_tab_partitions where table_name'ORDER_DETAI
LS' PARTITION_NAME ---------------------------
--- P_FIRST
21

• SQLgt insert into order_details
• values
• (10001,'15-JAN-2009')
• 1 row created.
• SQLgt commit
• Commit complete.
• SQLgt select partition_name from
  user_tab_partitions
• where table_name'ORDER_DETAILS'
• PARTITION_NAME
• ------------------------------
• P_FIRST
• SYS_P101

22
REF Partitioning

• Related tables benefit from the same partitioning
  strategy.
• ExampleOrders 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.

23
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
24
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
25

• 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')
• )
• SQLgt ALTER TABLE mycustomers ADD CONSTRAINT
  p_cust_id PRIMARY KEY (cust_id)
•  
• Table altered.
•  

26

• 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)
• SQLgt SELECT TABLE_NAME, PARTITIONING_TYPE,
• REF_PTN_CONSTRAINT_NAME FROM USER_PART_TABLES
  WHERE
• TABLE_NAME IN ('MYCUSTOMERS','MYSALES')
•  
• TABLE_NAME PARTITION
  REF_PTN_CONSTRAINT_NAME
• ------------------------------ ---------
  --------------------------
• MYCUSTOMERS LIST
• MYSALES REFERENCE
  FK_SALES_01

27
Extended Composite Partitioning
Data is partitioned along two dimesions Introduce
d 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 Order Date, Shipping
Date List/Range Salesman, Date of Sale List/List
State, County
28
Range-Range Partitioning
Ship_date

Jan 08
Feb 08





Dec 08





Jan 08
Feb 08
Mar 08
Dec 08
Order_date
29
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

30

• CREATE TABLE emp_year_sal
• (ename VARCHAR2(20),
• sal NUMBER,
• yearly_sal AS (sal12) 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))

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

• SQLgt SELECT ename,sal,yearly_sal FROM
  emp_year_sal PARTITION (low_sal)
• ENAME SAL YEARLY_SAL
• -------------------- ---------- ----------
• SMITH 800 9600
• ALLEN 1600 19200
• WARD 1250 15000
• MARTIN 1250 15000
• TURNER 1500 18000
• ADAMS 1100 13200
• SQLgt 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

32
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
33
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.
34
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_PARTITIONSDBA_IND_SUBPARTITIONS

35
Working with Partitions

• SQLgt select order_date from order_details
  partition(p_jan)
• SQLgt select count() from SALES_DATA_COMP
  subpartition(SALES_2000_SP2)
• exp system/manager TABLES(order_detailsp_jan)
  
• exp system/manager TABLES(order_detailsp_jan,
  order_detailsp_jan_subpart1)

36
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

37
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

38

• SQLgt select partition_name from
  user_tab_partitions where table_name'ORDER_DETAIL
  S'
• PARTITION_NAME
• ---------------
• P_FIRST
• SYS_P81
• SYS_P82
• SQLgt create index order_det_ind_local on
  order_details (order_date)
• LOCAL ltlt 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

39

• SQLgt create index order_det_ind_local on
  order_details (order_date)
• LOCAL
• tablespace example
• Index created.
• SQLgt select partition_name,tablespace_name from
  user_ind_partitions where index_name'ORDER_DET_
  IND_LOCAL'
• PARTITION_NAME TABLESPACE_NAME
• --------------- ------------------------------
• P_FIRST EXAMPLE
• SYS_P102 EXAMPLE
• SYS_P103 EXAMPLE

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

41
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

42

• 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_ind3 values less than (300001))
• ERROR at line 6
• ORA-14021 MAXVALUE must be specified for all
  columns

Table Partitioned on order_date
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))
43
Partition Maintenance Operations

• Add
• Coalesce
• Drop
• Truncate
• Split
• Exchange
• Move
• Rename
• Merge
• .
• Consider the effect of these operations on Index
  partitions ..

44
Partition Maintenance

• ALTER TABLE sales ADD PARTITION jan96 VALUES LESS
  THAN ( '01-FEB-1999' ) 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

45
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
• SQLLoader operations which bypass index
  maintenance

46

• SQLgt 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
• SQLgt ALTER TABLE sales_data MOVE PARTITION
  sales_1999 TABLESPACE users
• Table altered.
• SQLgt SELECT PARTITION_NAME,STATUS FROM
  USER_IND_PARTITIONS WHERE INDEX_NAME'SALES_DATA_I
  ND'
• PARTITION_NAME STATUS
• ------------------------------ --------

LOCAL Index
47

• SQLgt ALTER TABLE sales_data TRUNCATE PARTITION
  sales_1999_h2
• Table truncated.
• SQLgt select partition_name,status from
  user_ind_partitions where index_name'PROD_ID_IND'
  
• PARTITION_NAME STATUS
• ------------------------------ --------
• P1 UNUSABLE
• P2 UNUSABLE
• P_OTHERS UNUSABLE

ALL Global Index Partitions are marked as
UNUSABLE even though only one single table
partition has been accessed
48

• SQLgt 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
• SQLgt ALTER SESSION SET SKIP_UNUSABLE_INDEXESTRUE
  
• System altered.
• SQLgt SELECT COUNT () FROM sales_data
• WHERE time_id '01-DEC-1999
• COUNT()
• ----------
• 310

49

• SQLgt EXPLAIN PLAN FOR SELECT COUNT() FROM
  sales_data
• WHERE time_id '01-DEC-1999'
• Explained.
• SQLgt 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) 000005
  
• 1 SORT AGGREGATE
  1 9
  
• 2 PARTITION RANGE SINGLE
  276 2484 342 (26) 000005 2
  2
• 3 TABLE ACCESS FULL SALES_DATA
  276 2484 342 (26) 000005 2
  2
• --------------------------------------------------
  --------------------------------------------------
  --

Because index partition is in an UNUSABLE state,
a full table scan is being performed of the
SALES_DATA table
50

• SQLgt ALTER INDEX sales_data_ind REBUILD PARTITION
  sales_1999
• Index altered.
• SQLgt EXPLAIN PLAN FOR SELECT COUNT() FROM
  sales_data WHERE time_id '01-DEC-1999'
• Explained.
• SQLgt 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) 000001
  
• 1 SORT AGGREGATE
  1 9
  
• 2 PARTITION RANGE SINGLE
  310 2790 5 (0) 000001 2
  2

51
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
• SQLgt ALTER TABLE sales_data move partition
  sales_2000 tablespace example UPDATE GLOBAL
  INDEXES
• SQLgt SELECT PARTITION_NAME,STATUS FROM
  USER_IND_PARTITIONS WHERE INDEX_NAME'PROD_ID_IND'
  
• PARTITION_NAME STATUS
• ------------------------------ --------
• P1 USABLE
• P2 USABLE
• P_OTHERS USABLE

52
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

53

• SQLgt EXPLAIN PLAN FOR SELECT COUNT() FROM
  sales_data WHERE time_id'21-JAN-2000'
• Explained.
• SQLgt 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) 000003
  
• 1 SORT AGGREGATE
  1 8
  
• 2 PARTITION RANGE SINGLE
  468 3744 246 (3) 000003 5
  5
• 3 TABLE ACCESS FULL SALES_DATA
  468 3744 246 (3) 000003 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
54
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

55

• 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 NOLOGGING
• PARTITION BY HASH ("CUST_ID")
• PARTITIONS 4
• STORE IN (EXAMPLE, USERS)
• CREATE TABLE SH.CUSTOMERS_HASH
• (CUST_ID NUMBER,
• CUST_FIRST_NAME VARCHAR2(20),

Both tables are hash partitioned on the CUST_ID
column
56

• SQLgt 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) 000014
  
• 1 HASH GROUP BY
  839K 44M 1158 (10) 000014
  
• 2 PARTITION HASH ALL
  839K 44M 1085 (4) 000014 1
  4

57

• Statistics
• --------------------------------------------------
  --------
• 7 recursive calls
• 0 db block gets
• 4794 consistent gets
• 296 physical reads
• 0 redo size
• 18197 bytes sent via SQLNet to client
• 932 bytes received via SQLNet from
  client
• 42 SQLNet roundtrips to/from client
• 2 sorts (memory)
• 0 sorts (disk)
• Statistics
• --------------------------------------------
• 7 recursive calls
• 0 db block gets

Note the physical reads and consistent gets using
the Partition wise join on Hash Partitioned
versus Non Partitioned tables
58
Using DBMS_REDEFINITION

• SQLgt 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-JAN-1999','DD-MON-YYYY')),
• ...
• ...
• PARTITION SALES_2001 VALUES LESS THAN
  (TO_DATE('01-JAN-2002','DD-MON-YYYY')),
• PARTITION P_2009 VALUES LESS THAN (MAXVALUE)
• )

59

• DECLAREERROR_COUNT PLS_INTEGER
  0BEGINDBMS_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 /
• SQLgt EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE
  ('SH','SALES_NO_PART','SALES_INTERIM')
•  
• PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

60

• SQLgt SELECT PARTITION_NAME FROM
  USER_TAB_PARTITIONS WHERE TABLE_NAME'SALES_NO_PAR
  T'
•  
• PARTITION_NAME
• ------------------------------
• SALES_1998
• SALES_1999
• SALES_2000
• SALES_2001
• P_2009
•  
• SQLgt SELECT COUNT() FROM SALES_NO_PART
  PARTITION(SALES_1999)
•  
• COUNT()
• ----------
• 247945
•  
• SQLgt DROP TABLE SALES_INTERIM
• TABLE DROPPED.

61
Exchange Partition

• SQLgt select partition_name from
  user_tab_partitions where table_name'SALES_NO_PAR
  T'
• PARTITION_NAME
• ------------------------------
• SALES_1998
• SALES_1999
• SALES_2000
• SQLgt select count() from sales_2001 ltlt NON
  PARTITIONED TABLE
• COUNT()
• ----------
• 259418
• SQLgt alter table sales_no_part add partition
  sales_2001
• 2 values less than ('01-JAN-2002') tablespace
  example
• Table altered.

62

• SQLgt select partition_name from
  user_tab_partitions where table_name'SALES_NO_PAR
  T'
• PARTITION_NAME
• ------------------------------
• SALES_1998
• SALES_1999
• SALES_2000
• SALES_2001
• SQLgt ALTER TABLE sales_no_part
• EXCHANGE PARTITION sales_2001
• WITH TABLE sales_2001
• UPDATE GLOBAL INDEXES
• Table altered.
• SQLgt select count() from sales_no_part
  partition(sales_2001)

63

• THANKS FOR ATTENDING!
• Gavin Soorma
• gavin.soorma_at_bankwest.com.au
• Phone [phone]