On 10/11/21 15:49, Lok P wrote:
Hello Listers, We have one database on version 11.2.0.4 of oracle. And below query is spending quite a lot of time while scanning table 'DETAIL' in nested loop path i.e. step-9 below. This table does have an index with the leading column as RID(which is joined column) but still it's going for a 'TABLE ACCESS STORAGE FULL FIRST ROWS' within a nested loop. Not sure if it's just because we are reading a lot of rows from that table or if we are hitting any optimizer restriction. So I want to understand if we can modify this query to make it go for one time full table scan, maybe with a hash join kind of operation, so that this can complete in a faster time?
Table DETAIL having ~1.7million rows in it. And column RID having 31K distinct values in it. And table MASTER having ~34k in it.
SELECT TRIM (rf.fattr3) ,TRIM (rf.fattr) , rf.W_DATE ,rf.CODE ,NVL (SUM (rf.txn_cnt), 0) AS cnt,NVL (SUM (DECODE (TTYP, 'S', 1, -1) * rf.amt),0) amt,count(*) over () count1
FROM PBRF rf
WHERE rf.F_GRP = :b1
AND rf.F_CATG IN ( :b2, :b3)
AND (rf.fattr3, NVL (rf.fattr, '-')) NOT IN
(SELECT /*+ USE_HASH(dtl req) PARALLEL(dtl 16) */ DISTINCT dtl.FL_NM, NVL (dtl.SID, '-')
FROM MASTER req, DETAIL dtl
WHERE req.RID = dtl.RID
AND req.RSTS IN ('XX', 'YY')
AND req.RTYP = :b4
AND dtl.FL_TYP = 'DP')
AND rf.W_DATE BETWEEN TO_DATE ( :b5,'MM/DD/YYYY') AND TO_DATE ( :b6,'MM/DD/YYYY')
GROUP BY rf.CODE,TRIM (rf.fattr),TRIM (rf.fattr3), rf.W_DATE;
