Question

When implementing a physical database from a logical data model, you must consider database performance by allowing data in the database to be accessed more rapidly. In order to increase data availability, the DBA may break up data that are accessed together to be stored together. Which method will improve the performance of this structure when running queries? What are advantages and disadvantages of this method?

Answer 1

A method used to improve the performance of structure when running queries is known as Partitioning indexes.

The advantages of partitioning indexes are, It enables data management operations for example, index creation and rebuilding, It increases query performance.

The disadvantages are, one cannot define the primary index of partitioned table to be unique unless the whole partitioning column set is part of the primary index definition.

Step-by-step explanation:

Solution

Partitioning indexes are known as b -tress indexes that shows hon how to break up the index into separate or different partitions.

Partitioning is usually done to enhance the performance and increased availability. when data are spread over multiple partitions, one can be able to operate on one partition without affecting others. i.e to run utilities, or to take data offline.

Almost all DBMS products support partitioning, but in various ways. we must be sure to know the nuances of an individual a particular DBMS execution before partitioning.

The advantages of Partitioned indexes are as follows:

It enables data management operations for example, index creation and rebuilding

It increases performance of query

It can significantly the impact of schedule downtime for maintenance operations

Disadvantages:

The primary index of partitioned table cannot be described to be unique except if the whole partitioning column set is part of the primary index definition.