Table Join

[Former Member]

Hi ABAP Gurus,

I have got the following scenario

SELECT A_{FIELD1 A}FIELD2 B_{FIELD1 B}FIELD2

FROM TABLE 1 AS A

JOIN TABLE 2 AS B ON A_{FIELD1 = B}FIELD9

WHERE A~FIELD1 IN S_OPT1

AND A~FIELD2 IN S_OPT2

AND B~FIELD2 IN S_OPT3

AND B~FIELD3 IN S_OPT4.

TABLE1 key is FIELD1 and FIELD2. TABLE2 key is FIELD1.

When creating an index for TABLE2, should the index include the field used in the join I.E. FIELD9, FIELD2, FIELD3 or should it just contain FIELD 2 and FIELD3 from the where clause ?

Thanks for your help.

[Former Member]

Hi

It should just contain the fields used in your WHERE clause, also make sure the order is also the same.

Regards

Ranganath

[Former Member]

hi Bhai,

essentially, creation of indexes is very critical.

you must analyze/ensure the fields on which you are creating the index are optimum for the table or not. a transaction that helps you achieve this is DB05.

in this transaction, you enter the table name and field names on which you wish to create the index. the result of this is to show you how many unique entries can be found using these field criteria

the more unique the results the better or more optimum the index would be

please consult basis for this ..... if you create an index on a table with fields which return approximately half the dtaabase table entries every time, i.e. create that large a cache buffer, you can imagine how bad the program performance would be .....

regards,

aditya

[Former Member]

Are the tables standard SAP tables or custom tables? Are they updated in batch only or also online?

Rob

[Former Member]

Hi..

Alwayz remeber that the fields which you are using in WHERE condition should be Primary Key Fields.

Then only the statement will be efiicient.

If you are not using Primary key field in WHERE condition, then you should use Secondory indexes in WHERE condition to improve the efficiency.

Regards

sandeep.