BW:What is meant by Primary Index and Secondary Index

Explain the what is primary and secondary index.

When you activate an object say ODS / DSO, the system automatically generate an index based on the key fields and this is primary index.

In addition if you wish to create more indexes , then they are called secondary indexes.

The primary index is distinguished from the secondary indexes of a table. The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

You can also create further indexes on a table. These are called secondary indexes. This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access. Different indexes on the same table are distinguished with a three-place index identifier. 

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. 

   

源文档 <http://www.sap-img.com/business/what-is-meant-by-primary-index-and-secondary-index.htm>

   

 Note 401242 - Problems with InfoCube or aggregate indexes

   

Summary

Symptom

You have a BW 2.0B system with at least level SP3, or a higher system (BW 2.1C, BW 3.x ...).

The following problems may occur when the indexes of an InfoCube or aggregates are created:

• A yellow or red traffic light is displayed in the "Performance" tabstrip (Admin Workbench) according to the status of these indexes - pressing the "Repair" button does not change the status; or
• A yellow or red traffic light is displayed in transaction RSRV under the "Database" tabstrip and the test "InfoCube Indexes and its Aggregate" - in this case pressing "Repair" does not change the situation; or
• A number of primary or secondary indexes appear in DB02 (Name prefixes /BI*/F or /BI*/E) under "Missing Indices".

Other terms

OLAP, Business Information Warehouse, InfoCube, aggregates, index, DB02

   

   

Solution

(1) Run the SAP_INFOCUBE_INDEXES_REPAIR report. To do this, go to transaction SE38 and schedule this report as a background job.

(2) If you run this report and the indexes have still not been repaired, make sure during the checks in DB02 that the information can be refreshed by pressing the "Refresh" and "Perform Database Checks" buttons, then analyze the causes as follows:

• Look at the log in transaction RSRV, for "Database", "Indices of an InfoCube and its Aggregate", "Execute" (F8) and then "results" (F6).This should provide you with information about the problem.
• After the job (SAP_INFOCUBE_INDEXES_REPAIR) has run, look at the system log (transaction SM21).SQL errors are logged there, which provide information as to why an index was not created. For more information on analyzing SQL errors, see note 495256.
• Refer also to notes 337830 and 364650.

   

源文档 <https://websmp230.sap-ag.de/sap(bD16aCZjPTAwMQ==)/bc/bsp/spn/sapnotes/index2.htm?numm=401242>