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).