A company is developing a database system containing information about cities, towns, and villages in the country. The number of cities, towns, and villages are in thousands and the database program should allow users to search a particular place by name. Users should also be able to search all places that match a particular value or range of values for attributes such as location or population size. This particular feature is known as a range query.
A reasonable database system must answer queries quickly enough to satisfy a typical user. For an exact-match query, a few seconds is satisfactory. If the database is meant to support range queries that can return many cities that match the query specification, the entire operation may be allowed to take longer. To meet this requirement, it will be necessary to support operations that process range queries efficiently by processing all cities in the range as a batch, rather than as a series of operations on individual cities.
Different indexing methods can be used to perform this operation. What will be your choice if:
a) database is created once and has no change over a period of time
Solution: clustered index should be used when the database is created once and not change over time. It is because in clustered index data is sorted physically.sweet candy vulearners.com When ever you insert or delete data from table, the whole data is sorted physically which provide extra overhead and decrease performance.
b) database is created once and database is changed over a period of time
The non clustered index should be used when the database is created and changed often. This is because in non clustered index data is sorted physically, when ever you insert data or delete data nothing changes in the order of data stored physically. But what happens sorting is done logically, a file is maintained in which the pointer to actual data at physical location is maintained.This saves for overhead to ordering or sorting data physically.