How can i use index in select query.. facing problem with the select query.

[Former Member]

Hi Friends,

I am facing a serious problem in one of the select query. It is taking a lot of time to fetch data in Production Scenario.

Here is the query:

SELECT * APPENDING CORRESPONDING FIELDS OF TABLE tbl_summary

FROM ztftelat LEFT JOIN ztfzberep

ON ztfzberep~gjahr = st_input-gjahr

AND ztfzberep~poper = st_input-poper

AND ztfzberep_{cntr = ztftelat}rprctr

WHERE rldnr = c_telstra_accounting

AND rrcty = c_actual

AND rvers = c_ver_001

AND rbukrs = st_input-bukrs

AND racct = st_input-saknr

AND ryear = st_input-gjahr

And rzzlstar in r_lstar

AND rpmax = c_max_period.

There are 5 indices present for Table ZTFTELAT.

Indices of ZTFTELAT:

Name Description

0 Primary key( RCLNT,RLDNR,RRCTY,RVERS,RYEAR,ROBJNR,SOBJNR,RTCUR,RUNIT,DRCRK,RPMAX)

005 Profit (RCLNT,RPRCTR)

1 Ledger, company code, account (RLDNR,RBUKRS, RACCT)

2 Ledger, company code, cost center (RLDNR, RBUKRS,RCNTR)

3 Account, cost center (RACCT,RCNTR)

4 RCLNT/RLDNR/RRCTY/RVERS/RYEAR/RZZAUFNR

Z01 Activity Type, Account (RZZLSTAR,RACCT)

Z02 RYEAR-RBUKRS- RZZZBER-RLDNR

Can anyone help me out why it is taking so much time and how we can reduce it ? and also tell me if I want to use index number 1 then how can I use?

Thanks in advance.

[Former Member]

hi sourabh,

it might be due to huge amount of data in your table which is making the Select query to take time.

to my knowledge, you have 2 options here

1) using packet size in select query

2) create a Secondry index on fields rldnr, rrcty, rvers, rbukrs, racct, ryear, rzzlstar and rpmax

regards,

Shano

[Former Member]

Hi,

Database size is not a problem in this case because there is one more table which has similar amount of data and that is working fine.

can you please tell me how can i use index nuber 2 which contains fields "Ledger, company code, account" .

[Former Member]

Hi

Try as below:

1.Declare one more table tbl_summary1

2. Remove appending corresponding statement

3. Remove * and specify the required field as in table position

after fetching use append lines of tbl_summary1 to tbl_summary.

This will improve the performance

Thanks

Shiva

[Former Member]

Hi Shiva,

I am using two more select queries with the same manner ....

here are the other two select query :

***************1************************

SELECT * APPENDING CORRESPONDING FIELDS OF TABLE tbl_summary

FROM ztftelpt LEFT JOIN ztfzberep

ON ztfzberep~gjahr = st_input-gjahr

AND ztfzberep~poper = st_input-poper

AND ztfzberep_{cntr = ztftelpt}rprctr

WHERE rldnr = c_telstra_projects

AND rrcty = c_actual

AND rvers = c_ver_001

AND rbukrs = st_input-bukrs

AND racct = st_input-saknr

AND ryear = st_input-gjahr

and rzzlstar in r_lstar

AND rpmax = c_max_period.

and the second one is

*************************2************************

SELECT * APPENDING CORRESPONDING FIELDS OF TABLE tbl_summary

FROM ztftelnt LEFT JOIN ztfzberep

ON ztfzberep~gjahr = st_input-gjahr

AND ztfzberep~poper = st_input-poper

AND ztfzberep_{cntr = ztftelnt}rprctr

WHERE rldnr = c_telstra_networks

AND rrcty = c_actual

AND rvers = c_ver_001

AND rbukrs = st_input-bukrs

AND racct = st_input-saknr

AND ryear = st_input-gjahr

and rzzlstar in r_lstar

AND rpmax = c_max_period.

for both the above table program is taking very less time .... although both the table used in above queries have similar amount of data. And i can not remove the APPENDING CORRESPONDING. because i have to append the data after fetching from the tables. if i will not use it will delete all the data fetched earlier.

Thanks on advanced......

Sourabh

[Former Member]

Sourabh,

Get the data into separate tables and then use APPEND LINES OF ITAB1 TO ITAB2.

Regards

Shiva

[ThomasZloch]

Quite a number of indexes for one table, so maybe the CBO is not picking the right index (difficult to tell from apart, but it seems number 1 should be chosen). You can find out by running a ST05 SQL trace.

If it turns out that the wrong index is picked, first try updating the DB statistics for ztftelat (system admin task), if that doesn't help, you can think about using a database hint.

For Oracle, this would have to be added to the select statement:

%_hints oracle 'INDEX("ZTFTELAT" "ZTFTELAT~1")'.

Thomas

[former_member194613]

SELECT * 
             APPENDING CORRESPONDING FIELDS OF TABLE tbl_summary
            FROM ztftelat 
            LEFT JOIN ztfzberep
            ON ztfzberep~gjahr = st_input-gjahr
            AND ztfzberep~poper = st_input-poper
            AND ztfzberep~cntr = ztftelat~rprct r
  
           WHERE rldnr = c_telstra_accounting
            AND rrcty = c_actual
            AND rvers = c_ver_001
            AND rbukrs = st_input-bukrs
            AND racct = st_input-saknr
            AND ryear = st_input-gjahr
            And rzzlstar in r_lstar 
            AND rpmax = c_max_period.

What does the SQL Trace tell which index is actually taken

and what are the indexes of ztfzberep, which one is taken there.

How many records are in the two tables?

And what is that?

      ON ztfzberep~gjahr    = st_input-gjahr
      AND ztfzberep~poper = st_input-poper
      AND ztfzberep~cntr    = ztftelat~rprct r

In proper writing ON Conditions should contain only conditions between the two tables.

What you write are condtions on the second table. But conditions on the second table

are not allowed with left outer joins.

Maybe you should rethink your logic.

Siegfried

[former_member217316]

Hi Sourabh

Please follow this:

1) Remove the * and mention the fields that you wish to get in tbl_summary (incase you don't need all the fields from the database table).

2) Don't use the INTO CORRESPONDING FIELDS OF TABLE tbl_summary clause. Instead use INTO TABLE tbl_summary (But take care that during the declaration of the Itab tbl_summary, the fields declared are in order of the fields fetched from the database table).

Hoep this helps.

Regards

Harsh