누적 매출 구하기
- 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(지역) 이 들어간 쿼리를 말한다.
( 조인이 될 수 없는 값들이 맨 마지막 값으로 쿼리 되는 것 )