Skip to Content
author's profile photo Former Member
Former Member

Difference b/w index and unique

Hi,

Difference b/w index and unique?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2007 at 10:19 AM

    hi,

    The optional additions UNIQUE or NON-UNIQUE determine whether the key is to be unique or non-unique, that is, whether the table can accept duplicate entries. If you do not specify UNIQUE or NON-UNIQUE for the key, the table type is generic in this respect. As such, it can only be used for specifying types. When you specify the table type simultaneously, you must note the following restrictions:

    You cannot use the UNIQUE addition for standard tables. The system always generates the NON-UNIQUE addition automatically.

    You must always specify the UNIQUE option when you create a hashed table.

    INDEX:

    An index can be considered a copy of a database table that has been reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search. The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can also be read.

    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 in the ABAP Dictionary. 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.

    Indexes speed up data selection from the database. They consist of selected fields of a table, of which a copy is then made in sorted order. If you specify the index fields correctly in a condition in the WHERE or HAVING clause, the system only searches part of the index (index range scan).

    The system automatically creates the primary index. It consists of the primary key fields of the database table. This means that for each combination of fields in the index, there is a maximum of one line in the table. This kind of index is also known as UNIQUE.

    If you cannot use the primary index to determine the result set because, for example, none of the primary index fields occur in the WHERE or HAVINGclauses, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

    You create secondary indexes using the ABAP Dictionary. There you can create its columns and define it as UNIQUE. However, you should not create secondary indexes to cover all possible combinations of fields.

    Only create one if you select data by fields that are not contained in another index, and the performance is very poor. Furthermore, you should only create secondary indexes for database tables from which you mainly read, since indexes have to be updated each time the database table is changed. As a rule, secondary indexes should not contain more than four fields, and you should not have more than five indexes for a single database table.

    If a table has more than five indexes, you run the risk of the optimizer choosing the wrong one for a particular operation. For this reason, you should avoid indexes with overlapping contents.

    Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column’s selectivity. Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to, at most, five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.

    If all of the columns in the SELECT clause are contained in the index, the system does not have to search the actual table data after reading from the index. If you have a SELECT clause with very few columns, you can improve performance dramatically by including these columns in a secondary index.

    What is the difference between primary index and secondary index?

    http://help.sap.com/saphelp_47x200/helpdata/en/cf/21eb2d446011d189700000e8322d00/frameset.htm

    A difference is made between Primary & Secondary indexes to a table. the primary index consists of the key fields of the table and a pointer to the non-keys-fields of the table. The Primary index is generated automatically when a table is created and is created in the datebase as the same times as the table. It is also possible to define further indexes to a table in the ABAP/4 dictionary, which are then referred to as Secondary indexes.

    Message was edited by:

    Roja Velagapudi

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 29, 2007 at 10:23 AM

    Hi,

    Unique and INDEX are different things,

    Primary key of the table is Unique, and based on the primary key we will have Primary Index on the table.

    Primary index is an index over the fields on the Primary keys, so when you have all or some of the fields of the primary key in your WHERE clause Primary index may be used to get the data fast.

    Primary index is always unique, but secondary indexes need not be, as secondary indexes are generally created on fields which are not part of the primary so there is chance that we get more than one record using the secondary index.

    Regards,

    Sesh

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2007 at 10:23 AM

    Hi,

    Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values.

    We should create an index if a column contains a wide range of values, a column contains a large number of null values, one or more columns are frequently used together in a WHERE clause or a join condition and the table is large and most queries are expected to retrieve less than 2-4% of the row.

    If you want to exforce uniqunes, you should define a unique or primary key constraint in the table definition. then a unique index is created automatially.

    Regards

    Sudheer

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      we use unique if we dont want to genrate the duplicate entries for the particular field.

      whereas index is total no. of times the loop is activated to read the value from table.

      reward point if clear

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.