Talk about how to design an Oracle partition table

The concept of partitioning tables and tablespaces is distinguished between the design of Oracle partition tables:

tablespace: A table space is a collection of one or more data files, all of which are stored in the specified table space, but are primarily stored in tables, and are therefore called table spaces.

partition Table : Partitioning is dedicated to resolving key issues that support large tables and indexes. It uses them to break down into smaller and easier-to-manage methods called partitioned slices (piece). Once the partition is defined, the SQL statement can access the operation of one partition rather than the entire table, thus improving management efficiency. Partitioning is very effective for data warehouse applications because they often store and analyze huge amounts of historical data. When the amount of data in the table is increasing, the query data slows down and the performance of the application degrades, so you should consider partitioning the table. After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple table spaces (physical files) so that querying the data does not always scan the entire table.

the specific role of table partitioning :

Oracle's partitioned table capabilities bring great benefits to a wide variety of applications by improving manageability, performance, and availability.

In general, partitioning can greatly improve the performance of certain queries and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building gigabytes of data systems or ultra-high availability systems.

The partitioning feature can further subdivide a table, index, or index organization table into segments, where the segments of these database objects are called partitions. Each partition has its own name, and you can choose your own storage features. From the database administrator's point of view, a partitioned object has multiple segments that can be collectively managed or managed separately, which gives the database manager considerable flexibility in managing the objects after the partition. However, from the application's point of view, the partitioned table is exactly the same as the non-partitioned table, and no modifications are required to access the partitioned table using the SQL DML command.

When do you use partitioned tables?

1 , the size of the table exceeds 2GB .

2 , the table contains historical data, and new data is added to the new partition.

Advantages and disadvantages of table partitioning:

Advantages:

1. Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.

2. Enhanced usability: If one partition of the table fails, the data of the table in other partitions is still available;

3, Maintenance Convenience: If a partition of the table fails, you need to repair the data, only repair the partition;

4. Balanced I/O: Different partitions can be mapped to disk to balance I/O and improve overall system performance.

Disadvantages:

1, partition Table Related: Existing table No method can be directly converted into a partitioned table. However, Oracle provides the ability to redefine tables online.

2, the partition is committed to resolving the key issues that support maximum tables and indexes. It uses them to break down into smaller and easier-to-manage methods called partitioned slices (piece).

Classification of partitioned Tables

1. Range partitioning (range partition)
2, hash partitioning (hashed partition)
3. List partitioning (table partition)
4, Composite Range-hash partitioning (range-hash combination partition)
5, Composite range-list Partitioning (range-list combination partition)

when to select a range partition
You must be able to divide a table's records by the range of a column's values. You want to work with some data that often falls within a certain range, such as the month. If the data can be evenly distributed according to the partition's range, it will get the best performance. If the data is unevenly distributed, 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) parti tion by RANGE (sale_year, Sale_month, Sale_day) (PARTITION sales_q1 VALUES less THAN (1999,Geneva, on) tablespace TSA, PARTITION sales_q2 VALUES less THAN (1999, -, on) tablespace TSB, PARTITION sales_q3 VALUES less THAN (1999,Ten, on) tablespace TSC, PARTITION sales_q4 VALUES less THAN ( -, on, on) tablespace TSD);

when to select a hash partition
If the data is not easy to partition with range, but you want to improve performance and ease of management of the table. A hash partition provides a way to cross-distribute data evenly across a specified number of partitions. Rows are mapped to the corresponding partition based on the hash value of the partition key. Create and use hash partitioning you have the flexibility to place data, and you can improve performance by cross-accessing partitions on different I/O devices.

CREATE TABLE scubagear (ID number, name VARCHAR2 (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 disjoint partition key value for a partition. This differs from the range partition (partitioning with the range of key values), and also from the hash partition (you cannot control which partition a row is mapped to).

CREATE TABLE q1_sales_by_region (deptno number, deptname varchar2 ( -), Quarterly_sales number (Ten,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','VMS','NJ'), PARTITION q1_southeast VALUES ('FL','GA'), PARTITION q1_northcentral VALUES ('SD','WI'), PARTITION q1_southcentral VALUES ('OK','TX'));

partition table Design  
How to select the type of partition, how to select the column of the partition? Before that, you have to be clear about your purpose-manageability and performance, and what do you focus more on? The areas affected by the partitioning table can be categorized into the following categories: performance, manageability, data cleansing.  
performance:  
Easy to manage:  
for large tables that contain massive amounts of data, partitioning is very easy to manage. When you suggest an index based on a non-partitioned table, the only option is to create an entire index. If the table is partitioned, you can create an index for this table based on partition parallelism, for example:

In addition, you can do many things at the same time, such as changing table space, exporting tables, deleting table data, and so on.

Delete data:
We often need to delete some historical data from the table, as a general practice is delete, but this will cause the information of undo and redo to grow rapidly and affect the overall performance of the database. At this point we can use a drop partition to accomplish this task, for example:

When the partition of a table is deleted, the corresponding local index is also deleted. If a global index is still present, it becomes unusable state. To prevent this from happening, you can use:

Global