Answer to Question #211895 in Databases | SQL | Oracle | MS Access for Ajay

Question

Ali is an IT expert working as a Database Administrator in an IT industry from 5 years.

He has 7 years aggregate experience of creating and managing the database. At the same time,

Ahmed is also working in same company as a Database Administrator but he can only insert and

can view the data, all other operation including creating, managing, applying different constraint

on data will be performed by Ali. Ali has created a database and that database has 100 tables.

There is a one table named EMPLOYEE which is empty and has no single record in that table.

Ahmed is trying to insert the record in that table but he is unable to insert the record in mentioned

table.

Now, in the light of database administration explain with suitable examples that why the record

is not inserted in employee table?

Answer 1

Sometimes, you will have a table that you need to query by many columns. As the table grows, you will notice that the SELECTs on these columns slow down. If the table is big enough, you will think, logically, to create an index on each column that you use to access this table only to find almost immediately that the performance of SELECTs improves but INSERTs, UPDATEs, and DELETEs drop. This, of course, is due to the fact that indexes have to be kept synchronized with the table, which means massive overhead for the DBE. This is a typical case of over indexing that you can solve in many ways; for instance, having only one index on all the columns different from the primary key that you use to query the table, ordering these columns from the most used to the least may offer better performance in all CRUD operations than one index per column.