Five different between primary index and secondary index
Answers
Primary index
A primary index is an index on a set of fields that includes the unique primary key for the field and is guaranteed not to contain duplicates.
Also Called a **Clustered index**.
eg. Employee ID can be Example of it.
Secondary index
A Secondary index is an index that is not a primary index and may have duplicates.
eg. Employee name can be example of it. Because Employee name can have similar values.
Lets say you have an ODS and the Primary Key is defined as Document Nbr, Cal_day. These two fields insure that the records are unqiue, but lets lay you frequently want to run queries where you selct data based on the Bus Area and Document Type. In this case, we could create a secondary index on Bus Area, Doc Type. Then when the query runs, instead of having to read every record, it can use the index to select records that contain just the Bus Area and Doc type values you are looking for.
Just because you have a secondary index however, does not mean it will be used or should be used. This gets into the cardinality of the fields you are thinking about indexing. For most DBs, an index must be fairly selective to be of any value. That is, given the values you provide in a query for Bus Area and Doc Type, if it will retrieve a very small percentage of the rows form the table, the DB probably should use the index, but if the it would result in retrieving say 40% of the rows, it si almost always better to just read the entire table.
Having current DB statististics and possibly histograms can be very important as well. The DB statistics hold information on how many distinct values a field has, e.g. how many distinct values of Business Area are there, how many doc types.
Secondary indexes are usally added to ODS (which you can add using Admin Wkbench) based on your most frequently used queries. Secondary indexes might also be added to selected Dimension and Master data tables as well, but that usually requires a DBA, or someone with similar privileges to create in BW
HOPE IT HELPS YOU!!!