This is a really puzzling issue—let’s break down why this might be happening and walk through actionable fixes to resolve it.

Possible Root Causes #

1. Optimizer Execution Plan Discrepancy
   Oracle’s cost-based optimizer might choose a different execution path for a standalone SELECT versus CREATE TABLE AS SELECT (CTAS). With nested GROUP BY clauses like in your query, the optimizer could incorrectly omit or push down the outer grouping during CTAS, leading to unaggregated monthly rows instead of the expected single annual summary row.

2. Oracle Version Bug
   Certain older Oracle releases (e.g., some 11g versions) have documented bugs related to nested aggregation operations in CTAS. If you’re running an outdated version, this could be the culprit.

Recommended Fixes & Debugging Steps #

1. Simplify the Query with Conditional Aggregation

Your current query uses two levels of grouping, which can confuse the optimizer. Rewrite it using conditional aggregation in a single GROUP BY clause—this is more efficient and eliminates ambiguity for the optimizer:

CREATE TABLE "U_PR4_1"."MI_ETC_TEMP" AS
SELECT 
  PRJ_OBJECT_ID,
  TO_CHAR(SLICE_DATE, 'YYYY') AS YEAR,
  'ETC' AS UNIT,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '01' THEN SLICE ELSE 0 END) AS JAN,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '02' THEN SLICE ELSE 0 END) AS FEB,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '03' THEN SLICE ELSE 0 END) AS MAR,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '04' THEN SLICE ELSE 0 END) AS APR,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '05' THEN SLICE ELSE 0 END) AS MAY,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '06' THEN SLICE ELSE 0 END) AS JUN,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '07' THEN SLICE ELSE 0 END) AS JUL,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '08' THEN SLICE ELSE 0 END) AS AUG,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '09' THEN SLICE ELSE 0 END) AS SEP,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '10' THEN SLICE ELSE 0 END) AS OCT,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '11' THEN SLICE ELSE 0 END) AS NOV,
  SUM(CASE WHEN TO_CHAR(SLICE_DATE, 'MM') = '12' THEN SLICE ELSE 0 END) AS DECE,
  SUM(SLICE) AS TOTAL
FROM PRJ_BLB_SLICES_M_ETC 
INNER JOIN PRASSIGNMENT PA ON PA.PRID = PRJ_OBJECT_ID 
WHERE SLICE_REQUEST_ID = 113 
  AND TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE, -3), 'YYYY') 
  AND PRJ_OBJECT_ID = 5002239 
GROUP BY PRJ_OBJECT_ID, TO_CHAR(SLICE_DATE, 'YYYY');

This version directly calculates monthly sums within the annual grouping, removing the nested subquery and reducing optimizer confusion.

2. Compare Execution Plans

To confirm if the optimizer is handling grouping differently, generate execution plans for both queries:

For the standalone SELECT:

EXPLAIN PLAN FOR
SELECT PRJ_OBJECT_ID,SUBSTR(YEAR,1,4) YEAR,'ETC' UNIT, SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN, SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCTO) OCT,SUM(NOV) NOV,SUM(DECE) DECE,sum(total) total FROM( SELECT PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') YEAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('01'),SUM(SLICE),0) JAN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('02'),SUM(SLICE),0) FEB, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('03'),SUM(SLICE),0) MAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('04'),SUM(SLICE),0) APR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('05'),SUM(SLICE),0) MAY, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('06'),SUM(SLICE),0) JUN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('07'),SUM(SLICE),0) JUL, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('08'),SUM(SLICE),0) AUG, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('09'),SUM(SLICE),0) SEP, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('10'),SUM(SLICE),0) OCTO, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('11'),SUM(SLICE),0) NOV, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('12'),SUM(SLICE),0) DECE, NVL(SUM(SLICE),0) TOTAL FROM PRJ_BLB_SLICES_M_ETC INNER JOIN PRASSIGNMENT PA ON PA.PRID=PRJ_OBJECT_ID WHERE SLICE_REQUEST_ID=113 AND TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY') and PRJ_OBJECT_ID = 5002239 group by PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') ) group by PRJ_OBJECT_ID,SUBSTR(YEAR,1,4);

