Use Cases of partition and subpartition in ultra-large tables

In the use cases of partition and subpartition in super large tables, partition and subpartition are two common keywords in super large tables. They are mainly used for partitioning to improve data reading efficiency. (For more information about partition types and syntax, see the article on the Internet.) Today, we can see an application solution that combines range partitions with business processing. It feels good. Record the general idea. Www.2cto.com [requirement]: The daily data volume in the table is large, and the old data needs to be cleaned up daily. Different types of data have different retention periods. Obviously, it is very inefficient and troublesome to use delete to clear data every day. (Batch submission should also be considered) [idea]: bind type 1 to the partition condition of partion, and bind the date (daily interval) to the partition condition of subpartition. (To prevent the conditional expressions of table creation statements from being too complex and simplify the conditions by using virtual columns) after the binding is complete, a large number of operations are performed on subpatition, that is, the distinct object_id (subpatition) of the object data is first identified, and then the following command is used to perform batch operations. For example:
Delete Alter table [table name] drop subPartition [subPartition name] COMPRESS Alter table [table name] Modify subPartition [subPartition name] COMPRESS export exp user/pasword tables = [table name]: [Partition name]… [Advantage]: Data partitions are directly consistent with the logic blocks of data. Batch operations such as clearing old data, backup, and compression are more efficient. Www.2cto.com [disadvantage]: The application scenario is relatively limited (only two levels can be bound), but it should be enough in most cases. The introduction of virtual columns causes a loss of insert performance (minor ). In addition, you need to write a PL/SQL statement to implement the function. [Note]: When only the last subpartition exists in a partition, the above command throws an exception. In this case, you must capture the exception and delete the partition.