How to get NVLs in fast refresh UNION ALL mat views

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Wed, 1 Sep 2004 11:36:26 -0500
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E93CFC@qtiexch2.qgraph.com>

Hey all,

I'm trying to utilize fast refresh materialized views in 9.2.0.5.0 in = order to prevent yet another senseless weekly/daily/hourly query table = rebuild. Following the rules for fast refresh UNION ALL MVs, I've got = something that looks like this:

SELECT

	'1' "MV_MARKER",
	partno,
	SUM(CASE WHEN fy_quarter IN ('01','02','03') THEN qty ELSE 0 END) "Q1",
	COUNT(CASE WHEN fy_quarter IN ('01','02','03') THEN qty ELSE 0 END) =


"Q1_COUNT",

	SUM(CASE WHEN fy_quarter IN ('04','05','06') THEN qty ELSE 0 END) "Q2",
	COUNT(CASE WHEN fy_quarter IN ('04','05','06') THEN qty ELSE 0 END) =


"Q2_COUNT",

	COUNT(*) "MV_COUNT"

FROM myschema.inv_history
WHERE
warehouse =3D 'AB'
GROUP BY
partno
UNION ALL
SELECT

	'2' "MV_MARKER",
	partno,
	SUM(CASE WHEN fy_quarter IN ('01','02','03') THEN qty ELSE 0 END) "Q1",
	COUNT(CASE WHEN fy_quarter IN ('01','02','03') THEN qty ELSE 0 END) =


"Q1_COUNT",

	SUM(CASE WHEN fy_quarter IN ('04','05','06') THEN qty ELSE 0 END) "Q2",
	COUNT(CASE WHEN fy_quarter IN ('04','05','06') THEN qty ELSE 0 END) =


"Q2_COUNT",

	COUNT(*) "MV_COUNT"

FROM myschema.so_history
WHERE
warehouse =3D 'BA'
GROUP BY
partno

While it works great, I think it's technically inaccurate. I would = prefer changing all the "ELSE 0 END"s to "ELSE NULL END"s. This doesn't = appear to affect the aggregates, but the counts are now accurate. = Previously, the three COUNT buckets for each row held the same value. = With the "ELSE NULL", each COUNT now reflects the actual count of the = bucket it represents.

The problem is the resulting NULLs in the table. This seems like it = could cause some problems for the end user (possibly thru ad-hoc query = tools). And, since the docs don't seem to mention, I'm guessing that = the COUNTs are used to determine MV refreshes. If these COUNTs are = high, does that adversely affect the refresh performance?

To try and get around this problem, I wrapped each SUM and COUNT with an = NVL. Of course, this now breaks the requirements of a fast refresh and = ends up with a ORA-12015 (can't create fast refresh w/complex query).

I had thought about a view to the MV with NVLs, but I'd much rather fix = the issue than hide it. Or am I just overly worried about the NULLs and = this isn't a problem at all? Anyone?

TIA!
Rich

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Sep 01 2004 - 17:21:21 CDT