A typical enterprise database is usually very large, consisting hundreds of tabl

Question

A typical enterprise database is usually very large, consisting hundreds of tables. Some of the tables may be very huge, having millions of rows. It is critical to have optimum performance for any enterprise database. Our discussion this week focus on the technique how to improve access speed of the database.

Questions:

1)Why disk I/O is always a bottleneck for database performance?

2)Why do we use index to improve the table access performance?

3)If there is a small table containing only a list of 50 states, is it necessary to use index? If we do use index, will it improve or hinder the access speed?

4)A large table contains customer information from 50 states. It is decided to use index according to the states. Which type of index would be the most efficient for such application?

Explanation / Answer

1). Disk I/O are the very slower operations as compared to the RAM operations. That's why they are bottleneck for the database performance.

2). To improve the performance of the table access we create the indexes. A sperate database/data structure like B_tree, table (based upon index and table) is created for the indexes where index and location of the row are stored.

when we search the table based on the index. it first search through index table and row location is found.

3) There is no need to create the index for a small table. it will hinder the performance. In case of indexes, it will perform two operations first in index database then in the actual table.

4). We should use the clustered index for the states. Basically in clustered indexes, records with similar characteristics are grouped together and indexes are created for these groups. so we will have 50 groups here

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.