بسم الله الرحمن الرحيم


اخوان لدي استعلام يأخذ بياناته من جدولين ويتضمن عبارة group by , واريد ان اخزن نتيجة هذا الاستعلام في meterialized views من النوع الذي يحدث


نفسه من الجدول الاصلي عندما تتم اضافة بيانات جديدة على الجدول الاصلي.

اذا ممكن مثال توضيحي

بسم الله الرحمن الرحيم


اخوان لدي استعلام يأخذ بياناته من جدولين ويتضمن عبارة group by , واريد ان اخزن نتيجة هذا الاستعلام في meterialized views من النوع الذي يحدث


نفسه من الجدول الاصلي عندما تتم اضافة بيانات جديدة على الجدول الاصلي.

اذا ممكن مثال توضيحي

SQL> desc tst
Name                            Null?    Type
------------------------------- -------- ----
PWD                                      VARCHAR2(100)
ENC_PWD                                  VARCHAR2(200)

SQL> select * from tst;

PWD
----------------------------------------------------------------------------------------------------
ENC_PWD
----------------------------------------------------------------------------------------------------
ahmed.hassan
5C0B9EAF2B7A140907545366F6CE656C


SQL> ed
Wrote file afiedt.buf

 1  CREATE MATERIALIZED VIEW  MVW_TST FOR UPDATE AS
 2* SELECT * FROM TST
SQL> /
SELECT * FROM TST
             *
ERROR at line 2:
ORA-12014: table 'TST' does not contain a primary key constraint


SQL> /

Snapshot created.

SQL> DESC MVW_TST
Name                            Null?    Type
------------------------------- -------- ----
PWD                             NOT NULL VARCHAR2(100)
ENC_PWD                                  VARCHAR2(200)

SQL> SELECT * FROM MVW_TST;

PWD
----------------------------------------------------------------------------------------------------
ENC_PWD
----------------------------------------------------------------------------------------------------
ahmed.hassan
5C0B9EAF2B7A140907545366F6CE656C


SQL> INSERT INTO MVW_TST VALUES('AHMED.MOHAMMED','AAAAAAAAAABBBBBBBBBB');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM MVW_TST ;

PWD
----------------------------------------------------------------------------------------------------
ENC_PWD
----------------------------------------------------------------------------------------------------
ahmed.hassan
5C0B9EAF2B7A140907545366F6CE656C

AHMED.MOHAMMED
AAAAAAAAAABBBBBBBBBB


SQL> DELETE FROM  MVW_TST
 2  ;

2 rows deleted.

SQL> ALTER TABLE MVW_TST
 2   ADD (CURRENT_COUNT  NUMBER);

Table altered.

SQL> DESC TST
Name                            Null?    Type
------------------------------- -------- ----
PWD                             NOT NULL VARCHAR2(100)
ENC_PWD                                  VARCHAR2(200)

SQL> SELECT  PWD    , ENC_PWD        ,COUNT(*) FROM TST GROUP BY  PWD    , ENC_PWD        ;

PWD
----------------------------------------------------------------------------------------------------
ENC_PWD
----------------------------------------------------------------------------------------------------
COUNT(*)
---------
ahmed.hassan
5C0B9EAF2B7A140907545366F6CE656C
       1


SQL> ED
Wrote file afiedt.buf

 1  SELECT  PWD    , ENC_PWD        ,COUNT(*) FROM TST ,DUAL
 2* GROUP BY  PWD    , ENC_PWD
SQL> /

PWD
----------------------------------------------------------------------------------------------------
ENC_PWD
----------------------------------------------------------------------------------------------------
COUNT(*)
---------
ahmed.hassan
5C0B9EAF2B7A140907545366F6CE656C
       1


SQL> INSERT INTO MVW_TST 
 2  SELECT  PWD    , ENC_PWD        ,COUNT(*) FROM TST ,DUAL
 3  GROUP BY  PWD    , ENC_PWD;

1 row created.

SQL> SELECT * FROM  MVW_TST ;

PWD
----------------------------------------------------------------------------------------------------
ENC_PWD
----------------------------------------------------------------------------------------------------
CURRENT_COUNT
-------------
ahmed.hassan
5C0B9EAF2B7A140907545366F6CE656C
           1


SQL> 

Materialized Views
ON COMMIT

In some situations it would be convenient to have Oracle refresh a materialized view automatically whenever changes to the base table are committed. This is possible using the ON COMMIT refresh mode. Here is an example.

create materialized view log on t ;

create materialized view mv
 REFRESH FAST ON COMMIT
 as select * from t
;

select rowid, key, val from mv ;


ROWID                     KEY VAL
------------------ ---------- -----
AAAXNGAAEAAAAasAAA          1 a
AAAXNGAAEAAAAasAAB          2 b
AAAXNGAAEAAAAasAAC          3 c
AAAXNGAAEAAAAasAAD          4

Let's see what happens to the view in the course of an insert operation.

insert into t values ( 5, 'e' );

