Problems w/ materialized view.....

From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: "Oracle-L Mailing List (E-mail 2)" <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 11 Oct 2004 15:54:20 -0400
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,
 
pmd.pmd_start_date,
 
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 = dbpf.dbsb_id
   AND pms.prmd_id = prmd.prmd_id
   AND prmd.prmd_id = pmd.prmd_id(+)
   AND pmd.pmd_id = gps1.pmd_id(+)
   AND gps1.gps_gap_type(+) = 'OVER'
   AND pmd.pmd_id = gps2.pmd_id(+)
   AND gps2.gps_gap_type(+) = 'ORIG'
   AND prmd.prmd_id = pmna.prmd_id(+)
   AND pmna.prmn_id = prmn.prmn_id(+)
   AND pms.pms_id = pmna1.pms_id(+)
   AND pmna1.prmn_id = prmn1.prmn_id(+)
   AND prmd.prmd_display_flag = 'Y'
   AND pmd.pmd_display_flag(+) = 'Y'
   AND gps1.gps_display_flag(+) = 'Y'
   AND gps2.gps_display_flag(+) = '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 = pmd1.prmd_id AND pmd1.vc_id =
vc.vc_id
      and vc.vc_prog_alias='CITATION')
        OR prmd.prmd_force_display_flag = '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
Problems w/ materialized view.....

Other related posts:
