SQL> select 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

Execution Plan

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

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

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT              |         |     1 |    27 |     5  (40)|

|   1 |  HASH GROUP BY                |         |     1 |    27 |     5  (40)|

|   2 |   MERGE JOIN OUTER            |         |     4 |   108 |     4  (25)|

|*  3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)|

|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)|

|*  5 |    SORT JOIN                  |         |    12 |    84 |     2  (50)|

|   6 |     INDEX FULL SCAN           | EMP_X01 |    12 |    84 |     1   (0)|

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

Predicate Information (identified by operation id):

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

3 - filter("D"."LOC"='CHICAGO')

5 - access("DEPTNO"(+)="D"."DEPTNO")

filter("DEPTNO"(+)="D"."DEPTNO")

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

0  recursive calls

0  db block gets

3  consistent gets

0  physical reads

0  redo size

684  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select 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

Execution Plan

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

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

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |         |     1 |    20 |     3   (0)|

|   1 |  SORT AGGREGATE   |         |     1 |     7 |            |

|*  2 |   INDEX RANGE SCAN| EMP_X01 |     4 |    28 |     1   (0)|

|   3 |  SORT AGGREGATE   |         |     1 |     7 |            |

|*  4 |   INDEX RANGE SCAN| EMP_X01 |     4 |    28 |     1   (0)|

|   5 |  SORT AGGREGATE   |         |     1 |     7 |            |

|*  6 |   INDEX RANGE SCAN| EMP_X01 |     4 |    28 |     1   (0)|

|*  7 |  TABLE ACCESS FULL| DEPT    |     1 |    20 |     3   (0)|

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

Predicate Information (identified by operation id):

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

2 - access("DEPTNO"=:B1)

4 - access("DEPTNO"=:B1)

6 - access("DEPTNO"=:B1)

7 - filter("D"."LOC"='CHICAGO')

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

1  recursive calls

0  db block gets

11  consistent gets

0  physical reads

0  redo size

684  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select deptno, dname

2       , to_number(substr(sal, 1, 7)) avg_sal

3       , to_number(substr(sal, 8, 7)) min_sal

4       , to_number(substr(sal, 15))   max_sal

5  from (

6    select d.deptno, d.dname

7         ,(select lpad(avg(sal), 7) || lpad(min(sal), 7) || max(sal)

8           from emp where deptno = d.deptno) sal

9    from   dept d

10    where  d.loc = 'CHICAGO'

11  );

DEPTNO DNAME             AVG_SAL    MIN_SAL    MAX_SAL

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

30 SALES             1566.66        950       2850

Execution Plan

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

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT   |      |     1 |    58 |     3   (0)|

|   1 |  VIEW              |      |     1 |    58 |     3   (0)|

|*  2 |   TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)|

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

Predicate Information (identified by operation id):

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

2 - filter("D"."LOC"='CHICAGO')

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

0  recursive calls

0  db block gets

9  consistent gets

0  physical reads

0  redo size

667  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL> create or replace type sal_type as object

2  ( avg_sal number, min_sal number, max_sal number )

3  /

유형이 생성되었습니다.

SQL>

SQL> select deptno, dname

2       , a.sal.avg_sal, a.sal.min_sal, a.sal.max_sal

3  from (

4    select d.deptno, d.dname

5         ,(select sal_type( avg(sal), min(sal), max(sal) )

6           from emp where deptno = d.deptno) sal

7    from   dept d

8    where  d.loc = 'CHICAGO'

9  ) a

10  /

DEPTNO DNAME          SAL.AVG_SAL SAL.MIN_SAL SAL.MAX_SAL

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

30 SALES           1566.66667         950        2850

Execution Plan

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

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

| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  |         |     1 |    20 |     3   (0)|

|   1 |  SORT AGGREGATE   |         |     1 |     7 |            |

|*  2 |   INDEX RANGE SCAN| EMP_X01 |     4 |    28 |     1   (0)|

|   3 |  SORT AGGREGATE   |         |     1 |     7 |            |

|*  4 |   INDEX RANGE SCAN| EMP_X01 |     4 |    28 |     1   (0)|

|   5 |  SORT AGGREGATE   |         |     1 |     7 |            |

|*  6 |   INDEX RANGE SCAN| EMP_X01 |     4 |    28 |     1   (0)|

|*  7 |  TABLE ACCESS FULL| DEPT    |     1 |    20 |     3   (0)|

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

Predicate Information (identified by operation id):

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

2 - access("DEPTNO"=:B1)

4 - access("DEPTNO"=:B1)

6 - access("DEPTNO"=:B1)

7 - filter("D"."LOC"='CHICAGO')

Note

-----

- 'PLAN_TABLE' is old version

Statistics

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

0  recursive calls

0  db block gets

9  consistent gets

0  physical reads

0  redo size

696  bytes sent via SQL*Net to client

385  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>