木の非正規化

多くの場合、ツリーはアプリケーションアーキテクチャの基盤として使用されます。 簡単な例：国、国-地域、地域-都市、都市-組織、組織-労働者、商品または他のものがあります。 ツリーの使用は非常に論理的で正当化されます。 このようなシステムの階層は、特定の抽象的な表で示されます。 オブジェクトと呼びましょう：

CREATE TABLE object ( id NUMBER(11), parent_id NUMBER(11), type VARCHAR2(16) NOT NULL, name VARCHAR2(255) NOT NULL, CONSTRAINT pk_object PRIMARY KEY (id), CONSTRAINT fk_object_parent FOREIGN KEY (parent_id) REFERENCES object (id) ON DELETE CASCADE ENABLE );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

いくつかのデータを入力します。

 id | parent_id | type | name ------------------------------------------------------ 1 | NULL | country |  2 | 1 | region |   3 | 1 | region |   4 | 2 | city |  5 | 3 | city | 
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同時に、1つのリクエストで必要な接続を簡単に受信できます。

 --     SELECT * FROM object WHERE type = 'city' START WITH id = 1 CONNECT BY PRIOR id = parent_id; --  ,     SELECT * FROM object WHERE type = 'country' START WITH id = 5 CONNECT BY PRIOR parent_id = id;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ただし、テーブルに非常に多くのレコードがあり、再帰クエリが2分以上実行されると問題が発生します。 アーキテクチャ全体を変更するには少し遅すぎます...ここでは、ツリーの非正規化が役立ちます。 この記事では、このような非正規化の方法の1つについて説明します。



主なアイデアは、クエリに対してより便利な方法で関係を格納する実体化された表現を使用することです。

 CREATE MATERIALIZED VIEW object_fast REFRESH COMPLETE ON DEMAND START WITH trunc(sysdate)+4/24 NEXT (trunc(sysdate)+1)+4/24 AS SELECT rownum id, tree.* FROM ( SELECT CONNECT_BY_ROOT id object_id, CONNECT_BY_ROOT name object_name, CONNECT_BY_ROOT type object_type, id parent_id, name parent_name, type parent_type, level-1 nesting_level FROM object CONNECT BY PRIOR parent_id = id ORDER BY object_id, nesting_level ) tree;; ALTER TABLE object_fast ADD CONSTRAINT pk_object_fast PRIMARY KEY (id);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

これで、クエリで使用できる非正規化テーブルができました。

 id | object_id | object_name | object_type | parent_id | parent_name | parent_type | nesting_level ---------------------------------------------------------------------------------------------------------------------- 1 | 1 |  | country | 1 |  | country | 0 2 | 2 |   | region | 2 |   | region | 0 3 | 2 |   | region | 1 |  | country | 1 4 | 3 |   | region | 3 |   | region | 0 5 | 3 |   | region | 1 |  | country | 1 6 | 4 |  | city | 4 |  | city | 0 7 | 4 |  | city | 2 |   | region | 1 8 | 4 |  | city | 1 |  | country | 2 9 | 5 |  | city | 5 |  | city | 0 10 | 5 |  | city | 3 |   | region | 1 11 | 5 |  | city | 1 |  | country | 2
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ご覧のとおり、表には各オブジェクトとそのすべての親との接続があり、nesting_levelは親へのレベル数です。 idフィールドを追加したことに注意してください。これは必須ではありませんが、ORMを介してデータにアクセスする場合は非常に合理的です。 これで、上記のクエリは次のようになります。

 --     SELECT * FROM object_fast WHERE parent_id = 1 AND object_type = 'city'; --  ,     SELECT * FROM object_fast WHERE object_id = 5 AND parent_type = 'country';
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

さて、オプションで、インデックスを追加できます：

 CREATE INDEX object_fast_obj_id ON object_fast (object_id); CREATE INDEX object_fast_par_id ON object_fast (parent_id); CREATE INDEX object_fast_obj_type ON object_fast (object_type); CREATE INDEX object_fast_par_type ON object_fast (parent_type); CREATE INDEX object_fast_nesting ON object_fast (nesting_level);
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

以上です。 私自身のために、私たちのプロジェクトでは、この方法によりリクエストの速度が約60倍に増加したと言います。 賢明に使用し、取得したデータが常に関連するとは限らないことを忘れないでください。 このメソッドは、ほとんど追加または削除されないオブジェクトにのみ適用することをお勧めします。 さて、またはその後、マテリアライズドビューの運用上の更新を実装する価値があります。 ファンタジーの飛行に制限はありません...



更新：この記事は微調整され、メソッドはxtenderのコメントのおかげで大幅に簡素化されました。