Comments embedded.
On Jul 18, 1:11 am, weird0 <amiredi...@gmail.com> wrote:
> I would like to know what is the difference between a View and
> Materialized View?
>
> As far as my knowledge goes, view holds the result of select query
> from tables.
Incorrect. 'Regular' views store no data.
> It makes the application faster because the result-set is
> not calculated at run-time.
>
Incorrect. A view is nothing more than a stored query, accessed by
name, which executes when a select is issued against it. Values in
such views are ALWAYS recalculated at run-time so rarely, if ever,
does a 'regular' view improve performance.
> Mview is set to be refreshed on some basis i.e. daily, weekly and
> monthly. as opposed to view.
That is only part of the story. A materialized view is a physical
object containing data generated by a query; many times these queries
include aggregates which could be time-consuming to calculate 'on the
fly'. Since a materialized view is a physical object it can be
indexed and partitioned; a 'regular' view cannot. Materialized views
can be refreshed on a schedule, can be refreshed using either FAST or
COMPLETE mode, can be refreshed on commit or refreshed on demand.
FAST mode refreshes require materialized view logs on the base tables
to track data changes. Materialized views can also be used to enforce
constraints Oracle cannot normally enforce through a defined
constraint, such as having no more tan X number of records with a
given key value (one cannot use aggregates in a check constraint). An
example of a materialized view as a constraint:
SQL> create table product(
2 product_id number not null,
3 product_name varchar2(30) not null
4 );
Table created.
SQL>
SQL> alter table product
2 add constraint product_pk
3 primary key(product_id);
Table altered.
SQL>
SQL> create table prod_type(
2 type_id number not null,
3 type_name varchar2(30) not null
4 );
Table created.
SQL>
SQL> alter table prod_type
2 add constraint prodtype_pk
3 primary key(type_id);
Table altered.
SQL>
SQL> create table department(
2 dept_id number not null,
3 dept_name varchar2(20) not null
4 );
Table created.
SQL>
SQL> alter table department
2 add constraint department_pk
3 primary key(dept_id);
Table altered.
SQL>
SQL> create table dept_type(
2 dept_type_cd number not null,
3 dept_id number not null,
4 type_id number not null
5 );
Table created.
SQL>
SQL> alter table dept_type
2 add constraint dept_type_pk
3 primary key(dept_type_cd);
Table altered.
SQL>
SQL> alter table dept_type
2 add constraint dept_type_uk
3 unique(dept_id, type_id);
Table altered.
SQL>
SQL> create table product_to_type(
2 prod_type_cd number not null,
3 product_id number not null,
4 dept_type_cd number not null
5 );
Table created.
SQL>
SQL> alter table product_to_type
2 add constraint proddepttype_pk
3 primary key(prod_type_cd);
Table altered.
SQL>
SQL> alter table product_to_type
2 add constraint prodid_fk
3 foreign key(product_id)
4 references product;
Table altered.
SQL>
SQL> alter table product_to_type
2 add constraint prodtype_fk
3 foreign key(dept_type_cd)
4 references dept_type;
Table altered.
SQL>
SQL> insert all
2 into product
3 values(100, 'Foo')
4 into product
5 values(200, 'Bar')
6 into prod_type
7 values(1, 'Regular')
8 into prod_type
9 values(2, 'Extra')
10 into department
11 values(10, 'ACCOUNTING')
12 into department
13 values(20, 'SALES')
14 into dept_type
15 values(1, 10, 1)
16 into dept_type
17 values(2, 10, 2)
18 into dept_type
19 values(3, 20, 1)
20 into dept_type
21 values(4, 20, 2)
22 into product_to_type
23 values(101, 100, 1)
24 into product_to_type
25 values(102, 100, 2)
26 into product_to_type
27 values(201, 200, 3)
28 into product_to_type
29 values(202, 200, 4)
30 select * from dual;
14 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create materialized view log on product_to_type with rowid,
(product_id) including new values;
Materialized view log created.
SQL>
SQL> create materialized view prod_type_dept_ck
2 refresh fast on commit
3 as
4 select product_id, count(*) ct from product_to_type
5 group by product_id
6 /
Materialized view created.
SQL>
SQL> alter table prod_type_dept_ck
2 add constraint check_ct
3 check(ct < 3)
4 /
Table altered.
SQL>
SQL> select *
2 from product_to_type;
PROD_TYPE_CD PRODUCT_ID DEPT_TYPE_CD
------------ ---------- ------------
101 100 1
102 100 2
201 200 3
202 200 4
SQL>
SQL> insert
2 into product_to_type
3 values(203, 200, 2);
1 row created.
SQL>
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (BING.CHECK_CT) violated
SQL>
SQL> select *
2 from product_to_type;
PROD_TYPE_CD PRODUCT_ID DEPT_TYPE_CD
------------ ---------- ------------
101 100 1
102 100 2
201 200 3
202 200 4
SQL>
>
> Please confirm.
Sorry, no, your 'information' is wrong.
>
> Regards,
> Software Engineer
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
