Which is NOT an advantage provided by partitioning?
A.
Reduces storage requirements for tables
B.
Can add to the benefits of parallelism through parallel partition-wise joins
C.
Can improve performance by reducing I/O
D.
Provides added flexibility for maintenance operations
Explanation:
Table storage requirements would increase, but the benefits are huge.
Oracle partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL
performance. Anyone with un-partitioned databases over 500 gigabytes is courting disaster.
Databases become unmanageable, and serious problems occur:
* SQL may perform poorly – Without Oracle partitioning, SQL queries with full-table scans take
hours to complete. In a full scan, the smaller the Oracle partition, the faster the performance. Also,
index range scans become inefficient.
* Recovery – Files recovery takes days, not minutes
* Maintenance – Rebuilding indexes (important to re-claim space and improve performance)
Oracle partitioning has many benefits to improve performance and manageability:
* Stable
* Robust
* Faster backups
* Less overhead
* Easier management
Maintenance of Oracle partitioned tables is improved because maintenance can be focused on
particular portions of tables. 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. (not D)
* Faster SQL Oracle is partition-aware, and some SQL may improve is speed by several orders
of magnitude (over 100x faster).– Index range scans Oracle partitioning physically sequences rows in index-order causing a
dramatic improvement (over 10x faster) in the speed of partition-key scans.
– Full-table scans Oracle partition pruning only accesses those data blocks required by the
query.
– Table joins Oracle partition-wise joins take the specific sub-set of the query partitions, causing
huge speed improvements on nested loop and hash joins. (not C)
– You can also improve the performance of massive join operations when large amounts of data
(for example, several million rows) are joined together by using partition-wise joins. (not B)
– Updates Oracle parallel query for partitions improves batch load speed.
Reference: http://www.dba-oracle.com/oracle_tips_partitioning.htm