データの整合性を監視する手段としてのマテリアライズドビュー

データ整合性制御は、最も重要なDBMS機能の1つです。 この制御を慎重に編成すればするほど、適用されるロジックの実装が容易になります。データベースにより多くの制限が制御されるため、ロジックの実装時に提供される「what if」のバリエーションが少なくなります。 同時に、整合性の監視は、アプリケーション層の正しい動作を検証するために使用すると非常に便利です。 単体テストのようなもの。 「余分な」チェックは時々非常に良い仕事をすることができます。



従来の一連の制限-プライマリ、外部キーの制限、正規化を使用する場合の一意性は、制御ニーズの大部分のケースを満たすことができます。 ただし、制限が複数のテーブルと行の値に依存していることが判明した場合、これらの手段は十分ではありません。 このような制限は、トリガーロジックによって実装する必要があります。 また、実装は常に単純ではありません。 開発者は、競合環境でデータ変更を実行できることを念頭に置いておく必要があります。そのため、デッドロックを回避しようとしながら、リソースを自分でブロックする必要があります。 行制限を実装するには、同じテーブルの他の行へのアクセスが必要になる場合があります。これは、プラットフォームの制限です。Oracleは、現在変更されている（変更している）データセットへのアクセスを許可しません。



しかし、別の方法があります。 場合によっては、トランザクションの修正（コミット時の高速リフレッシュ）によって更新される実体化された表現に課せられた制限を使用することができます。 このような制限は据え置きとして機能し、突然データの整合性が侵害された場合、トランザクションをコミットできません。 変更トランザクションのフレームワーク内では、制限に違反する可能性があります。 これにより、データの変更が簡単になり、エラーの原因を特定することが難しくなります。 この記事では、このような制限の実装のいくつかの簡単な例を挙げたいと思います。

問題の声明

架空の簡単な例を使用して、アプローチの実装を示したいと思います。 そのような例を選択するのは十分に難しいので、知覚するのに十分簡単でしたが、同時に、アプローチの適用が正当化されるように、突然何かが起こったとしても私を責めないでください。



配置ゾーンのコンテキストで商品を考慮する必要があるとします。 この場合の配置は、店舗（S）または倉庫（W）です。



ゾーン-各特定の場所の物理的または論理的な領域。 たとえば、トレーディングフロア、またはトレーディングフロアの棚、材料室、冷蔵庫、失われた商品のゾーン。 各ロケーションには、各タイプの複数のゾーンを設定できますが、各タイプの1つのゾーンをプライマリとしてマークする必要があります。 操作のゾーンが明示的に定義されていない場合、デフォルトで使用されます。 メインゾーンは、ゾーンのタイプごとに1つだけである必要があります。 これは、実装しようとする最初のタイプの制限になります。



2番目のタイプの制限は、ゾーンの構成です。 この例では、次の3つのタイプのゾーンのルール設定に制限しています。

• 保管ゾーン（K）-商品が保管されるゾーン;このゾーンは倉庫には必須ですが、店舗に対して定義することもできます。
• 販売エリアの面積（S）-店舗に必要であり、倉庫に対して決定することはできません
• 失われた商品のゾーン（L）-ステータスが明確ではない商品の移動先の論理ゾーンには、明確化が必要です。 倉庫と店舗の両方に必要

各配置レコードには、ドラフト（W）、アクティブ（A）、非アクティブ（I）の3つの状態があります。 レコードが「ドラフト」状態の場合、ユーザーに最大の行動の自由を与え、この制限に違反することを許可します。

実装

テーブル

create table location ( loc number primary key ,loc_type varchar2(1 char) check (loc_type in ('S','W')) ,status varchar2(1 char) not null check (status in ('W','A','I')) ,loc_desc varchar2(200 char) ); create table zone( zone number primary key ,loc number references location(loc) ,is_pirmary varchar(1 char) not null check (is_pirmary in ('Y','N')) ,zone_type varchar2(1 char) not null ,zone_desc varchar2(200 char) ); insert into location values (1,'S','W',' 1  '); insert into zone values (1,1,'Y','K','   1'); insert into zone values (2,1,'Y','S','   1'); insert into zone values (3,1,'Y','L','    1'); commit;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

メインゾーンの数を制限する

