Oracle Partitioning is the splitting of data groups belonging to the same table into separate physical areas using separate partition tablespaces. Partitions can actually coexist in the same physical data file, but the same effect of partitioning might not be achieved. Separate partitions can be accessed in Parallel or individually or in groups.
How can partitioning improve the performance of Sql queries ?
Partitioning can be used to break large tables into smaller subsets.Processing of small subsets individually or in parallel ways is much faster than executing serial procession on large data sets. Partitioning is the most useful in very large database applications.
Benefits of Oracle Partitioning
- Parallel processing is a way of splitting data into separately located physical files. Separating table and index datafiles onto separate disks in a nonpartitioned database is a form of simple partitioning. It is often highly beneficial to read index and data spaces from separate locations because table and index physical spaces are often read almost in parallel.Partitioning can enhance the benefits of splitting datafiles.
- Different partitions within the same table can have different physical storage structures. Different partitions within a table can even be
both read-write and read only, perhaps allowing separation of archived and current data. - Backup, recovery, and utility usage (SQL*Loader, Export, andImport) can utilize parallel processing and activities on individual partitions.