1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 2.11 LV(Lateral View) View를 Scalar 서브쿼리처럼 사용하라

2.11 LV(Lateral View) View를 Scalar 서브쿼리처럼 사용하라 (by hun1361) [2013.06.11]

2.11 LV* (Lateral View) : View를 Scalar 서브쿼리처럼 사용하라

  • Lateral View : 스칼라 인라인뷰 (인라인뷰지만 마치 스칼라 서브쿼리처럼 메인쿼리의 컬럼들을 이용하여 조인하고 Filter 할 수 있다.)
{code:SQLtitle= 예제borderStyle=solid}
SQL> SELECT e.employee_id, e.email, d.department_id
FROM employee e LEFT OUTER JOIN department d
ON ( e.department_id = d.department_id AND e.employee_id > 7600);

EMPLOYE EMAIL DEPAR

---
-
-
189 JDILLY
190 TGATES
191 RPERKINS
192 SBELL
193 BEVERETT
.
.
. --중략
186 JDELLING
187 ACABRIO
188 KCHUNG

107 rows selected. --사번이 7600보다 큰 건들만 조회되지 않고 전체가 조회되었다.


{code:SQL|title= 10053 Trace |borderStyle=solid}
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "E"."EMPLOYEE_ID" "EMPLOYEE_ID",
			 "E"."EMAIL" "EMAIL",
			 "E"."DEPARTMENT_ID" "QCSJ_C000000000300000",
			 "from$_subquery$_004"."DEPARTMENT_ID_0" "QCSJ_C000000000300001"
FROM "TLO"."EMPLOYEE" "E",
			LATERAL( (SELECT "D"."DEPARTMENT_ID" "DEPARTMENT_ID_0"
			          FROM "TLO"."DEPARTMENT" "D"
			          WHERE "E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "E"."EMPLOYEE_ID">7600))(+) "from$_subquery$_004"
--인라인뷰를 Scalar서브쿼리처럼 사용

Plan분석


============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id  | Operation           | Name      | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |           |       |       |     3 |           |
| 1   |  NESTED LOOPS OUTER |           |   107 |  1605 |     2 |  00:00:01 |
| 2   |   TABLE ACCESS FULL | EMPLOYEE  |   107 |  1284 |     2 |  00:00:01 |
| 3   |   INDEX UNIQUE SCAN | DEPT_ID_PK|     1 |     3 |     0 |           |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
3 - filter("E"."EMPLOYEE_ID">CASE  WHEN ("D"."DEPARTMENT_ID" IS NOT NULL) THEN 7600 ELSE 7600 END )

결론


1) Lateral View는 결과 건수에 영향을 미치지 못하는 스칼라 인라인뷰이다.
2) E.EMPLOYEE_ID > 7600 조건은 결과 건수에 영향을 못미치고 DEPARTMENT와의 조인건수에만 영향을 끼친다.
3) Lateral View는 마치 스칼라 서브쿼리처럼 동작하므로 Sort Merge Join이나 Hash Jon을 사용할수 없고 Nested Loop Join만 가능하다.
또한 Driving 집합이 될 수 없다.

  • 튜닝관점에서 Lateral View활용방법
    {code:SQL
title= 예제1borderStyle=solid}
a. 직원구분코드(EMP_KND)생성
b. 모든 직원을 정규직(EMP_KND=1)과 계약직(EMP_KIND=2)으로 Update
c. 직원구분코드가 정규직인경우 정규직 테이블에 직원번호와 자택전화번호(HOME_PHON_NUMBER)를 Insert
d. 계약직인 경우 계약직 테이블에 직원번호와 계약회사 전화번호(OFFICE_PHONE_NUMBER)를 Insert해준다.
-- 직원구분코드 컬럼생성
ALTER TABLE employee
ADD (emp_kind VARCHAR2(1) DEFAULT 1 NOT NULL);
-- 직원구분코드 UPDATE
UPDATE employee
SET emp_kind = CASE WHEN MOD(EMPLOYEE_ID, 2) = 1 THEN 1 ELSE 2 END;

COMMIT ;

--정규직 테이블 생성
CREATE TABLE employee_kind1 AS
SELECT employee_id, phone_number || TO_CHAR(ROWNUM) AS home_phone_number FROM employee WHERE emp_kind = '1';

--계약직 테이블 생성
CREATE TABLE employee_kind2 AS
SELECT employee_id , phone_number || TO_CHAR(ROWNUM) AS office_phone_number FROM employee WHERE emp_kind = '2';

