Oracle Data Miner ile Birliktelik Analizi

Herkese Selam,

Bu yazıda Oracle Data Mining ile bir birliktelik  (Association Rules – Market Basket Analysis ) analizi yapacağım. Umarım farkındalık anlamında faydalı bir yazı olur.

Veri Bilimi ve Makine Öğrenmesi gibi konular günümüzde hakkında çokça söz ettiren başlıkların başında gelmekte. Öğrenmesi ve uygulaması uzmanlık gerektiren bu başlıklar için adreslenen problemleri ve yöntemleri günümüzde bir çok ürün veya yazılım ile gerçekleyebilmekteyiz. Oracle’da bu başlıklar altındaki problemlerin çözümüne ilişkin yöntem ve algoritmaları DBMS_DATA_MINING paketi ile desteklemekte.

DBMS_DATA_MINING paketi ile Custering, Classification, Regression, Anomaly Detection, Feature Extraction ve Assosication gibi modeller kurup, verimizi bu modellerden geçirip yorumlayabiliyoruz. Bu modellerden elde ettiğimiz sonuçları ise business kurgularımızda input olarak kullanabilmekteyiz.

DBMS_DATA_MINING paketi Oracle veritabanı üzerinde default kurulu olarak gelmiyor. Bu nedenle bu destekten faydalanabilmek için öncelikle bu paketi kurmak gerekiyor. Aşağıdaki linki takip ederek veritabanınıza Oracle Data Mining’ı kurabilirsiniz.

https://docs.oracle.com/cd/E11882_01/datamine.112/e16807/install_odm.htm#DMADM117

Oracle Data Mining paketi kurulumu ile beraber gelen 3 yeni dictionary tablomuz bulunmakta.

SELECT * FROM  ALL_MINING_MODELS;

SELECT * FROM ALL_MINING_MODEL_SETTINGS;

SELECT * FROM ALL_MINING_MODEL_ATTRIBUTES;

ALL_MINING_MODELS tablosunda Oracle’ın bu alt yapı ile bize sunmuş olduğu tüm modeller listesi ve modeller hakkında bilgiler bulunuyor.

ALL_MINING_MODEL_SETTINGS ve ALL_MINING_MODEL_ATTRIBUTES tablolarında ise bu modeller ile ilgili parametreler ve spesifik detaylar bulunmakta.

Şimdi bu alt yapının nasıl kullanıldığını görmek için bir örnek yapalım.

Örneği yapabilmek için bir veri setine ihtiyaç duyuyorum. Örneği uygulayacağım veri setini UCI üzerinde bulunan veri setlerinden Online Retail Data Set üzerinden yapacağım.

Öncelikle Online Retail Data Set i tanıyalım.

Online Retail veri seti 8 (3 numeric, 5 string) kolondan oluşan ve içerisinde 541909 kayıt bulunduran bir veri seti. Online Retail veri setinde bulunan her bir kolonun açıklaması aşağıda verilmiştir.

Column Name Description Data Type
InvoiceNo Invoice number. Nominal, a 6-digit integral number uniquely assigned to each transaction. If this code starts with letter ‘c’, it indicates a cancellation. String
StockCode Product (item) code. Nominal, a 5-digit integral number uniquely assigned to each distinct product. String
Description Product (item) name. Nominal. String
Quantity The quantities of each product (item) per transaction. Numeric. Numeric
InvoiceDate Invice Date and time. Numeric, the day and time when each transaction was generated. Date
UnitPrice Unit price. Numeric, Product price per unit in sterling. Numeric
CustomerID Customer number. Nominal, a 5-digit integral number uniquely assigned to each customer. Numeric
Country Country name. Nominal, the name of the country where each customer resides. String

Evet veri setimiz ile ile ilgili detaylara baktıktan sonra indirdiğimiz veri setini Oracle veritabanımıza yükleyelim.

Öncelikle indirdiğimiz veri setini (.xls) yükleyeceğimiz Oracle tablosunu yaratalım.

CREATE TABLE ONLINE_RETAIL
(
  INVOICENO    VARCHAR2(100 BYTE),
  STOCKCODE    VARCHAR2(100 BYTE),
  DESCRIPTION  VARCHAR2(200 BYTE),
  QUANTITY     NUMBER,
  INVOICEDATE  DATE,
  UNITPRICE    NUMBER,
  CUSTOMERID   NUMBER,
  COUNTRY      VARCHAR2(100 BYTE)
);

Tablomuzu yarattık, şimdi .xls olarak indirdiğimiz veri setimizi içeriye yükleyeceğiz, bunu yapmak için birden çok yöntemimiz bulunuyor. Bunlardan bazıları;

  • Oracle External Table yardımı ile yüklemek.
  • Oracle SQL Loader kullanarak yüklemek.
  • Kullandığımız editörlerden faydalanarak yüklemek.

Ben veri setini kullandığım editör yardımı ile yükleyeceğim. Editör olarak Toad kullanmaktayım. Toad ile aşağıdaki yolu takip ederek yükleme işlemini gerçekleştirebilirsiniz.

Database –> Import –> Import Table Data 

Toad ürünü ücretli olduğu için bu editörü kullanıyor olabilirsiniz. Ancak diğer editörlerin de bu özelliği mevcut, diğer editörlerle de bu işlemi kolaylıkla yapabilirsiniz. Örneğin ücretsiz olan Oracle SQL Developer ile veri yüklemesini aşağıdaki gibi yapabilirsiniz.

SELECT * FROM ONLINE_RETAIL;

Evet veri seti yükleme işlemini tamamladık.

