Mustafa Bektaş Tepe

Foreign key ilişkisi olupda index atılmamış sütünlar.

OLTP sistemlerde Foreign Key constraintleri veri bütünlüğünü sağlamak için sıkça kullanmaktayız. Foreign Key kullanımı veri bütünlüğünü sağlayabilmek açısından kullanılaması gereken mekanizmaların başında geliyor ancak foreign key constraint kullanırken yapmamız gereken bazı önemli ayrıntılar mevcut bunu atlamamamız gerekiyor.

Nedir bu önemli ayrıntı dediğimizde, yapmamız gereken şeyin foreign key olarak belirlediğimiz kolonu indexlemek olduğunu görmekteyiz. Indexin önemi ise;

• K kolonu indekslenmemiş bir tablo için Parent tabloda yapılacak bir değişiklik Child tablonun tamamının locklanmasına neden olur.
• Eğer F.K yaratırken ON DELETE CASCADE opsiyonu kullandık isek, F.K’yi indexlemediğimiz durumda bir sorunda burada yaşayacağız. Burada yaşayacağımız problem yapacağımız DELETE operasyonunun FULL TABLE SCAN operasyonuna neden olacağıdır. Parent tablodan sileceğimiz her kayıt için, Child tabloya erişim FULL TABLE SCAN ile yapılacak ve delete operasyonumuz oldukça yavaş gerçekleşecektir. Bu durumda yine gün sonunda sistemin eş zamanlılık seviyesini negatif yönde etkileyecektir.
• Parent ve child tablomuzu sık sık joinliyorsak yine F.K üzerinde bir indeksin olmaması sorgunun yavaş çalışmasına neden olacaktır.

Script 1

SELECT a.owner,
       CASE WHEN b.table_name IS NULL THEN 'unindexed' ELSE 'indexed' END
          AS status,
       a.table_name AS table_name,
       a.constraint_name AS fk_name,
       a.fk_columns AS fk_columns,
       b.index_name AS index_name,
       b.index_columns AS index_columns
  FROM (  SELECT a.owner,
                 a.table_name,
                 a.constraint_name,
                 listagg (a.column_name, ',')
                    WITHIN GROUP (ORDER BY a.position)
                    fk_columns
            FROM dba_cons_columns a, dba_constraints b
           WHERE     a.constraint_name = b.constraint_name
                 AND b.constraint_type = 'R'
                 AND a.owner = b.owner
               --  AND a.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
        GROUP BY a.owner, a.table_name, a.constraint_name) a,
       (  SELECT table_owner,
                 table_name,
                 index_name,
                 listagg (c.column_name, ',')
                    WITHIN GROUP (ORDER BY c.column_position)
                    index_columns
            FROM dba_ind_columns c
           --WHERE table_owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
        GROUP BY table_owner, table_name, index_name) b
 WHERE     a.table_name = b.table_name(+)
       AND b.index_columns(+) LIKE a.fk_columns || '%'
       AND a.owner = b.table_owner(+)
       order by 2 desc,1;

Script 2

SELECT    acc.owner
         || '-> '
         || acc.constraint_name
         || '('
         || acc.column_name
         || '['
         || acc.position
         || '])'
            "Owner, Name, Column, Position"
    FROM all_cons_columns acc, all_constraints ac
   WHERE ac.constraint_name = acc.constraint_name AND ac.constraint_type = 'R'
         AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
                (SELECT acc.owner,
                        acc.table_name,
                        acc.column_name,
                        acc.position
                   FROM all_cons_columns acc, all_constraints ac
                  WHERE ac.constraint_name = acc.constraint_name
                        AND ac.constraint_type = 'R'
                 MINUS
                 SELECT table_owner,
                        table_name,
                        column_name,
                        column_position
                   FROM all_ind_columns)
ORDER BY ACC.owner,
         ACC.constraint_name,
         ACC.column_name,
         ACC.position;

Referanslar
https://emrahmete.wordpress.com/2016/01/10/oracle-foreign-key-indexing/

Yararlı olması Dilegiyle…
Yazar : Mustafa Bektaş Tepe