Introduction to range partitions of Oracle partition tables

In the oracle10g world, partition tables are mainly divided into five types: range, hash, list, range-hash, and range-list. In oracle11g, partition combinations are developed to 3*3.

In the oracle 10g world, partition tables are mainly divided into five types: range, hash, list, range-hash, and range-list. In oracle 11g, partition combinations are developed to 3*3.

In the Oracle 10g world, partition tables are divided into five types: range, hash, list, range-hash, and range-list. In oracle 11g, it has evolved to the 3*3 partition combination type to meet more application scenarios! However, no matter under what circumstances, range partitioning is the most common method of Table Partitioning, especially when you need to organize and archive expired data and only retain data for a certain period of time, the range partitioning method is preferred! Partitioned Tables are a beneficial technology. When the data volume reaches a certain level (usually after GB), even if the ASM technology is used, serious I/O wait events will occur in the same database!

The following describes the main advantages of range partitioning:
1: Partitioned Tables can store tables in multiple tablespaces and then separate I/O;
2: At the same time, each partition maintains its own local indexes (generally using local indexes instead of global indexes );
3: The select statement can scan the partition range based on the index to reduce consistent reads caused by the query statement;
4: You can back up or truncate a single partition, archive or clear expired data;
5: You can easily add, delete, truncate, split, and MERGE table partitions.

1. Create a partition table. The partition conditions are defined by the sales date. At the same time, the index of the partition is a local index. Each partition corresponds to a separate tablespace, based on discrete I/O and convenient management

SQL> create table sale_data
2 (sale_id number (5), salesman_name varchar2 (30), sales_date date)
3 partition by range (sales_date)
4 (
5 partition sales_01 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale01,
6 partition sales_02 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale02,
7 partition sales_03 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale03,
8 partition sales_04 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale04,
9 partition sales_05 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale05,
10 partition sales_06 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale06,
11 partition sales_07 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale07,
12 partition sales_08 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale08,
13 partition sales_09 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale09,
14 partition sales_10 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale10,
15 partition sales_11 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale11,
16 * partition sales_12 values less than (to_date ('1970/123', 'dd/MM/YYYY ') tablespace tbs_sale12)
Table created.

SQL> select owner, partitioning_type, partition_count, status from dba_part_tables where table_name = 'sale _ date ';

Owner partiti PARTITION_COUNT STATUS
------------------------------------------------------------
Sale range 12 VALID

SQL> create index ind_sale_data_date on sale_data (sale_id) local
2 (
3 partition sales_01 tablespace tbs_sale01,
4 partition sales_02 tablespace tbs_sale02,
5 partition sales_03 tablespace tbs_sale03,
6 partition sales_04 tablespace tbs_sale04,
7 partition sales_05 tablespace tbs_sale05,
8 partition sales_06 tablespace tbs_sale06,
9 partition sales_07 tablespace tbs_sale07,
10 partition sales_08 tablespace tbs_sale08,
11 partition sales_09 tablespace tbs_sale09,
12 partition sales_10 tablespace tbs_sale10,
13 partition sales_11 tablespace tbs_sale11,
14 * partition sales_12 tablespace tbs_sale12)
Index created.

SQL> select segment_name, partition_name, tablespace_name from user_segments where segment_name in ('sale _ data', 'ind _ SALE_DATA_DATE ');

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------------------------
SALE_DATA SALES_01 TBS_SALE01
SALE_DATA SALES_02 TBS_SALE02
SALE_DATA SALES_03 TBS_SALE03
SALE_DATA SALES_04 TBS_SALE04
SALE_DATA SALES_05 TBS_SALE05
SALE_DATA SALES_06 TBS_SALE06
SALE_DATA SALES_07 TBS_SALE07
SALE_DATA SALES_08 TBS_SALE08
SALE_DATA SALES_09 TBS_SALE09
SALE_DATA SALES_10 TBS_SALE10
SALE_DATA SALES_11 TBS_SALE11

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------------------------
SALE_DATA SALES_12 TBS_SALE12
IND_SALE_DATA_DATE SALES_01 TBS_SALE01
IND_SALE_DATA_DATE SALES_02 TBS_SALE02
IND_SALE_DATA_DATE SALES_03 TBS_SALE03
IND_SALE_DATA_DATE SALES_04 TBS_SALE04
IND_SALE_DATA_DATE SALES_05 TBS_SALE05
IND_SALE_DATA_DATE SALES_06 TBS_SALE06
IND_SALE_DATA_DATE SALES_07 TBS_SALE07
IND_SALE_DATA_DATE SALES_08 TBS_SALE08
IND_SALE_DATA_DATE SALES_09 TBS_SALE09
IND_SALE_DATA_DATE SALES_10 TBS_SALE10

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------------------------
IND_SALE_DATA_DATE SALES_11 TBS_SALE11
IND_SALE_DATA_DATE SALES_12 TBS_SALE12