ORA-00979: Not a GROUP BY expression
Hi All
Environment info:
-------------------------
OS: Redhat (RHEL5) Linux 64 bit
DB: Oracle 10.2.0.3
Grid Control: 10.2.0.4
The query below runs as part of our nightly batch job. From AWR and ADDM report I can see this query has executed 1136 times with an average elapsed time of 3 seconds - so essentially this query consumes about 57 minutes of a 3 hour batch run. A good candidate for SQL tuning.
However, when I run the Tuning Advisor from Grid control I get the error message: ORA-00979: Not a GROUP BY expression
I find this odd as the query does run successfully every night.
Any ideas whether this is a tuning advisor bug? I have run the tuning advisor against a lot of queries in the past and have never come across this issue.
Any suggestions would be appreciated.
Thanks,
Andreas
SELECT fundid, fromentity, movementdate, SUM (unitsin), SUM (unitsout),
SUM (txnunits), toentity, splitpercent, policynumber
FROM (SELECT a.fundid, a.unitholderid AS fromentity,
DECODE (:b2,
'A', a.entrydate + :b1,
'T', a.transactiondate,
'P', a.pricedate,
'D', a.dealingdate
) movementdate,
SUM (unitsin * c.splitpercent / 100) unitsin,
SUM (unitsout * c.splitpercent / 100) unitsout,
SUM ( unitsin * c.splitpercent / 100
- unitsout * c.splitpercent / 100
) txnunits,
c.entityid AS toentity, 100 AS splitpercent,
a.policynumber
FROM uhballedgertbl a, uhintermediarytbl c
WHERE a.fundid = :b6
AND a.entrydate <= :b5
AND a.productid = :b4
AND a.unitholderid = c.unitholderid
AND a.oldtransactionnumber IS NULL
AND c.authrejectstatus = 'A'
AND c.entitytype = 'B'
AND c.defaultintermediary <> 0
AND c.introducer = 0
AND NOT EXISTS (
SELECT 1
FROM periodicloadaccrualtbl b
WHERE b.entityid = :b6
AND b.fromentity = a.unitholderid
AND b.toentity = c.entityid
AND b.referralentitytype = :b9
AND b.productid = :b4
AND a.policynumber = b.policynumber
AND b.fromentitytype = :b8
AND b.toentitytype = :b7)
AND ( ( :b3 <> 0
AND EXISTS (
SELECT 1
FROM criteriasatisfiedentitytbl d
WHERE c.entitytype = 'B'
AND d.entityid = c.entityid
AND d.keystring = :b10)
)
OR NVL (:b3, -1) = 0
)
GROUP BY a.fundid,
a.policynumber,
a.unitholderid,
c.entityid,
DECODE (:b2,
'A', a.entrydate + :b1,
'T', a.transactiondate,
'P', a.pricedate,
'D', a.dealingdate
)
UNION
SELECT a.fundid, a.unitholderid AS fromentity,
DECODE (:b2,
'A', a.entrydate + :b1,
'T', DECODE (:b11,
'A', a.entrydate,
a.transactiondate
),
'P', DECODE (:b11, 'A', a.entrydate, a.pricedate),
'D', DECODE (:b11,
'A', a.entrydate,
a.dealingdate
)
) movementdate,
SUM (unitsin * c.splitpercent / 100) unitsin,
SUM (unitsout * c.splitpercent / 100) unitsout,
SUM ( unitsin * c.splitpercent / 100
- unitsout * c.splitpercent / 100
) txnunits,
c.entityid AS toentity, 100 AS splitpercent,
a.policynumber
FROM uhballedgertbl a, uhintermediarytbl c
WHERE a.fundid = :b6
AND a.entrydate <= :b5
AND a.productid = :b4
AND a.unitholderid = c.unitholderid
AND a.oldtransactionnumber IS NOT NULL
AND c.authrejectstatus = 'A'
AND c.entitytype = 'B'
AND c.defaultintermediary <> 0
AND c.introducer = 0
AND NOT EXISTS (
SELECT 1
FROM periodicloadaccrualtbl b
WHERE b.entityid = :b6
AND b.fromentity = a.unitholderid
AND b.toentity = c.entityid
AND b.referralentitytype = :b9
AND b.productid = :b4
AND a.policynumber = b.policynumber
AND b.fromentitytype = :b8
AND b.toentitytype = :b7)
AND ( ( :b3 <> 0
AND EXISTS (
SELECT 1
FROM criteriasatisfiedentitytbl d
WHERE c.entitytype = 'B'
AND d.entityid = c.entityid
AND d.keystring = :b10)
)
OR NVL (:b3, -1) = 0
)
GROUP BY a.fundid,
a.policynumber,
a.unitholderid,
c.entityid,
DECODE (:b2,
'A', a.entrydate + :b1,
'T', DECODE (:b11,
'A', a.entrydate,
a.transactiondate
),
'P', DECODE (:b11, 'A', a.entrydate, a.pricedate),
'D', DECODE (:b11,
'A', a.entrydate,
a.dealingdate
)
))
WHERE movementdate <= :b12
GROUP BY fundid,
policynumber,
fromentity,
movementdate,
toentity,
splitpercent
ORDER BY fundid, policynumber, fromentity, toentity, movementdate;