Tom:

Would you please let us know how the oracle server process the query when you have CASE clauses in the group by clause?

Would you please show and explain how the oracle server will execute this query?

Also let us know what is the best way to improve this query so that the performance can be improved?

Here is an example of the query:

SELECT
ol.OrganizationLevelLabel AS ORG_LEVEL,
CASE WHEN op.D_OrganizationID > 2 THEN OrganizationDesc
WHEN op.D_OrganizationID = 0 THEN 'Unknown Consumer'
WHEN op.D_OrganizationID = 2 THEN 'Undefined Consumer'
ELSE 'Other Traffic' END AS ORGANIZATION,
SUM(op.BytesSent)/1048576.0 AS SENT_MB,
SUM(op.BytesReceived)/1048576.0 AS RECEIVED_MB,
SUM(op.BytesSent)/1048576.0 + SUM(op.BytesReceived)/1048576.0 AS TOTAL_MB
FROM
dbo.F_OrganizationProfileDaily op,
dbo.Organization o,
dbo.OrganizationLevel ol,
dbo.TimeDay t
WHERE
o.OrganizationID = op.D_OrganizationID
AND ol.OrganizationLevel = 3
AND t.ActualDate = '2004-07-04'
GROUP BY
ol.OrganizationLevelLabel,
CASE WHEN op.D_OrganizationID > 2 THEN OrganizationDesc
WHEN op.D_OrganizationID = 0 THEN 'Unknown Consumer'
WHEN op.D_OrganizationID = 2 THEN 'Undefined Consumer'
ELSE 'Other Traffic' END