RE: Question on views

Thank you very much. Come to find our the MV the ICDB folks were using did not have 'stale' data in it, but it flat out had the wrong data in it because they used the wrong selection parameters.

So essentially a MV is a SQL select stored to a table, kinda of like:

SELECT * FROM MyTable INTO MyOtherTable

v/r

Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
stephen.wolfe_at_macdill.af.mil
(813) 827-9974 DSN 651-9974
> -----Original Message-----
> From: Pardee, Roy E [mailto:roy.e.pardee_at_lmco.com]
> Sent: Tuesday, August 12, 2003 6:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Question on views
>
>
> From the concepts manual:
>
> <quote>
> Materialized Views
>
> A materialized view provides indirect access to table data by
> storing the results of a query in a separate schema object.
> Unlike an ordinary view, which does not take up any storage
> space or contain any data, a materialized view contains the
> rows resulting from a query against one or more base tables
> or views. A materialized view can be stored in the same
> database as its base table(s) or in a different database.
>
> Materialized views stored in the same database as their base
> tables can improve query performance through query rewrites.
> Query rewrites are particularly useful in a data warehouse
> environment. </quote>
>
> Roy Pardee
> Programmer/Analyst/DBA
> SWFPAC Lockheed Martin IT
> Extension 8487
>
> -----Original Message----- [mailto:Stephen.Wolfe_at_macdill.af.mil]
> Sent: Tuesday, August 12, 2003 8:25 AM
> To: Multiple recipients of list ORACLE-L
>
>
> It is confirmed it is a materialized view. How can they go
> stale? What is the difference in them and a traditional view?
>
> v/r
>
> Stephen S. Wolfe, GS-11, DAFC
> Data Services Manager
> stephen.wolfe_at_macdill.af.mil
> (813) 827-9974 DSN 651-9974
>
>
>
> > -----Original Message-----
> > From: Hately, Mike (LogicaCMG) [mailto:mike.hately_at_nedl.co.uk]
> > Sent: Tuesday, August 12, 2003 10:04 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: Question on views
> >
> >
> > Stephen,
> >
> > Traditional views don't go stale though they can be made
> > invalid if the underlying objects change. This shouldn't
> > really be happening on a regular basis unless the schema is
> > changing rapidly.
> > He may be using a materialized view. These can go stale.
> > Before going any further ask him which of these is the cause
> > and get the name of the view.
> > I'd have thought that if a materialized view was created by
> > his application then it's his responsibility.
> >
> > Cheers,
> > Mike Hately,
> > Consultant DBA
> >
> > -----Original Message----- [mailto:Stephen.Wolfe_at_macdill.af.mil]
> > Sent: 12 August 2003 13:40
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I just had a contractor tell me that the problem with my
> > Diabetic detection and Listing 'feature' in their product
> > (Integrated Clinical Database, ICDB) is because the 'view'
> > crafted for that detection is going 'stale'.
> >
> > I admit that most my SQL design experience is in MSSQL's
> > T-SQL although I'm starting to come up to speed on SQL*Plus,
> > anyway, the above explanation provided to be from the TriCare
> > guy seems bogus.
> >
> > Simply put there are four or five good Lab Chemistry
> > parameters one could use in a SQL select statement to
> > determine if a patient is diabetic or a diabetic candidate
> > without having specific ICD9 diagnosis coding that declares
> > the patient diabetic.
> >
> > A select statement returns a cursor of data that meets the
> > selection criteria and on MSSQL is a static snapshot of what
> > is in the database at the time the query was executed. Thus,
> > if a patient had parameters that met diabetic conditions
> > stored in the database over the past year, how can a view go
> > 'stale'? Is this just a Oracle peculiarity or am I getting a
> > smoke screen as I suspect?
> >
> > v/r
> >
> > Stephen S. Wolfe, GS-11, DAFC
> > Data Services Manager
> > stephen.wolfe_at_macdill.af.mil
> > (813) 827-9974 DSN 651-9974
> >
> >
> > **************************************************************
> > ******************************
> > E mail Disclaimer
> >
> > You agree that you have read and understood this disclaimer
> > and you agree to be bound by its terms.
> >
> > The information contained in this e-mail and any files
> > transmitted with it (if any) are confidential and intended
> > for the addressee only. If you have received this e-mail in
> > error please notify the originator.
> >
> > This e-mail and any attachments have been scanned for certain
> > viruses prior to sending but CE Electric UK Funding Company
> > nor any of its associated companies from whom this e-mail
> > originates shall be liable for any losses as a result of any
> > viruses being passed on.
> >
> > No warranty of any kind is given in respect of any
> > information contained in this e-mail and you should be
> > aware that that it might be incomplete, out of date or
> > incorrect. It is therefore essential that you verify all such
> > information with us before placing any reliance upon it.
> >
> > CE Electric UK Funding Company
> > Lloyds Court
> > 78 Grey Street
> > Newcastle upon Tyne
> > NE1 6AF
> > Registered in England and Wales: Number 3476201
> >
> > **************************************************************
> > ******************************
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Hately, Mike (LogicaCMG)
> > INET: mike.hately_at_nedl.co.uk
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web
> hosting services
> >
> ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in the message BODY, include a line containing: UNSUB
> > ORACLE-L (or the name of mailing list you want to be removed
> > from). You may also send the HELP command for other
> > information (like subscribing).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Wolfe Stephen S GS-11 6 MDSS/SGSI
> INET: Stephen.Wolfe_at_macdill.af.mil
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in the message BODY, include a line containing: UNSUB
> ORACLE-L (or the name of mailing list you want to be removed
> from). You may also send the HELP command for other
> information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Pardee, Roy E
> INET: roy.e.pardee_at_lmco.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> and in the message BODY, include a line containing: UNSUB
> ORACLE-L (or the name of mailing list you want to be removed
> from). You may also send the HELP command for other
> information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfe Stephen S GS-11 6 MDSS/SGSI
  INET: Stephen.Wolfe_at_macdill.af.mil

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).