Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Indexes in joins

aaparanjpe
Explorer
0 Kudos
364

Hi,

When I join two tables in an ABAP quey eg:

SELECT A B

FROM abc INNER JOIN xyz

ON abcC EQ xyzC

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.

7 REPLIES 7

Former Member
0 Kudos
210

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

Rob

venkata_ramisetti
Active Contributor
0 Kudos
210

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

0 Kudos
210

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

0 Kudos
210

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

0 Kudos
210

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
0 Kudos
210

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
Active Contributor
0 Kudos
210

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