Partition Table and query performance

The so-called partition to improve performance is to narrow the scan range during table access, with a very small cost, reduce I/O operations, and achieve higher performance. The global index and Local index created on the partition table are both in balancing the cost and scope of the index scan.
Therefore, the performance improvement is directly related to the application.
For example:
Table T1 = 10g, start_time Index = 1g, all data is inserted in chronological order of start_time. The time interval is very average, such as 10 MB per half hour, now 10 range partitions are divided by time, with each partition being 1 GB.
If the data you query is only in a certain partition, where start_time, the current performance is greatly improved. In the past, you may need to scan 10 Gb, now you only need to scan 1G. Similarly, if you use the index before the partition and the local index after the partition, you may need to scan 1g before the scan index, scan MB now.

However, if you need to scan all the 10 partitions to obtain the computing results you need, such as monthly and quarterly reports, scan the 10 partitions at this time, for full scan when scan is not partitioned, the performance is better when no partition is performed.
Of course, if there are not many calculated fields, you can create a global index to solve this problem.

So if you ask whether a partition can improve performance, you can't say anything about it.
I think the partition performance optimization is completely related to the table structure and SQL operations applied on the table, and the use of partition, it is easy to use, and its performance is greatly improved. If it is not used well, it will suffer from loss and worse performance.

---
Advantages of Oracle Partitioned Tables

Oracle Partition Table

Partitions facilitate the management of larger tables and indexes. A partition key is introduced in partitions, the partition key is used to aggregate execution Data Based on a range value, a specific value list, or a hash function.

Partition tables may have the following benefits:

1. improve data availability: the increase in availability comes from the independence of each partition. A partition may not be equivalent to the availability of the object itself. The optimizer knows this partition mechanism and removes unreferenced partitions from the query plan accordingly.

2. Ease management burden.

This is easy to understand. Manage 10 Gb tables and GB tables.

3. improve query performance

In terms of the performance of read-only queries, partitions work for two types of operations.

1. Partition Elimination: Some data partitions are not considered during query processing.

2. parallel operations: Parallel Full table scan and Parallel Index interval scan (related to the operating system)

4. Reduce resource competition.