Design _oracle of ORACLE partition table

The concept of partitioned tables
Partitions are committed to resolving key issues that support large tables and indexes. It uses the method that they decompose into smaller and manageable slices called partitions (piece). Once a partition is defined, the SQL statement can access one partition rather than the entire table, thereby increasing the efficiency of the administration. Partitioning is very effective for data warehouse applications because they often store and analyze huge amounts of historical data.

Classification of partitioned Tables
Range partitioning (range partition)
Hash partitioning (hash partition)
List partitioning (listing partition)
Composite Range-hash Partitioning (range-hash combination partition)
Composite Range-list Partitioning (range-list combination partition)

when to select a range partition
You must be able to divide the records of a table by the range of a column value. You want to process some data that is often within a certain range, such as the month. The best performance is achieved if the data can be evenly distributed across the partition. If the data distribution is uneven, you may have to choose a different partitioning method.
CREATE TABLE Sales
(Invoice_no number,
Sale_year INT not NULL,
Sale_month INT not NULL,
Sale_day INT not NULL)
PARTITION by RANGE (Sale_year, Sale_month, Sale_day)
(PARTITION sales_q1 VALUES less THAN (1999, 04, 01)
Tablespace TSA,
PARTITION sales_q2 VALUES Less THAN (1999, 07, 01)
Tablespace TSB,
PARTITION sales_q3 VALUES Less THAN (1999, 10, 01)
Tablespace TSC,
PARTITION sales_q4 VALUES Less THAN (2000, 01, 01)
Tablespace TSD);

when to select a hash partition
If the data is not easy to use in range partitioning, you want to improve performance and ease of management of the table. A hash partition provides a way to cross evenly distributed data within a specified number of partitions. The row is mapped to the appropriate partition based on the hash value of the partitioning key. Create and use a hash partition you can place data flexibly, and you can improve performance by crossing the partitions on different I/O devices.
CREATE TABLE scubagear (ID number, name VARCHAR2 (60))
PARTITION by HASH (ID)
Partitions 4 STORE in (Gear1, Gear2, Gear3, GEAR4);

when to select a list partition
Using the list partition you can directly control certain data maps to certain partitions. You can specify a discrete partition key value for a partition. This differs from a range partition (partitioning with a range of key values) and also different from a hash partition (you cannot control which partition a row maps to).
CREATE TABLE q1_sales_by_region
(deptno number, deptname varchar2 (), Quarterly_sales number (2), State VARCHAR2 (2))
PARTITION by LIST (state)
(PARTITION q1_northwest VALUES (' OR ', ' WA '),
PARTITION q1_southwest VALUES (' AZ ', ' UT ', ' NM '),
PARTITION q1_northeast VALUES (' NY ', ' VM ', ' NJ '),
PARTITION q1_southeast VALUES (' FL ', ' GA '),
PARTITION q1_northcentral VALUES (' SD ', ' WI '),
PARTITION q1_southcentral VALUES (' OK ', ' TX '));

Design of partitioned tables
How do I select a partition's type, and how do I select a partition's column? Before that you have to be clear about your purpose--manageability and performance--What do you pay more attention to? The areas affected by partitioned tables can be categorized into the following categories: performance, manageability, data cleansing.
The specific effect of the partitioned table on each item is as follows.
Performance:
This is generally the primary purpose of partitioning. Partitions turn a large table into a small table, avoiding a full table scan when the condition behind the where reflects the specific value of the partition field.
easy to manage:
For large tables that contain massive amounts of data, the ease of management of partitions is obvious. When you suggest an index based on a non-partitioned table, the only option is to create the entire index. If the table is partitioned, you can create indexes for this table in parallel to the partition, for example:
Alter index PAR_IND_01 REUILD partition yy05;
In addition, you can do a lot of things at the same time, such as changing the table table space, export table, delete table data and so on.
Data cleanup:
We often need to delete some of the historical data for the table, which is generally delete, but this results in a rapid increase in the information for undo and redo and affects the overall performance of the database. We can then use a drop partition to complete this task, for example:
ALTER TABLE tab_a drop partition yy01;
When a table partition is deleted, the corresponding local index is also deleted. If there is a global index, it becomes a unusable state. To prevent this from happening, you can use:
ALTER TABLE TAB_A DROP partition YY01 update global indexes;