classification indexing | DBMS

hi students coming to next topic that is classification of indexing in the previous video I explained what is an indexing okay and what is that structure of index so now let us see here the classification of indexing so the indexing can be classified like dense index text dense indexing and the sparse indexing so indexing is of two types whether it may be a dense indexing or sparse indexing so in dense indexing means so what you call this dense index so in dense indexing if an index entry is created for every search key value here and index entries created for every search key value so whatever the index entry is there for every index entry you are created for every search key value that you call it as a dense index now coming to this parse index so what is a sparse index so here an index entry is created index entry is created only for some records only for some records okay then it is called this parse index so this is the difference between those two here and then dense index for each and every entry if a search key value will be there okay whereas in sparse index an index entry is created only for some records only for some particular records only the index entry will be created where as indents for each entry a search key value will be there okay here the more search key values will be there here the less only whatever the required we want those keys will be present in this parse index so here see so suppose this is the index index structure so in index structure what you have you have the search key value and the block pointer okay so whatever the item we are searching for means that pointer will be there and this is the key value what we are searching for one two three like that so it should be in sorted order like that okay four five so five will be there so coming to the data block here we have to find our required data so what it does so it goes to each and every record one two three four five okay for each index entry is created for every search given so for every search key value a particular index entry is created and one two one will be there every for every every base basepoint block pointer is searching for the first record the record that is there in the data block okay so here it's searching each and every block in the disk okay now coming to this parse index sparse index means so this is the index structure here you're having the search key and the base pointer block pointer so you want to search one for suppose to like that seven and here the UK are having the data block two three four five six seven okay so instead after one instead of going to two with the help of the search key for directly it goes to the fourth block okay so instead of going to five it directly goes to the seven means it is not good to say it is not searching each and every key in the data block so this is the sparse ending sparse index is only here the index entry is created only for some records so whatever the records that are present in the search key those records will be accessed by the sparse index whereas in dense index each and every entry is created and every search key value will be there for those entry okay so this is a dense index and sparse index so let us see different types of indexes okay so the different types of India the indexes will be divided into a single level it may be a single level or multi level so single level is of different types that is the first is primary primary index cluster index and secondary index okay so these three types of index you call it you are you call it as a single level index whereas multi level index or Vitry B+ tree okay so the index may be a single level or a multi level single level is primary index cluster index secondary index multi-level is B 3 and B plus V okay so single level a primary index will use primary key plus it should be whatever the table you are using that should be in order okay and cluster index it may not use the primary key it should be a it depends upon the non key attribute on the data base table non key plus and that database table should be in ordered so next is the secondary in this secondary index is non key like just like a candidate or else it is a candidate key not a primary key maybe it should be a candidate key non key plus and unordered so secondary index means the database table may be in unordered okay where is in cluster index it the database should be ordered and we use the non key attribute for index so in the primary index we use primary key as a search key value and it should be ordered okay so let us see how the single index and the multi level index will be this suppose if I take the single index single index single index means it's a primary cluster secondary okay so they will look like suppose this is the index index is having the search key value and the block pointer okay and here this is the data item so we are just accessing the data item okay so this you call it as a single level here index and data directly communicating okay here index and data or directly connected to each other so you call this as a single index coming to multi level multi level index so what is this multi level index so here you take this is an outer index so these were the pointers and here you are having the inner index for each and every point you are having deep inner index and for these inner index we are having the actual data blocks okay these are the data blocks okay so this you call it as a multi level index so here a communication index will be communicating two data blocks with the help of another inner index so outer index is connected to the communicate way select the data in in inner index and here in inner index search for the particular data items in the data block okay so why we are using this multi level index if you use the single index there is if there is a large size of index that single index cannot be handle so for that case we will go for the multiple disk access okay single index cannot single index cannot handle larger size of index large size of index cannot handle by the large size of index cannot handle by this single index okay to place in memory so which leads to a for this reason which leads to a multiple disk access okay so if single index is used then a large size index cannot be kept in memory which leads to a multiple disk access so this is about different classifications of indexing dance indexing and the sparse indexing okay a single level index multi level index so single level index is the primary cluster and the secondary you call it as a single level whereas multi level means B 3 B plus tree so these two searching so you call it as a multi level index thank you

Channel: Education 4u

Views: 74,713

Rating: 4.7765956 out of 5

Keywords: indexing classification in database, classification of indexing, sparse indexing, dense indexing, sparse and dense indexing, classification and indexing, classification and indexing of records, classification of indexing in dbms, dense indexing in dbms, dense indexing and sparse indexing, sparse indexing in dbms

Id: mXPwAiGYl1w

Length: 11min 51sec (711 seconds)

Published: Tue Dec 19 2017