 basic concepts of indexing in database management system, myself, Rashmi Dixit. So let us begin the session. Learning outcome, at the end of this session, students will be able to explain need and basics of indexing, why indexing is required, what are the different types of indexing students are going to learn at the end of this session. So let us begin the session. So why indexing is used? This is a basic scenario or architecture, CPU, control processing unit, random access memory and hard disk, data is stored in the hard disk. Whatever the different tables, whatever the different records in the table are stored in the hard disk which is a secondary memory. However any user fires a query that is for example, consider select star from student where roll number is 1. If user want to find a record of a student for a particular roll number, he or she will fire a query, central processing unit will process that query. Now how communication takes place between CPU and hard disk, when the speed of the CPU is very fast as compared to the speed of the hard disk. So in the hard disk, data is already stored. So depending upon the requirement, the data from hard disk which is divided into logical blocks is bring into the RAM. So in the RAM, the block when student records are brought, CPU will search for a particular record in that particular block of the RAM. If the data or a particular record is found that is hit happens, the CPU will execute that query, otherwise the data from RAM again back to the particular block in the hard disk and this process continues unless and until a particular record of a student for a roll number is found. So that is a scenario or a general architecture, how a searching of a particular record takes place. Now where indexing comes into the picture, indexing is a mechanism used to speed up access to desired data. If you want to find a particular chapter, there are two things either you go page by page means for example, the chapter is on page 300, you have to perform 300 steps to find out that particular chapter or you will directly go to the index of the book, you will find out the page number and directly go to that particular page. Searching performs the same action for a database to find out the particular record. So how it is stored and index file consists of records which is called as index entries in the form search key with a pointer to that record. Now what is search key? Search key is an attribute or a set of attribute used to look up records in a file and pointer to that particular record. Search key is different from primary key or a candidate key so do not confuse between search key and the key attribute of our database. Any attribute will work as search key depending upon the requirement. Index files are typically much smaller than original file remember one thing because of which there is a speed up in the mechanism. Now two basic kinds of indices, ordered indices and hash indices. Ordered indices which are search keys are stored in a sorted order and hash indices search keys are distributed uniformly across buckets using a hash function. Now on which basis, which indices you are going to use for your database. There are different types of indices, ordered indices hash indices again there ordered indices are divided into different types of indices. We cannot directly say that this particular mechanism is based or this particular mechanism is worse this depends on that particular that particular technique. Now on which basis you are going to decide which index method is good. There are different index evaluation metrics access type that type of access supported efficiently for example records with a specified value in the attributes or records with an attribute value falling in a specified range of values. The time it takes to find a particular data atom insertion time as a name suggest or as a name indicate this time it takes to insert a new data atom but remember one thing insertion time also include a time to find a particular data atom plus time it takes to insert a new data atom. Deletion time type it takes to delete a particular data atom or a set of data atom from database like insertion time, deletion time also include searching time plus deletion time. Space overhead additional space occupied by index structure. So, these are different attribute which decides the particular index method best suited for your database criteria. All of you student please pause your video and try to answer the thing question using index method whether it optimizes query performance or whether it degrades query performance. So it is true or false naturally it is true why because with the help of index method searching becomes fast and as searching become fast deletion and insertion also become fast as compared to direct insertion or deletion in a database. Now what are the different types of index? Index there are three basic division primary indexing, clustering indexing and secondary indexing. Primary indexing again divide into dense and sparse. Now what is primary index? Primary index is defined on an ordered data file. The one important key or one important word is here ordered data file. The data file is ordered on a key field and the key field is generally the primary key of the relation. So read again carefully primary index is defined on an ordered data file. The data file is ordered on a key field and the key field is generally the primary key of the relation. Now data file, data or the data of a database is stored on the hard disk in the form of file. That file may be ordered or unordered. What are these ordered? What are these unordered? We will cover in the next session but primary index is defined on an ordered data file using a key attribute. Next type is secondary index. Secondary index may be generated from a field which is a candidate key and has a unique value in every record or a non key with duplicate values. So secondary key may be generated from a field which is a candidate key and has a unique value in every record or non key with a duplicate values. So secondary index may be applicable to unique key or non key. And the third one is clustering index. So clustering index is defined on an ordered data file. Order data file data is stored in an ordered and the data file is ordered on a non key field. So primary, secondary and clustering are the three types of indices. Primary, ordered data file, secondary, ordered or non ordered and clustering on ordered data file again on which key you are applying that decides primary, secondary and clustering. So primary, clustering and secondary, primary again divided into dense and sparse. So this is a reference. Thank you.