Database Management Systems (DBMS) Question Bank 01

DBMS question bank - why database administrators do not create index on every attribute of every relational table?

Question:

Why database administrators typically do NOT create an index on every single attribute of every single relation?

Answer:

• Indexes are separate tables. They consume extra space - In database, the indexes that we create in a table are stored as separate relations. They consume extra space. For example, if an index is created on an attribute X of relation R, then X will consists of at least two columns, one for index attribute and the other for pointer.

• Indexes slows-down the data queries that try to INSERT, DELETE or UPDATE data in the database - The records (keys) in indexes are stored in sorted order. This property helps in speeding up the look-up process. So, whenever we perform INSERT, DELETE, or UPDATE operation on the base table, the records in index has to be adjusted. This slows-down the process.

*************************