topic Re: Help On Table Index in Application Development and Automation Discussions

Help On Table Index

Former Member — Thu, 01 Jun 2006 08:06:51 GMT

Hi friends,

In my programe I am selecting MFRPN from Mara Table, in data dictionary the table has the index for the field MFRPN, should i mention the index name in my select statement in Prog ? if yes how to do this ? Any Sample code will help me a lot.

And in Data Dictionary the index is defined as Non Unique, and index not maintained in Oracle database, is this ok or should i change it as Unique & Maintain Index in Oracle database ?

This is very urgent, so kindly help me.

Thanks & regards,

Joseph

Re: Help On Table Index

Former Member — Thu, 01 Jun 2006 08:09:19 GMT

hi,

You can use %hints for this..

Consider following example..

SELECT * FROM RESB

WHERE MATNR = '200-100' AND WERKS = '1100'.

Two indexes are defined on RESB: the primary index with the ID '0' via the fields (MANDT,RSNUM,RSPOS,RSART) and a secondary index with the ID 'M' via (MANDT,MATNR,WERKS,XLOEK,KZEAR,BDTER). This M index can be used for the above statement.

A more direct way to access a table via an index is by using a hint and by letting the optimizer decide which index should be selected.

SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'%_HINTS ORACLE 'INDEX("&TABLE&")'.

After all, the M index should be predefined explicitly. Since the precise naming of the M index is not known, all alternatives are simply listed. The Oracle optimizer ignores the index names which do not exist. In this case, the &TABLE& substitution cannot be used for the index names since these names do not depend on a table alias in the FROM condition:

SELECT * FROM RESB WHERE MATNR = '200-100' AND WERKS = '1100'%_HINTS ORACLE 'INDEX("&TABLE&" "RESB~M" "RESB^M")'.

Regards,

Tanveer.

<b>Please mark helpful answers</b>

Re: Help On Table Index

Former Member — Thu, 01 Jun 2006 08:09:24 GMT

If a table has an index for field FIELD you'll use that index selecting by only that field.

SELECT * FROM <TABLE> WHERE FIELD = ......

In this situation my select's using the index.

Max

Re: Help On Table Index

Former Member — Thu, 01 Jun 2006 08:21:21 GMT

Hello Joseph,

Do an SQL trace on the program. This will tell you if your program is doing a read using primary index or alternative index which u have indicated. If it doing the read using ur index then u don't have to do anything but if not then..

u can use the keyword %HINT to force the database into using a perticular index. Please refer to OSS notes 129385.