Performance tuning thru table partitions

Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions।
Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes।
Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.

Partitioning the table along defined boundaries and separating the partitions onto separate physical disks.

Most of databases allow horizontal partitioning, while others also include vertical partitioning । Horizontal positioning divides a table based on rows, and vertical partitioning divides the table based on columns.


When a table gets very large, basic operations and maintenance on that table becomes
cumbersome. Table locks persist for too long. All manner of SQL queries begin to take an impractical amount of
time to return results. The classic method of dealing with this scenario is to divide the table into
smaller tables and then use a view to combine the smaller tables into a comprehensive view।

Tables can be partitioned by

a range
Common examples of range based partitioning
schemes might be invoice numbers, dates, or zip codes। Anything with a serial ordering can be divided by ranges.
A list
A list-based partition will divide the tables based on a list of values
a hash
A partition defined by a hash will segment the table based on the value returned from a hash function. Once a table is partitioned based on a sensible boundary, the pieces of the table can be physically located on separate disks.

If you are running into performance issues with large tables, seriously consider optimizing your database by partitioning the large tables onto separate disks instead of over-optimizing your existing queries।

How do we create and manage partitions?

SELECT * FROM schema.table PARTITION(part_name);

This syntax provides a simple way of viewing individual partitions as tables

ALTER TABLE DROP PARTITION

ALTER TABLE TRUNCATE PARTITION

ALTER TABLE SPLIT PARTITION

ALTER TABLE ADD आटीटीण

A striped RAID device may contain multiple physical disks, but it appears to Adaptive Server as a single logical device.
For a striped RAID device, you can use multiple partitions on the single logical device and achieve good parallel
query performance.