What is meant by primary index and secondary index

Source: Internet
Author: User

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 cocould 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.

Just because you have a secondary index however, does not mean it will be used or shoshould be used. this gets into the cardinality of the fields you are thinking about indexing. for most DBS, an index must be fairly selective
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 shocould use the index, but if the IT wowould result in retrieving say 40% of
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
Invalid 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.