Re: internal handling of views

From: HansF <Fuzzy.Greybeard_at_gmail.com>
Date: Sun, 06 Aug 2006 21:06:47 GMT
Message-ID: <pan.2006.08.06.21.09.19.447225@gmail.com>

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.

Of course, due to Oracle's Consistent Read model, a query by a second session on the view (or on the table) will not reflect the update until the commit is issued.

A simple test will prove this. You should be able to devise such a test in moments to provde it to yourself. Feel free to use the following as a template for your own test:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> rem Create a test user that can have tables and views
SQL> -----------------------------------------
SQL> create user test identified by test account unlock

2 default tablespace users temporary tablespace temp 3 quota unlimited on users;

User created.

SQL> grant create session, create table, create view to test;

Grant succeeded.

SQL> connect test/test
Connected.

SQL> rem Create a table with some data
SQL> -----------------------------------------
SQL> create table test1 as select * from all_objects;

Table created.

SQL> desc test1

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select count(*) from test1;

COUNT(*)

5290

SQL> select object_type, count(object_type) 2 from test1
3 group by object_type;

OBJECT_TYPE COUNT(OBJECT_TYPE)

------------------- ------------------
CONSUMER GROUP                       2
SEQUENCE                             7
SCHEDULE                             1
PROCEDURE                           22
OPERATOR                            45
WINDOW                               2
PACKAGE                            245
LIBRARY                             15
XML SCHEMA                          13
JOB CLASS                            1
TABLE                               71

OBJECT_TYPE         COUNT(OBJECT_TYPE)
------------------- ------------------
SYNONYM                           2765
VIEW                              1166
FUNCTION                           158
WINDOW GROUP                         1
INDEXTYPE                            8
TYPE                               767
EVALUATION CONTEXT                   1

18 rows selected.

SQL> select distinct(status) from test1
2 where object_type='TABLE';

STATUS

VALID

SQL> rem Create a view that reflects that select statement
SQL> rem ----------------------------------
SQL> create view test_view as

2 select distinct(status) from test1
3 where object_type='TABLE';

View created.

SQL> select * from test_view;

STATUS

VALID

SQL> Update the table and checkwether the view is 'instantly updated'
SQL> rem ----------------------------------
SQL> update test1 set status='FUNNY'

2 where object_type='TABLE'
3 and rownum<25;

24 rows updated.

SQL> select * from test_view;

STATUS

FUNNY
VALID

SQL> Note there is no commit to this point.  Therefore, a second session
SQL> (even one logged on as test) will not see the changed data from
SQL> either the view or from the underlying table.
SQL> rem ----------------------------------

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

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

A materialized view is a view (stored select) that is materialized - that is, the select is 'pre-resolved' into real data. The update of the materialized view depends on settings and may happen immediately when the underlying tables are updated, or on demand.

Materialized views are only useful when the optimizer is permitted to rewrite the user's select. If permitted, it will look through the library of materialized views and - if feasible - it will use the pre-resolved select information instead of going to the underlying table.

All of this is in the concepts manual, and can easily be proven through simple tests.

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.

Received on Sun Aug 06 2006 - 16:06:47 CDT