Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Tune select statement

N_RajNov 15 2016 — edited Nov 15 2016

Hi All,

We have the table ACC_REP which has around 7 core record. No Partition on that table.

Index on RES_Value .The below query takes around 20 hours to execute it.

Query

-----------

SELECT   V_D,

           L_ZONE_D,

           pK_ID,

           post_iD,

           CRI_AS_iD,

           AS_DEP_ID,

           BILL_TI,

           RE_TY EV_T,

           PUR_DATe,         

           SUBSTR (PUR_TI, 1, 2) AS PUR_TI,

           MAX (CON_ID) CON_ID

           MAX (SER_ID) SER_ID,

           MAX (GEN) GEN,

           MAX (RATE RATE,

           MAX (AD_IN) AD_IN,

           NULL V_AS_START_DT,

           NULL V_AS_END_DT,

           'Y' IS_BILL,

           COUNT (AS_EVENT_ID) PUR_COUNT,

           SUM (BILL_total) PUR_AMOUNT

    FROM   ACC_REP

   WHERE   RES_Value = 'PUR'

           AND TRUNC (TO_DATE (PUR_DATE, 'dd/mm/yyyy')) >=

                 TRUNC (SYSDATE - 1)

           AND TRUNC (TO_DATE (PUR, 'dd/mm/yyyy')) <=

                 TRUNC (SYSDATE - (1-1))

GROUP BY   V_D,

           L_ZONE_D,

          pK_ID,

           ZIP_CD,

           CRI_AS_iD,

           AS_DEP_ID,

           BILL_TI,

           RE_TY,

           PUR_DATe,

           SUBSTR(PUR_TI,1,2)

how to tune the above one

Thanks,

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 13 2016
Added on Nov 15 2016
10 comments
1,505 views