For the CTAS:

EXPLAIN PLAN FOR
CREATE TABLE "U_PR4_1"."MI_ETC_TEMP" as ( SELECT PRJ_OBJECT_ID,SUBSTR(YEAR,1,4) YEAR,'ETC' UNIT, SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN, SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCTO) OCT,SUM(NOV) NOV,SUM(DECE) DECE,sum(total) total FROM( SELECT PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') YEAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('01'),SUM(SLICE),0) JAN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('02'),SUM(SLICE),0) FEB, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('03'),SUM(SLICE),0) MAR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('04'),SUM(SLICE),0) APR, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('05'),SUM(SLICE),0) MAY, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('06'),SUM(SLICE),0) JUN, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('07'),SUM(SLICE),0) JUL, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('08'),SUM(SLICE),0) AUG, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('09'),SUM(SLICE),0) SEP, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('10'),SUM(SLICE),0) OCTO, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('11'),SUM(SLICE),0) NOV, DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||TO_CHAR('12'),SUM(SLICE),0) DECE, NVL(SUM(SLICE),0) TOTAL FROM PRJ_BLB_SLICES_M_ETC INNER JOIN PRASSIGNMENT PA ON PA.PRID=PRJ_OBJECT_ID WHERE SLICE_REQUEST_ID=113 AND TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY') and PRJ_OBJECT_ID = 5002239 group by PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') ) group by PRJ_OBJECT_ID,SUBSTR(YEAR,1,4));

Look for differences in how GROUP BY operations are applied. If the CTAS plan skips the outer grouping, that confirms an optimizer issue.

3. Materialize the Inner Query

If the simplified query doesn’t work, split the operation into two steps to force the inner grouping to be materialized first:

-- Create temp table with monthly data
CREATE TABLE temp_monthly_data AS
SELECT PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM') YEAR, 
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'01',SUM(SLICE),0) JAN,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'02',SUM(SLICE),0) FEB,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'03',SUM(SLICE),0) MAR,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'04',SUM(SLICE),0) APR,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'05',SUM(SLICE),0) MAY,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'06',SUM(SLICE),0) JUN,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'07',SUM(SLICE),0) JUL,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'08',SUM(SLICE),0) AUG,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'09',SUM(SLICE),0) SEP,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'10',SUM(SLICE),0) OCTO,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'11',SUM(SLICE),0) NOV,
  DECODE(TO_CHAR(SLICE_DATE,'YYYYMM'),SUBSTR(TO_CHAR(SLICE_DATE,'YYYYMM'),1,4)||'12',SUM(SLICE),0) DECE,
  NVL(SUM(SLICE),0) TOTAL 
FROM PRJ_BLB_SLICES_M_ETC 
INNER JOIN PRASSIGNMENT PA ON PA.PRID=PRJ_OBJECT_ID 
WHERE SLICE_REQUEST_ID=113 
  AND TO_CHAR(SLICE_DATE, 'YYYY') >= TO_CHAR(ADD_MONTHS(SYSDATE,-3), 'YYYY') 
  AND PRJ_OBJECT_ID = 5002239 
GROUP BY PRJ_OBJECT_ID,TO_CHAR(SLICE_DATE,'YYYYMM');

-- Create final aggregated table
CREATE TABLE "U_PR4_1"."MI_ETC_TEMP" AS
SELECT PRJ_OBJECT_ID,SUBSTR(YEAR,1,4) YEAR,'ETC' UNIT, 
  SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN, 
  SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCTO) OCT,SUM(NOV) NOV,SUM(DECE) DECE,sum(total) total 
FROM temp_monthly_data
GROUP BY PRJ_OBJECT_ID,SUBSTR(YEAR,1,4);

-- Clean up temp table
DROP TABLE temp_monthly_data;

This ensures the inner grouping is fully computed before applying the outer aggregation, avoiding any optimizer shortcuts.

4. Check Oracle Version & Apply Patches

If you’re running an older Oracle version (pre-12c), check Oracle Support for known bugs related to nested aggregation in CTAS. Applying the latest patches for your version might resolve the issue.

内容的提问来源于stack exchange，提问作者Nobel63