Oracle Partition (3) Partitioning and performance

In practical work, often hear beginners say: "Large table partition can certainly improve query performance." In fact, when you do not understand the application of the situation, blind to create a partition not only can not improve query performance, but also may lead to query performance degradation. Therefore, before deciding whether to use a partition, you must understand the current application environment.

Generally speaking, the application of database is divided into two kinds: OLTP and OLAP. OLTP refers to online transaction processing, such as Taobao shopping site, OLAP refers to online analysis system, such as Data Warehouse, data mart and so on. In practical applications, it is also possible to have the same database system for OLTP and OLAP.

For OLAP applications, tables and index partitions do improve query performance effectively, because OLAP often requires scanning a large amount of data (the execution plan is a full table scan), and partitioning can effectively reduce the amount of data scanned (that is, partition pruning Partition pruning). For example, suppose a Zhang has been stored for 5 years, your application usually only needs to access 1 months of data, before the partition, you need to scan all 5 years of data, but after the partition (assuming the time stamp as the partitioning key), only need to scan 1 months of data. And for OLAP applications, partitioning also has the advantage of being easy to manage, and partitioning is very handy if you need to migrate historical data.

But for OLTP applications, index partitions are very careful. Because OLTP usually requires only a small portion of the data to be accessed, indexed access is the most efficient. According to the principle of B + tree, even if the table is large, the height of the B + tree is generally the 2~3 layer, so the query performance of the index will almost not decrease with the increase in the amount of data (as the amount of data increase, B + tree more than one layer of Io only). However, if an index partition is available, it is possible to reduce IO times by partitioning key indexes (because of the pruning of index partitions), but if you are querying through a non-partitioning key index? Imagine that if a large table is divided into 100 partitions, the index through the non-partitioning key needs to be found in 100 partitions each time, performance will be reduced 100 times times! Therefore, for OLTP systems, a global, partitioned index is established, rather than a local partition index.

Therefore, if the table and index partitions can improve query performance, it must be based on the specific application decision. Do not blindly partition!

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/