Indexes in joins

[aaparanjpe]

Hi,

When I join two tables in an ABAP quey eg:

SELECT A B

FROM abc INNER JOIN xyz

ON abc_{C EQ xyz}C

WHERE abc~D = .... AND

abc~E = .... AND

xyz~J = ....

This is just an example.

My query is does a join make use indexes if they are available and how are the valid indexes identified.

[Former Member]

Well, it's up to the programmer to make sure that the tables are joined on index fields.

Rob

[venkata_ramisetti]

Hi Ashish,

You can find all the available indeces from SE11 (Open the table and click indexes in the applicattion tool bar)

System uses various mechanism to get the right index for the SELECT statement.

There is a good article in the SAP IT Toolbox, please check that.

http://sap.ittoolbox.com/documents/popular-q-and-a/specifying-the-index-to-be-used-2462#

Please also check the below docu.

http://help.sap.com/saphelp_nw2004s/helpdata/en/aa/47349d0f1c11d295380000e8353423/frameset.htm

As Rob mentioned, we can make WHERE clause in such a way, the system will choose the index based on the WHERE cluase fields.

Thanks

Ramakrishna

Message was edited by:

Ramakrishna Ramisetti

[aaparanjpe]

Hi ,

My doubt was whether a join query will use an index at all. I wanted to confirm this because there has been a lot of confusion in my mind regarding this.

Regards,

Ashish

[Former Member]

If you join on index fields an the where clause uses an index, you'll use the indexes.

This is true for primary key fields, but depending on how you construct the JOIN and WHERE, the optimizer may get "confused" and not be able to use the correct index.

The best thing is to try it out and see.

Rob

[venkata_ramisetti]

Hi Ashish,

You can find which INDEX it is using also from SQL Trace transaction(ST05).

Once you run the SQL trace transaction, goto the 'Display Trace' and click the 'DDIC Information' button in the application tool bar.

It will show you which index the Query is used.

If want to know how to run SQL Trace, Please check the below documentation.

http://help.sap.com/saphelp_nw04/helpdata/en/17/358df9c9fee2469105731e10756921/content.htm

Thanks

Ramakrishna

Message was edited by:

Ramakrishna Ramisetti

[Former Member]

Hi Ashish,

Yeah the joins definitely make use of Indexes.

You can identify the indexes in your table :

in se11 --> table name (display) ---> Goto ---> Indexes.

Here you can find out which is the default index being used and you can also create your own indexes.

Regards,

Vinod.

Close the thread if your problem solved.

[Clemenss]

Hi Ashish,

as already outlined: Switsch on trace with ST05, execute statement, stop trace and display. In the list, use 'EXPLAIN' function to see what is given to the database, what index is used and what are the estimated 'costs'.

If it does not use the index as expected, ORACLE database can be forced to use an index.

Examples:

%_HINTS ORACLE 'INDEX("&TABLE&"BTCV~I00")'

%_HINTS ORACLE 'ORDERED INDEX("&TABLE 1&","COVA~I03")'

ORACLE 'FIRST_ROWS'.

%_HINTS ORACLE 'FIRST_ROWS INDEX("&TABLE 2&",COVA~I03")'

ORACLE 'ORDERED'

Sorry, table names are faked here; should give you the direction. Google for "oracle hints", specify in the WHERE clause as rhis:

SELECT * UP TO 10 ROWS FROM csks

WHERE kokrs <> space AND

kostl <> space

%_HINTS ORACLE 'index(csks"J")'.

Regards,

Clemens