'CHICAGO'인 부서만 대상으로 급여 수준을 집계하려는 것인데, 사원 테이블 전체를 다 읽어야 하는 비효율 발생이라는데...(어라 변형이..._) |
SQL> select /*+gather_plan_statistics*/d.deptno, d.dname, avg_sal, min_sal, max_sal
2 from dept d
3 ,(select deptno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
4 from emp group by deptno) e
5 where e.deptno(+) = d.deptno
6 and d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
---------- -------------- ---------- ---------- ----------
30 SALES 1566.66667 950 2850
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Us
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 2 | MERGE JOIN OUTER | | 1 | 5 | 6 |00:00:00.01 | 3 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 4 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
|* 5 | SORT JOIN | | 1 | 14 | 6 |00:00:00.01 | 1 | 2048 | 2048 | 2048 (0)|
| 6 | INDEX FULL SCAN | EMP_X01 | 1 | 14 | 14 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
3 - filter("D"."LOC"='CHICAGO')
5 - access("DEPTNO"="D"."DEPTNO")
filter("DEPTNO"="D"."DEPTNO")
|
(잠시 딴애기)위 쿼리가 변형 일어나 아래처럼 됨.. 비효율 발생이 없는듯.. 근데 왜 머지로 풀리지.?? 할것없다구했는데.. ㅠ |
SQL> SELECT /*+gather_plan_statistics */D.deptno, D.DNAME, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
2 FROM EMP E
3 , DEPT D
4 WHERE E.DEPTNO(+) = D.DEPTNO
5 AND D.LOC = 'CHICAGO'
6 GROUP BY D.deptno, D.DNAME;
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
---------- -------------- ---------- ---------- ----------
30 SALES 1566.66667 950 2850
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Us
----------------------------------------------------------------------------------------------------
| 1 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 2 | MERGE JOIN OUTER | | 1 | 5 | 6 |00:00:00.01 | 3 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 4 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
|* 5 | SORT JOIN | | 1 | 14 | 6 |00:00:00.01 | 1 | 2048 | 2048 | 2048 (0)|
| 6 | INDEX FULL SCAN | EMP_X01 | 1 | 14 | 14 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("D"."LOC"='CHICAGO')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
5 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
|
|
(잠시 딴애기)NO_MERGE 1 |
SQL> select /*+gather_plan_statistics LEADING(E) INDEX(E PK_EMP) USE_HASH(E) NO_MERGE(E)*/
2 d.deptno, d.dname, avg_sal, min_sal, max_sal
3 from dept d
4 ,(select deptno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
5 from emp group by deptno) e
6 where e.deptno(+) = d.deptno
7 and d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
---------- -------------- ---------- ---------- ----------
30 SALES 1566.66667 950 2850
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | U
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 1 | 1 |00:00:00.01 | 9 | 921K| 921K| 291K (0)|
|* 2 | TABLE ACCESS FULL | DEPT | 1 | 1 | 1 |00:00:00.01 | 7 | | | |
| 3 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 2 | | | |
| 4 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 14 | 14 |00:00:00.01 | 2 | | |
| 6 | INDEX FULL SCAN | PK_EMP | 1 | 14 | 14 |00:00:00.01 | 1 | | |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("D"."LOC"='CHICAGO')
|
|
(잠시 딴애기)NO_MERGE 2 |
SQL> select /*+gather_plan_statistics no_merge(e)*/d.deptno, d.dname, avg_sal, min_sal, max_sal
2 from dept d
3 ,(select deptno, avg(sal) avg_sal, min(sal) min_sal, max(sal) max_sal
4 from emp group by deptno) e
5 where e.deptno(+) = d.deptno
6 and d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
---------- -------------- ---------- ---------- ----------
30 SALES 1566.66667 950 2850
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 1 | MERGE JOIN OUTER | | 1 | 1 | 1 |00:00:00.08 | 11 | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.04 | 4 | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.03 | 2 | | | |
|* 4 | SORT JOIN | | 1 | 3 | 1 |00:00:00.04 | 7 | 2048 | 2048 | 20
| 5 | VIEW | | 1 | 3 | 3 |00:00:00.04 | 7 | | | |
| 6 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.04 | 7 | | | |
| 7 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.02 | 7 | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
2 - filter("D"."LOC"='CHICAGO')
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
|
|
스칼라 서브 쿼리는 한 레코드당 하나의 값만 리턴함(아래 쿼리 불가능(X)) |
select d.deptno, d.dname
,(select avg(sal), min(sal), max(sal) from emp where deptno = d.deptno)
from dept d
where d.loc = 'CHICAGO';
|
|
반복적 수행 (비효율) |
SQL> select /*+gather_plan_statistics*/d.deptno, d.dname
2 ,(select avg(sal) from emp where deptno = d.deptno) avg_sal
3 ,(select min(sal) from emp where deptno = d.deptno) min_sal
4 ,(select max(sal) from emp where deptno = d.deptno) max_sal
5 from dept d
6 where d.loc = 'CHICAGO';
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
---------- -------------- ---------- ---------- ----------
30 SALES 1566.66667 950 2850
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN| EMP_X01 | 1 | 5 | 6 |00:00:00.01 | 1 |
| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 4 | INDEX RANGE SCAN| EMP_X01 | 1 | 5 | 6 |00:00:00.01 | 1 |
| 5 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 6 | INDEX RANGE SCAN| EMP_X01 | 1 | 5 | 6 |00:00:00.01 | 1 |
|* 7 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 8 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("DEPTNO"=:B1)
4 - access("DEPTNO"=:B1)
6 - access("DEPTNO"=:B1)
7 - filter("D"."LOC"='CHICAGO')
|
|
substr함수 사용(오~ XPLAN 두 되는군요 =_=) |
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
2 DEPTNO
3 , DNAME
4 , TO_NUMBER(SUBSTR(SAL, 1, 7)) AVG_SAL
5 , TO_NUMBER(SUBSTR(SAL, 8, 7)) MIN_SAL
6 , TO_NUMBER(SUBSTR(SAL, 15)) MAX_SAL
7 FROM (SELECT D.DEPTNO, D.DNAME
8 , (SELECT LPAD(AVG(SAL), 7)||LPAD(MIN(SAL),7)||MAX(SAL)
9 FROM EMP
10 WHERE DEPTNO = D.DEPTNO) SAL
11 FROM DEPT D
12 WHERE D.LOC = 'CHICAGO'
13 )
14 ;
DEPTNO DNAME AVG_SAL MIN_SAL MAX_SAL
---------- -------------- ---------- ---------- ----------
30 SALES 1566.66 950 2850
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN| EMP_X01 | 1 | 5 | 6 |00:00:00.01 | 1 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 8 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
3 - filter("D"."LOC"='CHICAGO')
|
|
오브젝트 TYPE을 사용하는 방식 |
SQL> CREATE OR REPLACE TYPE SAL_TYPE AS OBJECT
2 (AVG_SAL NUMBER, MIN_SAL NUMBER, MAX_SAL NUMBER)
3 /
유형이 생성되었습니다.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */DEPTNO, DNAME
2 , A.SAL.AVG_SAL, A.SAL.MIN_SAL, A.SAL.MAX_SAL
3 FROM (SELECT D.DEPTNO, D.DNAME
4 , (SELECT SAL_TYPE(AVG(SAL), MIN(SAL), MAX(SAL) )
5 FROM EMP WHERE DEPTNO = D.DEPTNO) SAL
6 FROM DEPT D WHERE D.LOC = 'CHICAGO')A
7 ;
DEPTNO DNAME SAL.AVG_SAL SAL.MIN_SAL SAL.MAX_SAL
---------- -------------- ----------- ----------- -----------
30 SALES 1566.66667 950 2850
SQL> select * from table( dbms_xplan.display_cursor( null, null, 'ALLSTATS LAST'));
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN| EMP_X01 | 1 | 5 | 6 |00:00:00.01 | 1 |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 8 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=:B1)
3 - filter("D"."LOC"='CHICAGO')
|
|