ORACLE Partition Table Design

Partition Table Concept
Partitioning is committed to solving the key issues that support large tables and indexes. It uses a small and easy-to-manage partition-based piece (piece) method. Once a partition is defined, SQL statements can access a partition rather than the entire table, thus improving management efficiency. Partitioning is very effective for Data Warehouse applications because they often store and analyze massive amounts of historical data.

Partition Table Category
Range partitioning)
Hash partitioning (Hash partition)
List partitioning (List partition)
Composite range-hash partitioning (range-hash combined partition)
Composite range-list partitioning (range-list combined partitions)

When to select range partitions
You must divide the table records according to the range of values in a column. You want to process some data, which is often within a certain range, such as month. If the data can be evenly distributed according to the partition range, the optimal performance will be achieved. If the data distribution is uneven, you may have to select another 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 HASH partitions
If data is not easy to use, but you want to improve performance and ease of table management. Hash partitions provide a method to evenly distribute data across a specified number of partitions. The row is mapped to the corresponding partition based on the hash value of the partition key. You can flexibly store data by creating and using hash partitions. You can use cross-Access to partition on different I/O devices to improve performance.
Create table scubagear (id NUMBER, name VARCHAR2 (60 ))
Partition by hash (id)
PARTITIONS 4 store in (gear1, gear2, gear3, gear4 );

When to select list partitions
Using LIST partitions, you can directly control the ing of some data to certain partitions. You can specify a non-consecutive partition key value for a partition. This is different from the RANGE partition (partition by key value RANGE), but also from the HASH partition (cannot control which partition a row is mapped ).
Create table q1_sales_by_region
(Deptno number, deptname varchar2 (20), quarterly_sales number (10, 2), state varchar2 (2 ))
Partition by list (state)
(PARTITION q1_northwest VALUES ('OR', 'wa '),
PARTITION qinisouthwest 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 '));

Partition Table Design
How do I select the partition type and the partition columns? Before that, you must clarify your purpose-ease of management and performance. Which of the following aspects do you pay more attention? The impact of partition tables can be classified into the following types: performance, manageability, and data cleaning.
The following describes the specific impact of a partition table on each item,
Performance:
This is generally the main purpose of partitioning. A partition changes a large table into a small table. When the condition after where reflects the specific value of the partition field, full table scan is avoided.
Easy to manage:
For large tables that contain massive data, the ease of management of partitions is very obvious. When we recommend that you create an index based on a non-partitioned table, the only option is to create the entire index. If a table is partitioned, you can create an index for the table in parallel based on the partition. For example:
Alter index par_ind_01 reuild partition yy05;
In addition, you can also do many things at the same time, such as changing the tablespace where the table is located, exporting the table, and deleting the table data.
Data cleanup:
We often need to delete some historical data of the table. The general practice is delete, but this will cause the rapid growth of undo and redo information and affect the overall performance of the database. In this case, we can use drop a 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 a global index still exists, it will become unusable. To avoid this problem, you can use:
Alter table tab_a drop partition yy01 update global indexes;