Re: RE: Question on views

Im dont know much about other databases so I dont know how they implement materialized views. All you have to do to make a materialized view 'current is to refresh it. I dont remember the exact syntax. Its on OTN. This could take a while and should be done during off-hours.

The reasoning behind a materialized view is that computing such as SUMS, division, adding, calculations etc... are expensive. So you create a structure that already computes the data for you based off of your existing tables.

This way your queries are faster. However this is 'static'. If your data changes the materialized view does not dynamicly change(dont know if you can set it to dynamicly recalculate or not).

see if your data changes, you need to 'refresh' the view. Couldnt you run a batch to refresh this nightly or weekly?

I dont know what the data dictionary views are for materialized views off the top of my head. can someone help him out here? When you get the names of those views, go see if the view_name in question is there. If so then its a materialized view, if not he is pulling your leg.

>
> From: "Wolfe Stephen S GS-11 6 MDSS/SGSI" <Stephen.Wolfe_at_macdill.af.mil>
> Date: 2003/08/12 Tue AM 11:24:42 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: RE: Question on views
>
> > you mixed a lot of functional information in your question, so
> > its difficult to follow the exact 'oracle' question you are asking.
>
> Sorry, but I'm still learning.
> >
> > if you are using a 'materialized view' you will need to
> > refresh the snapshot? this is used if you have
> > calculations(like sums, etc...) that you want to pre-compute
> > to speed things up and its easy to refresh.
>
> Yes the contractor is using a 'materialized view', I have never heard of
> that kind
> of view, is like a parameterized view with computed columns or what? Is
> the term 'materialized'
> an Oracle specific term?
>
> > see my embedded comments below... and you may want to
> > question the 'consultant' about his level of experience. I've
> > never heard of anyone using the term 'stale'.
> > >
> > > From: "Wolfe Stephen S GS-11 6 MDSS/SGSI"
> > > <Stephen.Wolfe_at_macdill.af.mil>
> > > Date: 2003/08/12 Tue AM 09:39:36 EDT
> > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > Subject: Question on views
> > >
> > > 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'.
> > >
> > << has your logic changed?>>
>
> No, the view should return columns (existing and computed) of static
> data residing across three tables.
>
> >
> > > 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
> >
> > << same thing happens in Oracle. Oracle uses rollbacks to
> > achieve this, SQL Server locks the rows. Has the same effect
> > in answering the question>>
>
> Understood.
>
> > > 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?
> > >
> > << In oracle a standard view is a select statement. what is
> > the background of this 'consultant'. Is he a vendor specific
> > guy or is he an oracle guy?
> >
> > what does he mean by 'stale'? Its hard to judge what your
> > asking. There is too much functional information in your
> > question. Since Im not familiar with your system its hard for
> > me to follow.
>
> I have never heard of the term 'stale' either ...
>
> I think they are just trying to buy time to fix the problem and I keep
> bugging them
> with the 'how' type of questions.
>
> Thanks, your response has been helpful.
>
> >
> > > v/r
> > >
> > > Stephen S. Wolfe, GS-11, DAFC
> > > Data Services Manager
> > > stephen.wolfe_at_macdill.af.mil
> > > (813) 827-9974 DSN 651-9974
> > > --
> --
> 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: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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