Oracle Database Partition technology

Oracle Database Partition technology Partition introduction Partition refers to dividing a very large table or index into smaller, easy-to-manage logical fragments-partitions according to the value of a column. Partitioning a table or index does not affect SQL statements and DML statements. Just like using a non-partitioned table, each partition has its own segment, because, DDL can break down large tasks into smaller particles. Only the definition information of a partition table is defined. Only each partition that stores data has its own segment. Www.2cto.com is like a large view with multiple identical column names and column types. The benefits of using the partition function can be described in the following aspects: performance can reduce the total amount of data retrieved, because it has partitionpruing and partition-wise joins. Partition pruing: When the predicate contains a partition key, OracleDatabase can automatically crop unnecessary partition without retrieving additional partition. Partition-wise joins: When two tables are joined, partitionkey is used as the join condition. OracleDatabase can divide the join operation into multiple single tables and join piece for each partition. For a single thread, the workload of each join operation is reduced, which can reduce the overhead of the system. For multithreading, multiple threads can be used for each join piece to accelerate the retrieval time (but consume more cpu ). Www.2cto.com uses the partition technology to manage and maintain large tables or indexes into multiple maintenance fragments, allowing you to manage and maintain these schema objects more flexibly. For example, there is a box containing an important file weighing 100 kilograms. You need to move it to the office. This is very tiring or even impossible. However, if the Partitioning technology is used, it equals to 10 minutes for a 100-kilogram box. At this time, you can move a small box each time. Availability because each partition in a partition table is isolated on the physical layer-each partition has its own segment. Therefore, when one partition is unavailable, it does not affect another partition. Partition classification can be divided into three methods: range, hash, and list. The following describes the applicable scenarios of each partition method. Range Partitioning is applicable to scenarios where data in tables is frequently scanned by Range, such as order time. In this case, Range Partitioning OracleDatabase can provide the Partition pruing function, greatly reducing the query time. Maintain regularly cleared data, such as application requirements. To ensure efficiency, only online data within 12 months is required. If Range partitions are used, you can use the partition exchange function when data is sent every month to load new data to the partition table, and then delete the data of the oldest month directly, or archive. Partitions use multiple segments, so these operations are very efficient. If a single table is used, you may need a dml statement to delete the data after the query. The performance is very low and the backup operation is the same. If Range partitions are used, only one ddl statement is required. Maintain big table data. Backup, recovery, and consumption of a very large table data are huge. However, if Range partitioning is used, the performance of data maintenance by time will be greatly improved. For example, if you use a Data Pump to back up data for a month, you must use a query statement if the table is not a partition table. However, if Range partitions are used, you can omit the query operations to ensure efficiency. Range Partitioning is applicable to time columns. Of course, there are other applicable scenarios. For example, the rows mapped with consecutive Column values have special meanings, such as the age of people and the price of goods. Starting from 11g, supports a new RangePartition method, Interval partition, which can automatically create the required partition according to the option. Compared with Range Partition, List Partitioning is applicable to scattered Column Value ing rows with special meanings. For example, to collect domestic data, you can use regions to create partitions. This makes it more efficient to load or collect regional data. Hash Partitioning is applicable in this scenario. This partition is suitable for average I/O scenarios. For example, if a large table is frequently accessed, I/O operations on the tablespace where the large table is located will be very frequent, at this time, you can place the Hash Partition on different tablespaces (the tablespaces are on different physical disks), with an average I/O load on each disk. This prevents high I/O loads on a single disk. (Hash partition also supports partition pruing, but this is meaningless)