Re: How to fix cache buffer chain issue

From: Pap <oracle.developer35_at_gmail.com>
Date: Tue, 22 Jun 2021 23:45:58 +0530
Message-ID: <CAEjw_fhQVTzRhWw0D394jiYP=zWtHFvAznDVZvTRG9SwwNm4yA_at_mail.gmail.com>

Thanks much Jonathan.

Trying to visualize/understand the code modification which you suggested. Agreed that the difference in the query is just the date criteria filter on column ADT of table TAD. But currently we are fetching sum(amt) and count(*) based on the grouping column in each of those queries.

So if i just consider the first two UNION queries then are you pointing towards modifying the query something as below? But in this case won't the SUM(AMT) and count(*) columns are now going to appear in the same row whereas in the existing query they were part of different rows?

**********First two UNION clauses of current query***********

INSERT INTO GTT1
     SELECT TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL,  TMCT.PDCMLPNT, sum(tad.amt), count(*)

       FROM TAD TAD, TMMC TMMC,  TMTD TMTD,    TMCT TMCT, TNI TNI
      WHERE     TAD.MCD = TMMC.PTMCD
            AND TMTD.PMENT = TAD.PMENT
            AND TMTD.CID = TMCT.SWCTID
            AND TNI.NE = 'XX'
            AND TNI.NID = TMTD.DID
            AND (TRUNC ( :B2) - ADT) <= 1
            AND (   (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
                 OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
            AND TAD.DT_CR IN (SELECT MAX (DT_CR)  FROM TAD)
   GROUP BY TMTD.PMENT,         TAD.MCD,      TMMC.PMCDVAL,

 TMCT.PDCMLPNT
   UNION
     SELECT TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL,  TMCT.PDCMLPNT, sum(tad.amt), count(*)

       FROM TAD TAD,     TMMC TMMC,   TMTD TMTD,  TMCT TMCT,    TNI TNI
      WHERE     TAD.MCD = TMMC.PTMCD
            AND TMTD.PMENT = TAD.PMENT
            AND TMTD.CID = TMCT.SWCTID
            AND TNI.NE = 'XX'
            AND TNI.NID = TMTD.DID
            AND (TRUNC ( :B2) - ADT) > 1
            AND (TRUNC ( :B2) - ADT) <= 2
            AND (   (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
                 OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
            AND TAD.DT_CR IN (SELECT MAX (DT_CR)     FROM TAD)

   GROUP BY TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT ********Modified query **********

SELECT TMTD.PMENT, TAD.MCD, TMMC.PMCDVAL, TMCT.PDCMLPNT, CASE when (TRUNC ( :B2) - ADT) <= 1 THEN count(*) ADT_1_count,

     when (TRUNC ( :B2) - ADT) > 1 AND (TRUNC ( :B2) - ADT) <= 2 THEN count(*) ADT_1_2_count,...
CASE when (TRUNC ( :B2) - ADT) <= 1 THEN sum(tad.amt) ADT_1_Amount,

     when (TRUNC ( :B2) - ADT) > 1 AND (TRUNC ( :B2) - ADT) <= 2 THEN sum(tad.amt) ADT_1_2_Amount

    ....
FROM TAD,TMMC TMMC, TMTD TMTD, TMCT TMCT, TNI TNI

      WHERE     TAD.MCD = TMMC.PTMCD
            AND TMTD.PMENT = TAD.PMENT
            AND TMTD.CID = TMCT.SWCTID
            AND TNI.NE = 'XX'
            AND TNI.NID = TMTD.DID
            AND (   (TAD.MCD = :B1 AND NVL ( :B1, 'ZZZZ') <> 'ZZZZ')
                 OR NVL ( :B1, 'ZZZZ') = 'ZZZZ')
            AND TAD.DT_CR IN (SELECT MAX (DT_CR)  FROM TAD
Group by TMTD.PMENT,         TAD.MCD,      TMMC.PMCDVAL,      TMCT.PDCMLPNT

Regards
Pap

On Mon, Jun 21, 2021 at 1:36 PM Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

>
> Thanks, Mladen and Andy, for prompting me to look at the original SQL.
>
> Without spending more than a couple of minutes on the code I get the
> impression that it's trying to
> 1) get all the data for a given date DT_CT, then
> 2) summarise it into non-overlapping ranges of adt.
>
> The code seems very repetitive with the main variation being:
> AND (TRUNC ( :B2) - ADT) <= 1
>
> AND (TRUNC ( :B2) - ADT) > 1
> AND (TRUNC ( :B2) - ADT) <= 2
>
> AND (TRUNC ( :B2) - ADT) > 2
> AND (TRUNC ( :B2) - ADT) <= 3
>
> AND (TRUNC ( :B2) - ADT) > 3
> AND (TRUNC ( :B2) - ADT) <= 4
>
> AND (TRUNC ( :B2) - ADT) > 4
> AND (TRUNC ( :B2) - ADT) <= 9
>
> AND (TRUNC ( :B2) - ADT) > 9
> AND (TRUNC ( :B2) - ADT) <= 14
>
> AND (TRUNC ( :B2) - ADT) > 60
> AND (TRUNC ( :B2) - ADT) <= 90
>
> AND (TRUNC ( :B2) - ADT) > 90
>
> A "global" improvement might be to extract all the data for the date and
> then use something like a case expression to sum the data by the required
> data ranges.
>
>
> Regards
> Jonathan Lewis
>
>

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

Received on Tue Jun 22 2021 - 20:15:58 CEST