Çalışma Zamanında Üretilen Dinamik Sorgu Sonuçlarının Performanslı Üretimi

Herkese Selam,

Bu yazıda spesifik bir konuya performanslı bir çözüm önereceğim, umarım farkındalık anlamında faydalı bir yazı olur.

Çalışma zamanında (Run-Time) belirli parametrelere göre dinamik sorgu üretip,  bu sorguyu çalıştırıp dönen sonucu kullanmak zaman zaman yaptığımız işlemlerden.

Şimdi  örnek bir case üzerinden böyle bir ihtiyacı daha performanslı nasıl çözüm üretebileceğimizi inceleyelim.

Örneğimizi SH şeması altındaki SALES tablosunda yapacağız. Tablonun kolonları ve tipleri aşağıdaki gibidir.

TABLE sh.sales
 
 Name                                      Null     Type                        
 ----------------------------------------- -------- --------------
 PROD_ID                                   NOT NULL NUMBER                      
 CUST_ID                                   NOT NULL NUMBER                      
 TIME_ID                                   NOT NULL DATE                        
 CHANNEL_ID                                NOT NULL NUMBER                      
 PROMO_ID                                  NOT NULL NUMBER                      
 QUANTITY_SOLD                             NOT NULL NUMBER(10,2)                
 AMOUNT_SOLD                               NOT NULL NUMBER(10,2)                

Şimdi simülasyonumuzu nasıl yapacağımızdan bahsedelim. Amacım SH.SALES tablosunun kullanıcı tarafından run time’da belirlenen kolonlarına göre dinamik bir SQL üretip bu sonucu ekranda göstermek. Sonuç ekranda göstermenin haricinde bir işlem de olabilir ancak ben rahat anlaşılması ve simülasyonunu kolaylaştırmak amaçlı bu yolu tercih ediyorum.

Gerçekleme adımlarım şöyle olacak;

1- Gönderilen parametreleri alıp içeride dinamik sorgu oluşturacak ve geriye bir sys_refcursor döndürecek bir fonksiyon yaratacağım.

2- Daha sonra 1 numaralı adımda ürettiğim sys_refcursor’ı alıp çalıştırıp ve ekranda gösterilmesi için geriye sonuçları döndürecek bir fonksiyon yaratacağım.

3.- Son adımda ise 2 numaralı fonksiyonumun ürettiği sonuçları bir select yardımı ile ekranda gösterilmesini sağlayacak sorgumu yazacağım. Bu sonucu Oracle’ın TABLE metodunu kullanarak yapmayı planlıyorum bundan dolayı 2 tane type yaratmaya ihtiyacım var bu işi gerçeklemek için.

İlk olarak alt yapımı kurmak için tiplerimi yaratacağım. İlk yaratacağım tip ekrana döndüreceğim sonuçların kolonlarını içeren bir obje olacak.

CREATE OR REPLACE TYPE typ_sh_sales AS OBJECT
                  (PROD_ID NUMBER,
                   CUST_ID NUMBER,
                   TIME_ID DATE,
                   CHANNEL_ID NUMBER,
                   PROMO_ID NUMBER,
                   QUANTITY_SOLD NUMBER (10, 2),
                   AMOUNT_SOLD NUMBER (10, 2));

ikinci adım olarak ise TABLE metodunu kullanabilmek için geriye yukarıda yarattığım tipte bir array döndürülmesini sağlamak. Bu yüzden yukarıda yarattığım tipin bir array’ini yeni bir tip olarak yaratıyorum.

CREATE OR REPLACE TYPE typ_sh_sales_arr IS TABLE OF typ_sh_sales;

Evet 2 ayrı kullanacağım tipi oluşturduktan sonra alt yapımı kurmak için 1 numaralı adımda bahsetmiş olduğum dinamik SQL’i üretip geriye bir cursor döndürecek fonksiyonu yaratıyorum.

CREATE OR REPLACE FUNCTION FN_GENERATE_SQL_FOR_SALES (
P_PROD_ID          NUMBER,
P_CUST_ID          NUMBER,
P_TIME_ID          DATE,
P_CHANNEL_ID       NUMBER,
P_PROMO_ID         NUMBER,
P_QUANTITY_SOLD    NUMBER,
P_AMOUNT_SOLD      NUMBER)
   RETURN SYS_REFCURSOR
IS
   v_sql        VARCHAR2 (4000);
   where_pred   VARCHAR2 (4000) := ' where 1=1 ';
   ref_sales    SYS_REFCURSOR;
