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

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 12 Oct 2004 10:51:09 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF275A9AF8@AABO-EXCHANGE02.bos.il.pqe>

Matthew,

I understand the implications of the different points in time, and that = this could happen if DML is occurring on any of the underlying tables.

That's not the case. I can repeatedly select from the view and get the = exact same number of rows and I can repeatedly drop and re-create the = m-view
and select exactly the same number of rows from it every time.

This is weird.

I'm now thinking it's not actually an m-view problem, but a view = problem. I can also demonstrate the problem this way:

SQL> create table tbl_ttl_tlskwttl nologging as select * from = vu_ttl_tlskwttl;

Table created.

SQL> select count(*) from tbl_ttl_tlskwttl;

  COUNT(*)

---

    540301

SQL> select count(*) from vu_ttl_tlskwttl;

  COUNT(*)

---

    563360

-----Original Message-----

From: Parker, Matthew [mailto:matthewp_at_amazon.com] Sent: Monday, October 11, 2004 6:51 PM
To: Bobak, Mark; Oracle-L Mailing List (E-mail 2) Subject: RE: Problems w/ materialized view.....

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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] 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?

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Tue Oct 12 2004 - 09:46:46 CDT