--PK 생성
ALTER TABLE employee_kind1
ADD CONSTRAINT pk_employee_kind1
PRIMARY KEY (employee_id) USING INDEX;

ALTER TABLE employee_kind2
ADD CONSTRAINT pk_employee_kind2
PRIMARY KEY (employee_id) USING INDEX;

exec dbms_stats.gather_table_stats(user,'EMPLOYEE_KIND1',cascade=>true);
exec dbms_stats.gather_table_stats(user,'EMPLOYEE_KIND2',cascade=>true);
exec dbms_stats.gather_table_stats(user,'EMPLOYEE',cascade=>true);

SELECT /*+ GATHER_PLAN_STATISTICS */
e.employee_id, e.first_name, e.last_name, e.emp_kind,
k1.home_phone_number, --자택 전화번호
k2.office_phone_number --계약회사 사무실 전화번호
FROM employee e, employee_kind1 k1, employee_kind2 k2
WHERE e.employee_id = k1.employee_id(+)
AND e.employee_id = k2.employee_id(+)
AND e.employee_id = 133;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1100:00:00.015
1NESTED LOOPS OUTER11100:00:00.015
2NESTED LOOPS OUTER11100:00:00.013
3TABLE ACCESS BY INDEX ROWIDEMPLOYEE11100:00:00.012
  • 4
INDEX UNIQUE SCANEMP_EMP_ID_PK11100:00:00.011
5TABLE ACCESS BY INDEX ROWIDEMPLOYEE_KIND211000:00:00.011
  • 6
INDEX UNIQUE SCANPK_EMPLOYEE_KIND211000:00:00.011-->비효율7TABLE ACCESS BY INDEX ROWIDEMPLOYEE_KIND111100:00:00.012133은 정규직 사원임에도 불구하고
  • 8
INDEX UNIQUE SCANPK_EMPLOYEE_KIND111100:00:00.011계약직 사원 테이블의 블럭을 Scan
-

Predicate Information (identified by operation id):

---

4 - access("E"."EMPLOYEE_ID"=133)
6 - access("K2"."EMPLOYEE_ID"=133)
8 - access("K1"."EMPLOYEE_ID"=133)

32 rows selected.


{code:SQL|title= 예제2 |borderStyle=solid}
SELECT /*+ GATHER_PLAN_STATISTICS ORDERED */
       e.employee_id, e.first_name, e.last_name, e.emp_kind,
       k1.home_phone_number,  --자택주소
       k2.office_phone_number --계약회사 사무실번호
  FROM employee e
       LEFT OUTER JOIN employee_kind1 k1
       ON (e.employee_id = k1.employee_id AND e.emp_kind = '1')
       LEFT OUTER JOIN employee_kind2 k2
       ON (e.employee_id = k2.employee_id AND e.emp_kind = '2')
 WHERE e.employee_id = 133;


select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  NESTED LOOPS OUTER           |                   |      1 |      1 |      1 |00:00:00.01 |       5 |
|   2 |   NESTED LOOPS OUTER          |                   |      1 |      1 |      1 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE          |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  4 |     INDEX UNIQUE SCAN         | EMP_EMP_ID_PK     |      1 |      1 |      1 |00:00:00.01 |       1 |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE_KIND1    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  6 |     INDEX UNIQUE SCAN         | PK_EMPLOYEE_KIND1 |      1 |      1 |      1 |00:00:00.01 |       1 |
|   7 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE_KIND2    |      1 |      1 |      0 |00:00:00.01 |       1 |
|*  8 |    INDEX UNIQUE SCAN          | PK_EMPLOYEE_KIND2 |      1 |      1 |      0 |00:00:00.01 |       1 |-->블록 scan이 없음...
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("E"."EMPLOYEE_ID"=133)
6 - access("K1"."EMPLOYEE_ID"=133)
filter(NVL("E"."EMP_KIND",'1')=CASE  WHEN ("K1"."EMPLOYEE_ID" IS NOT NULL) THEN '1' ELSE '1'END )
8 - access("K2"."EMPLOYEE_ID"=133)
filter(NVL("E"."EMP_KIND",'1')=CASE  WHEN ("K2"."EMPLOYEE_ID" IS NOT NULL) THEN '2' ELSE '2'END )

37 rows selected.


 ANSI SQL을 이용함으로써 선택적으로 조인할 수 있게 되었고 계약직 사원 테이블(EMPLOYEE_KIND2)
