Re: internal handling of views

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Mon, 7 Aug 2006 09:41:00 -0400
Message-ID: <p6mdnWh6vuhw30rZnZ2dnUVZ_sednZ2d@comcast.com>

"Charles M" <CharlesM_at_nowhere.com> wrote in message news:slrnedefbr.2bk.CharlesM_at_CMORRI12-1.cscfed.root.ad...
: On 2006-08-06, HansF <Fuzzy.Greybeard_at_gmail.com> wrote:
: > On Sun, 06 Aug 2006 10:39:12 -0500, Charles M wrote:
: >
: >
: >> 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).
: >
: > Yes. That is, in fact, what happens.
: >
:
: <snip>
:
: >
: > A view is nothing more than a STORED SELECT. It is evaluated at run
time.
: > Since the details are stored, the optimizer may elect to merge the
: > definition of the view with other information, should the view be used
in
: > a compound situation, such as a join ... it is possible that the
original
: > view may not 'executed' in such a situation.
: >
: > There is no 'data' attached to the view. It is in the table (and only
in
: > the table).
: >
:
: <snip>
:
: >>
: >
: > All of this is in the concepts manual, and can easily be proven through
: > simple tests.
: >
:
:
: Thanks for the reply Hans. The problem I have with the concept manual is
: that it states that a view can be THOUGHT OF (emphisis mine) as a stored
: query or virtual table. Well, when someone says something can be thought
of,
: they usually mean thats not what it truly is, or they would have use IS
: instead of THOUGHT OF. So, I'm unsure if the concept manual is just
talking
: this tact from there on forward or if they really, really mean thats what
it
: is.
:
: Also in discussing mechanics of a view the manual says that sql involving
a
: view is merged with the views definition (its stored sql). Well, thats all
: fine and for selecting from a view, but how does it work for updating a
view?
: You can't do an 'update .. select from' if the from doesn't contain any
data.
: It would have to do (possibly complex) logic to figure out where the
elements
: go, not merely merging a select query. On the other hand, the pointer
: description works just fine for this, and as best I can tell gives all the
: other characteristics of a view as well. But I've only run across the
pointer
: definition once (read somewhere or told by a DBA - don't recall) and every
: book I've seen since mentions the stored query definition. So I'm faced
with
: an answer that makes more sense but from a source that may be suspect, or
the
: more prevalent definition.
:
: Anyway, thanks again for the reply.
:
: CMM
:
:

drop the pointer idea -- it's just plain wrong

look at the contents of DBA_VIEWS (or USER_VIEWS or ALL_VIEWS) to understand some of the complexities of views and their benefits in encapsulating complex SQL -- also, realize that privileges can be granted on views without granting them on the underlying tables, making views an important option when designing security

statements such as this are perfectly legal:

    update (

       select *
       from   emp
       where  deptno = 10
       )

    set sal = sal * 1.1

updating a view that is defined with as such

    create view dept10 as

       select *
       from   emp
       where  deptno = 10

works exactly the same way -- oracle determines which rows currently are qualifed by the SELECT (whether from the sotred view or from the in-line SELECT -- also known as an in-line view) and applies the update to the table

so:

    update dept10 set sal = sal * 1.1 where sal > 1500

is perfectly valid and is actually the equivalent of

    update (

       select *
       from   emp
       where  deptno = 10
       )

    set sal = sal * 1.1
    where sal > 1500

Also recognize that tables are segments (see DBA_SEGMENTS, etc.) since they are assigned storage. Views are not segments since they are not assigned storage. However, materialized views (previously called snapshots) are segments, since they contain a copy of the table(s) data, as specified by the materialzed view definition.

no pointers involved. and oracle documentation is actually more accurate than a stray DBA comment which only makes sense based on partial information and incorrect assumptions.

++ mcs Received on Mon Aug 07 2006 - 08:41:00 CDT