In this lesson, you will learn-
- 1. Concept of database sorting.
- 2. Concept of database indexing.
- 3. Advantages and disadvantages of indexing.
- 4. Difference between sorting and indexing.
Database Sorting: Sorting means arranging the records in either ascending or descending order based on specific fields to make reported data more usable.
Database sorting are two types-
- 1.Ascending Order
- 2.Descending Order
The records of the following student_info table are sorted based on GPA field-
Database Indexing: Indexing is a data structure technique to efficiently retrieve records from the database files based on some attributes on which the indexing has been done. Indexing in database systems is similar to what we see in books.
An index or database index is a data structure which is used to quickly locate and access the data in a database table.
Some important points should be kept in minds when creating an index-
- 1. Normally index should be created based on key field of a table.
- 2. Index’s name should be given similar to field’s name that is used to make index. As a result, it will be easy to remember Index’s name.
- 3. A database table may have one or more index and they can be opened at the same time but only one index will be active at a time.
The advantages of indexes are as follows:
- 1. Their use in queries usually results in much better performance.
- 2. They make it possible to quickly retrieve (fetch) data.
- 3. They can be used for sorting. A post-fetch-sort operation can be eliminated.
- 4. Unique indexes guarantee uniquely identifiable records in the database.
The disadvantages of indexes are as follows:
- 1. They decrease performance on inserts, updates, and deletes.
- 2. They take up space (this increases with the number of fields used and the length of the fields).
- a. What is database sorting?
- a. What is database indexing?
- b. “Database sorting and Indexing are not same” – Explain.
- b. “It is not possible to do sorting on OLE Object”- Explain.
- b. On which data types sorting is not possible? Explain.
- b. If data is inserted in the indexed table, index is updated automatically. -Explain.
- b. Indexing makes database faster for functioning – Explain.
- b. Indexing does not change the main file of the database. -Explain.
- b. Why does Sorting need more memory than Indexing? Explain.