topic index in Application Development and Automation Discussions

index

Former Member — Fri, 14 Mar 2008 17:57:59 GMT

when u create an index in Data Dictionary..how do we call it in a program or report..?totally how many we can create?

Re: index

Former Member — Fri, 14 Mar 2008 18:20:23 GMT

Actually, you don't "call " and index in a program. An index is created in a transparent table to optimize data access. For example, if you have a non-key field, and you use it in a WHERE condition frequently in your programs, than you should consider creating an index for this field, because it will speed up your query. But you don't need to "call" the index, if it is created, it' s automatically used. I don't know if there is a database limit for index, but you can create with up to 3 characters, including letters, what in theory gives you a huge number of possibilities (more than you need and should create).

Re: index

Former Member — Fri, 14 Mar 2008 18:21:47 GMT

When you create am index in Data Dictionary for any table, you cannot directly call it.

The index that is used to access a database table is determined by the optimizer based on the selection criteria the select is executed with.

So in order to have the optimizer select your index provide values for every index field in your where condition.

e.g.

If you have a primary key of

field1

field2

field3

field4

and a secondary index of

field5

field1

field2

and you provide criteria for field1 and 2 only in your where condition the optimizer will go for the primary key.

In order to have your index used you need at least to provide values for field5 in your where condition, all fields of the index would be best.

In regard to the number of index you should only create as much index as necessary and as little as possible, because any new index will slow down the write access to that table because it has to be updated in addition to the data in the table.

Hope that helps,

Michael

Re: index

Former Member — Fri, 14 Mar 2008 18:21:58 GMT

Please do not duplicate or cross post.

Rob

Re: index

former_member125661 — Sat, 15 Mar 2008 22:11:30 GMT

U create an index for a table so that if u can speed up the querying on it. U dont call the index itself in a report..Again care should be taken regarding the number of indeces u can create, Usually u create indeces for master table...and not transactional tables...or u create indeces for table which are queried more than updated.

Re: index

Former Member — Sun, 16 Mar 2008 14:39:30 GMT

Hi,

No need to call any index from program. Bcz SAP take cares of those things and u can not call it too.

Generally indexes are used to organize the data in order to retrive the data in Faster manner. So don't think of calling indexes.

U can create upto 9 Indexes

Re: index

Former Member — Fri, 21 Mar 2008 06:24:29 GMT

Hi,

indexes main purpose is to retrieve the data easily and faster

from the database table.

-->When a base table has multiple indices, the where clause should be in the order of the index, either a primary or a secondary index.

To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields. One more tip is that if a table begins with MANDT, while an index does not, there is a high possibility that the optimizer might not use that index.

In certain scenarios, it is advisable to check whether a new index can speed up the performance of a program. This will come handy in programs that access data from the finance tables.

regards,

vineela.

Re: index

Former Member — Fri, 21 Mar 2008 06:30:38 GMT

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.