Oracle Foreign Key Indexing

Herkese Selam,

Bu yazıda sizlere son zamanlarda incelediğim kodlar arasında sık karşılaştığım bir durumdan ve bunun performans etkilerinden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

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. Peki foreign key olarak belirlediğimiz kolonu indexlemez isek ne gibi sorunlar ortaya çıkıyor öncelikle bir örnek üzerinden onu inceleyelim.

Örnekleri Oracle db içinde hazır gelen HR şeması üzerindeki F.K ilişkisi olan EMPLOYEES ve DEPARTMENTS tabloları üzerinde gerçekleyeceğim. Şimdi bu tabloları oluşturmak için gerekli scriptleri hali hazırda bu schema ya sahip olmayan okuyucular için paylaşalım.

CREATE TABLE HR.DEPARTMENTS
(
  DEPARTMENT_ID    NUMBER(4),
  DEPARTMENT_NAME  VARCHAR2(30 BYTE) CONSTRAINT DEPT_NAME_NN NOT NULL,
  MANAGER_ID       NUMBER(6),
  LOCATION_ID      NUMBER(4)
);

ALTER TABLE HR.DEPARTMENTS ADD (
  CONSTRAINT DEPT_ID_PK
  PRIMARY KEY
  (DEPARTMENT_ID)
  USING INDEX HR.DEPT_ID_PK);

