Various Index Types in Database

Replies

• 

  Ankita Katdare

  Hi King99,
  
  I will try to explain it to you, in as simple words as possible.
  
  An Index is a collection of data entries which is used to find a record in a file. These indices are stored in a table. An Index table consists of 2 parts:
  1. Value of attributes of the file (called as Index field)
  2. Pointer to the actual physical record in the database
  
  During searching of a file record, index table is searched for getting the memory address. The use of index enables fast searching of data from the secondary memory.
  
  Q. What are clustered indexes?
  In clustering, index file records are stored physically in order on a key attribute that does not have a unique value for each record. It is the same as dense index i.e. there is a record in index table for each unique value of the element-attribute being searched and a pointer to the first data record with that value.
  The other records with same value of search-attribute are stored sequentially after the first record.
• 

  Ankita Katdare

  Now let's see, what are primary and secondary indexes.
  
  1. Primary Index
  It will consist of all prime-key attributes of a table and a pointer to physical memory address of the record in the data file.
  
  For eg:
  
  A primary index for the following table looks like this:
  
  

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  
  
  
  2. Secondary Index
  It provides a secondary means of accessing a data. It may be on a 'candidate key' of a table.
  
  For eg:
  

  35 25 30 37 32 22 42 38 40 26 28 41

  It means that, if we land on index 32, it will point to record number: 5, 11 and 14 and perform a linear search to find the correct from these 3.
• 

  slashfear

  @abrakabarba: good explanation buddy 😀 I have few corrections (if it is wrong correct me back please) In clustered index the values will be unique and thats why when you make a column in a table as primary key it by default creates an clustered index on that column.... and more over the records will be physically reordered for example: if you have a table with records as follows,
  
  3
  4
  2
  5
  1
  
  So after you apply clustered index on that column it will be reordered as shown below:
  
  1
  2
  3
  4
  5
  
  which makes it easier to search the records by using binary search since the records are in ordered way......
  
  In non clustered index the records are not reordered but logical mapping is done so that the data's are retrieved in a faster way....
  
  
  -Arvind
• 

  Ankita Katdare

  @slashfear: Thanks for correcting! 😀 Took a note of it.
• 

  king99

  
  Please tell me which indexing scheme we can use for above Bank Schema , please justify attributes that you say we should indexed along the explanation and type of index to be used . These type of question will enable all the people who wish to learn more of index and can know how we can apply proper suitable indexing scheme and index on above schema , your explanation will help in more in depth understanding so please explain in simple language .
  
  Thanks again , database experts !!
• 

  slashfear

  @abrakadabra: Not a prob buddy 😀 anytime....
• 

  slashfear

  Hey king,
  
  Thanks for dropping in the schema here ok here is the answers,
  
  Before we go for explanation first you have to understand that these tables are related to each other!! ok no lets start,
  
  In the customer table the custno column will be the primary key(since it will have unique entry because each customer will be assigned a unique number to identify them). So when you have primary key placed it by default will have clustered index placed on that column. so accessing the customer details will be easy and fast when you search the table with the custno column.
  
  
  In the branch table the branchno column will be primary key so clustured index will be placed on the branchno column.
  
  
  The account table is a child table of the customer and branch table since a person who is a customer only will hold an account in that bank and from a specific branch. So the custno and the branchno column from the account table will refer to the customer table and branch table. which means these two columns will have a non-clustured index on these columns.
  
  The employee table will have clustured index placed on the empno column and it will also reffer to the branch table, since the employee can work in any branch right, So the branchno column in the employee table will have non clustured index which is referring to the branchno column of the branch table.
  
  
  Hope that helped you and guess its not your home work!! 😉
  
  -Arvind
• 

  king99

  Hi @slashfear, thanks for the simpler way you have explained the index for above scenario, My aim is to study index but many books confuse you making things complicated so it was a clear example and will help all who want to study index from start , further i can read book to go into depth and study , thanks for answer!!
  @ abrakadabra , thanks for initial response and starting explanation !!
• 

  slashfear

  @king: No prob buddy your welcome 😀
• 

  king99

  hi pls i have one doubt , in above answer why secondary index or primary index is not feasible to apply ??