Already on ArticleSlash?

Forgot your password? Sign Up

Basics of Partition Tables in Oracle


Visitors: 153

Partitioning is a method of splitting an object (a table or index) into separate parts (called partitions) based on some criterion that is assigned to the partition. The criterion might be a date range, a number range, or any other possible value. Imagine, for example, that you have a huge table that is driving you crazy because queries on it are really slow. You get so mad at this table that you take a logical hatchet and begin to slice the table up into many smaller tables is a partition. Each table has the some columns, and each stores a certain amount of data. Oracle collectively deals with these physical partitions as a single logical object – called the partitioned table of index that you can access.

In Oracle, you can partition the following items; tables, indexes, index organized tables, materialized views, and indexes on materialized views. Oracle doesn’t support partitioning of clusters or indexes on clusters.

Why partition tables and indexes? As time goes on, certain objects tend to grow quickly, some becoming very large indeed. This can lead to several problems. Queries take longer and longer to run on non-partitioned large tables. More time is needed to backup the tablespace in which the table resides. More time is needed to recover the tablespace, because the tablespace grows larger. The table becomes harder to manage. Object management issues, such as storage parameters and defragmentation, become more complicated as an object gets bigger. You cannot assign different parts of the non-partitioned table to other tablespaces. This ability might for better performance and faster backup and recovery.

Partitioning is one method of dealing effectively with these problems. It is a particularly effective strategy with certain types of very large databases. Partitioning is very good feature for maintaining smart security, an example, you can take one portion of table read only or disable.

Partitioning can be used with Decision Support System DSS databases, and Data Warehouse databases, which typically stored and process large amounts of data using complex queries. Partitioning might also assist with VLDBs that handle Online Transaction Processing OLTP. These databases may store large amounts of data, have high rates of concurrent activity, and have relatively simple queries processing a small amount of data.

Oracle has improved on the partitioning benefits introduced in Oracle 8. In Oracle8, you could only partition based on data values in a column using a partitioning method called range partitioning. Now, Oracle introduced so many other method of partitioning like has partitioning, list partitioning, composite partitioning. Oracle partitioning method is only available in Enterprise edition and not available in other edition.

Managing Oracle partitioning tables and indexes is not easy, high expertise and experienced Database DBA can perform this. Expert Remote Oracle Support provider can able to handle these tasks. You can get some help on good and nice Oracle DBA Forums . Without experience on same techinque, it is very hard to clear technical interviews without Oracle DBA Interview Questions books.


Article Source:

Rate this Article: 
Arizona Real Estate Law Using a Partition Action to Resolve an Ownership Dispute
Rated 4 / 5
based on 5 votes

Related Articles:

Partition Recovery - What Will You Do To Recover Deleted Partition?

by: Simran Malhotra (April 10, 2008) 
(Computers and Technology/Data Recovery)

The advanced Oracle recovery utility to retrieve your inaccessible Oracle ..

by: Colin Dolly (February 25, 2011) 
(Computers and Technology/Data Recovery)

Recover Oracle Database Successfully by Oracle Recovery Tool

by: Colin Dolly (January 18, 2011) 
(Computers and Technology/Data Recovery)

Safe Oracle Database Recovery Utility to Repair Oracle Database and Recover ..

by: Colin Dolly (February 09, 2011) 
(Computers and Technology/Data Recovery)

The Most Simplest Way To Unhide A Hidden Partition

by: Victor Pradel (March 21, 2007) 
(Computers and Technology)

Access To The Hidden Partition Pqservice

by: Victor Pradel (January 10, 2007) 
(Computers and Technology)

Billards Tables and Pool Tables Primer

by: Anne Clarke (July 11, 2008) 
(Recreation and Sports/Billiards)

Oracle Users Lists - Oracle Users Email List - B2B Technology Lists

by: Ray Wilson (April 08, 2019) 

How to Password Protect and Partition a USB Flash Drive

by: Derek Rogers (September 10, 2008) 
(Computers and Technology/Hardware)

Arizona Real Estate Law Using a Partition Action to Resolve an Ownership Dispute

by: Kevin R Harper (July 22, 2008) 
(Legal/Real Estate Law)