 Hello all welcome to the session on types 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 different types of indexing. So what are the different types of indexing as we have seen in the last video primary index, secondary index and clustering index. Primary index is defined on an order data file the data file is ordered on a key field. The key field is generally the primary key of the relation. 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 a duplicate value. And third one is clustering index, clustering index is defined on an ordered data file. The data file is ordered on a non key field. Now look at this diagram, this diagram gives you a glimpse of different types of indexing on which purpose. So primary index on an ordered file with key attribute that is a unique attribute. So ordered file, unordered file, ordered file the data is stored in a ordered manner or in a sorted manner on either key or non key attribute. Key attribute whenever the file we are performing indexing on a key attribute means unique value and in case of non key attribute there may be duplication of particular record. So whenever there is an ordered file with a key attribute primary index is preferable or we are going to use primary index, ordered file with non key attribute means duplicate records may be present at the time clustering index is beneficial and secondary index on an ordered file compulsory secondary index may be key or with a non key attribute. So remember one thing ordered file data is stored or on which you are applying indexing is in a ordered manner 1, 2, 3, 4, 5 or you can say that 5, 4, 3, 2, 1, unordered file 3, 9, 12, 6 that is there is no sequence or there is no ordering key 1, 2, 3, 4, 5 unique attribute and non key 3, 3, 2, 4, 5 there is a duplication of a particular record. So types of index primary, secondary and clustering remember this diagram so that you can get an idea where you are going to use which index. Now primary index is divided into two that is dense and sparse the definition is same no matter either you study in DA or data structure dense and sparse. So dense index record appear for every search key value in the file. So primary key sorry primary index with a dense property every record index record appear for every search key value in the file. So look at this diagram this particular now this is a instructor relation with ID, name, department and salary. So we are applying indexing on instructor ID now the search key is unique and the data is in ordered format. So naturally primary whenever record for every search key in index file we can say that dense indexing each and every primary key or each and every key appear here in a index file with pointer to that particular record. So this is dense index and now if you are going to apply dense index on a department name. So biology, computer science whatever the name of the department with pointer to that particular record having the same department whatever in the index file. So dense index record for every search key like it is a dense as compared to dense what happen in a sparse index file it contain index record for only some search key value. Applicable when record are sequentially ordered on search key index records are not created for every search key and index record here contain a search key and actual pointer to the data on the disk. Look at this diagram so as compared to dense where record for every search key here only the particular or some search key are selected in index file. Now find index record with the largest search key value which is less than key means if you want to find 15151 instructor record means instructor ID 15151. Now it will first go to 10101 then it will point to that particular record and its search in a sequential manner to find out the particular record with search key value 15151. So search the sequentially starting at the record to which the index record point. Now we will compare dense and sparse index. Now compared to dense indices sparse indexing require less space and less maintenance storing of all search key record and storing of a 3 to 4 search key record less space and less maintenance overhead for insertion and deletion less generally slower than dense index for locating a record. Now what is good tradeoff in sparse index? Sparse index with an index entry for every block in a file corresponding to least search key value in the block that is a main advantage of sparse index. Now secondary index index record point to a bucket that contain a pointer to all the actual record with that particular search key value. Now what is this? Look at this diagram. Now here the search key for your index is a salary. So it may be unique, it may be duplicate, it is unordered. Now that is one thing unordered. So search key here in index file and remember one thing index are always stored in a sorted or sequential manner. So rearranging of a salary it point to the bucket which point to the actual record. So 40000 it point to the bucket and from that bucket it point to the actual record where the record with a salary 40000 is present. So array to the record sorry pointer to the record through one bucket. Now look at here salary 80000 two records are there. So non key attribute is also there. So secondary index is performing on unordered file may be with a key attribute or non key attribute. Now the third sorry before clustering just take one question here which of the following is dense index primary cluster secondary and sparse. All of you student please pause your video and think for a while and try to give the answer to the particular question. So naturally secondary index here look at the diagram it is dense pointer for each and every search key. So what is the difference between primary and secondary index we will compare them within a couple of minutes. Indices offer substantial benefits when searching for a records ok. But updating indices imposes overhead on a database modification when a file is modified every index on a file must be updated. Sequential scan using a primary index is efficient but sequential scan using a secondary index is expensive. Each record access may fetch a new block from a disk and block fetch requires about 5 to 10 millisecond versus about 100 nanosecond for memory access. So when you are comparing order the primary index require the rows in a data block to be ordered on a index key while the secondary index does not have an impact on how the rows are actually organized in a data block. Key sorry ordered and non-order number of indices moreover there is a only one primary index while there can be multiple secondary index and duplicates when you are talking about duplicate in primary index there are no duplicate as we are performing in a key attribute but in a secondary index as we are may perform on a key or a non-key attribute. So there is a no surety about duplication of a record. So this is a reference thank you.