由于complex is null或条件,cognos multi-fact stitch查询将永远运行

我正试图用CognosAnalytics11.0.9解决一个失控的查询问题,这个问题是针对Oracle12C星型模式的。失控查询来自一个报告framework manager包的仪表板,涉及多事实计算。正如预期的那样,cognos生成使用公共表表达式查询虚拟事实表的sql,然后使用“stitch查询”基于公共一致维度合并这些结果。

特定的失控查询有4个事实表和3个公共维度。我们的oracle dba说where子句中的or条件导致oracle在计算公共表表达式之前尝试联接事实表,这非常昂贵。

or条件似乎被设计为处理公共维度中的空值。在我的数据模型中,不存在空值。 有没有办法让cognos生成假定不存在空值的多事实sql?

更多细节…为了可读性,我对此进行了一些编辑,但这些查询看起来像:

WITH "FS1" AS (
  SELECT dim.fiscal_year as fiscal_year, SUM(fact1.cost) as cost
  FROM fact1
  INNER JOIN dim on fact1.dim_key = dim.dim_key
  GROUP BY fiscal_year
)
WITH "FS2" AS (
  SELECT dim.fiscal_year as fiscal_year, SUM(fact2.cost) as cost
  FROM fact2
  INNER JOIN dim on fact2.dim_key = dim.dim_key
  GROUP BY fiscal_year
)
WITH "FS3" AS (
  SELECT dim.fiscal_year as fiscal_year, SUM(fact3.cost) as cost
  FROM fact3
  INNER JOIN dim on fact3.dim_key = dim.dim_key
  GROUP BY fiscal_year
)
SELECT COALESCE("FS1".fiscal_year, "FS2".fiscal_year, "FS3".fiscal_year) AS fiscal_year,
  ("FS1".cost + "FS2".cost) +"FS3".cost AS cost
FROM "FS1" FULL OUTER JOIN "FS2" ON
  ("FS1".fiscal_year = "FS2".fiscal_year OR ("FS1".cost IS NULL AND"FS2".cost IS NULL)) 
FULL OUTER JOIN "FS3" ON
  COALESCE( "FS2".cost, "FS1".cost) = "FS3".cost OR (COALESCE("FS2".cost, "FS1".cost) IS NULL AND "FS3".cost IS NULL)
FETCH FIRST 3001 ROWS ONLY