8 Replies Latest reply: Jan 21, 2009 10:36 AM by Harsh Talesra RSS

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

Sourabh Batwara
Currently Being Moderated

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 ztfzberepcntr  = ztftelatrprctr

    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.

  • Re: How can i use index in select query.. facing problem with the select qu
    Ravi Shankar
    Currently Being Moderated

    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

  • Re: How can i use index in select query.. facing problem with the select query.
    Shivakumar Hosaganiger
    Currently Being Moderated

    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

    • Re: How can i use index in select query.. facing problem with the select query.
      Sourabh Batwara
      Currently Being Moderated

      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 ztfzberepcntr  = ztftelptrprctr

          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 ztfzberepcntr  = ztftelntrprctr

          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

  • Re: How can i use index in select query.. facing problem with the select query.
    Thomas Zloch
    Currently Being Moderated

    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

  • Re: How can i use index in select query.. facing problem with the select query.
    Siegfried Boes
    Currently Being Moderated
    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

  • Re: How can i use index in select query.. facing problem with the select query.
    Harsh Talesra
    Currently Being Moderated

    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

Actions