I know that secondary index is used when there is a requirement to select database records in a sequence other than that defined by the root key. What is the biggest advantage of doing this in terms of performance. Can any one please explain this with a small example?
Joined: 22 Apr 2006 Posts: 6258 Location: Mumbai, India
In General, A secondary index provides additonal direct access to rows.
For example an "Merchant " table might have a primary key of "Merchant Number". Each merchant also has their current department code (Dept_code) in their Merchant table entry. If Dept_code was a secondary index, a query could directly retrieve all of the people in a specific department without traversing the entire table.
I have all the redbooks that I want. I have recently also built a secondary index and I am making use of it. I am using a HIDAM database. Secondary index in general gives me an option of direct access to rows. In a HIDAM database, how does a secondary index help me in terms of number of segment occurences accessed if I dont use secondary index and I dont use secondary index