Veriyi gözlemlediğimizde müşterilerin yaptığı alış verişlere ait detayları görmekteyiz. Her bir satırda hangi üründen alındığı,  ne kadar alındığı, bu ürünün kim tarafından aldığı, hangi tarihte aldığı, fiyatının ne kadar olduğu ve hangi ülkeden yapıldığına ait bilgiler bulunuyor. Ayrıca her bir satırda bulunan InvoiceNo bilgisi de o ürünün hangi fatura ile satıldığını göstermekte.  Veriyi detaylı incelediğimizde aynı InvoiceNo ya ait bir veya birden çok kayıt olduğunu da gözlemliyoruz. Müşterinin herhangi bir alış verişine ait satın almış olduğu tüm ürünleri InvoiceNo üzerinden görüntüleyebiliriz. Yani InvoiceNo bilgisini ilgili alış verişe ait bir sepet gibi düşünebiliriz.

Şimdi örnek bir alış verişe ait sepeti görüntüleyelim.

SELECT * FROM ONLINE_RETAIL WHERE INVOICENO = '536368';

Evet örnek bir alış veriş sepetimizi gördük. Şimdi bu veriden birliktelik analizi yaparak birlikte en çok satılan ürünleri tespit edelim.

Birliktelik analizine başlamadan önce konunun daha iyi anlaşılması için kullanılan algoritma hakkında biraz bilgi vereceğim.

DBMS_DATA_MINING paketi birliktelik analizini APRIORI algoritması ile gerçeklemekte.  Bu algoritmayı kullanmak içinse bizim bazı parametreleri tanımlamamız gerekiyor. Bu parametreler ve parametrelere ait default değerler aşağıdaki gibidir;

 Algoritma parametrelerini ve ne anlama geldiklerini daha detaylı anlayabilmek için linkteki makale incelenebilir.

Şimdi model ayarlarını okuyacağımız tabloyu oluşturalım ve içerisine algoritma parametrelerini insert edelim.

CREATE TABLE SETTINGS_ASSOCIATION_RULES
AS
   SELECT *
     FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
    WHERE SETTING_NAME LIKE 'ASSO_%';


BEGIN
   UPDATE SETTINGS_ASSOCIATION_RULES
      SET SETTING_VALUE = 3
    WHERE SETTING_NAME = DBMS_DATA_MINING.ASSO_MAX_RULE_LENGTH;
    
   UPDATE SETTINGS_ASSOCIATION_RULES
      SET SETTING_VALUE = 0.03
    WHERE SETTING_NAME = DBMS_DATA_MINING.ASSO_MIN_SUPPORT;
    
       UPDATE SETTINGS_ASSOCIATION_RULES
      SET SETTING_VALUE = 0.03
    WHERE SETTING_NAME = dbms_data_mining.asso_min_confidence;

   INSERT INTO SETTINGS_ASSOCIATION_RULES
        VALUES (DBMS_DATA_MINING.ODMS_ITEM_ID_COLUMN_NAME, 'STOCKCODE');

   COMMIT;
END;

Evet algoritma parametrelerimizi okuyacağımız tabloyu oluşturduk. Şimdi modelimizi oluşturma adımına geçebiliriz.

CREATE VIEW VW_ONLINE_RETAIL AS SELECT INVOICENO,STOCKCODE FROM ONLINE_RETAIL;

BEGIN 
   DBMS_DATA_MINING.CREATE_MODEL(
      model_name            => 'MD_ASSOC_ANLYSIS',
      mining_function       =>  DBMS_DATA_MINING.ASSOCIATION,
      data_table_name       => 'VW_ONLINE_RETAIL',
      case_id_column_name   => 'INVOICENO',
      target_column_name    =>  NULL,
      settings_table_name   => 'SETTINGS_ASSOCIATION_RULES');
END;

Modelimiz oluştu, şimdi modelimiz ile ilgili oluşan detaylara dictionary’den bakalım.

SELECT MODEL_NAME,
       ALGORITHM,
       COMMENTS,
       CREATION_DATE,
       MINING_FUNCTION,
       MODEL_SIZE
  FROM ALL_MINING_MODELS
 WHERE MODEL_NAME = 'MD_ASSOC_ANLYSIS';

SELECT SETTING_NAME, SETTING_VALUE
FROM ALL_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = 'MD_ASSOC_ANLYSIS';

Şimdi analizim sonucunda oluşan çıktılara bakalım.

SELECT RULE_ID,
       B.ATTRIBUTE_SUBNAME ANTECEDENT_STOCKCODE,
       C.ATTRIBUTE_SUBNAME CONSEQUENT_STOCKCODE,
       RULE_SUPPORT,
       RULE_CONFIDENCE
  FROM TABLE (DBMS_DATA_MINING.GET_ASSOCIATION_RULES ('MD_ASSOC_ANLYSIS')) A,
       TABLE (A.ANTECEDENT) B,
       TABLE (A.CONSEQUENT) C;


Algoritmaya verdiğimiz parametreler ile oluşturduğumuz model sonucunda beraber satılma oranı yüksek olan ürünlere ulaştık ve görüntüledik.
Algoritma parametrelerinde değişiklik yapılarak daha farklı analizlerde yapmak mümkün.

Advertisements
Posted in Oracle, Root, Uncategorized | Tagged , , , , , , , , , | Leave a comment

Oracle 18c Sürümünü Duyurdu

Herkese Selam,

Geçtiğimiz hafta boyunca Oracle’ın Dünya çapındaki en büyük etkinliği Oracle Open World 2017, San Francisco’da gerçekleşti. Yine Dünyanın dört bir yanından katılan binlerce katılımı ile gerçekleşen bu etkinlikte yine birbirinden önemli duyurular yapıldı.

Bu duyuruların ve haberlerin en önemlisi keynote oturumunda Oracle’ın CTO’su ve kurucusu Larry Ellison tarafından yapıldı. Duyurulan yenilik Oracle’ın bir sonraki veritabanı sürümü olan Oracle 18c ‘idi. Sürüm numarası olarak 12’den 18’e atlanması ilginç bir detay olarak kalsa da Oracle 18c çarpıcı özellikler ile geliyor.