BEGIN
   v_sql := 'SELECT TIME_ID,
                   QUANTITY_SOLD,
                   PROMO_ID,
                   PROD_ID,
                   CUST_ID,
                   CHANNEL_ID,
                   AMOUNT_SOLD
              FROM SH.SALES ';

   IF P_PROD_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and prod_id = ' || P_PROD_ID;
   END IF;

   IF P_CUST_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and cust_id = ' || P_CUST_ID;
   END IF;

   IF P_CHANNEL_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and CHANNEL_ID = ' || P_CHANNEL_ID;
   END IF;

   IF P_PROMO_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and PROMO_ID = ' || P_PROMO_ID;
   END IF;

   IF P_AMOUNT_SOLD IS NOT NULL
   THEN
      where_pred := where_pred || ' and AMOUNT_SOLD = ' || P_AMOUNT_SOLD;
   END IF;


   IF P_TIME_ID IS NOT NULL
   THEN
      where_pred := where_pred || ' and TIME_ID = ' || P_TIME_ID;
   END IF;

   OPEN ref_sales FOR v_sql || where_pred;

   RETURN ref_sales;
END;

Evet yukarıda yaratmış olduğum fonksiyonda gelen parametrelerin durumuna göre dinamik bir sorgu üretip bu içeriği çalıştırılmak üzere bir fonksiyona gönderiyorum.

Şimdi yukarıda yarattığım fonksiyonun döndürdüğü sys_refcursor ı alıp fetch edecek ve sonuçları döndürecek metodu yazıyorum.

CREATE OR REPLACE FUNCTION FN_GENERATE_SQL_FOR_SALES (i_cursor SYS_REFCURSOR)
   RETURN typ_sh_sales_arr
   PIPELINED
   PARALLEL_ENABLE(PARTITION i_cursor BY ANY)
IS
   tuple   typ_sh_sales
              := typ_sh_sales (NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL,
                               NULL);
BEGIN
   LOOP
      FETCH i_cursor
      INTO tuple.TIME_ID,
           tuple.QUANTITY_SOLD,
           tuple.PROMO_ID,
           tuple.PROD_ID,
           tuple.CUST_ID,
           tuple.CHANNEL_ID,
           tuple.AMOUNT_SOLD;

      EXIT WHEN i_cursor%NOTFOUND;
      PIPE ROW (tuple);
   END LOOP;

   CLOSE i_cursor;

   RETURN;
END;

Evet yukarıdaki fonksiyonda temel olarak gelen Cursor’ın fetch edilip her fetch işlemi yapıldığı anda sonucun o an geriye döndürülmesi sağlanıyor. Bunu sağlayan mekanizma PARALLEL PIPELINE mekanizması. Bu mekanizma ile ilgili detaylı bilgiye, nasıl kullanıldığına ve faydalarına “Oracle Parallel Pipelined Functions” başlıklı yazımdan bakabilirsiniz. Bu kullanılan alt yapı sayesinde sorgu sonucu çok hızlı bir şekilde ve daha az hafıza alanı tüketerek geriye dönmekte.

Şimdi işlemimizin son adımı olarak Test kısmına geçelim ve bu şekilde kurulmuş bir alt yapıyı nasıl select edeceğimize bakalım.

 

SELECT *
  FROM TABLE (
          FN_REILL_SQL_FOR_SALES (
             FN_GENERATE_SQL_FOR_SALES (P_PROD_ID => 13,
                                        P_CUST_ID         => NULL,
                                        P_TIME_ID         => NULL,
                                        P_CHANNEL_ID      => NULL,
                                        P_PROMO_ID        => NULL,
                                        P_QUANTITY_SOLD   => NULL,
                                        P_AMOUNT_SOLD     => NULL)));

/* 6002 rows selected.
Elapsed: 00:00:03.46 */

result

Evet görüldüğü üzere arka planda ön taraftan gönderdiğim parametreler ile dinamik SQL oluşturup bu SQL’i parallel ve pipelined bir biçimde execute edip sonucu ekrana döndürmüş oldum. Bu aşamada TABLE metodunu kullandım. Bu metot sayesinde geriye array döndüren bir fonksiyonu tablo gibi okumuş oldum. Klasik execute immediate yöntemiyle benzer işlemi yapmış olsaydık performansımız ve memory kullanımımızın ne kadar farklı olduğunu gözlemleyebilirdik.

 

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s