Which book is better to learn INDEX concept in database?

Replies

• 

  Kaustubh Katdare

  Why not discuss it here? I'm sure the collective knowledge of CEans & real-life experience of working on databases is better than what any textbook can offer. Go ahead, ask your questions, doubts about databases and INDEX concept and I'm sure CEans will help you.
  
  I'd recommend posting some information about what you've understood about the Index concept in DBs on your own and then ask CEans for clarification or the parts you found difficult to understand. That'd be a better approach towards learning. The same can be extended to all the concepts in Computer Science, IT which are difficult to understand.
• 

  Neeraj Sharma

  Consider a general scenario that we encounter in our daily life.. Suppose I have a book with me about C programming and Data structures which consists of 700 pages. I want to study about file concepts in C. Now, how can I find where I have the information in the book that I want? I need to go searching page by page and search for the concept that I require right? This consumes a lot of time. To avoid this time wastage, books come with an index that stores the page numbers against the name of each chapter,unit etc. This index helps us in saving hell lot of time because we can directly go to the page that we want.
  

  
  Same applies in databases too. An index is used to avoid wastage of time that is spent in searching for a particular record in a table.
  
  For example, consider the following table
  
  
  

  #-Link-Snipped-#​

  
  If you run the following query on this table:
  
  

  ​

  ​

  ​

  select * from emp where empno=7844;
  
  it will go and scan each and every block of data starting from the top row till bottom row and when it finds empno 6844, it gives the result. This is a small table, consider the same operation on a table with 10 million rows, it will definitely consume a lot of time. This can be reduced by using index. An index can be created as follows:
  
  create index ind_1 on emp(empno);
  
  In the above statement, we are telling oracle to create an index named ind_1 on empno filed of table emp.
  When this index is created, one more table is formed in the back end that stores the address of each and every row. Following diagram will give a clean idea about it:
  
  

  #-Link-Snipped-#​

  
  
  As shown in the diagram, every empno has a corresponding address which holds the address of each row of the emp table. Now whenever we want to extract row corresponding to a particular empno, we get the row very fastly compared to earlier method where we had no index.
  
  This is how indexes are helpful in databases..
  They are:
  
  --> B-Tree Index
  --> Reverse B-Tree Index
  --> Bitmap index
  --> Function based Index
  
  Any doubts are welcom​
• 

  Pensu

  Try Navathe, I actually dont know the full name, but its the best one for database....