Oracle 18c ile ilgili atılacak en büyük başlaık Oracle’ında ana özellik olarak duyurduğu “Autononomous Database” özelliği. Otonom veri tabanının en büyük özelliği olarak Oracle, veri tabanı yönetimi konusunda harcanan insan eforunu bir otomasyona çevirerek, bu tarafta harcanan insan gücünden  tasarruf etme ve insan gücünün yapma ihtimali olan hataların ortadan kaldırılmasına yönelik pozitif katkıların olacağını belirtti. Otonom veritabanı özellikleri ile beraber artık daha düşük veri tabanı yönetim maliyeti ile daha güvenli, daha performanslı ve daha erişilebilir bir veritabanı oluşturulması hedefleniyor.

Oracle 18c veritabanı otonomluğu ile aşağıdaki özelliklere sahip olması bekleniyor.

Self Driving:  Makine öğrenmesine dayalı sürekli olarak adaptif performans iyileştirmelerinin yapılacağı bir sistem planlanıyor. Veritabanı aynı zamanda otomatik olarak güncellemeleri alarak gerektiğinde otomatik olarak yeni patchleri kurabilecek. Buna ek olarak siber saldırılara karşı koruma sağlamak için otomatik olarak güvenlik güncellemelerini de yapması amaçlanıyor..

Self Scaling: Sistemde herhangi bir downtime olmadan depolama ve işlem gücünün otomatik olarak ayarlanması sağlanacak. Resource tüketiminin ihtiyaca göre ayarlanması maddi giderleri oldukça azaltarak rakiplerine karşı büyük avantaj sağlamayı hedefliyor.

Self Repearing: Oracle yeni sürümü ile beraber %99.995 oranında SLA’leri tuturarak downtime’lara karşı sistemi koruyacak. Ayrıca planlanmamış ve yaşanması olası olan downtime süresinin yılda 30 dakikadan daha fazla olmaması öngörülüyor.

Oracle’ın ilk otonom veritabanı opsiyonu olarak “Oracle Autonomous Data Warehouse Cloud” piyasaya sürecek. Oracle’ın açıklamasına göre bu yıl içerisinde bu sürüm kullanıma hazır olacak.

Oldukça iddialı söylemler ve özellikler ile duyurulan Oracle 18c ‘nin sahada neler göstereceği şimdiden merak konusu.

Posted in Oracle, Uncategorized | Tagged , , , , , | Leave a comment

DBMS_DATA_MINING ile Classification Problemi Çözmek

Herkese Selam,

Bu yazıda Oracle Data Mining ile bir classification problemi çözeceğim. Umarım farkındalık anlamında faydalı bir yazı olur.

Veri Bilimi ve Makine Öğrenmesi gibi konular günümüzde hakkında çokça söz ettiren başlıkların başında gelmekte. Öğrenmesi ve uygulaması uzmanlık gerektiren bu başlıklar için adreslenen problemleri ve yöntemleri günümüzde bir çok ürün veya yazılım ile gerçekleyebilmekteyiz. Oracle’da bu başlıklar altındaki problemlerin çözümüne ilişkin yöntem ve algoritmaları DBMS_DATA_MINING paketi ile desteklemekte.

DBMS_DATA_MINING paketi ile Custering, Classification, Regression, Anomaly Detection, Feature Extraction ve Assosication gibi modeller kurup, verimizi bu modellerden geçirip yorumlayabiliyoruz. Bu modellerden elde ettiğimiz sonuçları ise business kurgularımızda input olarak kullanabilmekteyiz.

DBMS_DATA_MINING paketi Oracle veritabanı üzerinde default kurulu olarak gelmiyor. Bu nedenle bu destekten faydalanabilmek için öncelikle bu paketi kurmak gerekiyor. Aşağıdaki linki takip ederek veritabanınıza Oracle Data Mining’ı kurabilirsiniz.

https://docs.oracle.com/cd/E11882_01/datamine.112/e16807/install_odm.htm#DMADM117

Oracle Data Mining paketi kurulumu ile beraber gelen 3 yeni dictionary tablomuz bulunmakta.

SELECT * FROM  ALL_MINING_MODELS;

SELECT * FROM ALL_MINING_MODEL_SETTINGS;

SELECT * FROM ALL_MINING_MODEL_ATTRIBUTES;

ALL_MINING_MODELS tablosunda Oracle’ın bu alt yapı ile bize sunmuş olduğu tüm modeller listesi ve modeller hakkında bilgiler bulunuyor.

ALL_MINING_MODEL_SETTINGS ve ALL_MINING_MODEL_ATTRIBUTES tablolarında ise bu modeller ile ilgili parametreler ve spesifik detaylar bulunmakta.

Şimdi bu alt yapının nasıl kullanıldığını görmek için bir örnek yapalım.

Örneği yapabilmek için bir veri setine ihtiyaç duyuyorum. Örneği uygulayacağım veri setini www.kaggle.com üzerinde bulunan veri setlerinden HR_ANALYTICS veri seti üzerinden yapacağım.

Öncelikle HR_ANALYTICS veri setini tanıyalım.

HR_ANALYTICS veri seti 10 (8 numeric, 2 string) kolondan oluşan ve içerisinde 15.000 kayıt bulunduran bir veri seti. HR_ANALYTICS veri setinde bulunan her bir kolonun açıklaması aşağıda verilmiştir.

Column Name Description Data Type
satisfaction_level Level of satisfaction (0-1) Numeric
last_evaluation Time since last performance evaluation (in Years) Numeric
number_project Number of projects completed while at work Numeric
average_montly_hours Average monthly hours at workplace Numeric
time_spend_company Number of years spent in the company Numeric
Work_accident Whether the employee had a workplace accident Numeric
left Whether the employee left the workplace or not (1 or 0) Factor Numeric
promotion_last_5years Whether the employee was promoted in the last five years Numeric
sales Department in which they work for String
salary Relative level of salary (high) String

Kaynak

Evet veri setimiz ile ile ilgili detaylara baktıktan sonra indirdiğimiz veri setini Oracle veritabanımıza yükleyelim.

Öncelikle indirdiğimiz veri setini (.csv) yükleyeceğimiz Oracle tablosunu yaratalım.

CREATE TABLE hr_data
(
   satisfaction_level      NUMBER,
   last_evaluation         NUMBER,
   number_project          NUMBER,
   average_montly_hours    NUMBER,
   time_spend_company      NUMBER,
   Work_accident           NUMBER,
   left                    NUMBER,
   promotion_last_5years   NUMBER,
   sales                   VARCHAR2 (20),
   salary                  VARCHAR2 (20)
);

Tablomuzu yarattık, şimdi .csv olarak indirdiğimiz veri setimizi içeriye yükleyeceğiz, bunu yapmak için birden çok yöntemimiz bulunuyor. Bunlardan bazıları;

  • Oracle External Table yardımı ile yüklemek.
  • Oracle SQL Loader kullanarak yüklemek.
  • Kullandığımız editörlerden faydalanarak yüklemek.

Ben veri setini kullandığım editör yardımı ile yükleyeceğim. Editör olarak Toad kullanmaktayım. Toad ile aşağıdaki yolu takip ederek yükleme işlemini gerçekleştirebilirsiniz.

Database –> Import –> Import Table Data 

Toad ürünü ücretli olduğu için bu editörü kullanıyor olabilirsiniz. Ancak diğer editörlerin de bu özelliği mevcut, diğer editörlerle de bu işlemi kolaylıkla yapabilirsiniz. Örneğin ücretsiz olan Oracle SQL Developer ile veri yüklemesini aşağıdaki gibi yapabilirsiniz.

SELECT * FROM HR_DATA;

Evet veri seti yükleme işlemini tamamladık. Şimdi veriyi hangi kolona göre sınıflandıracağımızı belirleyelim.

Veri setimizi incelediğimizde her bir çalışan ile ilgili bir takım bilgiler var olduğunu görüyoruz. Bu bilgilerin en sonunda ise bu çalışanın maaşı (SALARY) ile ilgili bir bilgi verilmiş (low-medium-high). Biz elimizdeki verinin bir kısmı (training set) ile üreteceğimiz sınıflandırma modeli ile, verinin kalan kısmının (test set) maaş (SALARY) kolonu hakkında bir sınıflandırma sonucu üreteceğiz.  Son olarak ise kurduğumuz model ile ürettiğimiz sonuçlar ile  gerçek sonuçlara bakıp karşılaştıracağız.

Öncelikle verimizi eğitim (training %70) ve test (%30) olarak 2 ayrı kümeye ayıralım.

CREATE TABLE HR_DATA_MAIN AS
SELECT *
  FROM (SELECT ROWNUM RN,
               SATISFACTION_LEVEL,
               LAST_EVALUATION,
               NUMBER_PROJECT,
               AVERAGE_MONTLY_HOURS,
               TIME_SPEND_COMPANY,
               WORK_ACCIDENT,
               LEFT,
               PROMOTION_LAST_5YEARS,
               SALES,
               SALARY
          FROM HR_DATA); 
          
          

CREATE TABLE HR_DATA_TRAINING
AS
   SELECT SATISFACTION_LEVEL,
          LAST_EVALUATION,
          NUMBER_PROJECT,
          AVERAGE_MONTLY_HOURS,
          TIME_SPEND_COMPANY,
          WORK_ACCIDENT,
          LEFT,
          PROMOTION_LAST_5YEARS,
          SALES,
          SALARY
     FROM HR_DATA_MAIN
    WHERE RN < 10500;


CREATE TABLE HR_DATA_TEST
AS
   SELECT SATISFACTION_LEVEL,
          LAST_EVALUATION,
          NUMBER_PROJECT,
          AVERAGE_MONTLY_HOURS,
          TIME_SPEND_COMPANY,
          WORK_ACCIDENT,
          LEFT,
          PROMOTION_LAST_5YEARS,
          SALES,
          SALARY
     FROM HR_DATA_MAIN
    WHERE RN >= 10500; 

Eğitim ve test veri setimizi hazırladık. Şimdi algoritma ayarlarını okuyacağımız tablomuzu oluşturalım ve bu tabloya kullanacağımız algoritma parametrelerini girelim. Algoritmanın çalışması için gerekli tüm parametreleri girmek durumunda değiliz, opsiyonel olan parametreleri tanımlamadığımız takdirde Oracle bu parametreleri sistemde default tanımlı değerleri ile kullanmakta. Aşağıdaki tabloda Decision Tree algoritması için tanımlı parametreleri ve bu parametrelerin default değerlerini bulabilirsiniz.

KAYNAK

CREATE TABLE DTSETTINGS
(
   SETTING_NAME    VARCHAR2 (200),
   SETTING_VALUE   VARCHAR2 (200)
);


BEGIN
INSERT INTO DTSETTINGS
VALUES ('ALGO_NAME', 'ALGO_DECISION_TREE');

INSERT INTO DTSETTINGS
VALUES (dbms_data_mining.tree_impurity_metric, 'TREE_IMPURITY_ENTROPY');

COMMIT;
END;

Veri setimizi eğitim ve test olarak ayırdık, ardından algoritma ayarlarımızı oluşturduk. Şimdi modelimizi oluşturma adımına geçebiliriz.
(Eğitim için ayırdığımız veri setinin bulunduğu tablo adını ve algoritma parametrelerini kullanmak için oluşturduğumuz tablo adını DBMS_DATA_MINING.CREATE_MODEL metoduna parametre olarak gönderiyoruz.)

BEGIN
   DBMS_DATA_MINING.CREATE_MODEL (
      model_name            => 'DT_MODEL',
      mining_function       => DBMS_DATA_MINING.CLASSIFICATION,
      data_table_name       => 'HR_DATA_TRAINING',
      case_id_column_name   => NULL,
      target_column_name    => 'SALARY',
      settings_table_name   => 'DTSETTINGS');
END;

Modelimiz oluştu, şimdi modelimiz ile ilgili oluşan detaylara dictionary’den bakalım.

SELECT MODEL_NAME,
       ALGORITHM,
       COMMENTS,
       CREATION_DATE,
       MINING_FUNCTION,
       MODEL_SIZE
  FROM ALL_MINING_MODELS
 WHERE MODEL_NAME = 'DT_MODEL';

SELECT SETTING_NAME, SETTING_VALUE
FROM ALL_MINING_MODEL_SETTINGS
WHERE MODEL_NAME = 'DT_MODEL';

Modelimiz ile ilgili oluşmuş detayları da gördükten sonra şimdi modelimizi kullanarak test veri setimiz üzerinden prediction yapabiliriz.

SELECT T.SALARY ACTUAL,
 PREDICTION (DT_MODEL USING *) MODEL_PREDICT_RESPONSE,
 PREDICTION_PROBABILITY (DT_MODEL USING *) MODEL_PROBABILTY_RESPONSE
FROM HR_DATA_TEST T;

Evet test veri setimize modelimizi uyguladık ve modelimizin ürettiği tahminleri gözlemledik.

Kurduğumuz modelin başarımını kendimiz hesaplayabileceğimiz gibi DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX metodu ile de hesaplayabiliriz.

DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
      accuracy                     OUT NUMBER,
      apply_result_table_name      IN  VARCHAR2,
      target_table_name            IN  VARCHAR2,
      case_id_column_name          IN  VARCHAR2,
      target_column_name           IN  VARCHAR2,
      confusion_matrix_table_name  IN  VARCHAR2,
      score_column_name            IN  VARCHAR2 DEFAULT 'PREDICTION',
      score_criterion_column_name  IN  VARCHAR2 DEFAULT 'PROBABILITY',
      cost_matrix_table_name       IN  VARCHAR2 DEFAULT NULL,
      apply_result_schema_name     IN  VARCHAR2 DEFAULT NULL,
      target_schema_name           IN  VARCHAR2 DEFAULT NULL,
      cost_matrix_schema_name      IN  VARCHAR2 DEFAULT NULL,
      score_criterion_type         IN  VARCHAR2 DEFAULT 'PROBABILITY');

DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX metodunun kullanımına ait detaylı açıklamaları linke tıklayarak ulaşabilirsiniz.

Posted in Oracle, Uncategorized | Tagged , , , , , , , , | Leave a comment

Oracle ile Veri Şifreleme

Herkese Selam,

Bu yazıda Oracle’ın veri şifreleme desteğinden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

Oracle, yazılım geliştiricelere veri şifreleme konusunda 2 farklı paket sunuyor. Bunlar;

  • DBMS_CRYPTO (Oracle 10g ile geldi.)
  • DBMS_OBFUSCATION_TOOLKIT (Oracle 8i ile geldi.)

DBMS_CRYPTO daha güncel olması nedeni ile DBMS_OBFUSCATION_TOOLKIT’den daha fazla ve daha geniş özelliklere sahip. Aşağıda temel olarak bu 2 paket arasındaki farkları görebilirsiniz. 

DBMS_CRYPTO literatürdeki bir çok şifreleme ve hashleme algoritmalarını başarı ile desteklemekte. İhtiyacınız olan algoritmaları kolay bir şekilde seçerek istediğiniz veriyi şifreleyebilir/hashleyebilir veya şifrelenmiş bilgileri açabilirsiniz.

DBMS_CRYPTO Paketinin temel fonksiyonlarına aşağıdan göz atabilirsiniz.

Şimdi anlaşılması ve kullanılması kolay olan bu paket ile karmaşık bir ihtiyacı gideren bir örnek yapalım.

Senaryo: Kendisine parametre olarak gelen değeri DES (Cipher olarak CBC, Padding Olarak PCKS5 ayarları seçilmeli) algoritmasını kullanarak şifreleyip base64 formatında bir çıktı üreten şifreleme metodu yapalım? (Aynı yöntem ile şifrelenmiş veriyi açma işlemini yapacak bir metot daha yazalım.)

Cipher Mode : CBC

Padding: PCKS5

Şifreleme için kullanılacak key: 52AB32;^$!ER94988OPS3W21

Şifreleme için kullanılacak initialization vector (IV): TY54ABCX

Not: Initialization Vector şifreleme ve şifrelenmiş veriyi açmada zorunlu olan parametrelerden değil ancak biz örneği karmaşıklaştırmak için bu parametreyi de kullanıyoruz.

DBMS_CRYPTO paketinin ENCRYPT fonksiyonunun imzası;

DBMS_CRYPTO.ENCRYPT(
                            dst IN OUT NOCOPY BLOB,
                            src IN CLOB CHARACTER SET ANY_CS,
                            typ IN PLS_INTEGER,
                            KEY IN RAW,
                            iv  IN RAW DEFAULT NULL);

Kodu yazmadan önce şifrelemede kullanacağım KEY ve IV bilgisini hexadecimal’e çevirip bir tabloda saklamak istiyorum. Daha sonra Encryption ve Decryption adımlarında bu bilgileri yarattığım tablodan okuyacağım.

CREATE TABLE ALGPARAMETERS
(
  NAME   VARCHAR2(100 BYTE),
  VALUE  NVARCHAR2(100)
);

INSERT INTO ALGPARAMETERS
   SELECT 'key' NAME,
          RAWTOHEX ('52AB32;^$!ER94988OPS3W21') VALUE
     FROM DUAL
   UNION
   SELECT 'iv' NAME, RAWTOHEX ('TY54ABCX') VALUE FROM DUAL;

COMMIT;

SELECT * FROM ALGPARAMETERS;


Şimdi şifrelemeyi yapacak fonksiyonumuzu yazalım.

CREATE OR REPLACE FUNCTION F_ENCRYPT (p_input VARCHAR2)
   RETURN VARCHAR2
AS
   v_encrypted_raw     RAW (2000);
   v_key               RAW (320);
   v_encryption_type   PLS_INTEGER
      :=   DBMS_CRYPTO.DES_CBC_PKCS5;
   v_iv                RAW (320);
BEGIN
   SELECT VALUE
     INTO v_key
     FROM algparameters
    WHERE name = 'key';

   SELECT VALUE
     INTO v_iv
     FROM algparameters
    WHERE name = 'iv';

   v_encrypted_raw :=
      DBMS_CRYPTO.encrypt (src   => UTL_I18N.STRING_TO_RAW (p_input, 'AL32UTF8'),
                           typ   => v_encryption_type,
                           key   => v_key,
                           iv    => v_iv);
   RETURN UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.base64_encode (v_encrypted_raw));
END;

Şimdi şifrelenmiş veriyi açan fonksiyonumuzu yazalım.

DBMS_CRYPTO paketinin DECRYPT fonksiyonunun imzası;

DBMS_CRYPTO.DECRYPT(
                             dst IN OUT NOCOPY BLOB,
                             src IN            BLOB,
                             typ IN            PLS_INTEGER,
                             key IN            RAW,
                             iv  IN            RAW          DEFAULT NULL);  

 

CREATE OR REPLACE FUNCTION F_DECRYPT (p_input VARCHAR2)
   RETURN VARCHAR2
AS
   v_decrypted_raw     RAW (2000);
   v_key               RAW (320);
   v_encryption_type   PLS_INTEGER := DBMS_CRYPTO.DES_CBC_PKCS5;
   v_iv                RAW (320);
BEGIN
   SELECT VALUE
     INTO v_key
     FROM algparameters
    WHERE name = 'key';

   SELECT VALUE
     INTO v_iv
     FROM algparameters
    WHERE name = 'iv';


   v_decrypted_raw :=
      DBMS_CRYPTO.DECRYPT (
         src   => UTL_ENCODE.base64_decode (UTL_RAW.CAST_TO_RAW (p_input)),
         typ   => v_encryption_type,
         key   => v_key,
         iv    => v_iv);


   RETURN UTL_I18N.RAW_TO_CHAR (v_decrypted_raw, 'AL32UTF8');
END;

Şifreleme işlemini gerçekleştiren ve şifrelenmiş bilgiyi açan fonksiyonlarımızı yazdık. Sıra geldi bu fonksiyonları test etmeye.

SELECT 'TEST123TEST' INPUT, 
        F_ENCRYPT('TEST123TEST') ENCRYPTED_RESULT,
        F_DECRYPT(F_ENCRYPT('TEST123TEST')) DECRYPT_RESULT 
FROM DUAL;

Sonuçtan da görüldüğü gibi, ihtiyaçlarımız doğrultusunda şifreleme ve şifre açma işlemlerini başarılı ve hızlı bir şekilde yapabildik.

DBMS_CRYPTO paketi ile ilgili daha detaylı bilgiye erişmek için aşağıdaki linki takip edip ihtiyaçlarınıza uygun çözümü hızlıca uygulayabilirsiniz.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1003081

Posted in Oracle, Uncategorized | Tagged , , , , | Leave a comment

Oracle Data Mining (DBMS_DATA_MINING) ile Clustering Problemi Çözmek

Herkese Selam,

Bu yazıda Oracle Data Mining ile bir clustering problemi çözeceğim. Umarım farkındalık anlamında faydalı bir yazı olur.

Veri Bilimi ve Makine Öğrenmesi gibi konular günümüzde hakkında çokça söz ettiren başlıkların başında gelmekte. Öğrenmesi ve uygulaması uzmanlık gerektiren bu başlıklar için adreslenen problemleri ve yöntemleri günümüzde bir çok ürün veya yazılım ile gerçekleyebilmekteyiz. Oracle’da bu başlıklar altındaki problemlerin çözümüne ilişkin yöntem ve algoritmaları DBMS_DATA_MINING paketi ile desteklemekte.

DBMS_DATA_MINING paketi ile Custering, Classification, Regression, Anomaly Detection, Feature Extraction ve Assosication gibi modeller kurup, verimizi bu modellerden geçirip yorumlayabiliyoruz. Bu modellerden elde ettiğimiz sonuçları ise business kurgularımızda input olarak kullanabilmekteyiz.

DBMS_DATA_MINING paketi Oracle veritabanı üzerinde default kurulu olarak gelmiyor. Bu nedenle bu destekten faydalanabilmek için öncelikle bu paketi kurmak gerekiyor. Aşağıdaki linki takip ederek veritabanınıza Oracle Data Mining’ı kurabilirsiniz.

https://docs.oracle.com/cd/E11882_01/datamine.112/e16807/install_odm.htm#DMADM117

Oracle Data Mining paketi kurulumu ile beraber gelen 3 yeni dictionary tablomuz bulunmakta.

SELECT * FROM  ALL_MINING_MODELS;

SELECT * FROM ALL_MINING_MODEL_SETTINGS;

SELECT * FROM ALL_MINING_MODEL_ATTRIBUTES;

ALL_MINING_MODELS tablosunda Oracle’ın bu alt yapı ile bize sunmuş olduğu tüm modeller listesi ve modeller hakkında bilgiler bulunuyor.

ALL_MINING_MODEL_SETTINGS ve ALL_MINING_MODEL_ATTRIBUTES tablolarında ise bu modeller ile ilgili paramtereler ve spesifik detaylar bulunmakta.

Şimdi örnek bir kümeleme problemi çözmek için anlaşılması kolay bir veri seti hazırlayalım.

CREATE TABLE KMEANSDATA
(
   INSTANCE   NUMBER,
   X_AXIS     NUMBER,
   Y_AXIS     NUMBER
);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (1, 3, 5);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (2, 2, 1);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (3, 1, 1);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (4, 4, 3);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (5, 6, 1);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (6, 7, 5);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (7, 4, 4);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (8, 5, 6);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (9, 3, 8);

INSERT INTO KMEANSDATA (INSTANCE, X_AXIS, Y_AXIS)
     VALUES (10, 5, 6);

COMMIT;

Problemin anlaşılması adına basit bir veri seti ürettim. Amacım bu veri seti içerisinde bir birine yakın olanları K-Means algoritması ile kümelemek .

Öncelikle kısaca K-Means algoritması hakkında bilgi vermek istiyorum. K-Means verilen veri seti üzerindeki özelliklere bakarak bir birine benzeyen verileri aynı küme içerisinde toplayarak bize verilen veri setindeki benzerlikleri gösteren bir algoritma. K algoritmanın bir parametresi ve veri setinin kaç kümeye bölüneceğini bize söylüyor.

K-Means algoritmasının detaylı anlatımı için aşağıdaki linkler takip edilebilir.

https://sites.google.com/site/dataclusteringalgorithms/k-means-clustering-algorithm

https://docs.oracle.com/cd/E11882_01/datamine.112/e16808/algo_kmeans.htm#DMCON238

Data setimde toplamda 10 adet kayıt bulunmakta. Ben bu 10 kayıdı 3 farklı kümede toplamak istiyorum bu yüzden K değerimi 3 olarak seçeceğim. İlk olarak algoritmamın default parametre ayarlarına bakıyorum.

SELECT *
  FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
 WHERE setting_name LIKE 'KMNS%' OR setting_name like '%CLUS_NUM_CLUSTERS%';;

Evet default ayarlardan görüleceği üzere algoritmanın default K parametresi (CLUS_NUM_CLUSTERS) 10 olarak ayarlanmış. Bunu kendi problemimi çözmek için 3 olarak ayarlayacağım ve bununiçin yeni bir tablo yaratıyorum ve ilgili parametreyi güncelliyorum.

CREATE TABLE kmeanssettings
AS
   SELECT *
     FROM TABLE (DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
    WHERE setting_name LIKE 'KMNS%' OR setting_name like '%CLUS_NUM_CLUSTERS%';
    
UPDATE kmeanssettings
   SET setting_value = 3
 WHERE setting_name = 'CLUS_NUM_CLUSTERS';

UPDATE kmeanssettings
   SET setting_value = 10
 WHERE setting_name = 'KMNS_ITERATIONS';

COMMIT;

SELECT * FROM kmeanssettings;

Evet görüldüğü üzere ilgili parametreyi güncelledim. Şimdi bu ayarları kullanarak modelimi oluşturacağım.

 

 

BEGIN
   DBMS_DATA_MINING.CREATE_MODEL (
      model_name            => 'K_MEANNS_MODEL',
      mining_function       => DBMS_DATA_MINING.CLUSTERING,
      data_table_name       => 'KMEANSDATA',
      case_id_column_name   => 'INSTANCE',
      target_column_name    => NULL,
      settings_table_name   => 'KMEANSSETTINGS');
END;

Kodumuz çalıştı ve modelimizi belirlediğimiz güncel ayarlara göre oluşturduk. Şimdi bu modele veri setimizi verelim ve kümeleme sonuçlarını inceleyelim.

BEGIN
   DBMS_DATA_MINING.APPLY (model_name            => 'K_MEANNS_MODEL',
                           data_table_name       => 'KMEANSDATA',
                           case_id_column_name   => 'INSTANCE',
                           result_table_name     => 'KMEANS_RESULT');
END;

Şimdi sonuçlarımızı parametre olarak verdiğimiz tablodan inceleyelim.

SELECT * FROM KMEANS_RESULT;

Sonuç kümemize baktığımızda toplam 30 kayıt görüyoruz. Bunun nedeni, toplam veri setimizde 10 kayıt vardı ve biz algoritmaya küme sayısı olarak 3 değerini verdik. Bu durumda algoritma çıktısında her bir kaydın, farklı her küme için ayrı ayrı olasılığı hesaplandı (3X10 = 30). Bu noktadan sonra tam sonucu görebilmek için her bir elemanının maksimum olasılıklı kaydını bulup listeleyelim.

SELECT t1.instance,
       t1.CLUSTER_ID,
       t1.probability,
       t2.x_axis,
       t2.y_axis
  FROM (SELECT INSTANCE, CLUSTER_ID, PROBABILITY
          FROM (SELECT T.*,
                       MAX (PROBABILITY)
                       OVER (PARTITION BY INSTANCE ORDER BY PROBABILITY DESC)
                          MAXP
                  FROM KMEANS_RESULT T)
         WHERE MAXP = PROBABILITY) t1,
       KMEANSDATA t2
 WHERE t1.instance = t2.instance order by cluster_id;

Veri setimizin 3 farklı kümeye dağıldığını görüyoruz. Son olarak grafik üzerinden son duruma bakalım.

Grafik’ten de anlaşılacağı üzere modelimiz verimizi K-MEANS algoritmasına göre mantıklı bir şekilde kümeledi.

Posted in Oracle, Uncategorized | Tagged , , , , , , , , | Leave a comment

Oracle Hakkında Faydalı Linkler

Herkese Selamlar,

Bir süredir, üniversite öğrencisi olan ve Oracle veritabanı öğrenmek isteyen arkadaşlardan nasıl başlamalıyız? tadında sorular alıyorum. Bu soruları takiben nasıl, nereden ve hangi kaynakları kullanabiliriz gibi sorularda geliyor. Bu sorulara istinaden Oracle kaynaklarını içeren bir liste hazırladım. Bu liste yeni başlayan veya kendisini bu teknolojiler üzerinden geliştirecek arkadaşlara ışık tutar diye düşünüyorum. Linkler ve aklınıza takılan farklı sorular için çekinmeden iletişime geçebilirsiniz.

ORACLE İLE İLGİLİ FAYDALI LİNKLER

Oracle Database Documentation

–        http://docs.oracle.com/cd/E11882_01/index.htm

Database Architecture

–        http://docs.oracle.com/cd/B19306_01/server.102/b14220/intro.htm

–        http://www.juliandyke.com/Presentations/Presentations.html

–        http://www.juliandyke.com/Internals/Internals.html

SQL

–        http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm

–        http://www.oracle-base.com/articles/sql/articles-sql.php#11g

PL/SQL

–        http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm

–        http://psoug.org/reference/library.html  (Morgans Libarary)

–        http://www.stevenfeuerstein.com/

–        http://www.oracle-base.com/articles/plsql/articles-plsql.php#11g

Performance Tuning

–        http://docs.oracle.com/cd/E11882_01/server.112/e41573.pdf

–        http://jonathanlewis.wordpress.com/

–        https://blogs.oracle.com/optimizer/

–        http://blog.tanelpoder.com/

Oracle Forum (Konular-Sorular-Cevaplar) / Community

–        https://community.oracle.com/community/developer/english

–        http://asktom.oracle.com/pls/apex/f?p=100:1:0

–        http://www.oaktable.net/main

Takip Edilmesi Faydalı Bloglar/Sayfalar

–        http://asktom.oracle.com/pls/apex/f?p=100:1:0

–        http://jonathanlewis.wordpress.com/

–        http://www.stevenfeuerstein.com/

–        http://blog.tanelpoder.com/

–        http://arup.blogspot.com.tr/

–        http://kamranagayev.com/

–        http://kerryosborne.oracle-guy.com/

–        http://juliandontcheff.wordpress.com/

–        http://www.oaktable.net/main

–        www.troug.org

–        http://www.ukoug.org/home/

Okunması Faydalı Olacak Kitaplar

–        Expert One-on-One Oracle “Thomas Kyte”

–        Expert Oracle Database Architecture “Thomas Kyte”

–        Oracle PL/SQL Programming “Steven Feuerstein”

–        Oracle Essentials, Oracle Database 11g  “Rick Greenwald”

–        Troubleshooting Oracle Performance “Christian Antognini”

Posted in Oracle, Uncategorized | Tagged , , , , , , , , , | Leave a comment

Oracle 12c R2 – Partitioning İyileştirmeleri

Herkese Selam,

Bu yazıda Oracle 12c R2 ile beraber gelen partitioning konusundaki iyileştirmelerden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

Partitioning bilindiği üzere Oracle’ın en temel ve en çok beğendiğim özelliklerinden biri. Özellikle büyük hacimli tablolardaki verilere ulaşmada oldukça işimizi kolaylaştıran ve genel sorgu performansımızı arttıran bir veritabanı özelliği.

Oracle 12c R2’den önce bir tabloyu sadece yaratma aşamasında fiziksel olarak partitionlı bir şekilde yaratabiliyorduk. Bunun dışında partitionsız yaratılmış ve hızla büyüyen bir tabloyu partitionlı bir hale getirmek istediğimizde ne yazık ki bu işlemi gerçekleştiremiyorduk ve devamında veriye erişim performanslarımızda düşüşler yaşıyorduk.

Oracle 12c R2’den önce davranışın ne olduğuna bakmak gerekirse;

//db_version: 11g R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;

COMMIT;

ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707))

ORA-14006: geçersiz bölüm adı

Script Terminated on line 21.

Yukarıda partitionsız yaratmış olduğum tabloya Oracle 11g R2’de partitionlı bir yapıya getirmek istediğimde, bu işlemi doğrudan tablo üzerinde online olarak yapamamaktayım.

Şimdi bu operasyonu Oracle 12c R2 ile deneyelim.

//db_version: 12C R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;
   
   COMMIT;
   
ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707)) ONLINE;

Table PARTITIONTEST created.

99.999 rows inserted.

Commit complete.

Table PARTITIONTEST altered.

Şimdi partitionları kontrol edelim.

SELECT table_name, partition_name, high_value
  FROM all_tab_partitions
 WHERE table_name = 'PARTITIONTEST';

 

 

 

 

 

 

Evet görüldüğü üzere tabloyu partitionsız yaratıp daha sonra içine data attıktan sonra tabloyu fiziksel olarak partitionlı hale getirebildim. Bu işlemi yapmak için yazmış olduğum “ALTER TABLE … MODIFY” cümlesinin sonunda kullanmış olduğum “ONLINE” komutu tablonun fiziksel olarak partitionlama işlemi sürerken, tabloya eş zamanlı gelmesi muhtemel DML operasyonlarının da işlenmesine olanak sağladı. Böylelikle tabloda büyük bir fiziksel değişim yaparken herhangi bir downtime yaşamamış oldum.

Oracle 12c R2 ile beraber gelen bu yenilik sayesinde Non-Partitioned tabloları Partitioned hale getirme işleminin operasyonel ve downtime maliyetlerini ortadan kaldırılarak önemli gelişim sağlanmış oldu.

Posted in Oracle, Uncategorized | Tagged , , , , , | 1 Comment