SET DEFINE OFF;
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (10, 'Administration', 200, 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (20, 'Marketing', 201, 1800);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (30, 'Purchasing', 114, 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (40, 'Human Resources', 203, 2400);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (50, 'Shipping', 121, 1500);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (60, 'IT', 103, 1400);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (70, 'Public Relations', 204, 2700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (80, 'Sales', 145, 2500);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (90, 'Executive', 100, 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (100, 'Finance', 108, 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
 Values
   (110, 'Accounting', 205, 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (120, 'Treasury', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (130, 'Corporate Tax', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (140, 'Control And Credit', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (150, 'Shareholder Services', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (160, 'Benefits', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (170, 'Manufacturing', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (180, 'Construction', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (190, 'Contracting', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (200, 'Operations', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (210, 'IT Support', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (220, 'NOC', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (230, 'IT Helpdesk', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (240, 'Government Sales', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (250, 'Retail Sales', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (260, 'Recruiting', 1700);
Insert into HR.DEPARTMENTS
   (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
 Values
   (270, 'Payroll', 1700);
COMMIT;

CREATE TABLE HR.EMPLOYEES
(
   EMPLOYEE_ID      NUMBER (6),
   FIRST_NAME       VARCHAR2 (20 BYTE),
   LAST_NAME        VARCHAR2 (25 BYTE) CONSTRAINT EMP_LAST_NAME_NN NOT NULL,
   EMAIL            VARCHAR2 (25 BYTE) CONSTRAINT EMP_EMAIL_NN NOT NULL,
   PHONE_NUMBER     VARCHAR2 (20 BYTE),
   HIRE_DATE        DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL,
   JOB_ID           VARCHAR2 (10 BYTE) CONSTRAINT EMP_JOB_NN NOT NULL,
   SALARY           NUMBER (8, 2),
   COMMISSION_PCT   NUMBER (2, 2),
   MANAGER_ID       NUMBER (6),
   DEPARTMENT_ID    NUMBER (4)
);

ALTER TABLE HR.EMPLOYEES ADD (
  CONSTRAINT EMP_DEPT_FK
  FOREIGN KEY (DEPARTMENT_ID)
  REFERENCES HR.DEPARTMENTS (DEPARTMENT_ID));


Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', 
    TO_DATE('06/21/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'SH_CLERK', 2600, 124, 50);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', 
    TO_DATE('01/13/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'SH_CLERK', 2600, 124, 50);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', 
    TO_DATE('09/17/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AD_ASST', 4400, 101, 10);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', 
    TO_DATE('02/17/2004 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'MK_MAN', 13000, 100, 20);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', 
    TO_DATE('08/17/2005 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'MK_REP', 6000, 201, 20);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', 
    TO_DATE('06/07/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'HR_REP', 6500, 101, 40);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', 
    TO_DATE('06/07/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'PR_REP', 10000, 101, 70);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', 
    TO_DATE('06/07/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AC_MGR', 12008, 101, 110);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', 
    TO_DATE('06/07/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AC_ACCOUNT', 8300, 205, 110);
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, DEPARTMENT_ID)
 Values
   (100, 'Steven', 'King', 'SKING', '515.123.4567', 
    TO_DATE('06/17/2003 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'AD_PRES', 24000, 90);
COMMIT;

Evet öncelikle EMPLOYEES tablosundaki department_id kolonunu foreign key olarak belirledik ve başlangıçta indexlemedik.
Şimdi olabilecek handikaplara göz atalım.

1- Parent Tablonun Update Edilmesi
Yaptığımız örnekte parent tablomuz referans edilen departments tablosu. Şimdi child tablomuzda herhangi bir işlem çalıştırıp sonlandırmadan, başka bir session’a geçip parent tablomuz üzerinde bir değişiklik yaptığımızda ne gibi bir sonuçla karşılaşacağız. ?

 

T2 anında Session 2 nin bloklanmasının sebebi; Parent tablonun (departments), child tablonun(employees) tamamında bir lock oluşturma isteğidir. Session 1 de Child tablo üzerindeki satırlardan biri üzerinde sistem tarafından lock alındığı için T2 anındaki Session 2 işlemi sistem tarafından bloklandı. Bu blok T3 anında Session 1 tarafından çalıştırılan commit işlemi sonucunda Session 1 transactionının sonlanması ve child tablo üzerindeki lockların sisteme iade edilmesi ile beraber son buldu.

Evet yukarıdaki örnekte gördüğümüz üzere F.K kolonu indekslenmemiş bir tablo için Parent tabloda yapılacak bir değişiklik Child tablonun tamamının locklanmasına neden olduğunu gözlemledik.

Şimdi child (employees) tablomuzdaki F.K kolonuna bir index oluşturuo işlemi tekrarladığımızda sessionların hiç birinin bloklanmadığını göreceğiz.

CREATE INDEX HR.EMP_DEPARTMENT_IX
   ON HR.EMPLOYEES (DEPARTMENT_ID);

Evet F.K indexlemenin sistemin concurrency seviyesi düşürdüğünü görmüş olduk. Eş zamanlılığın çok kullanıcılı sistemler için çok ama çok öenmli olduğunu düşündüğümüzde bu durumun performansı etkilecek önemli bir sorun olacağını düşünebiliriz.

2-  “ON DELETE CASCADE” 

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.

Şimdi bu durumu örnekleyelim.

DROP INDEX EMP_DEPARTMENT_IX;

ALTER TABLE EMPLOYEES
DROP CONSTRAINT EMP_DEPT_FK;
 
ALTER TABLE HR.EMPLOYEES ADD (
  CONSTRAINT EMP_DEPT_FK
  FOREIGN KEY (DEPARTMENT_ID)
  REFERENCES HR.DEPARTMENTS (DEPARTMENT_ID) ON DELETE CASCADE);

Tablomuzdaki F.K üzerindeki indexi ve ilgili constrainti silip yeni opsiyonla beraber tekrar yarattık. Şimdi parent tablodan bir kayıt dizisi silmeye çalışalım.

DELETE hr.departments
 WHERE department_id IN (10, 20, 30, 40, 50, 60, 70);

Yukarıdaki operasyonu yaptığımızda Oracle arka tarafta her bir department_id için employees tablosuna full table scan yöntemiyle gidip ilgili department_id ye sahip olan kayıtlarısilecek. Yani bu liste için düşünürsek tam 7 kere employees tablosunu full table scan ile okuyarak inanılmaz bir performans kaybına sebebiyet verecek. Bu bağlamda bu tarz bir sorunu da bertaraf etmek için yine F.K constraintimiz var ise indexlemek gelen sistem performansını arttıracaktır.

3- Parent Child Tablonun Joinlenmesi 

Bu en sık yaşayacağımız problemlerden biri aslında. Eğer parent ve child tablomuzu sık sık joinliyorsak yine F.K üzerinde bir indeksin olmaması sorgunun yavaş çalışmasına neden olacaktır.

  
DROP INDEX EMP_DEPARTMENT_IX;

SELECT d.department_id, COUNT (e.employee_id)
    FROM departments d, employees E
   WHERE D.DEPARTMENT_ID = e.department_id(+)
GROUP BY d.department_id
ORDER BY 1, 2;

Evet gördüğümüz üzre employees tablosunu FULL TABLE SCAN ile eriştik. Şimdi F.K kolonumuza index atıp aynı sorgunun planını yeniden inceleyelim.

CREATE INDEX HR.EMP_DEPARTMENT_IX
   ON HR.EMPLOYEES (DEPARTMENT_ID);

SELECT d.department_id, COUNT (e.employee_id)
    FROM departments d, employees E
   WHERE D.DEPARTMENT_ID = e.department_id(+)
GROUP BY d.department_id
ORDER BY 1, 2;

Evet yeni plandan da gördüğümüz üzere artık child tablomuza indexle gidiyoruz ve plan costumuz bir miktar düşmüş durumda.

Sonuç olarak F.K kolonumuzun indekslenmemesi sonucunda oluşabilecek belli başlı problemlerden bahsettik. Bu problemler büyük sistemler için gerçekten performans problemlerine sebebiyet verebilecek durumlar. Tablolarımızda F.K yaratırken bu kolonlar üzerinde otomatik olarak index atmayı unutmamamız genelsistem performansımızı pozitif yönde etkileyecektir. Ancak aşağıdaki durumları genel olarak karşılıyor isek F.K kolonlarımızı indexlemeye ihtiyacımızda olmayabilir;

1- Eğer parent ve child tablolarımızı joinleme ihtiyacımız doğmuyorsa;

2- Eğer parent tablodan herhangi bir kayıt silme durumumuz olmuyorsa;

3- Eğer parent tablo üzerindeki primary veya unique key üzerinde bir update çalıştırmıyorsanız;

F.K kolonlarınıza index atmayabilirsiniz.

Umarım farkındalık anlamında faydalı bir yazı olmuştur.

Not: Tüm testler Oracle 11g R2 üzerinde gerçeklendi.

REFERENCES

https://asktom.oracle.com/

https://docs.oracle.com/cd/E17952_01/refman-5.5-en/create-table-foreign-keys.html