1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

【Oracle】MView動作検証 その1〜カラムの追加/削除したら完全リフレッシュできなくなる?

Posted at

結論はMVIEWに使っているカラム以外での追加/削除なら完全リフレッシュに影響は出ない
次は、高速リフレッシュの時はどうなるか。。。だな

MVIEWの参照元となるテーブルを作成
SQL> create table tbl_test
  2  (col1 number, col2 varchar2(30), col3 varchar2(300));

表が作成されました。
MVIEWを作成するための権限を付与
SQL> grant create materialized view to scott;

権限付与が成功しました。
MVIEWを作成
SQL> create materialized view mv_test as select * from tbl_test;

マテリアライズド・ビューが作成されました。

SQL> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

セッションが変更されました。

SQL> col object_name for a20

SQL> select object_name, object_type, status, last_ddl_time from user_objects where object_name = 'MV_TEST';

OBJECT_NAME	     OBJECT_TYPE	     STATUS  LAST_DDL_TIME
-------------------- ----------------------- ------- -------------------
MV_TEST 	     MATERIALIZED VIEW	     VALID   2019/03/02 14:21:11
MV_TEST 	     TABLE		     VALID   2019/03/02 14:21:11
MVIEWのリフレッシュ
SQL> exec dbms_mview.refresh('MV_TEST');

PL/SQLプロシージャが正常に完了しました。

SQL> col owner for a10
SQL> col mview_name for a15
SQL> select owner, mview_name, refresh_mode, last_refresh_date from user_mviews;

OWNER	   MVIEW_NAME	   REFRES LAST_REFRESH_DATE
---------- --------------- ------ -------------------
SCOTT	   MV_TEST	   DEMAND 2019/03/02 14:21:56
MVIEWが参照しているカラムを削除
SQL> desc tbl_test
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COL1						    NUMBER
 COL2						    VARCHAR2(30)
 COL3						    VARCHAR2(300)

SQL> alter table tbl_test drop column col3;

表が変更されました。

SQL> desc tbl_test
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COL1						    NUMBER
 COL2						    VARCHAR2(30)
MVIEWの状態を確認する
SQL> select object_name, object_type, status, last_ddl_time from user_objects where object_name = 'MV_TEST';

OBJECT_NAME	     OBJECT_TYPE	     STATUS  LAST_DDL_TIME
-------------------- ----------------------- ------- -------------------
MV_TEST 	     MATERIALIZED VIEW	     INVALID 2019/03/02 14:21:11
MV_TEST 	     TABLE		     VALID   2019/03/02 14:21:11
MVIEWをリフレッシュする
SQL> exec dbms_mview.refresh('MV_TEST');
BEGIN dbms_mview.refresh('MV_TEST'); END;

*
行1でエラーが発生しました。:
ORA-12008: マテリアライズド・ビューのリフレッシュ・パスでエラーが発生しました。 
ORA-00947: 値の個数が不足しています。 
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2821
ORA-06512: "SYS.DBMS_SNAPSHOT", 行3058
ORA-06512: "SYS.DBMS_SNAPSHOT", 行3017
ORA-06512: 行1
元のカラムを追加してみる
SQL> alter table tbl_test add col3 varchar2(300);

表が変更されました。

SQL> exec dbms_mview.refresh('MV_TEST');

PL/SQLプロシージャが正常に完了しました。

SQL> select owner, mview_name, refresh_mode, last_refresh_date from user_mviews;

OWNER	   MVIEW_NAME	   REFRES LAST_REFRESH_DATE
---------- --------------- ------ -------------------
SCOTT	   MV_TEST	   DEMAND 2019/03/02 14:27:27
カラムを追加してみる
SQL> alter table tbl_test add col4 varchar2(300);

表が変更されました。

SQL> desc tbl_test
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COL1						    NUMBER
 COL2						    VARCHAR2(30)
 COL3						    VARCHAR2(300)
 COL4						    VARCHAR2(300)

SQL> select object_name, object_type, status, last_ddl_time from user_objects where object_name = 'MV_TEST';

