잡동사니 보물섬

수정 가능 조인 뷰 활용 

 - 먼저 set 절에 여러 컬럼들은 해당 컬럼들이 한 테이블에 있는 컬럼들이라면 (,) 로 연결 하여 

    서브쿼리 사용 시 테이블을 한 번만 읽어서 쿼리 할 수 있다. 

 - 또한 update 해야 하는 테이블은 총 고객 수가 아주 많다면 exists 서브쿼리를 통해 

    해시 세미 조인으로 유도하는 것을 고려할 수 있다. 

 - set 절에 사용된 서브쿼리에는 캐싱 매커니즘이 작용하므로 distinct  value 개수가 적은 

    1쪽 집합을 읽어 M쪽 집합을 갱신할 때 효과적이다. 

    (즉 update 해야 할 테이블 M , 읽어야 할 테이블 1 일때 ) 

    (물론 exists 서브쿼리가 NL 세미 조인이나 필터방식으로 처리된다면 거기서도 캐싱 효과가 

     나타난다. ) 

수정 가능 조인 뷰 

 - 수정 가능 조인 뷰를 활용하면 참조 테이블과 두 번 조인하는 비효율을 없앨 수 있다. 

 - '조인 뷰'는 from 절에 두 개 이상 테이브을 가진 뷰를 가리킨다. 

 - '수정 가능 조인 뷰' 는 말 그대로 입력, 수정, 삭제가 허용되는 조인 뷰를 말한다. 

 - 단, 1쪽 집합과 조인되는 M쪽 집합에만 입력, 수정, 삭제가 허용된다.  

 - 1쪽 집합에 PK 제약을 설정하거나 Unique 인덱스를 생성해야 수정 가능 조인 뷰를 통한 

     입력/수정/삭제가 가능해진다. 

 - PK 제약을 설정하면 M쪽 집합은 '키-보존 테이블'이 되고, dept 테이블은 '비 키-보존 테이블'로 

     남는다. 

키 보존 테이블이란 

 - 조인된 결과집합을 통해서도 중복 값 없이 Unique하게 식별이 가능한 테이블 

 - 단적으로 말해 '키 보존 테이블'이란, 뷰에 rowid를 제공하는 테이블 

 -  *_UPDATABLE_COLUMNS 뷰를 통해 키 보존 테이블이 컬럼인지 아닌지 쉽게 확인할 수 있다.

수정가능 조인 뷰 제약 회피 

 - bypass_ujvc 힌트는 키 보본 테이블이 없더라도 update 수행이 가능하게 하는 힌트

 - update를 위해 참조하는 집합에 중복 레코드가 없을 때만 이 힌트를 사용해야 한다. 

Merge문 활용 

 - DW에서 가장 흔히 발생하는 오퍼레이션은, 기간계 시스템에서 가져온 신규 트랜잭션  

   테이터를 반영함으로써 두 시스템 간 데이터를 동기화시키는 작업 

 - bypass_ujvc 힌트가 필요할 때 아래와 같이 merge문으로 처리를 할 수도 있다. 

 - when not matched then 구문은 when matched then 에 해당 하지 않으며 

   있을 시 where 절에도 해당 하는 row들이 적용 된다. 

 - delete 절은 when matched then 절에서만 사용할 수 있다. 

   ( 또한 , update를 한 결과물로 제한이 되어서 실행 된다. ) 

다중 테이블 insert 활용 

 - 큰 테이블을 읽어서 여러 테이블에 insert 해야 할 때 큰 테이블을 한 번만 읽어서 

   여러 테이블에 insert를 효율적으로 할 수 있게 하는 기능 

 - insert first, insert all 이 있다. 

스칼라 서브쿼리 

 - 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 '스칼라 서브쿼리' 라고 한다. 

 - 스칼라 서브쿼리는 주로 select-list 에서 사용되지만 몇 가지 예외사황을 뺀다면 컬럼이 

    올 수 있는 대부분 위치에서 사용 가능하다. 

 - 스칼라 서브쿼리를 이용해서 outer 조인문과 같은 결과를 낼 수도 있다. 

 - 스칼라 서브쿼리는 내부적으로 NL 조인과 수행하는 처리 경로도 동일하다. 

    ( 내부적으로 캐싱 기법이 작용한다는 점이 달라, 이를 이용한 튜닝이 자주 행해진다. ) 

스칼라 서브쿼리의 캐싱 효과 

 - 오라클은 스칼라 서브쿼리 수행횟수를 최소화하려고 그 입력 값과 출력 값을 내부 캐시에 

    저장해 둔다. 

 - 스칼라 서브쿼리가 수행될 때만 일단 '입력 값'을 캐시에서 찾아보고 거기 있으면 

    '저장된 출력 값' 을 리턴한다. 

 - 캐시에서 찾지 못할 때만 쿼리르 수행하며, 결과를 버리지 않고 캐시에 저장해 둔다.

    * 여기서 나의 해석을 더 하자면 

       - M:1 일 경우 1은 스칼라 서브쿼리의 테이블일 시 입력 값의 중복이 많기 때문에 

         동일한 결과 값을 사용할 수 있는 경우가 커서 활용도가 높다. 

 - 반복 수행되는 함수 때문에 쿼리 성능이 크게 저하될 때, 스칼라 서브쿼리를 덧씌움으로써 

   호출 횟수를 줄일 수 있다. 

    ( 그냥 사용자 정의 함수를 사용하게 되면 row 마다 함수가 호출 된다. 

       그러나 스칼라 서브 쿼리르 사용하게 되면 캐싱 기능을 사용할 수 있다. ) 

 - 입력 값과 출력 값을 빠르게 저장하고 찾기 위해 오라클은 해싱 알고리즘을 사용한다. 

 - 해시 충돌이 발생했을 때 기존 엔트리를 밀어내고 새로 수행한 입력 값과 출력값을 

    대체하는 것이 아닌 오라클은 기존 캐시 엔트리를 그대로 둔 채 스칼라 서브쿼리만 

    한 번 더 수행하고 만다. 따라서 해시 충동리 발생한 입력 값이 반복적으로 입력되면 

    스칼라 서브쿼리도 반복 수행된다. 

 - 결론적으로, 스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 

    적을 때랑야 효과가 있으며, 반대의 경우라면 캐시를 확인하는 비용 때문에 오히려 성능은 

    저하되고 CPU 사용률만 높게 만든다. 

 - 스칼라 서브쿼리를 사용하면 NL 조인에서 Inner 쪽 인덱스와 테이블에 나타나는 버퍼 Pinning

    효과도 사라진다는 사실을 기억할 필요가 있다.  

두 개 이상의 값을 리턴하고 싶을 때 

 - 구하고자 하는 값들을 모두 결합하고 바깥쪽 엑세스 쿼리에서 substr 함수로 분리하는 방법 

     ( || 을 사용해 in-line view 안에서 스칼라 서브쿼리르 한 번 사용하고 , 

       다시 바깥쪽에서 substr 를 이용해 잘라서 사용 ) 

 - 오브젝트 TYPE을 사용 , TYPE을 미리 선언해 두어야 하는 불편함이 있지만 

    SQL은 훨씬 깔끔해진다. 

Outer NL 조인 

 - Outer 기호(+)가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택된다. 

    ( (+)가 붙은 쪽이 나머지를 null로 붙인다고 생각하면 편하다. ) 

 - outer 조인은 ERD 관계와 연관이 많기 때문에 ERD 표기법에 대한 이해가 필수이다. 

   ( ERD 표기법은 데이버베이스 프로그래머에게는 필수이기는 한다. ) 

   ( 엔터티 간 관계를 해석할 때도 카디널리티만 보지 말고 Optionality를 반드시 따져봐야한다. ) 

 - 혹시 있을지 모를 null 값을 두려워해 습관적으로 Outer 기호(+)를 붙인다면 성능상 

    불이익이 생길 수 있다. 

Outer 소트 머지 조인 

 - Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정된다. 

Outer 해시 조인 

 - 9i에서 Outer 해시 조인을 수행해 보면, Outer 기호(+)가 붙지 않은 테이블이 항상 

     Build Input으로 선택된다. 

 - 해시 조인은 10g 부터 swap_join_input 힌트를 통해서 outer 조인의 순서 변경이 가능하다. 

9i 이전 버전에서 Outer 해시 조인 튜닝 

 - 테이블을 2번 사용하여 한 번은 최소한의 상태로 hash 조인 하고 조인 후 build input을 

     줄인 상태에서 한번 더 hash_join을 한다. 

 - 구간을 나눠 쿼리를 여러번 수행하는 방법도 생각해볼 수 있음 

   ( 해시 버킷당 엔트리 개수를 최소화 하면, 고객 테이브을 반복적으로 읽는 비효율에도 

     불구하고 더 바르게 수행될 수 있음 ) 

Full outer 조인 

 - Left Outer 조인 + Union All + Anti 조인 (Not Exists 필터) 이용 (9i 이전) 

 - ANSI Full outer 조인 ( 9i 이후 ) 

 - Native Hash Full Outer 조인 

    ● 11g 에서 'Native Hash Full Outer 조인'을 선보였고, 

        필요하면 10.2.0.4 버전에서도 Hidden 파라미터를 조정해 이 기능을 사용할 수 있음 

    ● 양쪽 테이블을 한 번씩 엑세스 한다는 것이 가장 큰 변화,  null인 레코드가 마지막에 

         출력된 것을 통해, 내부적으로 어떤 식으로 처리하는지 추정 가능 

 - Union All을 이용한 Full Outer 조인

    ● 두 테이블을 각각 한 번씩만 엑세스하였으며, 조인 대신 sort(또는 hash) group by 연산을 

        수행한다. 

       ( union all 을 사용할 때는 중복 제거가 없어 group by , sum 을 사용해야 한다.)