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 188.8.131.52, 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