4 Replies Latest reply: Jan 4, 2013 11:08 AM by rp0428 RSS

    Query Index

    873006
      Hi Team

      the below query with is using in procedure with is taking more time this below table have cb_sub_ar_ap have index

      SELECT   trans_num_v, serial_num_n, trans_date_d, account_link_code_n,
               main_account_link_code_n, subsidiary_code_v, db_cr_v, overall_amt_n,
               cleared_amt_n, temp_clr_amt_n, old_yr_adjust_amt_n,
               bill_cleared_flg_v, ROWID
          FROM cb_sub_ar_ap
         WHERE trans_num_v = :b4 || TRIM (:b3)
           AND serial_num_n = :b2
           AND main_account_link_code_n = :b1
           AND bill_cleared_flg_v = 'N'
      ORDER BY trans_date_d
      Index also created
      CREATE UNIQUE INDEX CB_SUB_AR_AP$1 ON CB_SUB_AR_AP
      (TRANS_NUM_V, SERIAL_NUM_N, SUBSIDIARY_CODE_V, ACCOUNT_LINK_CODE_N)
      
      CREATE INDEX CB_SUB_AR_AP$2 ON CB_SUB_AR_AP
      (ACCOUNT_LINK_CODE_N, TRANS_DATE_D)
      
      CREATE INDEX CB_SUB_AR_AP$3 ON CB_SUB_AR_AP
      (MAIN_ACCOUNT_LINK_CODE_N, TRANS_DATE_D)
      
      CREATE INDEX CB_SUB_AR_AP$4 ON CB_SUB_AR_AP
      (ACCOUNT_LINK_CODE_N, DB_CR_V, BILL_CLEARED_FLG_V)
      Please guide where is prob. taking time?

      Edited by: 870003 on Jan 4, 2013 7:27 AM

      Edited by: 870003 on Jan 4, 2013 7:27 AM
        • 1. Re: Query Index
          ranit B
          Did you check the query plan?

          Please do this and post the result :
          -- "Step - [1]"
          EXPLAIN PLAN 
          for
          <your_query>;
          
          -- "Step - [2]"
          SELECT *
           FROM
            TABLE(DBMS_XPLAN.Display);
          • 2. Re: Query Index
            sb92075
            870003 wrote:
            Hi Team

            the below query with is using in procedure with is taking more time this below table have cb_sub_ar_ap have index

            SELECT   trans_num_v, serial_num_n, trans_date_d, account_link_code_n,
            main_account_link_code_n, subsidiary_code_v, db_cr_v, overall_amt_n,
            cleared_amt_n, temp_clr_amt_n, old_yr_adjust_amt_n,
            bill_cleared_flg_v, ROWID
            FROM cb_sub_ar_ap
            WHERE trans_num_v = :b4 || TRIM (:b3)
            AND serial_num_n = :b2
            AND main_account_link_code_n = :b1
            AND bill_cleared_flg_v = 'N'
            ORDER BY trans_date_d
            Index also created
            CREATE UNIQUE INDEX CB_SUB_AR_AP$1 ON CB_SUB_AR_AP
            (TRANS_NUM_V, SERIAL_NUM_N, SUBSIDIARY_CODE_V, ACCOUNT_LINK_CODE_N)
            
            CREATE INDEX CB_SUB_AR_AP$2 ON CB_SUB_AR_AP
            (ACCOUNT_LINK_CODE_N, TRANS_DATE_D)
            
            CREATE INDEX CB_SUB_AR_AP$3 ON CB_SUB_AR_AP
            (MAIN_ACCOUNT_LINK_CODE_N, TRANS_DATE_D)
            
            CREATE INDEX CB_SUB_AR_AP$4 ON CB_SUB_AR_AP
            (ACCOUNT_LINK_CODE_N, DB_CR_V, BILL_CLEARED_FLG_V)
            Please guide where is prob. taking time?

            Edited by: 870003 on Jan 4, 2013 7:27 AM

            Edited by: 870003 on Jan 4, 2013 7:27 AM
            HOW To Make TUNING request
            SQL and PL/SQL FAQ
            • 3. Re: Query Index
              873006
              Thank for Reply but will doing explain plan for the query it is asking some parameter but present i am running in the procedure
              if we execute individual then it taking cost 2 only. but will running in the procedure in toad current statement showing this query from past 1hr.
              • 4. Re: Query Index
                rp0428
                >
                Thank for Reply but will doing explain plan for the query it is asking some parameter but present i am running in the procedure
                if we execute individual then it taking cost 2 only. but will running in the procedure in toad current statement showing this query from past 1hr.
                >
                Then execute the query and post the plan from the V$SQL_PLAN view.
                select plan_table_output
                    from table(dbms_xplan.display_cursor(null,null,'basic'));
                See this article (method #2) by the Oracle optimizer team:
                https://blogs.oracle.com/optimizer/entry/displaying_and_reading_the_execution_plans_for_a_sql_statement