database table Partitioning Instance summary

What is a database table partition

A database table partition (partitioning) that splits a smaller portion of a large data table (table) and its index (index). These partitions can have different names, even storage methods.

Why do you partition

• Storage equalization: You can allocate large amounts of data to different storage media.
• Ease of management: Easy DBA management of data tables for various operations, such as deleting stale data.
• Direct location Query fast: According to the partition policy query data, you can directly navigate to the target partition, reduce the query time.
• Parallel queries improve efficiency: When doing aggregate queries, multiple partitions (disks) of parallel queries can improve efficiency.

How to do partitioning

Partitioning policy

• Range: A section rule is a continuous interval.
• List: The partition rule is a list.
• Hash: A given number of partitions, evenly distributing elements to each partition.

Partitioning method

• Single-level Partitioning (single-level partitioning): Only one level of partitioning.
• Composite Partitioning (Multilevel partitioning): You can have multiple layers of partitions, that is, partitioning (sub-partition) on top of the partition.

Partition instance

Range Partitioning

CREATE TABLESales_range (salesman_id Number(5), Salesman_nameVARCHAR2( -), Sales_Amount Number(Ten), Sales_dateDATE)PARTITION  by RANGE(sales_date) (PARTITIONsales_jan2000VALUES  Less THAN(to_date(' 02/01/2000 ',' mm/dd/yyyy ')),PARTITIONsales_feb2000VALUES  Less THAN(to_date(' 03/01/2000 ',' mm/dd/yyyy ')),PARTITIONsales_mar2000VALUES  Less THAN(to_date(' 04/01/2000 ',' mm/dd/yyyy ')),PARTITIONsales_apr2000VALUES  Less THAN(to_date(' 05/01/2000 ',' mm/dd/yyyy ')));

List Partitioning

CREATE TABLESales_list (salesman_id Number(5), Salesman_nameVARCHAR2( -), Sales_stateVARCHAR2( -), Sales_Amount Number(Ten), Sales_dateDATE)PARTITION  by LIST(sales_state) (PARTITIONSales_westVALUES(' California ',' Hawaii '),PARTITIONSales_eastVALUES(' New York ',' Virginia ',' Florida '),PARTITIONSales_centralVALUES(' Texas ',' Illinois '),PARTITIONSales_otherVALUES(DEFAULT));

Hash Partitioning

CREATETABLE sales_hash(salesman_id  NUMBER(5VARCHAR2(30), sales_amount  NUMBER(10), week_no       NUMBER(2PARTITIONBYHASHPARTITIONS4STOREIN (ts1, ts2, ts3, ts4);

Delete Partition

To delete a partition, you can use the following sql:

ALTERTABLEDROPPARTITION partition-name;

Further, you can write a function to delete the data table stale data, as follows:

--Delete the partition older than input_days days (exclusive) in Input_tableCreate or Replace procedureDo_delete_old_partition (input_tableinch VARCHAR, Input_daysinch  Number) asBEGIN     forPinch(SELECTPartition_name, High_value fromUser_tab_partitionsWHEREtable_name = input_table)LOOP        EXECUTE IMMEDIATE          ' BEGINIF to_date ('|| P.high_value | |',"'YYYYMMDD"') <= (sysdate-'|| Input_days | |') ThenEXECUTE IMMEDIATE                "'ALTER TABLE '|| input_table | |' DROP PARTITION '|| P.partition_name | |'"';END IF;END; ';END LOOP;END;/

Link

• Data table partitioning strategy and implementation
• Oracle Partitioning Overview

database table Partitioning Instance summary