Quick links
Latest articles
Internship
Members
New...
Algorithms
Discrete Mathematics
Big data
Languages
C
C++
C++ STL
Java
Data Structure
C#.Net
Android
Kotlin
SQL
Web
PHP
Python
JavaScript
CSS
Ajax
Node.js
Web prog.
Programs
C
C++
DS
Java
C#
Python
Aptitude
C
C++
Java
DBMS
Interview
C
Embedded C
Java
SEO
HR
CS Subjects
CS Basics
O.S.
Networks
DBMS
Embedded Systems
Cloud Computing
Machine learning
CS Organizations
Linux
DOS
More...
Articles
Puzzles
News/Updates

Home » DBMS

Design Decision about Indexing | DBMS



In this article, we are going to learn about design decision about indexing and its categories in DBMS.
Submitted by Akash Kumar, on November 05, 2018

Introduction:

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.

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.






Quick links:
C FAQ(s) C Advance programs C/C++ Tips & Tricks Puzzles JavaScript CSS Python Linux Commands PHP Android Articles More...

Featured post:
Introduction to Linux (Its modes, Safety, Most popular Applications)
Linux Best Distribution Software (Distros) of 2018

Was this page helpful? Please share with your friends...

Are you a blogger? Join our Blogging forum.

Comments and Discussions



Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing » Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates



© https://www.includehelp.com (2015-2018), Some rights reserved.