RE: Problems w/ materialized view.....

From: "Parker, Matthew" <matthewp@xxxxxxxxxx>
To: <Mark.Bobak@xxxxxxxxxxxxxxx>, "Oracle-L Mailing List (E-mail 2)" <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 11 Oct 2004 15:51:18 -0700
So the problem is the select from vu_ttl_tlskwttl is at one point and =
time. The create materialized view is at another point in time. If =
anything changed in the underlying tables that made up the original =
view, you could get different counts back. The same would happen for a =
regular materialized view if the table it was based on continued to have =
inserts,updates, and deletes and you only refreshed the materilaized =
view once, then counted both.

I haven't found any related bugs with materilaized views on top of =
views. It hasn't happened in my production environment either we we use =
some of these.

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Bobak, Mark
Sent: Monday, October 11, 2004 12:54 PM
To: Oracle-L Mailing List (E-mail 2)
Subject: Problems w/ materialized view.....

Anyone seen something like this before?
(Solaris8, Oracle 9.2.0.5, 64-bit EE)
SQL> select count(*) from vu_ttl_tlskwttl;

  COUNT(*)
----------
    563360


create materialized view mvu_ttl_tlskwttl nologging as select * from =
vu_ttl_tlskwttl;

Materialized view created.

SQL> select count(*) from mvu_ttl_tlskwttl;

  COUNT(*)
----------
    540301



Now, vu_ttl_tlskwttl is a convential, non-materialized view.
It's definition is the rather nasty looking:
SELECT PMS.DBSB_ID,
       DBPF.VP_ID,
       NVL(prmd.prmd_id, 0) prmd_id,
       NVL(prmd.vd_id,0)    vd_id,
       pms.pms_display_flag,
       NVL (prmd.prmd_overwrite_pm_name, prmd.prmd_original_pm_name) =
title_name,
       DECODE (prmd.prmd_issn,NULL,NULL,SUBSTR(prmd.prmd_issn, 1, =
4)||'-'||SUBSTR(prmd.prmd_issn, 5, 4)) issn,
       prmd.prmd_peer_review_flag, prmd.prmd_journal_code,
       NVL (prmd.prmd_external_id, 0) pq_pmid,
       DECODE(
              RTRIM(
              LTRIM(prmn.prmn_notes||';'||prmn1.prmn_notes)),';', '',
              RTRIM(
              LTRIM(DECODE(NVL(prmn.prmn_notes, '-'),'-', '', =
prmn.prmn_notes || ';') || prmn1.prmn_notes))) notes,
       DECODE (pms.pms_new_title_flag,'Y','New =
title',TLSFCTXT(prmd.prmd_id, dbpf.vp_id, 0) changes,
       NVL (pmd.vc_id, 0) vc_id,
                TO_CHAR(DECODE(pmd.pmd_overwrite_start_date,NULL,
=20
pmd.pmd_start_date,
=20
pmd.pmd_overwrite_start_date),'MM/DD/YYYY') pmd_start_date,
       TO_CHAR(DECODE(pmd.pmd_overwrite_end_date,NULL,
                      tlsfucur (pmd.pmd_end_date,'ORIG', =
prmd.prmd_coverage_ceased_flag, pmd.pmd_permission_expired_flag, 0),
                      tlsfucur (pmd.pmd_overwrite_end_date, 'OVER', =
prmd.prmd_coverage_ceased_flag, pmd.pmd_permission_expired_flag, 0)
               ),'MM/DD/YYYY') pmd_end_date,
        NVL(pmd_embargo_days, 0) embargo_days,
        TO_CHAR(DECODE (gps1.gps_start_date,NULL,
                        gps2.gps_start_date,
                        gps1.gps_start_date),'MM/DD/YYYY')
gap_start_date,
        TO_CHAR(DECODE (gps1.gps_end_date,NULL,
                        gps2.gps_end_date,
gps1.gps_end_date),'MM/DD/YYYY') gap_end_date
  FROM ttl_pm_subsets pms,
       ttl_db_platforms dbpf,
       ttl_print_medias prmd,
       ttl_print_media_comps pmd,
       ttl_gaps gps1,
       ttl_gaps gps2,
       ttl_pm_note_assignments pmna, -- for note from PRMD_id
       ttl_print_media_notes prmn,
       ttl_pm_note_assignments pmna1, -- for notes from PMS_id
       ttl_print_media_notes prmn1
 WHERE pms.dbsb_id =3D dbpf.dbsb_id
   AND pms.prmd_id =3D prmd.prmd_id
   AND prmd.prmd_id =3D pmd.prmd_id(+)
   AND pmd.pmd_id =3D gps1.pmd_id(+)
   AND gps1.gps_gap_type(+) =3D 'OVER'
   AND pmd.pmd_id =3D gps2.pmd_id(+)
   AND gps2.gps_gap_type(+) =3D 'ORIG'
   AND prmd.prmd_id =3D pmna.prmd_id(+)
   AND pmna.prmn_id =3D prmn.prmn_id(+)
   AND pms.pms_id =3D pmna1.pms_id(+)
   AND pmna1.prmn_id =3D prmn1.prmn_id(+)
   AND prmd.prmd_display_flag =3D 'Y'
   AND pmd.pmd_display_flag(+) =3D 'Y'
   AND gps1.gps_display_flag(+) =3D 'Y'
   AND gps2.gps_display_flag(+) =3D 'Y'
   AND pms.pms_logical_delete_date IS NULL
   AND pmd.pmd_logical_delete_date(+) IS NULL
   AND prmd.prmd_logical_delete_date IS NULL
   AND (   EXISTS ( SELECT 'X'
                      FROM ttl_print_media_comps =
pmd1,ttl_valid_components vc
                     WHERE pmd.prmd_id =3D pmd1.prmd_id AND pmd1.vc_id =
=3D vc.vc_id
      and vc.vc_prog_alias=3D'CITATION')
        OR prmd.prmd_force_display_flag =3D 'Y'
       );

No, I didn't write it, just trying to understand it all gives me a =
headache.

But, bottom line, I'm thinking this is a bug.  Can anyone see any =
circumstance by which this is not a bug?


--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
RE: Problems w/ materialized view.....

Other related posts:
