what is the purpose of secondary index?

[Former Member]

what is the purpose of secondary index?

[Former Member]

Secondary indexes are used to enhancement performance when retrieving data. For example, say you have a table AFKO where the key to the table is AUFNR. You can select data from this table using AUFNR, the performance will be great because you are using the key. Say you need to hit this table with GLTRP(finish date), it is not part of the key, so the performance would be poor. If you created a secondard index using this field GLTRP, the performance would be improved. Make sense?

http://help.sap.com/saphelp_470/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm

Please make sure to award points for helpful answers and mark your posts as solved when solved completely. THanks.

[Former Member]

Hi Selva,

Secondary only means additional indexes with respect to the primary index, which is the very basic index for any table in a relational database.

And also please go through this link and see if it helps..

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

Rgds,

prakash

[Former Member]

Hi Selva,

Secondary indexes are used to enhancement performance when retrieving data. For example, say you have a table AFKO where the key to the table is AUFNR. You can select data from this table using AUFNR, the performance will be great because you are using the key. Say you need to hit this table with GLTRP(finish date), it is not part of the key, so the performance would be poor. If you created a secondard index using this field GLTRP, the performance would be improved. Make sense?

http://help.sap.com/saphelp_470/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm

Please make sure to award points for helpful answers and mark your posts as solved when solved completely. THanks.

Regards,

Mukesh Kumar

[ashok_kumar24]

Hi selva

Good

Secondary Database

-

---

First it must be stated that table design is a more logical work while index design is rather technical. In table design it might make sense to place certain fields (client, company code, ...) in the beginning. In index design, this is not advisable. Very important for an index is that it contains very selective fields in the beginning. Those are fields like object numbers. Not selective are client, company code, ...

Indexes should be small (few fields). The Database optimizer can combine two or more indexes to execute a query.

Indexes of one table should be disjoint (have few common fields), in order not to confuse the optimizer which index to use.

Note that each index slows the inserts into the table down. Updates are only slowed down if indexed fields are updated. In general, heavy inserted tables should have only few indexes while heavy selected tables might have more.

Logical Database

-

---

Using logical databases is a good method to write reports. Logical databases can be optimized centrally. But ensure that the structure of the logical database fits well to your report. Otherwise the effect can be the opposite.

Secondary Index

-

---

If you cannot use the primary index to determine the result set because, for example, none of the fields primary index occur in the WHERE or HAVING clause, 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 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. secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible to improve performance..

Cursor is used to read the data:-----

When you use a cursor to read data, you decouple the process from the SELECT statement. To do this, you must open a cursor for a SELECT statement. Afterwards, you can place the lines from the selection into a flat target area. An open cursor points to an internal handler, similarly to a reference variable pointing to an object.

Cursor is used for one more purpose that is ,You can set the cursor on the current list dynamically from within your program. You can do this to support the user with entering values into input fields or selecting fields or lines. To set the cursor we use SET CURSOR command.

SET cursor <col> <lin>.

This statement sets the cursor to column <col> of line <lin> of the output window.

Good Luck and reward me for the same

Thanks

Ashok.N

[Former Member]

Hi selva,

<b>1. SECONDARY INDEX.

They are generally used for faster access.</b>

EG.

In a table there are 10 fields.

1,2 are primary fields (primary index)

2. But the table is queried many times

on field number 6 (eg).

So we can create a NEW Index

(Secondary index)

only on that 6th field.

3. Due to this,

the sql will become faster

because NOW

the database will search on the

basis of secnodary index (made on 6th field)

regards,

amit m.

[Former Member]

Hi Amit Mittal

thanks for ur convincing answer