Semi-Join ve Anti-Join Optimizasyonu

Herkese Selam,

Bu yazıda sizlere Semi-Join ve Anti-Join yöntemlerinin performans etkilerinden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

Semi-Join 

Semi-Join yöntemi, eğer sorgu içerisinde bir subquerry varsa ve bu subquerry’de IN, EXISTS veya =ANY  gibi ifadeler ile  ana sorguya bağlanmış ise kuvvetle muhtemel Oracle Cost Based optimizer tarafından iki kümeyi bağlayabilmek için seçilecek yöntem olacaktır.

Döndürdüğü sonuç itibari ile standart inner join’e benzesede aralarında bazı temel farklar mevcuttur. Inner join yönteminde ana sorgudan çekilen her kayıt için, alt sorguda eşleşmenin sağlandığı tüm kayıtlar geriye döndürülürken, Semi-Join’de alt sorguda ilk eşleşme sağlandığı takdirde, alt sorguda arama yapılmaya devam edilmeden üst sorguya dönülerek yeni bir kayıt alınarak işleme devam edilir.

Şimdi bir sorgu ile optimizer’ın semi join seçimini gözlemleyelim.
Sorgu: Satis tablosunda en az 1 kez alış veriş kaydı olan müşterileri listeleyelim.

SELECT /* https://emrahmete.wordpress.com */ *
  FROM SH.CUSTOMERS c
 WHERE EXISTS
          (SELECT '1'
             FROM sh.sales s
            WHERE C.CUST_ID = S.CUST_ID); 

-- Execution Plani Görelim
SELECT *
 FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('ccaruwgqtq88q'));

sjeexplan

SELECT /* https://emrahmete.wordpress.com */    *
  FROM SH.CUSTOMERS c
 WHERE c.cust_id IN (SELECT s.cust_id
                       FROM sh.sales s);


-- Execution Plani Görelim
SELECT *
FROM v$sql
WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('335xqjwmy1v7y'));

sjeexplan

Evet yukarıdaki 2 sorgudan da görüleceği üzere her iki planda Semi-Join kullanarak aynı sonuca ulaştı.

Peki aynı sorguyu standart join yönetemleri ile yapsaydık performans anlamında nasıl bir sonuç elde edecektik.

Inner joinin ürettiği sonuç ile semi-joinin ürettiği sonucun aynı olamayacağını yukarıda açıklamıştık. Bu bağlamda bu sorgu sonucunu inner join ile çözmek için DISTINCT söz deyimini kullanmalıyız.

SET TIMING ON
SELECT  /* https://emrahmete.wordpress.com  */ 
DISTINCT C.CUST_ID
  FROM sh.customers c, sh.sales s
 WHERE C.CUST_ID = S.CUST_ID;
 /* 7059 rows selected.
Elapsed: 00:00:05.68 */
 
-- Execution Plani Görelim
SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('db8bys0f37pq6'));

ijtest

SET TIMING ON
SELECT /*  https://emrahmete.wordpress.com */  C.CUST_ID
  FROM SH.CUSTOMERS c
 WHERE EXISTS
          (SELECT '1'
             FROM sh.sales s
            WHERE C.CUST_ID = S.CUST_ID);    
/* 7059 rows selected.
Elapsed: 00:00:04.18 */            
             
-- Execution Plani Görelim

SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('8sbu2vth574h9'));

sjeexplan

Evet gördüğümüz planlardaki cost ve çalışma sürelerini incelediğimizde aynı sorgu sonucunu semi-join ile daha hızlı bir şekilde gerçeklendiğini görmekteyiz.

Sorgularımızın Semi-Join metodunu kullanabilmesi için belli kısıtlamalar var. Sorgu optimizasyonu yaparken bu yöntemi kullanmak istiyorsak mutlaka aşağıdaki hususları göz önünde bulundurmalıyız aksi takdirde sorgumuz dilediğimiz gibi çalışmayacaktır.

  • Yazdığımız sorgular IN, EXISTS veya =ANY içermeli.
  • EXISTS veya IN içeren sorgularımızın where koşulunu OR ile başka bir condition ile birleştirmemeliyiz.
  • EXISTS kullanıyorsak mutlaka dış sorguya bağımlı yani correlated olmalı.

Anti Join

 Adreslediği problem olarak semi-join in zıttıdır diyebiliriz.  Yine inner join ve farklı alternatif yollar ile bu problemleri çözebilsekte anti-join daha optimize ve daha performanslı sonuçlar ortaya koyabilmekte.

anti

 

Temel olarak çalışma mekanizması, dışdaki sorgudan gelen ve içerdeki sorguda karşılığı olmayan sonuçları döndüren sorgulama yöntemi olarak ifade edebiliriz.

 

Şimdi örnek bir soru ile bu yöntemi ve performansını inceleyelim.

Sorgu: Satis tablosunda hiç kaydı olMAyan müşterileri listeleyen sorguyu yazalım.

NOT IN

SET TIMING ON
SELECT /* https://emrahmete.wordpress.com */    *
  FROM SH.CUSTOMERS c
 WHERE c.cust_id NOT IN (SELECT s.cust_id
                       FROM sh.sales s);

-- Execution planını görelim
SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('4h3jxygcczh64'));

anti1

NOT EXISTS

SET TIMING ON
SELECT /* https://emrahmete.wordpress.com */  C.CUST_ID
  FROM SH.CUSTOMERS c
 WHERE NOT EXISTS
          (SELECT '1'
             FROM sh.sales s
            WHERE C.CUST_ID = S.CUST_ID);
/*48441 rows selected.
Elapsed: 00:00:28.08*/

-- Execution Plani Görelim
SELECT *
FROM v$sql
WHERE sql_text LIKE '%https://emrahmete.wordpress.com%';

SET LINESIZE 2000
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('1wu6k0hh2zwvj'));

anti not exist

notexistist

 

Şimdi aynı sorguyu standart minus ile çözmeye çalışalım.

SET TIMING ON
SELECT CUST_ID 
FROM SH.CUSTOMERS
MINUS
SELECT CUST_ID
FROM SH.SALES;
/*48441 rows selected.
Elapsed: 00:00:30.69*/

SELECT *
  FROM v$sql
 WHERE sql_text LIKE '%MINUS%';

SET LINESIZE 2000

SELECT * FROM TABLE (DBMS_XPLAN.display_cursor ('53j1b490zwvxf'));

minus

minexist

Evet planları ve çalışma istatistiklerini incelediğimizde optimizer ın anti-join seçimi ile daha performanslı bir iş yaptığını görmekteyiz.

Sorgularımızın  Anti-Join metodunu kullanabilmesi için belli kısıtlamalar var. Sorgu optimizasyonu yaparken bu yöntemi kullanmak istiyorsak mutlaka aşağıdaki hususları göz önünde bulundurmalıyız aksi takdirde sorgumuz dilediğimiz gibi çalışmayacaktır.

  • NOT IN, NOT EXISTS veya !=ALL conditionlarını kullanarak sorgumuzu yazmalıyız.
  • NOT EXISTS kullanıyorsak, subquerry nin dış sorguya bağlı olmasına dikkat etmeliyiz. Yani correlated bir sorgu yazmalıyız.
  • NOT IN veya NOT EXISTS kullanılarak bir yapı kurduysak OR conditionı ile başka bir koşul koymamalıyız.
  • Eğer 10g veritabanı kullanıyorsak ve NOT IN ile bu işi gerçeklemeye çalışıyorsak subquerry den dönen sonuç içerisinde NULL bir değer gelmemesini garanti etmeliyiz. aksi takdirde anti-join yöntemi kullanılamaz. (NVL veya NOT NULL Constraint  veya IS NOT NULL yapıları kullanılarak sağlanabilir.)

 

REFERENCES

https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

https://jonathanlewis.wordpress.com/join-ordering-pt2/

PRO Oracle SQL

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