Hi I am new to mainframes.
I have a doubt that why we need indexes.
Primary keys are used to identify unique rows but I read that index can be same for more than one row, then what was the purpose for which we created the index.
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
I read that index can be same for more than one row
an example might be in a "personnel" system.
Each person-id is unique, but let's say each department may have between 0 and many people assigned to it. If dept_no was a secondary key (index) a query could directly obtain all of the people assigned to that department and there would be no extra overhead traversing the entire table to return only those people assigned to the particular department(s) used in the WHERE. It could very quickly also determine that there were no people assigned to "that" department.