Partitioning

Post date: Apr 10, 2020 3:45:48 AM

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a rich variety of partitioning strategies and extensions to address every business requirement.

partitioning can be used with any application, from packaged OLTP applications to data warehouses.

Key Benefits

• Increases performance by only working on the data that is relevant.

• Improves availability through individual partition manageability.

• Decreases costs by storing data in the most appropriate manner.

• Is easy as to implement as it requires no changes to applications and queries.

• Is a mature, well proven feature used by thousands of Oracle customers.

Partitioning Methods

Oracle supports a wide array of partitioning methods:

SELECT TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='INTERVAL_PAR_DEMO' ORDER BY PARTITION_NAME

• Range Partitioning - the data is distributed based on a range of values.

create table interval_par_demo ( start_date DATE, store_id NUMBER, inventory_id NUMBER(6), qty_sold NUMBER(3) ) PARTITION BY RANGE (start_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION interval_par_demo_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')), PARTITION interval_par_demo_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY')) )

• List Partitioning -The data distribution is defined by a discrete list of values. One or multiple columns can be used as partition key.

• Auto-List Partitioning - Extends the capabilities of the list method by automatically defining new partitions for any new partition key values.

CREATE TABLE sales_auto_list ( salesman_id NUMBER(5) NOT NULL, salesman_name VARCHAR2(30), sales_state VARCHAR2(20) NOT NULL, sales_amount NUMBER(10), sales_date DATE NOT NULL ) PARTITION BY LIST (sales_state) AUTOMATIC (PARTITION P_CAL VALUES ('CALIFORNIA') )

• Hash Partitioning - An internal hash algorithm is applied to the partitioning key to determine the partition.

Creating a composite interval-hash partitioned table

CREATE TABLE sales_interval_hash      ( prod_id       NUMBER(6)       , cust_id       NUMBER       , time_id       DATE       , amount_sold   NUMBER(10,2)       )      PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 4      (PARTITION before_2016 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy'))      )

• Composite Partitioning - Combinations of two data distribution methods are used. First, the table is partitioned by data distribution method one and then each partition is further subdivided into subpartitions using the second data distribution method.

Create the QUARTERLY_REGIONAL_SALES table

CREATE TABLE quarterly_regional_sales (      product_id       NUMBER(6),      sale_date        DATE,      state_code       VARCHAR(2)  )    PARTITION BY RANGE (sale_date)   SUBPARTITION BY LIST (state_code)    (PARTITION sales_q1_2014 VALUES LESS THAN (TO_DATE('01-APR-2014','dd-MON-yyyy'))            (SUBPARTITION sales_q1_region_east_2014                VALUES ('CT','MA','MD','ME','NH','NJ','NY','PA','VA'),             SUBPARTITION sales_q1_region_west_2014               VALUES ('AZ','CA','CO','NM','NV','OR','UT','WA'),             SUBPARTITION                VALUES (DEFAULT)            ),     PARTITION sales_q2_2014 VALUES LESS THAN (TO_DATE('01-JUL-2014','dd-MON-yyyy'))            (SUBPARTITION sales_q2_region_east_2014                VALUES ('CT','MA','MD','ME','NH','NJ','NY','PA','VA'),             SUBPARTITION sales_q2_region_west_2014               VALUES ('AZ','CA','CO','NM','NV','OR','UT','WA'),             SUBPARTITION                VALUES (DEFAULT)            ))

• Multi-Column Range Partitioning - An option for when the partitioning key is composed of several columns and subsequent columns define a higher level of granularity than the preceding ones.

• Interval Partitioning - Extends the capabilites of the range method by automatically defining equi-partitioned ranges for any future partitions using an interval definition as part of the table metadata.

create table interval_par_demo ( start_date DATE, store_id NUMBER, inventory_id NUMBER(6), qty_sold NUMBER(3) ) PARTITION BY RANGE (start_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION interval_par_demo_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')), PARTITION interval_par_demo_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY')) )

• Reference Partitioning Partitions - A table by leveraging an existing parent-child relationship. The primary key relationship is used to inherit the partitioning strategy of the parent table to its child table.

• Virtual Column Based Partitioning - Allows the partitioning key to be an expression, using one or more existing columns of a table, and storing the expression as metadata only.

• Interval Reference Partitioning - An extension to reference partitioning that allows the use of interval partitioned tables as parent tables for reference partitioning.