How to get NVLs in fast refresh UNION ALL mat views

From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
Date: Wed, 1 Sep 2004 11:36:26 -0500

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

How to get NVLs in fast refresh UNION ALL mat views

Other related posts: