How to get NVLs in fast refresh UNION ALL mat views 2004-09-01 - By Jesse, Rich
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 (See ORA-12015.ora-code.com) (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@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