쪽의 Block Scan 량이 사라졌다. 1블록(8KB)의 차이에 불과하지만 자주 사용하는 SQL이라면
차이는 기하급수적으로 증가할 것이므로 이런 경우 Lateral View를 반드시 고려하여야 한다.
  • ANSI SOL을 사용할 수 없는 경우는 아래와 같이 조인절에 DECODE나 CASE 문을 사용하여도 같은
    효과를누렬 수있다.
    {code:SQL
title= 같은효과borderStyle=solid}
SELECT /*+ GATHER_PLAN_STATISTICS */
e.employee_id, e.first_name, e.last_name, e.emp_kind,
k1.home_phone_number, --자택 전화번호
k2.office_phone_number --계약회사 사무실 전화번호
FROM employee e, employee_kind1 k1, employee_kind2 k2
WHERE DECODE (e.emp_kind, '1', e.employee_id) = k1.employee_id(+)
AND DECODE (e.emp_kind, '2', e.employee_id) = k2.employee_id(+)
AND e.employee_id = 133;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-

IdOperationNameStartsE-RowsA-RowsA-TimeBuffers

-

0SELECT STATEMENT1100:00:00.014
1NESTED LOOPS OUTER11100:00:00.014
2NESTED LOOPS OUTER11100:00:00.014
3TABLE ACCESS BY INDEX ROWIDEMPLOYEE11100:00:00.012
  • 4
INDEX UNIQUE SCANEMP_EMP_ID_PK11100:00:00.011
5TABLE ACCESS BY INDEX ROWIDEMPLOYEE_KIND111100:00:00.012
  • 6
INDEX UNIQUE SCANPK_EMPLOYEE_KIND111100:00:00.011
7TABLE ACCESS BY INDEX ROWIDEMPLOYEE_KIND211000:00:00.010
  • 8
INDEX UNIQUE SCANPK_EMPLOYEE_KIND211000:00:00.010

-

Predicate Information (identified by operation id):

---

4 - access("E"."EMPLOYEE_ID"=133)
6 - access("K1"."EMPLOYEE_ID"=DECODE(NVL("E"."EMP_KIND",'1'),'1',"E"."EMPLOYEE_ID"))
8 - access("K2"."EMPLOYEE_ID"=DECODE(NVL("E"."EMP_KIND",'1'),'2',"E"."EMPLOYEE_ID"))

33 rows selected.


{code:none|borderStyle=solid}Lateral View는 현재 오라클이 내부적으로만 사용하고 있다
ObjectType을 하나 선언하면 스칼라 인라인뷰를 사용할 수 있다

|

{code:SQLtitle= ObjectType선언borderStyle=solid}
CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT
(
PROD_COUNT NUMBER(5),
CHANNEL_COUNT NUMBER(2),
AMOUNT_TOT NUMBER
);
/

explain plan for
SELECT s.cust_id, s.cust_year_of_birth,
s.sales_cust.prod_count,
s.sales_cust.channel_count,
s.sales_cust.amount_tot
FROM (SELECT c.cust_id, c.cust_year_of_birth,
(SELECT sales_cust_type
(COUNT (DISTINCT s.prod_id), -->위에서 선언한 TYPE사용
COUNT (DISTINCT s.channel_id),
SUM (s.amount_sold)
)
FROM sales s
WHERE s.cust_id = c.cust_id) AS sales_cust
FROM customers c
WHERE c.cust_year_of_birth= 1987) s ;

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT

-
Plan hash value: 1494525079

-

IdOperationNameRowsBytesCost (%CPU)TimePstartPstop

-

0SELECT STATEMENT15113594 (0)00:00:01
1SORT GROUP BY125
2PARTITION RANGE ALL13032506 (0)00:00:01128
3TABLE ACCESS BY LOCAL INDEX ROWIDSALES13032506 (0)00:00:01128
4BITMAP CONVERSION TO ROWIDS
  • 5
BITMAP INDEX SINGLE VALUESALES_CUST_BIX128
6TABLE ACCESS BY INDEX ROWIDCUSTOMERS15113594 (0)00:00:01
7BITMAP CONVERSION TO ROWIDS
  • 8
BITMAP INDEX SINGLE VALUECUSTOMERS_YOB_BIX

-

Predicate Information (identified by operation id):

---

5 - access("S"."CUST_ID"=:B1)
8 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


{code:none|borderStyle=solid}
Plan을 보면 스칼라 서브쿼리를 사용했을 때와 같음을 알 수 있다
여러가지의 문제로 Laleral View의 기능을 사용해야 하지만 그릴 수 없는 경우의 좋은 해결책이다.

||

  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 2.11 LV(Lateral View) View를 Scalar 서브쿼리처럼 사용하라