この制限を実装するには、各タイプの配置ゾーンのメインゾーンを計算する実体化された表現を作成し、計算値の単位との厳密な同等性を制御する制約を上に課します。 クエリでは、どの実体化された表現が構築されるかに基づいて、いくつかの制限が定義され、さらに、 高速メソッドを使用して更新するための要件を課すことによって大幅に強化されます 。 この場合、集計されたマテリアライズドビューがあるため、rowidと新しい値を含むゾーンテーブルのマテリアライズドビューログを作成する必要があります。フィールドのリストには、クエリ結果に影響を与える可能性のあるすべての値が含まれます

 create materialized view log on zone with rowid ,sequence (zone,loc,zone_type,is_primary) including new values
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

また、リクエストによって返される結果に値「count（*）」を含める義務があります

  create materialized view mv$zoneloc_pimary$chk refresh fast on commit as select loc ,zone_type ,count(decode(is_primary,'Y',1)) primary_count ,count(*) cnt from zone group by loc,zone_type;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ここで注意する必要があります：要求に基づいて構築されたマテリアライズドビューが高速な更新方法を使用できるかどうかを評価するために、 dbms_mivew.explain_mviewプロシージャが存在します。 高速更新方法をプレゼンテーションに使用できるかどうかを制御するために使用することを強くお勧めします。 たとえば、リクエストでカウント（*）を指定するのを忘れた場合、マテリアライズドビューは正常に作成され、挿入操作の実行時に正しく機能します。 ただし、変更、削除時に、primary_countの値は再カウントされず、制限のロジックに違反します。 ただし、explain_mviewを使用する場合、Oracleは計算ミスを報告するのに役立ちます。

  SQL> set serveroutput on SQL> declare 2 result SYS.ExplainMVArrayType; 3 begin 4 dbms_mview.explain_mview(mv => 'select loc 5 ,zone_type 6 ,count(decode(is_primary,''Y'',1)) primary_counnt 7 --,count(*) cnt 8 from zone 9 group by loc,zone_type' 10 ,msg_array => result 11 ); 12 for i in 1..result.count 13 loop 14 dbms_output.put(rpad(result(i).capability_name,30,' ')); 15 dbms_output.put(' '||result(i).POSSIBLE); 16 dbms_output.put(' '||result(i).MSGTXT); 17 dbms_output.put_line(null); 18 end loop; 19 end; 20 / PCT F REFRESH_COMPLETE T REFRESH_FAST T REWRITE T PCT_TABLE F relation is not a partitioned table REFRESH_FAST_AFTER_INSERT T REFRESH_FAST_AFTER_ONETAB_DML F COUNT(*) is not present in the select list REFRESH_FAST_AFTER_ANY_DML F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled REFRESH_FAST_PCT F PCT is not possible on any of the detail tables in the materialized view REWRITE_FULL_TEXT_MATCH T REWRITE_PARTIAL_TEXT_MATCH T REWRITE_GENERAL T REWRITE_PCT F general rewrite is not possible or PCT is not possible on any of the detail tables PCT_TABLE_REWRITE F relation is not a partitioned table PL/SQL procedure successfully completed
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

したがって、マテリアライズドビューが作成され、制限を追加するためだけに残ります。

  alter table mv$zoneloc_pimary$chk add constraint zone_loc_primary$chk check (primary_count=1) deferrable initially deferred;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

制限は遅延して作成されることに注意してください。 実際、Oracleによるプレゼンテーションの更新プロセスの途中で、一時的に制限に違反することが判明する場合があります。 このような誤検知を回避するには、そのような制限を意図的に遅らせることをお勧めします。



この制限の動作を確認してください

  SQL> insert into location values (2,'S','W',' 2     '); 1 row inserted SQL> commit; Commit complete
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

「メイン」マークがないタイプのゾーンを作成してみましょう。

  SQL> insert into zone values (4,2,'N','S','    '); 1 row inserted SQL> commit; ORA-02091: transaction rolled back ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同じタイプの配置用の2つのメインゾーンを識別しようとします。

  SQL> insert into zone values (5,2,'Y','L','    '); 1 row inserted SQL> commit; Commit complete SQL> insert into zone values (6,2,'Y','L','    '); 1 row inserted SQL> commit; ORA-02091: transaction rolled back ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

宿泊施設の構成を制限する

