Re: Execution path having full scan in a nested loop

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 11 Oct 2021 17:21:30 -0400

On 10/11/21 15:49, Lok P wrote:

Hello Listers, We have one database on version 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
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, '-')
 WHERE     req.RID = dtl.RID
AND req.RSTS IN ('XX', 'YY')
AND req.RTYP = :b4
AND dtl.FL_TYP = 'DP')
  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.


Mladen Gogala
Database Consultant
Tel: (347) 321-1217


Other related posts: