Birebir Çoklama – ORA-02437 ORA-02299 Validation Hatası

Veri Ambarı (DWH) işlerinde çoklama sorunu ile çok sık karşılaşmaktayız. Farklı farklı çoklama şekilleri olsa da ben en sık karşılaşılan örneklerden birini paylaşacağım. Veri Ambarı’nda çoklama olmaması için Primary Key (PK) veya Unique Constraint kullanılır. Hatta tabloya çok fazla data insert edileceği zaman, constraint’ler yavaşlığa sebep olmaması için öncesinde disable edilir veya drop edilir. İnsert veya exchange partition işlemi gerçekleştirildikten sonra, PK veya Unique Constraint valide edilir. Eğer çoklama varsa ve tablodaki PK valide edilmek istenir ise kod “ORA-02437: cannot validate (C##USER2.C_PK) – primary key violated” hatasını alacaktır. Eğer çoklama varsa ve tablodaki Unique Constraint valide edilmek istenirse kod “ORA-02299: cannot validate (C##USER2.C_U) – duplicate keys found” hatasını alacaktır. Çözüm, çoklamayı silip, validasyon adımını yapmaktır.

Öncelikle hatayı simüle etmek için kendim tbl_customer_hist tablosunu dummy data ile ürettim. Tabloyu elimden geldiğince kapsamlı yaptım çünkü farklı konuları aktarmak için de bu tabloları kullanacağım. BU LINK‘teki test kodlarını sırayla çalıştırınca tablomuzda çoklama olacak ve aşağıdaki kodlardan herhangi birini çalıştırınca çoklama hatası alacağız:

ALTER TABLE tbl_customer_hist ADD CONSTRAINT c_pk /* unique */ PRIMARY KEY (as_of_date,customer_id,branch_id) ;
--ORA-02437: cannot validate (C##USER2.C_PK) - primary key violated
ALTER TABLE tbl_customer_hist ADD CONSTRAINT c_pk /* unique */ UNIQUE (as_of_date,customer_id,branch_id) ;
--ORA-02299: cannot validate (C##USER2.C_U) - duplicate keys found

/* 
veya önce constraint oluşturup, sonra validasyon yapılabilir. 
ALTER TABLE tbl_customer_hist ADD CONSTRAINT c_pk PRIMARY KEY (as_of_date,customer_id,branch_id) NOVALIDATE ;
ALTER TABLE TBL_CUSTOMER_HIST  MODIFY CONSTRAINT C_PK  VALIDATE;
--ORA-02437: cannot validate (C##USER2.C_PK) - primary key violated
*/

Tablonun PK’sı ne ise, onun üzerinden çoklama kontrolü yapıp, kaç adet PK’da çoklama olduğu bulunmalıdır. (Tüm kodları TXT dosyası olarak en aşağıda paylaşıyorum.)

  SELECT as_of_date,customer_id,branch_id,COUNT(*) AS adet
    FROM tbl_customer_hist
GROUP BY as_of_date,customer_id,branch_id
  HAVING COUNT(*) >1;
  --43 adet kayitta çoklama var. 
Çoklayan Kayıtların Kontrolü

Çoklayan kayıtlara aşağıdaki şekilde göz atabiliriz:

SELECT a.* 
  FROM tbl_customer_hist a
 WHERE (as_of_date,customer_id,branch_id) IN ( 
                                               SELECT as_of_date,customer_id,branch_id
                                                 FROM tbl_customer_hist
                                             GROUP BY as_of_date,customer_id,branch_id
                                               HAVING COUNT(*) >1
                                             )
ORDER BY 1,2,3,4,5,6;
Birebir çoklayan kayıtlara göz atma

Çoklayan kayıtlar, subquery yardımıyla ile birebir çoklayıp çoklamadığı tespit edilir. Eğer üstteki sorguya DISTINCT eklenip bakıldığında, where condition’daki subquery’den gelen satır sayısı kadar kayıt dönüyorsa, birebir çoklamış N adet kayıt var diyebiliriz.

Birbir çokladığından emin olma

Çoklayan kayıtları silme işleminden önce, kayıtların yedeklerini alacağım. Bunu yaparken yanına silerken kullanacağım rowid bilgisini ve PK’ya göre ROW_NUMBER() fonksiyonunu kullanarak verdiğim sıra numarasını ek sütunlar olarak ekliyeceğim.

CREATE TABLE tbl_customer_hist_dpl_20220131 AS
SELECT a.*,
       ROW_NUMBER() OVER (PARTITION BY as_of_date,customer_id,branch_id ORDER BY 1) as sira,
       --sileceğm kayıtlara sıra numarası vermek için sira sütununu kullandım.
       a.rowid AS ri    --hangisini sileceğimi tutmak için
  FROM tbl_customer_hist a
 WHERE (as_of_date,customer_id,branch_id) IN ( 
                                               SELECT as_of_date,customer_id,branch_id
                                                 FROM tbl_customer_hist
                                             GROUP BY as_of_date,customer_id,branch_id
                                               HAVING COUNT(*) >1
                                             );

Yukarıdaki sorguda sira>1 filtresi verirsek, çoklayan N adet kaydı bulmuş oluruz. Bunun rowid bilgisi ile tablodan nokta atış silme işlemini gerçekleştirebiliriz.

DELETE FROM tbl_customer_hist
      WHERE ROWID IN (  SELECT ri
                          FROM tbl_customer_hist_dpl_20220131 
                         WHERE sira>1 --1. kaydi tutuyorum 2,3,4,5... kayitlari siliyorum
                     );
COMMIT;
--çoklayan kayıtlar silindiğine göre constraint/PK valide edilebilir
ALTER TABLE tbl_customer_hist  MODIFY CONSTRAINT c_pk  VALIDATE;

Not1: Eğer tablodaki çoklama milyon seviyesinde ise, yani kartezyene girmiş ise sorun bu şekilde çözülmez. Alternatif yöntemler kullanılmalıdır. Yukardaki yöntemi yöntemi ufak tefek çoklamaları çözmek için anlattım.

Not2: Hedef tabloda constraint enable ise, hatayı direk insert yaparken de alabilirsiniz ama genelde veri ambarında insert öncesi constraint disable edilir, insert sonrası constraint geri açılır. Doğrusu da budur. Yoksa bulk insert yapılan veri ambarında insert işleminin performansı olumsuz etkilenir. Bu sebeple ben validasyon adımında alınan hata üzerinden örneğimi göstermek istedim.

Bu yazıda bahsi geçen tüm kodlara BURAYA TIKLAYARAK ulaşabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *