Re: Execution path having full scan in a nested loop

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;

BTW, this looks like a good candidate for a rewrite with "NOT EXISTS". Also, using "DISTINCT" in a subquery is a really bad idea.

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

--
http://www.freelists.org/webpage/oracle-l