Re: internal handling of views

On 2006-08-06, Mark C. Stock <mcstockX_at_Xenquery> wrote:
>
> "Charles M" <CharlesM_at_nowhere.com> wrote in message
> news:tJCdneITiIafwEjZnZ2dnUVZ_tqdnZ2d_at_giganews.com...
>:I was having a discussion with a fellow wherein he remarked that changing a
>: value in one of a view's originating table(s) causes the views defining
> sql
>: to fire in order to populate the view.
>
> always ask for references and test cases. these statements are not true.
>
>: I was always under the impression that
>: a view element was a pointer (as in the C programming language) back to
> the
>: same memory storage as in the underlying table*, i.e no sql, triggers, etc

<snip>

> are
>: fired - changing a value is really changing the memory storage, and the
> table
>: and view are both pointing back to that memory, so one being changed IS
>: (IS as in 'exactly' not 'the same thing as') the other being changed.
>
> conceptually, true, but as Herod has already posted, a view is nothing other
> than a stored SQL SELECT statement, evaluated when used. so updating the
> source table(s) has no affect on the view until the view is accessed in
> another SQL statement. at that time the data qualified by the view will be
> based on the committed data in the source table(s) at the time of data
> access -- the view's 'dataset' is not affected by subsequent changes
> (committed or not) to the source table(s) unless the view is re-accessed,
> ie., the dataset of the view is not dynamically updated as the source data
> changes.
>

Im not sure what you mean here by conceptually true. If its true then updating the table source would indeed have an instantaneous effect on the view (because the data element is the same).

> technically, the table defines persistent storage on disk (which may be
> buffered in memory) and views are stored SQL SELECT statements
>

Yes I've seen that mentioned in some books (including some by Oracle Press, but I've also seen it the other way, and presumed the books were just making it 'simple' for the reader (or the author really didn't know himself, after all you can be a competent Oracle administrator without having to know how this works). The way I described also make more sense to me. Why bother running a query when there is literally no work to be done(if a view is simply a pointer). Why waste execution time (however small vs totally zero)? The storage of a query definition can't be any less that merely making pointers (and probably needs more storage), so, as best I can determine, there no reason not to do it (via pointers) and, actually, you'ld take a performance hit the other way (with queries). In other words when I heard how this (supposedly) works, it was one of those 'Oh, that's how they're REALLY doing it, not the way a book quickly usually describes it! realizations. To be honest, I expect it would take an Oracle developer (one of the guys who actually write the Oracle code) and probably one who specializes in views, to give a true answer to this. I certainly don't know of any easy way to verify one way or the other, but I was hoping someone here would.*

<snip>

>:
>: CMM
>:
>:
>: * Put another way: if you could shut down the database, and using some
> other
>: method, change the value in that memory storage location of the underlying
>: table(so that the value changed, but NOT through anything or any means in
>: Oracle) and then, also without having Oracle or any triggers,sql,etc
> running,
>: you could somehow look at the value for the view, what would you find
> there?
>
> when you shut down the database, memory is cleared
> assuming you mean disk, it is not supported or recommended to modify data
> blocks outside of the RDBMS or oracle utilities
> put for purpose of illustration, if data of a table is changed by any means,
> the next time the views is accessed, the data from the table would be
> retrieved at that point in time via the view definition's SELECT as if the
> SELECT statement was manually typed in.
>
> views are pull, not push, paths into the data

Not sure what you mean here. You can generally update a view. Only if the structure doesn't support uniquely determining a row back in the originating table(s) is it not allowed. (Although, I was wondering if something like this condition could be somehow used to verify/disprove my original question).
>
> ++ mcs
>
>

• I realize 'materialized views' can't work this way (because they can link to a table on another netork that may not be available when data changes),but a materialized view has always seemed to me to be a view in name only (really works nothing like a view, more in common with replication).