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, so they are 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 manageable 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 the efficiency of management. Partitioning works well 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, just the data in the table is physically stored in multiple table spaces (physical files), so querying the data does not always scan the entire table.

detailed function 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 enables the performance of certain queries and maintenance operations to be greatly improved. 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 an application perspective, partitioned tables are exactly the same as non-partitioned tables, and it is not necessary to use SQL DML commands to access partitioned tables without any changes.

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:

Strengths:

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

2. Enhanced usability: Assuming that there is a problem with one partition of the table, the data of the table in other partitions is still available;

3, Maintenance Convenience: If a partition of the table has problems, need to repair the data, just repair the partition can be;

4. Balanced I/O: Ability to map different partitions to disk to balance I/O and improve overall system performance.

Disadvantages:

1, partition Table Related: Existing tables No method can be directly converted into a partitioned table. Only 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 manageable 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 partition the records of a table according to the range of values in a column. You want to work with some data that often falls within a certain range, such as the month. Assuming that the data can be evenly distributed according to the extent of the partition, it will get the best performance. Assuming the data distribution is very uneven, you may have to choose other partitioning methods.

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
Suppose 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. The row is 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 to improve performance by cross-interviewing 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 choose the type of partition, how to select the column of the partition? Before you have to understand your purpose-manageability and performance, what do you focus more on? The areas affected by the partitioning table can be categorized as follows: Performance, manageability, data cleansing.  
performance:  
Easy to manage:  
for large tables that include massive amounts of data, it is obvious that partitioning brings ease of management. When you suggest an index based on a non-partitioned table, the only option is to create an entire index. Assuming that the table is partitioned, you can create an index for this table based on partition parallelism, for example:

In addition, you can do a lot of things at the same time, like 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 are able to use a drop partition to complete this task, such as:

When a table partition is deleted, the corresponding local index is also deleted at the same time. Assuming there is a global index, it becomes unusable state. To prevent this from happening, you can use:

Global