select rowid, key, val from mv ;


ROWID                     KEY VAL
------------------ ---------- -----
AAAXNGAAEAAAAasAAA          1 a
AAAXNGAAEAAAAasAAB          2 b
AAAXNGAAEAAAAasAAC          3 c
AAAXNGAAEAAAAasAAD          4

Nothing happend yet. Let's issue a COMMIT.

commit;

select rowid, key, val from mv ;


ROWID                     KEY VAL
------------------ ---------- -----
AAAXNGAAEAAAAasAAA          1 a
AAAXNGAAEAAAAasAAB          2 b
AAAXNGAAEAAAAasAAC          3 c
AAAXNGAAEAAAAasAAD          4
AAAXNGAAEAAAAatAAA          5 e

Note how the materialized view was automatically fast refreshed after the COMMIT command. No call to DBMS_MVIEW.REFRESH was required.
Restrictions

Materialized views can only refresh ON COMMIT in certain situations.

1. The materialized view cannot contain object types or Oracle-supplied types.
2. The base tables will never have any distributed transactions applied to them.

The first case produces an error during the CREATE MATERIALIZED VIEW command.

-- this materialized view is not fast refreshable
-- because the materialized view contains an Oracle-supplied type

create materialized view mv2
 REFRESH FAST ON COMMIT
 as select key, val, sys_xmlgen( val ) as val_xml from t
;
 as select key, val, sys_xmlgen( val ) as val_xml from t
                                                       *
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

The second case generates an error when a distributed transaction is attempted on the base table. In the following example materialized view MV (created at the top of this page) was created with REFRESH FAST. Attempting a distributed transaction on its base table, T, will therefore raise an error.

insert into t select key+10, val from T@REMOTE ;
commit;
commit
*
ERROR at line 1:
ORA-02050: transaction 5.21.5632 rolled back, some remote DBs may be in-doubt
ORA-02051: another session in same transaction failed




(REMOTE is a database link which loops back to the current account.)

ON DEMAND materialized views have no such restriction, as the following snippet demonstrates.

alter materialized view mv refresh ON DEMAND ;

insert into t select key+10, val from T@REMOTE ;
commit;

select * from t ;


      KEY VAL
---------- -----
        1 a
        2 b
        3 c
        4
        5 e
       11 a
       12 b
       13 c
       14
       15 e

-- cleanup test data in preparation for next section

delete from t where key >= 5 ;
commit ;

Gotcha

The SQL Language Reference manual says this about the ON COMMIT clause.

"Specify ON COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view."
-- Oracle® Database SQL Language Reference: CREATE MATERIALIZED VIEW

When I first read this I assumed it meant that "REFRESH COMPLETE ON COMMIT" is not allowed. I also assumed that specifying "REFRESH ON COMMIT" is equivalent to specifying "REFRESH FAST ON COMMIT". The following examples prove neither is correct however.

create materialized view mv2
 REFRESH COMPLETE ON COMMIT
 as select key, val from t
;

As we can see the CREATE MATERIALZIED view command succeeded even though COMPLETE, not FAST, was specified with ON COMMIT. The next example examines the behavior of "REFRESH ON COMMIT" without a specified refresh method.

drop materialized view log on t ;

-- fast refreshable materialized views on T can no longer be created on T
-- because it has no materialized view log

drop materialized view mv2 ;

create materialized view mv2
 REFRESH ON COMMIT
 as select key, val from t
;
select rowid, key, val from mv2 ;


ROWID                     KEY VAL
------------------ ---------- -----
AAAXNMAAEAAAAakAAA          1 a
AAAXNMAAEAAAAakAAB          2 b
AAAXNMAAEAAAAakAAC          3 c
AAAXNMAAEAAAAakAAD          4


insert into t values ( 5, 'e' );
commit ;

select rowid, key, val from mv2 ;


ROWID                     KEY VAL
------------------ ---------- -----
AAAXNMAAEAAAAakAAE          1 a
AAAXNMAAEAAAAakAAF          2 b
AAAXNMAAEAAAAakAAG          3 c
AAAXNMAAEAAAAakAAH          4
AAAXNMAAEAAAAakAAI          5 e

The fact that all the rowid's in MV2 changed after the INSERT transaction committed confirms that a complete refresh took place during the commit. "REFRESH ON COMMIT" is not therefore equivalent to "REFRESH FAST ON COMMIT". In fact, when no REFRESH method is specified the default behaviour is "REFRESH FORCE" regardless of whether ON COMMIT is used or not.

Given these observations I can only conclude the documentation is either in error or misleading when it says "specify ON COMMIT to indicate that a fast refresh is to occur".
Cleanup

drop materialized view mv ;

drop materialized view mv2 ;

delete from t where key >= 5 ;
commit ;

بارك الله فيك اخي ماجد ، على هذه الاجابة الممتازة ، اسأل العلي القدير ان يجعل كل حرف من حروف اجابتك ، كوزن جبل احد حسنات في ميزان حسناتك