(12cR1 신기능) 오라클 Scalar Subquery Unnesting (_optimizer_unnest_scalar_sq)

Oracle 12cR1 New Feature 중에서 Scalar Subquery Unnesting 이라는 것이 있습니다.

스칼라 서브쿼리를 Unnest 한다는 얘기인데요.

"스칼라 서브쿼리"란 SQL문의 Select 절에 들어있는 또다른 Select 문을 가리킵니다.

 

 

Unnest 는 "둥지에서 쫒아낸다"는 의미의 단어입니다.

즉, 스칼라서브쿼리 부분을 그 위치에서 쫒아내서 다르게 변환시켜서 수행하겠다는 것입니다.

 

왜?

그러면, 많이 빨라지는 경우가 있기 때문이죠~

한번 확인해 볼까요??

 

 

아래와 같이 테스트 테이블 t1, t2 를 만듭니다.

극적인 상황 연출을 위해 t2 테이블은 자기자신을 3번 더 복제해서 좀 크게 만듭니다.

 

-- 테이블 t1 생성
create table t1 
as 
select * 
from all_users;

-- 테이블 t2 생성
create table t2 
as
select b.*
from all_users a, all_objects b
where a.username = b.owner;

-- 3번 복제하여 t2 테이블의 데이터를 크게 증식
insert into t2 select * from t2;
insert into t2 select * from t2;
insert into t2 select * from t2;

 

아래와 같이 t1 테이블 37건으로 작은 테이블이고, t2 테이블은 37만건으로 꽤 큰 테이블이 되었습니다.

 

 

아래의 SQL 문으로 테스트해 보겠습니다.

SQL> select t1.user_id,
       (select max(created)
        from t2
        where t2.owner = t1.username) cnt
from t1
where t1.username like '%'
order by 1,2;

 

먼저, set autotrace traceonly explain 커맨드로 Plan 을 확인해보겠습니다.

 

 

처음 실행했을때는 디폴트로 Scalar Subquery Unnest 가 작동하여 자동으로 Plan 이 바뀐 것입니다.

마치 처음부터 스칼라 서브쿼리는 없었던 것처럼, t1, t2 두 테이블을 Hash Join 하는 형태로 Plan 이 바뀌었습니다.

 

두번째는 Unnest 기능을 끄고 실행한 것인데,

t1, t2 테이블 Access 만 나오고, Join 하는 Plan 은 없습니다. 이게 스칼라로 처리했음을 의미합니다.

 

위 2가지의 Plan 중 어떤것이 빠를까요?

당연히 첫번째 Plan 이 빠릅니다.

t2 테이블에 아무 Index 도 만들지 않았기 때문에, t1 에서 나온 결과만큼(37건) t2 테이블을 37번 Full Scan 해야 합니다.

이 스칼라쿼리를 그대로 실행하면 매우 느릴 수 밖에 없습니다.

 

하지만, Hash Join 의 경우는 각각 t1, t2 테이블을 한번씩만 읽어서 Join 하기 때문에 빠릅니다.

 

실제로 실행시켜서 확인해보겠습니다.

 

Unnest 가 작동한 경우는 0.62 초 걸렸습니다.

 

 

alter session set "_optimizer_unnest_scalar_sq" = false;   커맨드로 unnest 기능을 끄고 실행했더니,

무려 32초나 걸렸네요. 32배 느려진 것을 확인할 수 있습니다.

 

놀라운데요^^

 

이 테스트는 아래 톰아저씨의 글을 참조했습니다.

https://asktom.oracle.com/pls/apex/asktom.search?tag=scalar-subquery-unnesting