PERFORMANS TUNING -6 OPTIMIZER JOIN METHOTDS

Join operasyonları genelde (tek tablo üzerinde de dahi join olabilir. Olamaz demeyin HR employees tablosunda olabiliyor 🙂 ) birden fazla tablodan getirilen verilerin belirli şartlara göre birleştirilmesi operasyonlarıdır, genelde tablolar arasında belirli ilişkiler kurulur ve bu ilişkiler üzerinden birleştirme işlemleri gerçekleştirilir. Bu işlem gerçekleşirken Optimizer farklı Join yöntemlerini seçebilir bunlar;

• Nested loops
• Sort-Merge Join
• Hash Join
• Cartesian Join
• Cluster Join

SELECT e.ename, d.dname
FROM dept d JOIN emp e USING (deptno)       <--Join predicate
WHERE e.job = 'ANALYST' OR e.empno = 9999;
  <--Nonjoin predicate

SELECT e.ename,d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND            <-- Join predicate
(e.job = 'ANALYST' OR e.empno = 9999);   <-- Nonjoin predicate

NESTED LOOPS JOIN

Nesteed Loop bir tablodan alınan veriler ile diğer tablodan döngüsel olarak erişim yapılarak getirilmesidir. (İç içe for döngüsü şeklinde çalışan joindir).

Yukarıdaki sorguyu iç içe for döngüsü olarak yazarsak aşağıdaki gibi bir durum ortaya çıkacaktır.

for r1 in (select rows from EMP that match single table predicate) loop
   for r2 in (select rows from DEPT that match current row from EMP) loop
      output values from current row of EMP and current row of DEPT
     end loop
end loop

Nested Loop ta join ikinci tabloya çok hızlı erişiyorsa ve tablonun küçük bir kısmına erişiyorsa fayda sağlayacaktır. Inner ve outher tablolara CBO karar vermektedir.

MERGE (SORT) JOIN

Tabloarın birleştirilmesi belli bir sıralama koşuluna dayanıyor ise bu join methodu kullanılır. Eğer bu Join yöntemin CBO tarafından kullanılması zorlayacaksak "USE_MERGE(alias1 alias2)" seklinde HINT kullanabiliriz.

select /*+ USE_MERGE(d e) NO_INDEX(d) */ ename, e.deptno, d.deptno, dname
from emp e, dept d where e.deptno = d.deptno and ename > 'A'

Çoğu durumda Merge Join Performans katilidir ve Hash join daha performanslı çalışabilir. Ancak join yapılan alanlarda sıralama işlemi varsa merge join veriyi sıralı getirdiği için daha iyi performans alınabilir (Veri indexten gelmiyorsa). Sort işlemlerinin CPU maliyetinin yüksek olduğu unutulmamalıdır ve Merge Join HINT çok elzem değilse kullanılmamalıdır.

HASH JOIN

Tablolardan birinin HASH değerleri hesaplanarak memory üzerine alınması diğer tablonun da sorgulanarak bu hash ile eşleşme sonucunun join yapılması mantığında dayanır, genelde performans açısından CBO küçük tabloyu hash alarak memory üzerine almayı tercih eder. Büyük tablolar için performanslı bir yoldur ve buna uygun sorgular varsa "USE_HASH" HINT kullanabiliriz.

Eğer sorgularda memory yetmez TEMP tablespace kullanmaya başlarsa performans oldukça kötü etkilenecektir.

select /*+ USE_HASH(e d) */
ename, e.deptno, d.deptno, dname
from emp e, dept d
where e.deptno = d.deptno and ename like 'A%’

Cartesian Join

Join yapılan tabloların kartezyen çarpımı yapılarak birleştirilmesidir. Sonuç her iki tablonun satrır sayılarının çarpımı kadardır, genelde join şartı unutulduğu durumlarda karşımız çıkar, çok küçük tablolar için kullanılabilir ancak onun dışında eğer karşımıza çıkıyor ise bundan mutlak kurtulmak gerekir.

SQL> explain plan for select e.FIRST_NAME,d.DEPARTMENT_ID,e.DEPARTMENT_ID,d.DEPARTMENT_NAME from hr.employees e, hr.departments d where e.FIRST_NAME like 'A%';

Explained.

SQL> select * from table(dbms_xplan.display);
Plan hash value: 2111462691

----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |   285 |  7410 |    20   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN                |             |   285 |  7410 |    20   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES   |    11 |   110 |     3   (0)| 00:00:01 |
|*  3 |    INDEX SKIP SCAN                   | EMP_NAME_IX |    11 |       |     1   (0)| 00:00:01 |
|   4 |   BUFFER SORT                        |             |    27 |   432 |    17   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                 | DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |

JOIN PERFORMANCE

Yukarıda verilen Her bir Join Metodu için en uygun şartlar genelde aşağıdaki durumlarda oluşmaktadır.

• Nested Join
  • Küçük Tablolar
  • Join yapılan kolonlar indexli
• Hash Join
  • Büyük Tablolar
  • Bir Büyük ve Bir küçük Tablo
  • Join için büyük bir tablonun bir kısmı seçilmişse
• Merge-Sort Join
  • Non-equijoin (eşit olmayan küçüktür büyüktür veya bir aralık ile joinleme)
  • İstenen satırlar sıralı bir şekilde isteniyorsa

JOIN TÜRLERİ

• – Join (Equijoin/Natural
•   – Nonequijoin)
•   – Outer join (Full, Left, and Right)
•   – Semi join: EXISTS subquery
•   – Anti join: NOT IN subquery
•   – Star join (Optimization)

Equijoins & Nonequijoins

Bir eşitlik üzerine birşleştirme yapılan join türüne Equijoin , bir aralık ve sıralama üzerine bir birleştirme yapılıyorsa buna Nonequijoins denir.

OUTHER JOIN

Birleştirme işleminde eşleşmeyen kayıtlarında birleştirilmesi ile sonuçlanan join operasyonlarıdır.

SEMI JOIN

İlk buldıuğu kayıtı döndürür. Exist kullanımı ile gerçekleşir.

ANTI JOIN

Eşleşmeyen kayıtları birleştirir, not in kullanımı ile gerçekleşir.