부제목: view에서 union, minus, intersect를 제거하라
많은 사람들이 union과 union all의 차이점에 대해 알고 있다. 즉 union은 Sort와 중복제거라는 기능으로 인해 UNION ALL에 비하여 성능이 떨어진다는 것이다. 옳은 말이다. 하지만 union대신에 union all을 써야 하는 또 다른 이유가 있다는 것을 아는 사람은 얼마나 될까? 이 사실을 알지 못하면 불필요한 엑세스가 추가될 수 있으므로 성능이 저하된다.
먼저 고객테이블을 이용하여 뷰를 하나 만들고 그것을 이용한 SQL문을 만들어 보자.
환경 : Oracle 11.2.0.1
create or replace view vw_cust5 as
select *
from customers
union --> union을 사용함
select *
from customers;
select a.cust_id,
b.prod_id,
b.time_id,
b.channel_id,
b.quantity_sold
from vw_cust5 a,
sales b
where a.cust_id = b.cust_id
and a.cust_id = 14865 ;
뷰에서 사용하는 컬럼은 a.cust_id 하나 뿐이다. 따라서 고객 테이블에 PK인덱스를 사용한다면 customers 테이블로 엑세스 하지 않아도 된다. 하지만 아래의 실행계획을 본다면 문제를 발견할 수 있다.
-------------------------------------------------------------+----------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | | 62 |
| 1 | HASH JOIN | | 260 | 62 |
| 2 | VIEW | VW_CUST5 | 2 | 6 |
| 3 | SORT UNIQUE | | 2 | 6 |
| 4 | UNION-ALL | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 6 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 |
| 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 8 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 |
| 9 | PARTITION RANGE ALL | | 130 | 56 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 | 56 |
| 11 | BITMAP CONVERSION TO ROWIDS | | | |
| 12 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX| | |
-------------------------------------------------------------+----------------
PK 인덱스를 사용하였지만 customers 테이블로 불필요한 엑세스를 하였다. 이유가 무엇일까? 10053 trace를 보자.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
…중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)UNION (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
…중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "B"."CUST_ID"=14865
위의 trace는 쿼리변환을 끝낸 상태의 SQL이다. 그런데 SQL을 자세히 보면 뷰 내부의 모든 컬럼을 select 하고 있다. 다시 말해 뷰 내부의 컬럼중에 cust_id만 존재하면 되는데, 나머지 컬럼이 삭제되지 않고 남아있다. 이것 때문에 불필요한 테이블 엑세스가 나타난 것이다.
union 대신에 union all을 사용해보자
테스트를 위하여 100건 짜리 고객테이블을 만들고 unique 인덱스를 만들어 보자.
select *
from customers
where rownum < 101;
create unique index PK_CUSTOMERS_100 on customers_100 (cust_id);
이제 union 대신에 union all로 뷰를 생성하여 테스트한다.
select *
from customers
UNION ALL
select *
from customers_100 ;
SELECT a.cust_id,
b.prod_id,
b.time_id,
b.channel_id,
b.quantity_sold
FROM vw_cust2 a,
sales b
WHERE a.cust_id = b.cust_id
AND a.cust_id = 14865 ;
---------------------------------------------------------------+----------------
| Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | | 57 |
| 1 | HASH JOIN | | 260 | 57 |
| 2 | VIEW | VW_CUST2 | 2 | 1 |
| 3 | UNION-ALL | | | |
| 4 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 |
| 5 | INDEX UNIQUE SCAN | PK_CUSTOMERS_100| 1 | 0 |
| 6 | PARTITION RANGE ALL | | 130 | 56 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 | 56 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | |
| 9 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
---------------------------------------------------------------+----------------
SLP란 무엇인가?
실행계획에서 보듯이 union all 을 사용하니 테이블 엑세스가 사라졌다. 이것은 SLP(Select List Pruning)라는 쿼리변환의 기능 때문에 가능한 것이다. SLP는 union all이 들어있는 뷰를 사용할 때 발생하며 뷰의 컬럼중에 사용하지 않는 것을 제거한다. 이제 10053 trace 내용 중에서 SLP에 대해 분석해보자. 특히 SLP 변환 전 SQL과 SLP 변환 후 SQL을 비교해보라. 아래의 10053 trace 내용이 복잡해 보이지만 구조는 단순하며 다음과 같다.
1) 쿼리변환 전 SQL
2) 쿼리변환(SLP)
3) 쿼리변환 후 SQL
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS"."CUST_GENDER"
…중간생략
"CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID","CUSTOMERS_100"."CUST_FIRST_NAME""CUST_FIRST_NAME",
"CUSTOMERS_100"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS_100"."CUST_GENDER"
…중간생략
"CUST_SRC_ID","CUSTOMERS_100"."CUST_EFF_FROM""CUST_EFF_FROM","CUSTOMERS_100"."CUST_EFF_TO"
"CUST_EFF_TO" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865
Query block SEL$1 (#0) unchanged
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$3
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$2
SLP: Removed select list item CUST_FIRST_NAME from query block SET$1
...중간생략
SLP: Removed select list item CUST_EFF_TO from query block SEL$3
SLP: Removed select list item CUST_EFF_TO from query block SEL$2
SLP: Removed select list item CUST_EFF_TO from query block SET$1
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865
필요한 컬럼만 살아남다
SLP(Select List Pruning)가 발생하여 사용하지 않는 모든 컬럼을 삭제하였다. 쿼리변환 후의 SQL을 보면 뷰 내부의 컬럼은 모두 제거되고 cust_id만 남아있다. SLP 기능에 의해 테이블 엑세스가 없어진 것이다. 뷰 내부에는 union뿐만 아니라 minus,intersect 등의 집합 연산의 사용을 자제해야 한다. SLP가 발생하지 않기 때문이다.
그렇다면 뷰내부에 union all과 minus를 동시에 사용하면 어떻게 될까? 이제 union all과 minus를 동시에 사용한 뷰를 생성하고 SLP가 발생하는지 테스트 해보자.
SELECT *
FROM customers
UNION ALL
SELECT *
FROM customers
MINUS
SELECT *
FROM customers_100;
이제 위의 뷰를 사용하여 select문을 실행해보자.
SELECT a.cust_id,
b.prod_id,
b.time_id,
b.channel_id,
b.quantity_sold
FROM vw_cust33 a, sales b
WHERE a.cust_id = b.cust_id
AND a.cust_id = 14865 ;
---------------------------------------------------------------+----------------
| Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | | 63 |
| 1 | HASH JOIN | | 260 | 63 |
| 2 | VIEW | VW_CUST33 | 2 | 7 |
| 3 | MINUS | | | |
| 4 | SORT UNIQUE | | 2 | |
| 5 | VIEW | | 2 | 4 |
| 6 | UNION-ALL | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 8 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 |
| 9 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 10 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 |
| 11 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS_100 | 1 | 1 |
| 12 | INDEX UNIQUE SCAN | PK_CUSTOMERS_100| 1 | 0 |
| 13 | PARTITION RANGE ALL | | 130 | 56 |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 | 56 |
| 15 | BITMAP CONVERSION TO ROWIDS | | | |
| 16 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
---------------------------------------------------------------+----------------
minus가 존재하여 SLP가 발생되지 않았다. Minus 때문에 불필요한 테이블 엑세스가 세 번이나 발생되었다. 어떻게 하면 이 문제를 해결할 수 있을까? 물론 부정형 서브쿼리(not exists)를 사용하면 minus 를 대신할 수 있으므로 불필요한 테이블 엑세스는 없어질 것이다.
minus를 사용하면서 SLP가 가능한가?
문제는 minus를 사용하면서 불필요한 엑세스를 방지할 수 있는 방법이 있냐는 것이다. 가장 쉬운 방법은 뷰에서 minus 부분을 제거하는 것이다. 즉 아래의 SQL처럼 minus 대신에 테이블을 직접 사용하면 된다.
select * from customers
UNION ALL
select * from customers ;
SELECT a.cust_id,
b.prod_id,
b.time_id,
b.channel_id,
b.quantity_sold
FROM VW_CUST a, --minus가 빠진 뷰를 사용함
sales b
WHERE a.cust_id = b.cust_id
AND a.cust_id = 14865
MINUS
SELECT a.cust_id,
b.prod_id,
b.time_id,
b.channel_id,
b.quantity_sold
FROM CUSTOMERS_100 a, --테이블을 직접 사용함
sales b
WHERE a.cust_id = b.cust_id
AND a.cust_id = 14865 ;
-----------------------------------------------------------------+----------------
| Id | Operation | Name | Rows | Cost |
-----------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | | 115 |
| 1 | MINUS | | | |
| 2 | SORT UNIQUE | | 260 | 59 |
| 3 | HASH JOIN | | 260 | 58 |
| 4 | VIEW | VW_CUST | 2 | 2 |
| 5 | UNION-ALL | | | |
| 6 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 |
| 7 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | 1 |
| 8 | PARTITION RANGE ALL | | 130 | 56 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 | 56 |
| 10 | BITMAP CONVERSION TO ROWIDS | | | |
| 11 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
| 12 | SORT UNIQUE | | 130 | 56 |
| 13 | NESTED LOOPS | | 130 | 55 |
| 14 | INDEX UNIQUE SCAN | PK_CUSTOMERS_100| 1 | 0 |
| 15 | PARTITION RANGE ALL | | 130 | 55 |
| 16 | TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 | 55 |
| 17 | BITMAP CONVERSION TO ROWIDS | | | |
| 18 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
-----------------------------------------------------------------+----------------
예상대로 뷰에서 minus를 삭제하니 성공적으로 SLP가 발생되었고 테이블 엑세스가 모두 사라졌다.
결론
이번 시간에는 union과 union all의 또 다른 차이점에 대해 알아보았다. union all을 사용하면 SLP가 발생되어 뷰에서 사용되지 않는 컬럼을 제거한다. 이때 인덱스만으로 scan을 끝낼수 있는 경우 불필요한 테이블스캔이 방지된다. 따라서 뷰 내부에서는 union, minus, intersect를 빼는 것이 유리하다. 뷰 내부의 minus는 not exists로 바꾸면 된다. 대부분의 경우 뷰 내부에 Intersect도 필요치 않다. Intersect란 교집합이며 이것은 조인으로 해결할 수 있다. 왜냐하면 조인이란 두 집합에서 조인된 컬럼을 기준으로 값이 같은 것만 추출하는 기능이기 때문이다. 그렇지 않은가?
'Oracle > Optimizer' 카테고리의 다른 글
| Cardinality Feedback이 위험할 때 (10) | 2010.10.25 |
|---|---|
| 공지 - Cardinality Feed Back이 위험할 때 (1) | 2010.10.22 |
| 메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2) | 2010.04.28 |
| 해결사 되기 (15) | 2010.02.04 |
| Distinct Elimination : 불필요한 Distinct를 제거하라 (6) | 2010.01.25 |
