Question

In: Computer Science

What performance advantages can be gained by partitioning a table?

What performance advantages can be gained by partitioning a table?

Solutions

Expert Solution

Partitioning can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for partitioning to greatly improve the performance of certain queries or maintenance operations. Moreover, partitioning can greatly simplify common administration tasks.

performance advantages can be gained by partitioning a table:

By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides multiple performance benefits. Partitioning features include:

Partition Pruning

Partition pruning is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude. For example, suppose an application contains an Orders table containing a historical record of orders, and that this table has been partitioned by week. A query requesting orders for a single week would only access a single partition of the Orders table. If the Orders table had 2 years of historical data, then this query would access one partition instead of 104 partitions. This query could potentially execute 100 times faster simply because of partition pruning.

Partition pruning works with all of Oracle performance features. Oracle uses partition pruning with any indexing or join technique, or parallel access method.

Partition-Wise Joins

Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.


Related Solutions

a) List four insights that can be gained on a company’s financial position / performance from...
a) List four insights that can be gained on a company’s financial position / performance from its cash flow statement. b) Briefly explain the relationship and differences between the income statement and the cash flow statement. c) Discuss whether accountants would be prepared to recognise something like an underground water supply as an asset on the company’s balance sheet.
In a well written paragraph, explain what business advantages are gained by using risk analysis for...
In a well written paragraph, explain what business advantages are gained by using risk analysis for investment projects.
Implement Quicksort in Java by sorting in parallel after partitioning; for this, the parent thread can...
Implement Quicksort in Java by sorting in parallel after partitioning; for this, the parent thread can continue sorting one segment and a child thread is created for sorting the other segment. However, create a new thread only if both segments contain more than S elements, otherwise sort sequentially both segments. %%writefile Quicksort.java import java.util.Random; YOUR CODE HERE public class Quicksort { static int N; // number of elements to be sorted static int S; // threshold for creating a sub-thread...
What are the advantages and disadvantages of developing and using standard costs for performance evaluation?
What are the advantages and disadvantages of developing and using standard costs for performance evaluation?
What are the advantages and disadvantages of globalization? The advantages and disadvantages of globalization can change...
What are the advantages and disadvantages of globalization? The advantages and disadvantages of globalization can change depending on whether or not they are being evaluated from a social or economic perspective. From an economic standpoint, globalization has provided ample opportunity for a number of American businesses to grow exponentially. Globalization has also had a large impact on how businesses are structured by altering supply chains which has arguably led to greater efficiency as well as lowering the cost of manufacturing...
What are advantages and disadvantages of earnings per share as a measure of corporate performance? 7
What are advantages and disadvantages of earnings per share as a measure of corporate performance? 7
What are some of the ways you can apply the ‘sociological imagination’ you have gained in...
What are some of the ways you can apply the ‘sociological imagination’ you have gained in this course to help you better understand your everyday life? Discuss two or three examples where you might now look at culture differently.
What information can be gained from a passive assessment and an active assessment of joint motion?
What information can be gained from a passive assessment and an active assessment of joint motion?
When research does not go as planned, what information can be gained from the results?
When research does not go as planned, what information can be gained from the results?
Genetic testing/screening – what info can be gained from this testing. Where can someone obtain genetic...
Genetic testing/screening – what info can be gained from this testing. Where can someone obtain genetic testing and what are the considerations when interpreting results as well as doing at-home testing?
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT