🔑 SQL 튜닝 - CH 4. 조인 튜닝 - NL 조인

JOIN ▶  연결고리 

🍲 NL 조인

조인의 기본은 NL 조인이다 . 

NL 조인 ▶  인덱스를 이용한 조인

❗ NL 조인을 이해 한다면 다른 조인 방식도 쉽게 이해 할수 있다

NL 조인
2개의 테이블을 조인할 때 A 테이블의 각 행에 대해 B 테이블의 모든 행을 중첩반복 검사를 해서
조인 조건을 만족하는 행을 찾는다.

바깥쪽 반복문은 A 테이블의 행들을 돌고,
안쪽 반복문은 B 테이블의 행들을 돈다.
이렇게 반복문을 통해 조인 조건을 만족하는 행들을 찾아 결과를 반환한다.

1 - 1 기본 메커니즘

select e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사일자 >= '19960101'
and c.관리사원 번호 = e.사원번호
;

① 사원 _X1 인덱스에서 입사일자가 보이는데 여기서 입사일자 >= 19960101 행을 찾는다
② 인덱스에서 읽은 ROWID 로 사원 테이블에 행을 찾아간다
③ 사원 테이블에서 읽은 사원번호는 0006인데  고객_X1의 인덱스에서 탐색한다
④ 고객_X1 인덱스에서 읽은 ROWID 로 고객 테이블에 행을 찾는다 '0006'은 2개가 존재한다

다시 사원_X1 인덱스로 이동해 스캔해서 그다음 행인 19960712 행을읽어 사원테이블 행을 찾는다
앞에 말한 순서와 같은 식이며 이렇게 반복한다.

✔ 인덱스를 보면 이미 정렬이 되어있어서 테이블에서 행을 빠르게 찾을수 있다

 

 

❗ ROWID 가 뭔데 ?

테이블에서 행의 위치를 지정하는 논리적인 주소값이라고 한다.

Oracle DB에서 사용되는 용어이며, 테이블의 각 행을 고유하게 식별하는 값을 의미한다.
행의 물리적 위치를 나타내며
인덱스를 사용하여 특정 조건을 만족하는 행을 검색할 때, 인덱스는 해당 행의 ROWID를 제공한다
ROWID를 사용하면 DB는 직접적으로 해당 행의 물리적 위치에 접근할 수 있다.
이렇게 하면 특정 행에 매우 빠르게 접근할 수 있으므로 성능이 향상된다.

 

 

---

1 - 2 실행계획 제어

NL 조인 실행 계획 
▶위에 사원 테이블 기준으로 아래 고객테이블과 NL조인 한다는 것을 알수 있다
    각 테이블을 액세스 (읽다) 할 때 인덱스를 이용한다는 사실 또한 실행 계획에 나와있다.

 

ordered 힌트가 보인다 FROM절에 쓰여있는 순서대로 조인하라고 옵티마이저에게 지시한다

use_nl 힌트는 NL 방식으로 조인하라고 지시한다

🔑 3개 이상의 테이블을 조인할 때 힌트 사용 방법 !

 

1 ) 해석해보면 A - B - C - D 순서로 조인을하라 , 대신 B와 조인할때 그리고 C와 조인할때는  NL 방식으로 조인하고
     D 와 조인할 때는 해시 방식으로 조인하라     

/* A -> B -> C -> D 순으로 조회 */
select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ *
from A, B, C, D
where ...
;

 

2 ) ordered 대신 leading 을 사용했다. leading을 사용하면 FROM 절을 바꾸지  않고 마음대로 순서를 제어할수 있다

C - A  - D - B 순서로 되어있는데 C를 먼저 읽고 A를 조인한뒤 그 결과로 D를 조인하고 
다시 그 결과로 B를 조인한다  
이렇게 되면 옵티마이저의 자동선택을 무시하고 올바른 선택을 내가 줄수 있다 
또한 편리함은 덤이다.

/* leading 힌트를 사용하면 원본을 바꾸지 않고 순서를 제어할 수 있다. */
select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hasg(B) */ *
from A, B, C, D
where ...
;

 

3 ) 아래에는 ordered 나 leading 이 없다.. 4개의 테이블을 NL 방식으로 조인하는데  옵티마이저가 스스로 정하게 한다.

select /*+ use_nl(A,B,C,D) */  *
from A,B,C,D
where ........

 

NL조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리(OLTP)에 적합한 조인 방식이다.

* NL 조인 재구성

아래 코드에서 잘못된 곳은?

select *
from   PRA_HST_STC a, ODM_TRMS b
where  a.SALE_ORG_ID = :sale_ord_id
and    a.STRD_GRP_ID = b.STRD_GRP_ID
and    a.STRD_ID     = b.STRD_ID
order by a.STC_DT desc
;

 

Inner 테이블 alias를 왼쪽에 기술하는 것이 중요

select *
from   PRA_HST_STC a, ODM_TRMS b      /* a가 outer 테이블, b가 inner 테이블 */
where  a.SALE_ORD_ID = :sale_org_id
and    b.STRD_GRP_ID = a.STRD_GRP_ID  /* inner테이블 alias를 왼쪽에 기술!!! */
and    b.STRD_ID     = a.STRD_ID
order by a.STC_DT desc
;

 

 

 

옵티마이저 가 뭔데 자기가 판단해 ?

SQL 옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할수 있는 최적의 데이터 액세스 경로를 선택해주는
DBMS의 핵심엔진이다.
옵티마이저가 뭔데 자기가 판단할까 생각하다가 기록 해두었던 글을 다시 찾아보았다...

https://gi-dor.tistory.com/131

🔑SQL 튜닝 - SQL 처리과정 & I / O

 

옵티마이저는 데이터베이스의 구성요소 중 하나로, 쿼리를 어떻게 실행할 것인지를 결정하는 컴포넌트
쿼리를 실행하는 데 있어 ,  가장 효율적인 방법을 찾아내는 역할을 합니다. 
데이터베이스의 통계 정보, 테이블의 크기, 인덱스의 유무, 데이터의 분포 등 여러 가지 요인을 고려합니다
. 옵티마이저의 목표는 쿼리의 결과를 빠르게 반환하면서 동시에 자원 사용을 최소화하는 것입니다.
조인 순서도 이러한 고려사항 중 하나로,
옵티마이저는 여러 테이블을 조인하는 쿼리의 경우 가능한 모든 조인 순서를 평가하여
가장 효율적인 실행 계획을 선택합니다.

그러나, 때로는 개발자가 특정 조인 순서를 강제하고 싶을 때가 있습니다.
예를 들어, 옵티마이저가 통계 정보가 부정확하거나 최신 상태가 아니기 때문에 최적의 계획을 선택하지 못하는 경우가 있을 수 있습니다.
이런 경우에 LEADING이나 ORDERED 힌트를 사용하여 옵티마이저의 선택을 오버라이드(재정의)할 수 있습니다.