Skip to main content

Benefits of Partitioning

Partitioning Overview

Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.
Benefits of Partitioning

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.
Partitioning also enables database designers and administrators to solve some difficult problems posed by cutting-edge applications. Partitioning is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.
This section contains the following topics:

Partitioning for Performance

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.

Partitioning for Manageability

Partitioning enables you to partition tables and indexes into smaller, more manageable units, providing database administrators with the ability to pursue a divide and conquer approach to data management. With partitioning, maintenance operations can be focused on particular portions of tables. For example, you could back up a single partition of a table, rather than back up the entire table. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.
A typical usage of partitioning for manageability is to support a rolling window load process in a data warehouse. Suppose that you load new data into a table on a weekly basis. That table could be partitioned so that each partition contains one week of data. The load process is simply the addition of a new partition using a partition exchange load. Adding a single partition is much more efficient than modifying the entire table, because you do not need to modify any other partitions.

Partitioning for Availability

Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy. For example, if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table, and these database operations can run successfully, provided they do not need to access the unavailable partition.
The database administrator can specify that each partition be stored in a separate tablespace; the most common scenario is having these tablespaces stored on different storage tiers. Storing different partitions in different tablespaces enables you to do backup and recovery operations on each individual partition, independent of the other partitions in the table. Thus allowing the active parts of the database to be made available sooner so access to the system can continue, while the inactive data is still being restored. Moreover, partitioning can reduce scheduled downtime. The performance gains provided by partitioning may enable you to complete maintenance operations on large database objects in relatively small batch windows.

https://docs.oracle.com/database/121/VLDBG/GUID-3EEC044E-B4B6-4456-9F5C-2006130D024D.htm#VLDBG1053

Comments

Post a Comment

Popular posts from this blog

Oracle SQL92_SECURITY parameter

The Oracle SQL92_SECURITY parameter must be set to TRUE. The configuration option SQL92_SECURITY specifies whether table-level SELECT privileges are required to execute an update or delete that references table column values. If this option is disabled (set to FALSE), the UPDATE privilege can be used to determine values that should require SELECT privileges. The SQL92_SECURITY setting of TRUE prevents the exploitation of user credentials with only DELETE or UPDATE privileges on a table from being able to derive column values in that table by performing a series of update/delete statements using a where clause, and rolling back the change.  In the following example, with SQL92_SECURITY set to FALSE, a user with only delete privilege on the scott.emp table is able to derive that there is one employee with a salary greater than 3000. With SQL92_SECURITY set to TRUE, that user is prevented from attempting to derive a value.  SQL92_SECURITY = FALSE SQL> delete from s...
Post a Comment
Read more

Ofruesit e sistemeve të menaxhimit të bazës së të dhënave

Ofruesit e sistemeve të menaxhimit të bazës së të dhënave Cila është mënyra më e mirë për të përcaktuar se cili lloj i shërbimit të bazës së të dhënave ose bazës së të dhënave është më e mira për ndërmarrjen tuaj? E gjitha varet nga lloji i rastit të përdorimit që ju nevojitet. Zbuloni më shumë në këtë artikull , dhe artikujt vijues. Sistemi I menaxhimit të të dhenave Në thelb të gjitha informacionet digjitale që përdorim në baza ditore janë në një sistem të menaxhimit të bazës së të dhënave ose një grupi ruajtës diku në botë. Këto mund të shkojnë nga një pajisje e vogël e ruajtjes si një smartphone   ose a aq i madh sa një sistem ruajtje relativisht i pakufizuar i cloud. Më së miri është të zbuloni se cila DBMS është për ndërmarrjen tuaj? A duhet të abonoheni në një shërbim në AWS, Azure, Google ose ofrues tjetër cloud ose duhet të blini magazinimin e qendrës së të dhënave dhe serverat dhe ta ekzekutoni atë vetë? E gjitha varet nga lloji i rastit të përdorimit që ju ...
Post a Comment
Read more

Autentifikimi, Autorizimi dhe Llogaritja (AAA)- Authentication, Authorization, and Accounting (AAA)

Autentifikimi, Autorizimi dhe Llogaritja (AAA)- Authentication, Authorization, and Accounting (AAA) Autentifikimi, autorizimi dhe llogaritja (AAA) është një term për një kornizë për kontrollin inteligjent të qasjes në burimet kompjuterike, zbatimin e politikave, përdorimin e auditimit dhe sigurimin e informacionit të nevojshëm për të faturuar shërbimet. Këto procese të kombinuara konsiderohen të rëndësishme për menaxhimin efektiv të rrjetit dhe sigurinë. Si proces i parë, autentifikimi siguron një mënyrë për identifikimin e një përdoruesi, zakonisht duke i dhënë përdoruesit një emër përdoruesi të vlefshëm dhe një fjalëkalim të vlefshëm përpara se qasja të jepet. Procesi i legalizimit bazohet në secilin përdorues që ka një grup të kritereve unike për të fituar akses. Serveri AAA krahason kredencialet e identifikimit të përdoruesit me kredencialet e tjera të përdoruesit të ruajtura në një bazë të dhënash. Nëse kredencialet përputhen, përdoruesit i jepet aksesi në rrjet. Nëse kre...
Post a Comment
Read more