멀티컬럼 스칼라뷰를 이용한 1:M 쿼리 속도향상

1. 일반적인 1:M 조인에서 M 부분을 group by 시키고 조인하는 쿼리

select /*+ gather_plan_statistics */ a.apt_name, b.* 

from apt_info a, (select apt_code, count(distinct apt_code) a_cnt, count(*) d_cnt from dong_info group by apt_code) b

where a.apt_code = b.apt_code

and a.bjd_code = '1111010100'

and rownum <= 10;

10 rows selected.

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=10)

   2 - access("A"."APT_CODE"="B"."APT_CODE")

   4 - access("A"."BJD_CODE"='1111010100')

   

   

문제점 : group by 되는 테이블의 FULL SCAN 이 일어남, 만약 해당 테이블에 인덱스가 잘 되어 있더라도 INDEX FAST FULL SCAN은 일어나야 함   

   

   

2. 1과 결과는 같으나 스칼라 서브쿼리를 이용해 group by 되는 부분을 RANGE SCAN으로 바꾸는 방법

CREATE OR REPLACE TYPE TEST_TYPE AS OBJECT

(a_cnt NUMBER,

 b_cnt NUMBER);

/ -> 멀티 컬럼을 담기 위한 OBJECT 생성 

select /*+ gather_plan_statistics */  b.apt_name, b.dong_cnt.a_cnt, b.dong_cnt.b_cnt

from (select a.apt_name,  

(select TEST_TYPE (count(distinct apt_code), count(*)) from dong_info b where b.apt_code = a.apt_code group by apt_code) as dong_cnt

from apt_info a

where a.bjd_code = '1111010100') b

where rownum <= 10           

and b.dong_cnt.a_cnt > 0; -- apt_info 와 조인되는 컬럼만 뽑기 위한 방편

결과는 위와 상동

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("APT_CODE"=:B1)

   3 - filter(ROWNUM<=10)

   6 - access("A"."BJD_CODE"='1111010100')

       filter(SYS_OP_ATG(,1,2,2)>0)

   8 - access("APT_CODE"=:B1)

   

결과 : group by 절의 테이블이 INDEX RANGE SCAN으로 바뀌어서 실행 시간 및 접근 ROW가 줄어 들었다.   

참고자료 : 멀티로우 멀티컬럼을 담기 위한 방법

CREATE OR REPLACE TYPE NUMBER_TOWN_ARRAY

AS VARRAY(100) OF NUMBER_TWO_TYPE ;

select b.apt_code, d.a_number, d.b_number from (

select apt_code, CAST( MULTISET (select apt_code, dong_code from dong_info b where b.apt_code = a.apt_code) as NUMBER_TWO_ARRAY) as dong_info_multi

from apt_info a

where apt_code  = 1381

) b,

TABLE(dong_info_multi) d -> 앞서 제시한 멀티 로우 멀티 컬럼 스칼라 서브쿼리를 테이블화 시키는 구문

where d.a_number > 0  -> 스칼라 서브쿼리 내에서 아우터 조인으로 인한 빠지는 값을 제거하기 위한 조치