성능도고화 2-8. 고급 조인 테크닉

누적 매출 구하기 

 - 8i부터 제공되기 시작한 분석함수를 이용하면 간단함 

 - 분석함수를 이용할 수 없는 상황에서 부등호 조인과 group by를 이용해 구할 수 있음 

선분이력 끊기 

 - greate, least 함수를 기억하라 

   (max, min이 수직적인 계산이라면, greate, least 행으로의 계산으로 이해하면 쉽다. ) 

데이터 복제를 통한 소계 구하기 

 - 오라클 9i부터는 dual 테이블을 사용하면 편하다. 

  * grouping sets 에 대한 테스트 내용 

     - grouping sets를 사용하면서도 기본적으로 그룹함수가 사용되지 않음 컬럼들은 

       group by절에 모두 사용 되어야 한다. 

    

상호배타적 관계의 조인 

 - 어떤 엔터티가 두 개 이상의 다른 엔터티의 합집합과 관계를 갖는 것을 

   '상호배타적 관계' 라고 한다 . 

    ( 여기서는 합집합 관계를 갇는 것을 상호배타적이라고 해서 헷갈렸다. 

       아직도 이건 잘못된 정의로 쓴 것 같다. ) 

 - 상호배타적인 관계의 경우 2가지 방법으로 테이블을 구성할 수 있다. 

     두 개의 컬럼을 두어서 각각 해당 되는 컬럼에 값을 입력하고 나머지는 null 처리 방법 

       - 이 경우는 outer 조인으로 간단하게 쿼리를 작성할 수 있다. 

     한 개의 컬럼을 두어서 해당 컬럼에 '1,2' 와 같이 값을 나누어서 넣는 방법

       - 이 경우는 union all을 활용하거나 

       - where 절에 decode 함수나 outer 조인을 활용한다.

최종 출력 건에 대해서만 조인하기 

 - 조인을 완료하여 order by를 하고 rownum 를 뽑아서 필요한 부분만 처리하는 경우 

   부하가 심하다.

 - 해당 부분에 부하를 줄이기 위하여 하나의 테이블의 컬럼들로 order by를 진행할 수 있고 

    해당 테이블이 인덱스가 where 조건에서 사용 되고 그로인하여 필터링이 다량 되어 

    최종 결과를 크게 줄일 수 있을 때, 해당 테이블의 where절과 order by를 우선적으로 실행하고

    거에서 rownum 값을 뽑아서 다른 테이블들과 조인을 시도 하는 방법이 있다. 

      * 나의 생각 

          - 그러나 이런한 경우는 위와 같이 제약이 많이 때문에 잘 따져보고 사용해야 한다. 

 

징검다리 테이블 조인을 이용한 튜닝

 - from절에서 조인되는 테이블 개수를 늘려 성능을 향상시키는 사례 

 - 최종 결과 건수는 얼마 되지 않으면서, 필터 조건만으로 각 부분을 따로 읽으면 결과 

   건수가 아주 많을 때 튜닝하기가 가장 어렵다. NL 조인 과정에서 Random I/O 부하가 심하게 

   발생하기 때문이며, 어느 쪽으로 드라이빙하더라도 결과는 마찬가지다. 

인조 식별자 사용에 으한 조인 성능 이슈 

 - 인조 식별자를 잘못 사용하게 되면 인덱스에 조건절을 실제 쿼리사 사용하지 못하여 

    더 많은 스캔을 발생 시킬 수도 있다. 

인조 식별자를 둘 때 주의 사항 

 - 인조식별자를 두면 PK, FK가 단일 컬럼으로 구성되므로 테이블 간 연결 구조가 단순해지고, 

   제약조건을 위해 사용되는 인덱스 저장공간이 최소화되는 장점이 있다. 그리고 다중 컬럼으로

   조인할 때보다 아무래도 조인 연산을 위한 CPU 사용량이 조금 줄 수 있다. 

 - 하지만 조인 연산할 때의 CPU 사용량 감소느 ㄴ아주 미밈한 수준이고, 오히려 앞서 설명한 

   사례처럼 조인 연산 횟수와 블록 I/O증가로 더 많은 시스템 리소스를 낭비하기 쉽다.  

 - 논리적이 데이터 모델링 단계에서는 가급적 인조 식별자를 두지 않는 것이 좋다. 

 - 의미상 주어에 해당하는 속성들을 그대로 식별자로 사용했다가 나중에 물리 설계 단계에서 

   저장 효율과 엑세스 효율등을 고려해 결정하는 것이 바람직하다.  

점이력 조회 

 - 데이터 변경이 발생할 때마다 변경일자와 함께 새로운 이력 레코드를 쌓는 방식 

  * 점이력 조회시 주의사항 

     - 원래 스칼라 서브쿼리를 사용하게 되면 버퍼 pinning 효과가 사라진다. 

        그러나 그룹 함수를 사용하게 되면 buffer pinning효과가 나타난다. 

        하지만 이 경우도 그룹함수에 들어가는 컬럼이 가공되면 buffer pinning 효과가 사라진다. 

 - 만약 2개 이상의 컬럼을 읽어야 한다면 스칼라 서브쿼리 내에서 필요한 컬럼 문자열을 

   연겨랗고, 메인 쿼리에서 substr 함수로 잘라 쓰는 방법을 사용해야 한다. 

 - 컬럼이 많아지만 스칼라 서브쿼리에서 rowid 값만 취하고 고객별연체이력을 한 번 더 

   조인하는 방법을 생각해 볼 수 있다. 

   ( 이 경우도 where절로 인하여 해당 되는 row수가 크게 줄을 때 효과가 클 것이다. )

 - where 절에 스칼라 서브쿼리를 사용하여 해당 되는 row들을 찾는 경우 

   서브쿼리에서 조인 절을 사용하였다면 따로 조인 조건을 기술해줄 필요 없고 

   버퍼 pinning도 사용할 수 있을 것으로 생각된다. 

정해진 시점 기준으로 조회 

 - 같은 테이블을 두번 사용해서 한 번은 조건을 줄이고 (정해진 시점으로) 다시 자신의 테이블에 

    조인해서 필요한 컬럼들을 뽑아 낼 경우, 그냥 필요한 컬럼들을 한번에 붙여서 

    substr를 사용해서 필요한 컬럼들만 뽑아 낼 수 있다. 

 - 분석 함수(max, min () over ) 보다는 row_number , rank 함수가 성능성 유리한다고 한다. 

   (이유에 대한 부분은 나온 것은 없고 책에는 테스트를 통해서만 이야기 하였다. )  

선분이력 조인 

 - 과거/현재/미래의 임의 시점 조회 

     과거 시점 between and 사용 

     현재 시점 종료일자 = '99991231'

                   ( 미래일자를 미리 입력하는 경우 sysdate 와 between and 사용 )

     임의의 시점 조회시 

        - 바로 그 시점을 조회시에는 

            해당 시점의 컬럼 과 between을 사용해서 쿼리 

          현재 시점의 종목명과 상장주식수를 출력시 

            sysdate와 between 을 사용해서 쿼리 

 

선분이력 조인 튜닝 

 - 선분이력에서는 where 절에 입력되는 조건값에 따라서 인덱스의 순서도 중요하다. 

    ( where 절에 입력되는 조건 값에 따라서 시작일이나 종료일 중 무엇이 앞에 오는 것이 

       유리 할 지 달라진다. ) 

 - Between 조인 튜닝은 조회 대상이 많지 않을 때 

 - 일반 조인문으로는 index , rownum 힌트로 튜닝할 수 없지만 조인 컬럼이 unique 한 값으로 

   소량만 조인이 되는 것이라면 하나의 테이블에서 그 양을 줄여준 다음 스칼라 서브 쿼리를 

   통해 index, rownum힌트를 사용하여 튜닝하는 것도  하나의 방법이 될 수 있다.

   ( 스칼라 서브쿼리의 특징을 사용하여 튜닝하는 방법으로 생각 됨  )  

 - 위의 방법을 사용하여 여러개의 컬럼을 출력할 경우는 스칼라 서브 쿼리에서 rowid 를 

   출력하고 다시 한 번 해당 rowid를 통해서 자신과 조인하는 방법이 있다. 

   ( 스칼라 서브쿼리에서 한 번만 출력하고 substr로 잘라 쓰는 방법도 있다. ) 

 - 또한, where 절에 스칼라 서브 쿼리를 써서 rowid 조인을 할 수도 있다.  

   ( 이 부분은 쿼리 실행 해석이 좀 재미 있는 부분이다. ) 

   * 위의 3가지 경우는 모두 같은 조건이 성립되어야 원하는 성능 튜닝이 제대로 될 것이다. 

Between 조인 튜닝 - 조회 대상이 많지만 대상별 이력 레코드가 많지 않을 때 

 - 만약 전체 고객을 대상으로 한다면 Random 엑세스 위주의 NL 조인보다 아래처럼 해시 

   조인을 이용하는 것이 효과적이다. 

Between 조인 튜닝 - 대상별 이력 레코드가 많을 때 

 - 대상별 이력 레코드가 많을 때의 between 조인이 가장 튜닝하기 어렵다. 

   ( 해시 조인을 하더라도 해당 키 값에 중복되는 것들이 많아서 해시 버킷에 해시 체인들이 

     많이 들어가서 해시 버킷을 읽는데 시간이 오래걸린다.  )

 - 이럴 때 글쓴이는 첫번째, 두 개 이상 월에 걸치는 이력이 생기기 않도록 매월 말일 시점에 

   강제로 이력을 끊어주는 것이다. 

    ( 해시 체인을 스캔하는 비효율을 완전히 없앨 수는 없지만 최대 31개가 넘지 않도록 

     제한하려는 것이다. ) 

 - 두 개 이상 월에 걸치는 이력이 없도록 쿼리 시점에 선분이력을 변환해주는 것이다. 

   그런 다음 조인하는 방법은 앞에서와 같고, 마찬가지로 해시 체인을 스캔하는 양은 

   최대 31개로 제한될 것이다. 

   ( 이 방식을 사용하면 '일별상품거래'와 조인할 때는 빠르지만, '월도' 테이블과 조인하는 

    과정에서 오히려 병목이 생길 수 있다. ) 

조인에 실패한 레코드 읽기 

 - 그 in (' ', c.지역) , max(지역) 이 들어간 쿼리를 말한다. 

   ( 조인이 될 수 없는 값들이 맨 마지막 값으로 쿼리 되는 것 )