OBJECT_NAME	     OBJECT_TYPE	     STATUS  LAST_DDL_TIME
-------------------- ----------------------- ------- -------------------
MV_TEST 	     MATERIALIZED VIEW	     INVALID 2019/03/02 14:27:27
MV_TEST 	     TABLE		     VALID   2019/03/02 14:21:11
*で全カラム参照でMVIEWを作成したのでリフレッシュエラー
SQL> exec dbms_mview.refresh('MV_TEST');
BEGIN dbms_mview.refresh('MV_TEST'); END;

*
行1でエラーが発生しました。:
ORA-12008: マテリアライズド・ビューのリフレッシュ・パスでエラーが発生しました。 ORA-00913: 値の個数が多すぎます。
ORA-00904: "TBL_TEST"."COL4": 無効な識別子です。
ORA-00904: "TBL_TEST"."COL4": 無効な識別子です。
ORA-06512: "SYS.DBMS_SNAPSHOT", 行2821
ORA-06512: "SYS.DBMS_SNAPSHOT", 行3058
ORA-06512: "SYS.DBMS_SNAPSHOT", 行3017
ORA-06512: 行1
一部カラムを指定してMVIEWを作り直す
SQL> drop materialized view MV_TEST;

マテリアライズド・ビューが削除されました。

SQL> create materialized view MV_TEST
  2  as
  3  select col1, col2, nvl2(col3, '1', col3) col3 from tbl_test;


マテリアライズド・ビューが作成されました。

SQL> select object_name, object_type, status, last_ddl_time from user_objects where object_name = 'MV_TEST';

OBJECT_NAME	     OBJECT_TYPE	     STATUS  LAST_DDL_TIME
-------------------- ----------------------- ------- -------------------
MV_TEST 	     MATERIALIZED VIEW	     VALID   2019/03/02 14:34:46
MV_TEST 	     TABLE		     VALID   2019/03/02 14:34:46
MVIEWのリフレッシュ
SQL> exec dbms_mview.refresh('MV_TEST');

PL/SQLプロシージャが正常に完了しました。

SQL> select owner, mview_name, refresh_mode, last_refresh_date from user_mviews;

OWNER	   MVIEW_NAME	   REFRES LAST_REFRESH_DATE
---------- --------------- ------ -------------------
SCOTT	   MV_TEST	   DEMAND 2019/03/02 14:35:20

SQL> select object_name, object_type, status, last_ddl_time from user_objects where object_name = 'MV_TEST';

OBJECT_NAME	     OBJECT_TYPE	     STATUS  LAST_DDL_TIME
-------------------- ----------------------- ------- -------------------
MV_TEST 	     MATERIALIZED VIEW	     VALID   2019/03/02 14:34:46
MV_TEST 	     TABLE		     VALID   2019/03/02 14:34:46
参照元テーブルにカラムを追加
SQL> alter table tbl_test drop column col4;

表が変更されました。

SQL> desc tbl_test
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 COL1						    NUMBER
 COL2						    VARCHAR2(30)
 COL3						    VARCHAR2(300)

SQL> select object_name, object_type, status, last_ddl_time from user_objects where object_name = 'MV_TEST';

OBJECT_NAME	     OBJECT_TYPE	     STATUS  LAST_DDL_TIME
-------------------- ----------------------- ------- -------------------
MV_TEST 	     MATERIALIZED VIEW	     INVALID 2019/03/02 14:34:46
MV_TEST 	     TABLE		     VALID   2019/03/02 14:34:46
MVIEWのリフレッシュ
SQL> exec dbms_mview.refresh('MV_TEST');

PL/SQLプロシージャが正常に完了しました。

SQL> select object_name, object_type, status, last_ddl_time from user_objects where object_name = 'MV_TEST';

OBJECT_NAME	     OBJECT_TYPE	     STATUS  LAST_DDL_TIME
-------------------- ----------------------- ------- -------------------
MV_TEST 	     MATERIALIZED VIEW	     VALID   2019/03/02 14:36:59
MV_TEST 	     TABLE		     VALID   2019/03/02 14:34:46
1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?