Home » RDBMS Server » Performance Tuning » Views
Views [message #64749] Wed, 21 January 2004 04:45 Go to next message
woodchuck
Messages: 12
Registered: May 2003
Junior Member
hello,

should views be used to do anything else but queries?  ie. is it good practice to never use views for updates, inserts, deletes?

also, when are views 'updated' or 'refreshed'?  disregarding the actual query that makes up the view for the moment, but when precisely does a view get updated with current data?.. does the view update itself at the time when the view is queried?  ..or does the view update itself automatically when it's source tables are modified?

thx in advance!

woodchuck
Re: Views [message #64751 is a reply to message #64749] Wed, 21 January 2004 05:24 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi,
a VIEW is nothing but a stored query( I hope you are not talking about materialised views, which actually stores data in itself based on view definition). An Ordinary view acts as a virtual table and does not store data of its own and so there is no refresh. When you query from the view,you are actually querying from the base tables that the view is based on.

For eg)
SQL> create or replace view t_view as select * from t where rownum < 11;

View created.

-- this definition is stored in the data dictionary and is used by Oracle when you query against the view

SQL> select view_name,text  from user_views where view_name='T_VIEW';

VIEW_NAME
------------------------------
TEXT
--------------------------------------------------------------------------------
T_VIEW
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from t where
 rownum < 11

SQL> set autotrace on explain

SQL> select ename from t_view ;    

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER

10 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=70)
   1    0   VIEW OF 'T_VIEW' (Cost=2 Card=10 Bytes=70)
   2    1     COUNT (STOPKEY)
   3    2      <B> TABLE ACCESS (FULL) OF 'T'</B> (Cost=2 Card=14 Bytes=70)

-- as you see ,its the Table T where it gets the data from.



Views can be used to Insert,Update & Delete data , depending on your requirments and with restrictions.For eg to update a join view,it should have atleast one Key preserved table.A key preserved table is one which has unique rows in the join view (ie every primary or unique key in the original table should also be unique in the join view result...its keys are preserved through the join). Also you cannot perform DML on a rows that the view cannot access/ascertain.


SQL> delete from t_view;
delete from t_view
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

-- this is becos we used ROWNUM to restrict to 10 rows and these can be ANY row.

-- now lets be more specific

SQL> create or replace view t_view as select * from t where ename='SMITH';

View created.

SQL> select * from t_view;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

SQL> delete from t_view;

1 row deleted.

SQL> commit;

Commit complete.

SQL>  select * from t_view;

no rows selected

SQL> select * from t where ename='SMITH';

no rows selected

-- now,lets insert a record into the table through the view

SQL> insert into t_view values(1,'TEST','CLERK',7369,sysdate,900,null,20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t_view;

no rows selected

-- the view doesnt return any rows becos the view T_VIEW cant see the record of employee 'TEST', becos its not its definition(ie where ename='SMITH')

SQL> select * from t where ename='TEST';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
         1 TEST       CLERK           7369 21-JAN-04        900
        20

-- but the record is there in the table .



Hope this brings some light.
Thiru
Re: Views [message #64752 is a reply to message #64751] Wed, 21 January 2004 08:00 Go to previous messageGo to next message
woodchuck
Messages: 12
Registered: May 2003
Junior Member
thx Thiru!

up to now, i have always thought an ordinary view was like a temporary table with actual data. from what you described a view is much like a stored procedure! that does explain a lot and helps me to understand my situation.

i have a view that is taking 10-20 minutes long to resolve everytime i use it. this is mainly due to the need to join many large tables and do aggregate functions on them like MAX, DISTINCT, sub queries, NULL comparisons... basically it is a very complex view, and because the tables were all designed in strict 3rd normal form, so my view is very costly.

these tables are large because they are basically keeping status/historical data (ie. information about something that changes with respect to a given date).

should i use materialised views in my situation?... since you said materialised views actually stores data, it sounds like it can help in my situation to improve live performance.. my question i guess, is when or how does materialised views get updated with current data? (ie. is the refreshing of data happen at the time the materialised view is used, at arbitrary intervals that we define, and/or when it's source tables are modified?)

thx in advance!
woodchuck
Re: Views [message #64753 is a reply to message #64752] Wed, 21 January 2004 08:17 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Exactly. The materialized view (also called summary tables ) stores the aggregate information its own segment and hence the joins/max/distinct/group etc need not be performed real time. They can be defined to refresh ON COMMIT(ie when transactions insert/update data on the base tables: there are some restrictions) or ON DEMAND(manually and/or periodally using dbms_jobs). Also depending on the definition of the view, they can be refreshed COMPLETE or FAST(incremental)...

For more details --> Materialized views

-Thiru
Re: Views [message #64754 is a reply to message #64753] Wed, 21 January 2004 09:21 Go to previous message
woodchuck
Messages: 12
Registered: May 2003
Junior Member
thx Thiru, i will definitely give it a try!! :)

woodchuck
Previous Topic: purging of data
Next Topic: Partitioned table, Merge Query
Goto Forum:
  


Current Time: Sat Sep 19 00:37:35 CDT 2020