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,