前の制限とこの制限は、1つのテーブルではなく2つのテーブルの値に依存するという点で異なります。 つまり 同時に、 接続および集約ビューを 持つビューの高速更新メソッドの要件を満たす必要があります 。 しかし、これは不可能です。 アタッチされた行のROWIDと結果へのカウント（*）を同時に出力することはできません。 このため、実体化された表現のカスケードを構築する必要があります。 一方ではデータセットが接続され、もう一方では集約されます。



最初に、アロケーションテーブルのマテリアライズされたveiwログを作成する必要があります。 ゾーンテーブルの場合、以前に作成されたログが使用されます。

 create materialized view log on location with rowid ,sequence (loc,loc_type,status) including new values;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

次に、join mivewを作成します。 残念ながら、ANSIはここでは構文を認識しません。古いスタイルの結合を使用します。

  create materialized view mv$location$zone$join refresh fast on commit as select l.loc ,l.loc_type ,z.zone ,z.zone_type ,l.rowid l_rowid ,z.rowid z_rowid from location l ,zone z where z.loc(+) = l.loc and l.status in ('A','I')
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

マテリアライズされたveiwログを作成してビューに参加する

 create materialized view log on mv$location$zone$join with rowid ,sequence (loc,loc_type,zone_type) including new values;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

集計マテリアライズドビューを作成する

  create materialized view mv$location$zone$agg refresh fast on commit as select loc ,loc_type ,count(decode(zone_type,'K',1)) K_cnt ,count(decode(zone_type,'S',1)) S_cnt ,count(decode(zone_type,'L',1)) L_cnt ,count(*) cnt from mv$location$zone$join group by loc,loc_type;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

まあ、制限自体

  alter table mv$location$zone$agg add constraint wh_zones_chk check(loc_type != 'W' or K_cnt > 0 and S_cnt = 0 and L_cnt > 0) deferrable initially deferred; alter table mv$location$zone$agg add constraint store_zones_chk check(loc_type != 'S' or K_cnt >= 0 and S_cnt > 0 and L_cnt > 0) deferrable initially deferred;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

制限の動作を確認します。

  SQL> insert into location (loc,loc_type,status,loc_desc) 2 values (3,'S','W',' 3    '); 1 row inserted SQL> commit; Commit complete
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

プレースメントはドラフトステータスで正常に作成されました。 有効化してみましょう：

  SQL> update location set status = 'A' where loc = 3; 1 row updated SQL> commit; ORA-02091: transaction rolled back ORA-02290: check constraint (ZTXN.STORE_ZONES_CHK) violated
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

いや タイプに必要なゾーンが定義されていない場合、プレースメントをアクティブにできません。

  SQL> insert into zone(zone,loc,is_primary,zone_type) values (7,3,'Y','S'); 1 row inserted SQL> insert into zone(zone,loc,is_primary,zone_type) values (8,3,'Y','L'); 1 row inserted SQL> update location set status = 'A' where loc = 3; 1 row updated Commit complete
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

おわりに

いつ使うか

まず第一に、接続が非常に複雑でリスクが高いため、アプリケーション開発者がすべてを考慮に入れることができず、不一致が重大な場合です。 7つのテーブルを介して外部キーを使用してそれ自体でループする構造を設計したときに、このアプローチを使用したのはこれが初めてでした。 さらに、これらのテーブルは異なるビジネスユニットによって維持されます。 この制限は今日でも有効です。 そして今日まで、ユーザーは抜け穴を見つけ、この制限がトリガーされたときにスクリーンショットを送信し、アプリケーションモジュールの抜け穴を閉じるために再現することはできません。複数のユーザーの状況の組み合わせが必要です。



テスト、開始、プロジェクトの安定化の段階で、ロジックの正しい動作に自信がなく、アプリケーションリンクを超えてデータを変更できる場合に、このような制限を設けることは非常に便利です。

使用しない場合

明らかに、パフォーマンスが著しく低下する場合は、このアプローチを使用しないでください。



変更、データスキームの改良の場合、ビューのコンテンツは信頼できない場合があり、完全な更新が必要になります。 マテリアライズドビューを完全に更新する時間が、技術的な作業規則の実装を危険にさらす可能性がある場合は、おそらくこのアプローチも使用しないでください。