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.