Home » DBMS

Physical Database Design Decisions

In this tutorial, we will learn about the physical database design decisions and its categories in DBMS. By Akash Kumar Last updated : May 29, 2023

Overview

The attributes whose values are required inequality or range conditions and those that are keys or that participate in join conditions require access paths.

The performance of queries largely depends upon what indexes or hashing schemas exist to expedite the processing of selections and joins. On the other hand, when we do insertion, deletion, or updating operations, the existence of indexes adds to the overhead. This overhead need to be justified in terms of the increase in efficiency by expediting queries and also the transactions.

Physical Database Design Decisions Categories

The physical design decision for indexing falls into the following categories:

1) Whether too index an attribute

The attribute must be a key or there must be some query that uses that attribute either in a selection condition or in a join. One factor.

In favor of setting up many indexes is that some queries can be processed by just scanning the indexes without retrieving any data.

2) What attributes or attributes to index on

An index can be constructed one or multiple attributes. If there are multiple attributes from one relation that are involved together in several queries,

A multiattribute index is warranted. The ordering of attributes within a multiattribute index must correspond to the queries. For example, the above index assumes that queries would be based on an ordering of colors within a GARMENT_style_ #rather than vice-versa.

3) Whether to set up a clustered index

At most, one index per table can be primary or clustering index because this implies that the file is physically ordered on that attribute. In most RDBMS this is specified by the keyword CLUSTER.

If a table requires several indexes, the decision about which one should be a clustered index depends upon whether keeping the table ordered on that attribute is needed. Range queries benefit a great deal from clustering. If several attributes require the range queries, relative benefits must be evaluated before deciding which attribute to cluster on. A clustering index may be set up as a multi-attribute index if range retrieval by that composite key is useful in report creation.

4) Whether to use a hash index over a tree index

In general, RDBMS use B+ trees for indexing. However, ISAM and hash indexes are also provided in some systems.B+ trees support equality and range queries on the attribute used as the search key. Hash indexes work very well with equality conditions, to find a matching record during joins.

5) Whether to use dynamic hashing for the file

For files that are very volatile, that is those that grow and shrink continuously one of the dynamic hashing schemas would be suitable. Currently, they are not offered by commercial RDBMSs.