0 Replies Latest reply on Jul 15, 2013 1:48 PM by mrm_23

    Query tuning

    mrm_23

      SELECT A.claim_type_cde,

                               A.diag_gid,

                               A.ptnt_age_hip_nbr,

                               A.ptnt_gndr_cde,

                               COUNT(DISTINCT A.prc_rel_gid) prctr_cnt,

                               COUNT(DISTINCT A.ptnt_gid) ptnt_cnt,

                               COUNT(DISTINCT A.claim_gid) claim_cnt

                          FROM p_unprj_diag_ptnt_prctr A

                         WHERE A.mktf_prtn_key BETWEEN prd.agg_strt_key AND prd.agg_end_key

                      GROUP BY A.diag_gid,

                               A.ptnt_age_hip_nbr,

                               A.ptnt_gndr_cde,

                               ROLLUP(A.claim_type_cde);

        

      This query is used in insert statement of 9 procedures.

      All the procedures are using group by clause with different select columns but all the selects are from the same table.

      Each procedure will run in different sessions.

      Table contains huge records, so each procedure taking min 8 hrs to execute.

      I want to increase the performance by using only one select for all the procedures.

      Anybody can give idea to use some other sql statement instead of this to improve performance..

      Thanks in advance