إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

عاوز اخزن نتيجة استعلام في Meterialized Views


4 رد (ردود) على هذا الموضوع

#1 chairman

chairman

    عضو نشط

  • الأعضــاء
  • 256 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 30 May 2010 - 06:54 AM

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


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


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

اذا ممكن مثال توضيحي
الحمــــــــد لله علــــــــــــى نعـــــــمة الاســــــــــــــــــــــــــلام




(من كان في حاجة أخيه كان الله في حاجته)

(الله في عون العبد ما كان العبد في عون أخيه )

ابـتـسـم .. فرزقك مقسوم, وقدرك محسوم، وأحوال الدنيا لا تستحق الهموم، لأنها بين يدى الحى القيوم

#2 chairman

chairman

    عضو نشط

  • الأعضــاء
  • 256 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 30 May 2010 - 03:10 PM

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


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


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

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


الحمــــــــد لله علــــــــــــى نعـــــــمة الاســــــــــــــــــــــــــلام




(من كان في حاجة أخيه كان الله في حاجته)

(الله في عون العبد ما كان العبد في عون أخيه )

ابـتـسـم .. فرزقك مقسوم, وقدرك محسوم، وأحوال الدنيا لا تستحق الهموم، لأنها بين يدى الحى القيوم

#3 mageed_ahmed

mageed_ahmed

    مشرف قسم الـ DBA

  • فريق الإشراف
  • 956 مشاركة
  • البـلـد: Country Flag
  • الاهتمامات:.

تاريخ المشاركة 30 May 2010 - 04:39 PM






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> 




("واتقوا يوما ترجعون فيه الي اللــــــه")

#4 mageed_ahmed

mageed_ahmed

    مشرف قسم الـ DBA

  • فريق الإشراف
  • 956 مشاركة
  • البـلـد: Country Flag
  • الاهتمامات:.

تاريخ المشاركة 30 May 2010 - 05:01 PM

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 ;

("واتقوا يوما ترجعون فيه الي اللــــــه")

#5 chairman

chairman

    عضو نشط

  • الأعضــاء
  • 256 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 30 May 2010 - 06:28 PM

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




(من كان في حاجة أخيه كان الله في حاجته)

(الله في عون العبد ما كان العبد في عون أخيه )

ابـتـسـم .. فرزقك مقسوم, وقدرك محسوم، وأحوال الدنيا لا تستحق الهموم، لأنها بين يدى الحى القيوم