Thanks to eva one for clearing out my db concepts.
On Jul 20, 3:02 am, ddf <orat...@msn.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
